如何使用odbc连接到vb.net的as400

Question

我试图通过vb.net应用程序连接到使用ODBC驱动程序的AS400服务器，但问题是我试图填充数据集，每当我想显示数据时，我找不到任何东西

这是我的代码:

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
```

Answer 1

我不知道您使用的是哪个提供商。请检查https://www.connectionstrings.com/as-400/以检查您的连接字符串。我没有看到与您的字符串的语法匹配的连接字符串。

我不知道AS400中的选择字符串，所以我只是使用了一个标准的Sql字符串。把你的try/catch放到UI代码中，这样你就可以显示一个带有错误的消息框。

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

在你的函数中，你返回一个DataTable，所以为什么你要混淆DataSet?只要装上DataTable。Connections、Commands、DataReaders均需处理。Using...End Using阻塞处理关闭和处理，即使有错误。

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

Answer 2

这个c#代码为我工作。

我正在使用IBM访问客户端解决方案64位ODBC驱动程序。

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 ;
}
}
}