VB.NET and AS/400 connection and bind to datagrid?

How do I set up ODBC for the AS400 (DB2) and bind to a datagrid?
bfth1505Asked:
Who is Participating?
 
donhenderson1961Connect With a Mentor Commented:
Alter this to suit your needs:

Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click
        Dim strConnection As String
        Dim strSQL As String
        btnReport.Enabled = False
        'Replace the connection string
        strConnection = "Driver={Client Access ODBC Driver (32-bit)};System=<ip address>;UID=ODBCUSER;PWD=<pwd>;"
        'strConnection = "Driver={Client Access ODBC Driver (32-bit)};System=<ip address>;UID=ODBCUSER;PWD=<pwd>;"
        Dim objConnection As New Odbc.OdbcConnection(strConnection)
        Dim objCommand As Odbc.OdbcCommand
        Me.Cursor = Cursors.WaitCursor
        Try
            If ValidateWindow() Then
                Dim strFromDate As String = Mid(dtpFromDate.Text, 7, 2) & Mid(dtpFromDate.Text, 9, 2) & Mid(dtpFromDate.Text, 1, 2) & Mid(dtpFromDate.Text, 4, 2)
                Dim strToDate As String = Mid(dtpToDate.Text, 7, 2) & Mid(dtpToDate.Text, 9, 2) & Mid(dtpToDate.Text, 1, 2) & Mid(dtpToDate.Text, 4, 2)
                Dim intFromDate As Integer
                Dim intToDate As Integer
                intFromDate = strFromDate
                intToDate = strToDate


                If txtFromClient.Text <> "" Then
                    strSQL = "Select pcomp, cltgrp, pclnt#, glast, gfirst, pdate, ptype, payamt, pbalaf, gdebt#, pcomm, pguar#, cltgrp, gcomp, ccomp, cclnt" & _
                             " from data.scpaymi, data.scacct, data.scclnt" & _
                                 " Where pcomp = gcomp and pguar# = gdebt# and pcomp = ccomp and pclnt# = cclnt" & _
                                        " And pcomp = " & txtDivision.Text & _
                                        " And cclnt >= " & txtFromClient.Text & " and cclnt <= " & txtToClient.Text & _
                                        " And pdate >= " & intFromDate & " And pdate <= " & intToDate & _
                                        " order by pcomp, pclnt#, glast, gfirst"
                Else
                    strSQL = "Select pcomp, cltgrp, pclnt#, glast, gfirst, pdate, ptype, payamt, pbalaf, gdebt#, pcomm, pguar#, cltgrp, gcomp, ccomp, cclnt" & _
                                    " from cdata.scpaymi, data.scacct, data.scclnt" & _
                                    " Where pcomp = gcomp and pguar# = gdebt# and pcomp = ccomp and pclnt# = cclnt and " & _
                                    "pcomp = " & txtDivision.Text & _
                                    " and cltgrp = " & txtClientGroup.Text & _
                                    " And pdate >= " & intFromDate & _
                                    " and pdate <= " & intToDate & _
                                    " order by pcomp, cltgrp, pclnt#, glast, gfirst"
                End If

                lblError.Text = "Attempting connection"

                objCommand = New Odbc.OdbcCommand(strSQL)
                objCommand.Connection = objConnection

                Dim dareport As New Odbc.OdbcDataAdapter(objCommand)
                Dim dsReport As New DataSet
                dareport.Fill(dsReport)

                Dim dvLocalReport As DataView = dsReport.Tables(0).DefaultView
                dvLocalReport = dsReport.Tables(0).DefaultView
                dgPaymentRpt.DataSource = dvLocalReport
                dgPaymentRpt.SetDataBinding(dvLocalReport, "")

                Me.ReportDataView = dvLocalReport

                lblError.Text = lblError.Text & vbCrLf & "Retrieve complete, no error."
            End If
            If Me.ReportDataView.Count = 0 Then
                lblNoRecords.Visible = True
                btnReport.Enabled = False
            Else
                btnReport.Enabled = True
                lblNoRecords.Visible = False
            End If

        Catch ex As Odbc.OdbcException
            btnReport.Enabled = False
            lblValidationError.Text = "Is the VPN connected?" & vbCrLf & "Is the ISeries setup?"
            lblError.Text = "btnRetrieve_Click: ODBC ERROR" & vbCrLf & "ex.Source: " & ex.Source & vbCrLf & "ex.Message: " & ex.Message & vbCrLf & " Stack Trace: " & ex.StackTrace
        Catch ex As Exception
            btnReport.Enabled = False
            lblValidationError.Text = "Is the VPN connected?" & vbCrLf & "Is the ISeries setup?"
            lblError.Text = "btnRetrieve_Click: Exception ERROR" & vbCrLf & "ex.Source: " & ex.Source & vbCrLf & "ex.Message: " & ex.Message & vbCrLf & " Stack Trace: " & ex.StackTrace
        Finally
            Me.Cursor = Cursors.Arrow
            objCommand = Nothing
            If objConnection.State = ConnectionState.Open Then
                objConnection.Close()
            End If
            objConnection = Nothing
        End Try
    End Sub
0
 
bfth1505Author Commented:
Wow, what a great answer.  I put it into my application and altered a few lines and it worked perfectly.  Thanks Don, that is a very professional answer!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.