Solved

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

Posted on 2006-06-12
3
521 Views
Last Modified: 2008-02-01
How do I set up ODBC for the AS400 (DB2) and bind to a datagrid?
0
Comment
Question by:bfth1505
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 10

Expert Comment

by:vadim63
ID: 16888531
0
 
LVL 1

Accepted Solution

by:
donhenderson1961 earned 500 total points
ID: 16894389
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
 

Author Comment

by:bfth1505
ID: 16894414
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

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Visual studio 2015 1 43
VB.net Adding a row to a DataGridView that has a checkbox column 5 57
Help with Report Viewer in VS2013 6 28
Convert a string to date 4 39
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question