How to connect using odbc to as400 for vb.net

Question

I'm trying to connect to a AS400 server using ODBC driver via vb.net application but the problem is that I am trying to fill a dataset and whenever I want to display the data I don't find any thing

This is my code:

    Dim cn As OdbcConnection
    Dim cm As OdbcCommand
    Dim dm As OdbcDataAdapter


    Sub ConnServer()
        Try
            cn = New OdbcConnection("DSN=AS400_CA;UID=root;PWD=*****;")
            cn.Open()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Ecriture Comptable")
        End Try

    End Sub

    Public Function GetData(query As String) As DataTable
        Try

            cn = New OdbcConnection("DSN=AS400_CA;UID=root;PWD=*****;")
            Dim cmd As OdbcCommand = New OdbcCommand(query, cn)

            cn.Open()

            Dim ds = New DataSet()
            cmd.Connection = cn
            dm.SelectCommand = cmd
            dm.Fill(ds, "table")
            Dim data = ds.Tables("table")
            cn.Close()
            Return data
        Catch ex As Exception
            con.Close()
            Return New DataTable()
        End Try
    End Function
```

Did you check if the GetData ends in an exception? This code cannot tell you if there is an error or if the query produces an empty table — Steve, Aug 09 '21 at 12:25
The following may be helpful: https://stackoverflow.com/questions/3336646/connect-to-as400-using-net — Tu deschizi eu inchid, Aug 09 '21 at 12:42
Have you at least put a breakpoint in the Catch block of GetData? Also in your catch, you've got con.Close(). Where does con come from, think it's cn everywhere else. Your code might also benefit from a "Using" statement which will help you manage the connections — Hursey, Aug 09 '21 at 21:02

score 1 · Answer 1 · answered Aug 11 '21 at 00:23

I do not know what provider you are using. Check https://www.connectionstrings.com/as-400/ to check your connection string. I do not see a connection string that matches the syntax of your string.

I have no idea of select strings in AS400 so I just used a standard Sql string. Put your try/catch in the UI code so you can show a message box with the error.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt As DataTable
    Try
        dt = GetData("Select * From SomeTable;")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    DataGridView1.DataSource = dt
End Sub

In your function you are returning a DataTable so why are you messing with a DataSet? Just load the DataTable. Connections, Commands, and DataReaders all need to be disposed. Using...End Using blocks handle closing and disposing even if there is an error.

Public Function GetData(query As String) As DataTable
    Dim dt As New DataTable
    Using cn = New OdbcConnection("DSN=AS400_CA;UID=root;PWD=*****;"),
        cmd As OdbcCommand = New OdbcCommand(query, cn)
        cn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

score 0 · Answer 2 · answered Aug 10 '21 at 05:09

this C# code worked for me.

I am using the IBM access client solutions 64bit ODBC driver.

using System;
using System.Data.Odbc;
using System.Data;

namespace IntroUI
{
class Program
{
  static void Main(string[] args)
  {

// make sure using 64 bit IBM i Access ODBC driver
  var conn = OpenConnection("dsn", "user", "pass");

  OdbcCommand cmd = conn.CreateCommand();
  var query = "select  a.* from qrpglesrc a";
  var table = GetData( conn, query ) ;

  var numRows = table.Rows.Count ;
  for( var ix = 0 ; ix < numRows ; ++ix )
  {
    var row = table.Rows[ix] ;
    var srcdta = row.ItemArray[2] ;
    Console.WriteLine( srcdta ) ;
    if ( ix > 20 )
      break ;
  }

  conn.Close( ) ;

  }

  // InDsn is the dsn from the odbc administration window
  static OdbcConnection OpenConnection( string InDsn, string InUser, string InPwd )
    {
      string connString = "DSN=" + InDsn + "; UID=" + InUser +
        "; PWD=" + InPwd + ";" ;
      OdbcConnection conn = new OdbcConnection( connString ) ;
      conn.Open( ) ;
      return conn ;
    }

    static DataTable GetData( OdbcConnection conn, string query )
    {
      OdbcDataAdapter dm = new OdbcDataAdapter() ;
      OdbcCommand cmd = new OdbcCommand(query, conn);
      DataSet ds = new DataSet( ) ;
      dm.SelectCommand = cmd ;
      dm.Fill( ds, "table") ;
      var data = ds.Tables["table"] ;
      return data ;
    }

  }
}

Many database objects including Connections and Commands need to be disposed. It is nor practical to declare a connection outside of the method where it is used. — Mary, Aug 11 '21 at 00:08

How to connect using odbc to as400 for vb.net

2 Answers2