Oh, I see what you are doing. Use this class to get your DataTable using the Query method.
Imports IBM.Data.DB2.iSeries
Imports System.Data
Public Class DB2ADO
Private _cn_connString As String = ""
Private _cn As iDB2Connection
Private _cn_iSeriesFQDN As String = "myas400.domain.com"
Private _cn_initialCatalog As String = "*SYSBAS"
Private _cn_connTimeout As String = "20"
Private _cn_user As String = ""
Private _cn_pass As String = ""
Private _cn_SSL As String = "FALSE"
Private _cn_Naming As String = "SQL" ' "System"
Private _cn_LibraryList = "*USRLIBL"
Private _cn_DataCompression = "TRUE"
Private _cn_HexParser = "Character" ' Not needed
Private _ConvertByte As Boolean = False
'Private _cn_defaultCollection As String = "AMFLIB"
' Trace Facility
Private _trace_bin As String = "C:\program files\ibm\Client Access\cwbmptrc.exe"
Private _trace_out As String = "C:\program files\ibm\Client Access\idb2trace.txt"
Public Sub New(ByVal iSeriesFQDN As String, ByVal user As String, ByVal pass As String, Optional ByVal ConvertByte As Boolean = True)
_cn_iSeriesFQDN = iSeriesFQDN
_cn_user = user
_cn_pass = pass
_ConvertByte = ConvertByte
_cn_connString = "DataSource=" & _cn_iSeriesFQDN & _
";ConnectionTimeout=" & _cn_connTimeout & _
";Database=" & _cn_initialCatalog & _
";HexParserOption=" & _cn_HexParser & _
";UserID=" & _cn_user & _
";Password=" & _cn_pass & _
";SSL=" & _cn_SSL & _
";DataCompression=" & _cn_DataCompression & _
";Naming=" & _cn_Naming & _
";LibraryList=" & _cn_LibraryList
_cn_pass = ""
End Sub
Public Function Connect() As Boolean
_cn = New iDB2Connection
_cn.ConnectionString = _cn_connString
Dim failFlag As Boolean = True
Try
_cn.Open()
Console.WriteLine("Server Version: " & _cn.ServerVersion)
Console.WriteLine("Default Collection: " & _cn.DefaultCollection)
Console.WriteLine("Job: " & _cn.JobName)
Catch ex As Exception
failFlag = False
End Try
Return failFlag
End Function
Public Sub Disconnect()
_cn.Close()
End Sub
Public Function Query(ByVal SelectCommand As String) As DataTable
If _cn.State = ConnectionState.Open Then
If _ConvertByte Then
iDB2ProviderSettings.CharBitDataAsString = True iDB2ProviderSettings.CharBitDataCcsid = 37
Else
iDB2ProviderSettings.CharBitDataAsString = False
End If
Dim cmd As New iDB2Command(SelectCommand, _cn)
Dim da As New iDB2DataAdapter(cmd)
Dim ds As New DataSet
Try
da.Fill(ds, "table0")
If ds.Tables("table0").Rows.Count = 0 Then
Return New DataTable
ElseIf ds.Tables("table0").Rows.Count > 0 Then
Return ds.Tables("table0")
End If
Catch ex As Exception
Console.WriteLine(ex.Message)
Return New DataTable
End Try
Else
Return New DataTable
End If
End Function
End Class
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Tuesday, February 16, 2010 8:55 AM
To: '.net use with the System i'
Subject: Re: [SystemiDotNet] Gridview and iSeries question...
Hello,
I tried your suggestion and I must be doing something wrong because the page is failing at the connection string with the following error:
The connection property is invalid.
I did notice that you have the connection parameters in the gridview itself. I am setting all the connection properties in code as you can see in my code here:
Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
Dim reportSelected As Integer = rblReportSelection.SelectedIndex
Select Case reportSelected
Case 0 'Trouble Calls with no Drop Bury
generateReport("WONODROP", rblReportSelection.SelectedValue)
Case 1 'JZ Work orders with no scheduled Drop Bury
generateReport("WOJZNODROP", rblReportSelection.SelectedValue)
End Select
End Sub
Private Sub generateReport(ByVal tableName As String, ByVal gridCaption As String)
Dim connection As New iDB2Connection()
connection.ConnectionString = "Data Source = PRVPAS2C; CharBitDataAsString=True;CharBitDataCcsid=1"
Try
connection.Open()
Catch ex As iDB2CommErrorException
Label2.Text = "Connection to iSeries failed!!!"
Label2.BackColor = Drawing.Color.Red
Exit Sub
Catch ex As iDB2ConnectionFailedException
Label2.Text = "Connection to iSeries failed!!!"
Label2.BackColor = Drawing.Color.Red
Exit Sub
End Try
Label2.Text = "Connection to iSeries was a success!!!"
Label2.BackColor = Drawing.Color.White
'populate the datagrid with house data
Dim cmd As iDB2Command = connection.CreateCommand()
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = "select * from COHAMIRA." & tableName
Dim rdr As iDB2DataReader = cmd.ExecuteReader()
Dim woNoDrops As New DataTable()
woNoDrops.Load(rdr, LoadOption.Upsert)
connection.Close()
'Add the column headers
woNoDrops.Columns(0).ColumnName = "Site ID"
woNoDrops.Columns(1).ColumnName = "Work Order Number"
woNoDrops.Columns(2).ColumnName = "Account Number"
woNoDrops.Columns(3).ColumnName = "Address Line 1"
woNoDrops.Columns(4).ColumnName = "Address Line 2"
woNoDrops.Columns(5).ColumnName = "Address Line 3"
woNoDrops.Columns(6).ColumnName = "Address Line 4"
woNoDrops.Columns(7).ColumnName = "Pool"
woNoDrops.Columns(8).ColumnName = "Tech Number"
woNoDrops.Columns(9).ColumnName = "Tech Name"
woNoDrops.Columns(10).ColumnName = "Work Order Status"
woNoDrops.Columns(11).ColumnName = "Install Completion Date"
GridView1.DataSource = woNoDrops
GridView1.DataBind()
GridView1.Caption = gridCaption
GridView1.CaptionAlign = TableCaptionAlign.Left
End Sub
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
cell: 614 365 0489
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Monday, February 15, 2010 4:45 PM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Gridview and iSeries question...
Hmm. I got mine working ok. Can you post some code?
1. Inside VS I set up a Data Connection to iSeries using the "IBM DB2
for i5/OS" Data Source/provider.
2. Set the "CharBitDataAsString" to true, set the "CharBitDataCCSID" to
1.
3.
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ORDNO"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="ORDNO" HeaderText="ORDNO"
ReadOnly="True"
SortExpression="ORDNO" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="DataSource=myAS400;UserID=chris;DefaultCollection=amfl
ib;LibraryList=amflib;CharBitDataAsString=True;CharBitDataCcsid=1"
ProviderName="IBM.Data.DB2.iSeries" SelectCommand="SELECT ORDNO FROM
MOMAST">
</asp:SqlDataSource>
Ref:
http://www.itjungle.com/fhg/fhg071509-story01.html
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx
[mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Monday, February 15, 2010 12:41 PM
To: 'systemidotnet@xxxxxxxxxxxx'
Subject: [SystemiDotNet] Gridview and iSeries question...
Hello,
I'm working on a simple web app where the user selects a choice by
clicking on a radio button. Depending on the selection, I fill a
gridview. For some reason, the gridview does not behave as expected
(paging doesn't work, sorting doesn't work). I am using the .net Data
Provider. However, when I use an ODBC driver to connect to the iSeries,
the gridview works perfectly. Has anyone run into this problem? If so,
how did you resolve it?
Thanks for any help that you can provide.
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
cell: 614 365 0489
This E-mail and any of its attachments may contain Time Warner
Cable proprietary information, which is privileged, confidential,
or subject to copyright belonging to Time Warner Cable. This E-mail
is intended solely for the use of the individual or entity to which
it is addressed. If you are not the intended recipient of this
E-mail, you are hereby notified that any dissemination,
distribution, copying, or action taken in relation to the contents
of and attachments to this E-mail is strictly prohibited and may be
unlawful. If you have received this E-mail in error, please notify
the sender immediately and permanently delete the original and any
copy of this E-mail and any printout.
midrange.com
