How to create a ODBC connection to a Table

HaroldVanAarsen

We are using the Native database of Navison 4 SP1
I am trying to create a connection to the user table in the database by using the N/ODBC connection with VB.Net 2003.

I can create the connection, when i tell VB.net to show me the records -> No problem, i can see them all.
but then i try to select the records of the table and fill it into a dataset.
This doesn't work.

[Code]

Dim MyConn As New OdbcConnection
Dim MyDataAdapter As New OdbcDataAdapter
Dim MyDataset As New Dataset1

MyConn.ConnectionString = "DSN=Navision MBS40Live;UID=harold;PWD=hva1910"
Try
MyConn.Open()
Me.StatusBarPanel1.Text = "Connection open"

Catch ex As Exception
MessageBox.Show("Failed to connect to data source" & vbCrLf & ex.ToString)
Me.StatusBarPanel1.Text = "Connection failed"

End Try

Try
MyDataAdapter.SelectCommand = New OdbcCommand("SELECT * FROM User WHERE Name <> ''", MyConn)
Me.StatusBarPanel1.Text = "Table Selected"

Catch ex As Exception
MessageBox.Show("Failed to Select Table" & vbCrLf & ex.ToString)
Me.StatusBarPanel1.Text = "Failed to select Table"
Exit Sub
End Try

Try
MyDataAdapter.Fill(MyDataset.UsersTable)
Me.StatusBarPanel1.Text = "Dataset filled"

Catch ex As Exception
MessageBox.Show("Failed to Fill Dataset" & vbCrLf & ex.ToString)
Me.StatusBarPanel1.Text = "Failed to Fill Dataset"
Exit Sub
End Try

Try
MyConn.Close()
Me.StatusBarPanel1.Text = "Connection close"

Catch ex As Exception
MessageBox.Show("Failed to close the data source" & vbCrLf & ex.ToString)
Me.StatusBarPanel1.Text = "Connection ???"
Exit Sub

End Try

Try
Me.DataGrid1.DataSource = MyDataset.UsersTable

Catch ex As Exception
MessageBox.Show("Failed set Datagrid Datasource" & vbCrLf & ex.ToString)
Me.StatusBarPanel1.Text = "Failed set Datagrid Datasource"
Exit Sub

End Try


[\Code]

[Error message]

Failed to Fill Dataset
System.Data.Odbc.OdbcException: ERROR [42000] [Simba][SimbaEngine ODBC Driver]SELECT * FROM User<< ??? >> WHERE Name <> ''
ERROR [42000] [Simba][SimbaEngine ODBC Driver]Syntax Error.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at NavisionConnection.Form1.ConnectToOdbc() in C:\Documents and Settings\H.vanAarsen\Mijn documenten\Visual Studio Projects\OnderZoek\NavisionConnection\Form1.vb:line 130

[\Error message]


Can anybody tell me what is wrong
Or perhaps send me some code that does work ?

With kind regards,

charlp

Harold,

My VB is a bit rusty, but shouldn't this line
MyDataAdapter.Fill(MyDataset.UsersTable)
read
MyDataAdapter.Fill(MyDataset("UsersTable"))

Charl

HaroldVanAarsen

charlp wrote:

Harold,

My VB is a bit rusty, but shouldn't this line
MyDataAdapter.Fill(MyDataset.UsersTable)
read
MyDataAdapter.Fill(MyDataset("UsersTable"))

Charl

Almost right, you can write it the I do or:
MyDataAdapter.Fill(MyDataset, "UsersTable")

Both work the same way.

ara3n

is this line a typo?
Dim MyDataset As New Dataset1

or should it be
Dim MyDataset As New Dataset

HaroldVanAarsen

ara3n wrote:

is this line a typo?
Dim MyDataset As New Dataset1

or should it be
Dim MyDataset As New Dataset

I use a predifined dataset and just for the ease of it called it DataSet1

Droezel

I'm having the same problem with the user table, any chance you found a solution HaroldVanAarsen?

PoweRoy

the driver doesnt onderstand datasets

solution in C# .net:

string SQLstring = "SELECT * FROM ProductioN BOM line
OdbcCommand myCommand = new OdbcCommand(SQLstring ,myConn);
Console.WriteLine("Execute Query " + DateTime.Now);
//execute sql-query
OdbcDataReader myReader = myCommand.ExecuteReader();
Console.WriteLine("Query executed" + DateTime.Now);
				
//table
Console.WriteLine("showing data " + DateTime.Now);
DataTable dTable = new DataTable("List Records");
//columns
for (int fCount = 0; fCount < myReader.FieldCount; fCount++)
{
	dTable.Columns.Add(new DataColumn(myReader.GetName(fCount)));
}
//data in table
DataRow dRow;
int i = 0; 
while (myReader.Read()) 
{ 
	dRow = dTable.NewRow();
	for (int k = 0; k < myReader.FieldCount; k++)
	{
		dRow[k] = myReader.GetValue(k);
	}
	dTable.Rows.Add(dRow);
	i++; 					
}
//showing data
dataGrid1.SetDataBinding(dTable,null);
Console.WriteLine("showing data done " + DateTime.Now);
Console.WriteLine(dTable.Rows.Count + " records fetched");
//close connection
myConn.Close();

now goodluck going from c# -> VB

Droezel

I do not use datasets, like you i use a datareader and dynamically create my queries to read data from the native database, and write all data to an sql server 2005 db.

So far this is working extremely wel for many tables like sales header, sales line, item, customer, ...

But trying to access table 2000000002 User gives the error shown in the start post.

My guess is that the Navision ODBC driver does not like the expression "User"