Problem with DataAdapter.Fill(Dataset)

[Stanav]

Hello all,
I'm developing a web application using VB.Net 2003 and Framework 1.1. This
application queries an AS/400 database. I'm using the IBM OleDb provider
that came with IBM Client Access for Windows (V5R3). Everything works fine
on my development PC, but when I move the application to a Windows Server
2003, it crashes when trying to fill a dataset. I've double-checked that the
Win 2k3 server does have Client Access installed, that it has .NET Framework
1.1, and running IIS. The only difference between my development PC and the
2k3 Server is that my PC is running WinXP Pro (thus it runs IIS v.5.1) while
the server is running IIS v.6.0.
Here is my code:

Public Sub LoadDataSet(ByVal source As String, ByVal Collection As String, _
ByVal table As String, ByVal field As String, ByVal keyValue As
String)

'Connection string to as400 database
conStr400 = "Provider=IBMDA400;Data Source=" & source & _
";User ID=myID;Password=myPwd;Default Connection=" & _

Collection
'Create connection object
conn400 = New OleDb.OleDbConnection(conStr400)

'Building SQL select string
Dim selStr As String = "SELECT " & selectedFields & " FROM " & _
table & " WHERE " & field & " = " & keyValue & _
" ORDER BY DSNUMB"

'Create data adapter
da = New OleDb.OleDbDataAdapter(selStr, conn400)

Dim count As Integer = 0

'Open connection to database.
'The Response.Write statements are for debugging purpose only

Response.Write("Conn State before open: " & conn400.State & "<br>")
'shows "0"

conn400.Open()

Response.Write("Conn State after open: " & conn400.State & "<br>")
'>>> shows "1": connected

'Try to load dataset
Try
ds.Clear()

Response.Write("Before Fill: " & ds.Tables.Count & "<br>")
'>>> shows "Before Fill: 0" : ds was created, just doesn't have any data,
yet.

count = da.Fill(ds) <<<<<<<< Null Exception occurs here

Response.Write("After Fill: " & ds.Tables.Count & _
"<br>Rec count = " & count) <----- Doesn't show
anything

Catch ex As Exception

TextBox2.Text = "Fill Error: " & ex.Message
'>>>shows "Object reference not set to an instance of the object."

Finally
conn400.Close()
Response.Write("Conn State after close: " & conn400.State)
'>>> Shows "Finally: 0" : disconnected
End Try
End Sub

At the line of code where that causes exception, the only objects referred
are the Data Adapter "da" and the DataSet "ds".
The dataset "ds" is not the problem because I can count the number of tables
in it before the fill. So the thing that got left is the data adapter.
Why isn't it created/instanitiated?
Please help... I'm stuck with this for the last 3 days I still can't figure
out the cause/solution for it.
Thank you very much.
Stanav.

[Chris]

Stanav wrote:

Hello all,
I'm developing a web application using VB.Net 2003 and Framework 1.1. This
application queries an AS/400 database. I'm using the IBM OleDb provider
that came with IBM Client Access for Windows (V5R3). Everything works fine
on my development PC, but when I move the application to a Windows Server
2003, it crashes when trying to fill a dataset. I've double-checked that the
Win 2k3 server does have Client Access installed, that it has .NET Framework
1.1, and running IIS. The only difference between my development PC and the
2k3 Server is that my PC is running WinXP Pro (thus it runs IIS v.5.1) while
the server is running IIS v.6.0.
Here is my code:

Public Sub LoadDataSet(ByVal source As String, ByVal Collection As String, _
ByVal table As String, ByVal field As String, ByVal keyValue As
String)

'Connection string to as400 database
conStr400 = "Provider=IBMDA400;Data Source=" & source & _
";User ID=myID;Password=myPwd;Default Connection=" & _

Collection
'Create connection object
conn400 = New OleDb.OleDbConnection(conStr400)

'Building SQL select string
Dim selStr As String = "SELECT " & selectedFields & " FROM " & _
table & " WHERE " & field & " = " & keyValue & _
" ORDER BY DSNUMB"

'Create data adapter
da = New OleDb.OleDbDataAdapter(selStr, conn400)

Dim count As Integer = 0

'Open connection to database.
'The Response.Write statements are for debugging purpose only

Response.Write("Conn State before open: " & conn400.State & "<br>")
'shows "0"

conn400.Open()

Response.Write("Conn State after open: " & conn400.State & "<br>")
'>>> shows "1": connected

'Try to load dataset
Try
ds.Clear()

Response.Write("Before Fill: " & ds.Tables.Count & "<br>")
'>>> shows "Before Fill: 0" : ds was created, just doesn't have any data,
yet.

count = da.Fill(ds) <<<<<<<< Null Exception occurs here

Response.Write("After Fill: " & ds.Tables.Count & _
"<br>Rec count = " & count) <----- Doesn't show
anything

Catch ex As Exception

TextBox2.Text = "Fill Error: " & ex.Message
'>>>shows "Object reference not set to an instance of the object."

Finally
conn400.Close()
Response.Write("Conn State after close: " & conn400.State)
'>>> Shows "Finally: 0" : disconnected
End Try
End Sub

At the line of code where that causes exception, the only objects referred
are the Data Adapter "da" and the DataSet "ds".
The dataset "ds" is not the problem because I can count the number of tables
in it before the fill. So the thing that got left is the data adapter.
Why isn't it created/instanitiated?
Please help... I'm stuck with this for the last 3 days I still can't figure
out the cause/solution for it.
Thank you very much.
Stanav.

Are you quering the same data on your machine and the server? My gut
says that it is a problem in the data that is being retrieved and not in
the da or ds objects.

Chris

[Stanav]

Thanks for replying to my post, Chris.
And yes, I'm quering the same data on both machines.
I'm still stuck on this... Any other suggestions/ideas?

[Cor Ligthert [MVP]]

Stanav,

My idea says me that you are getting the data with two different users.

Can that not be the problem?

(Although you have your connection to the database, can the database maybe
be restricted)

Just a guess

Cor