[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

Datareader not returning recordset

Steve Villiers

9/5/2002 11:57:00 PM

Hi guys,

I'm using VB.NET with ODBC to access a DB2 database
running on an AS400. I have a system DSN set up through
the control panel using a DB2 provider from IBM.

Setting up DB2 through the Server Explorer option, I can
right-click the table and choose "Retrieve Data from
Table" successfully.

However, if I use code then no dataset is returned.
Peforming the ODBCDataReader.Read() function to return the
first row results in nothing.

Any ideas??

I have posted the code below for anyone who is interested.

Simon.

Module FileProcessor

Dim myConnection As New OdbcConnection
("Provider=MSDASQL.1;DSN=myDB2")

Public Function TestODBC()
Dim myCommand As OdbcCommand
Dim myReader As OdbcDataReader
Dim RetVal As Object
Try
myConnection.Open()
myCommand = myConnection.CreateCommand()
myCommand.CommandText = "Select * from
Accounts"
myReader = myCommand.ExecuteReader
MsgBox(myReader.FieldCount) ' this works
RetVal = 0
While myReader.Read() ' count no. of rows
RetVal = RetVal + 1 ' never get here
End While
MsgBox(RetVal)
myConnection.Close()
Catch err As Exception
MsgBox(err.Message)
End Try
End Function
End Module
3 Answers

Dave McCallum

9/9/2002 9:42:00 PM

0

Simon:

I seem to be getting the same problem with a DB2 mainframe
database. My C# code does not return any data from a SQOL
call. I created a .Net database project using the same
connection string and created a query with the same SQL in
it and the query WORKS where the C# ODBC call from the
code simply does not return data....

All the connections seem to be working and the transaction
gets to the mainframe (verified on the mainframe) but no
data is returned. Neither is an error message (other than
data not in field when accessing a data field).

If you get any responses to your plea or figure out a fix,
can you forward them to me? thanks

Dave McCallum

>-----Original Message-----
>Hi guys,
>
>I'm using VB.NET with ODBC to access a DB2 database
>running on an AS400. I have a system DSN set up through
>the control panel using a DB2 provider from IBM.
>
>Setting up DB2 through the Server Explorer option, I can
>right-click the table and choose "Retrieve Data from
>Table" successfully.
>
>However, if I use code then no dataset is returned.
>Peforming the ODBCDataReader.Read() function to return
the
>first row results in nothing.
>
>Any ideas??
>
>I have posted the code below for anyone who is interested.
>
>Simon.
>
>Module FileProcessor
>
> Dim myConnection As New OdbcConnection
>("Provider=MSDASQL.1;DSN=myDB2")
>
> Public Function TestODBC()
> Dim myCommand As OdbcCommand
> Dim myReader As OdbcDataReader
> Dim RetVal As Object
> Try
> myConnection.Open()
> myCommand = myConnection.CreateCommand()
> myCommand.CommandText = "Select * from
>Accounts"
> myReader = myCommand.ExecuteReader
> MsgBox(myReader.FieldCount) ' this works
> RetVal = 0
> While myReader.Read() ' count no. of rows
> RetVal = RetVal + 1 ' never get here
> End While
> MsgBox(RetVal)
> myConnection.Close()
> Catch err As Exception
> MsgBox(err.Message)
> End Try
> End Function
>End Module
>
>
>.
>

Dave McCallum

9/9/2002 10:40:00 PM

0

Simon:

I did get my sql to return data. But not with a
datareader - I gave up! I hope this solution helps you.

I used a dataadapter - here is some generic code:

OdbcConnection cn = new OdbcConnection();
cn.ConnectionString ="DSN=xxxx; UID=YYYY; PWD=ZZZZ;";
cn.ConnectionTimeout = 15;
cn.ConnectionTimeout = 145;

OdbcCommand sqlstatement = new OdbcCommand("SELECT
A.FIELD1 B.FIELD2 FROM TABLE_NAME_1 A, TABLE_nAME_2 B
WHERE A.FIELD1 = B.FIELD2 AND A.FIELD1 = " + MYINPUT_1 ,
cn);

try
{
//Command object
OdbcCommand cmd = new OdbcCommand(sqlstatement, cn);
DataSet mydata = new DataSet();
cn.Open();
OdbcDataAdapter da = new OdbcDataAdapter();

da.SelectCommand = sqlstatement;
int irows = da.Fill(mydata);
if (irows > 0)
{
retval = true;

}
string aa = mydata.GetXml(); //WILL SHOW YOU THE DATA

da.Dispose();
}

cheers

Dave
>-----Original Message-----
>Hi guys,
>
>I'm using VB.NET with ODBC to access a DB2 database
>running on an AS400. I have a system DSN set up through
>the control panel using a DB2 provider from IBM.
>
>Setting up DB2 through the Server Explorer option, I can
>right-click the table and choose "Retrieve Data from
>Table" successfully.
>
>However, if I use code then no dataset is returned.
>Peforming the ODBCDataReader.Read() function to return
the
>first row results in nothing.
>
>Any ideas??
>
>I have posted the code below for anyone who is interested.
>
>Simon.
>
>Module FileProcessor
>
> Dim myConnection As New OdbcConnection
>("Provider=MSDASQL.1;DSN=myDB2")
>
> Public Function TestODBC()
> Dim myCommand As OdbcCommand
> Dim myReader As OdbcDataReader
> Dim RetVal As Object
> Try
> myConnection.Open()
> myCommand = myConnection.CreateCommand()
> myCommand.CommandText = "Select * from
>Accounts"
> myReader = myCommand.ExecuteReader
> MsgBox(myReader.FieldCount) ' this works
> RetVal = 0
> While myReader.Read() ' count no. of rows
> RetVal = RetVal + 1 ' never get here
> End While
> MsgBox(RetVal)
> myConnection.Close()
> Catch err As Exception
> MsgBox(err.Message)
> End Try
> End Function
>End Module
>
>
>.
>

Steve Villiers

9/10/2002 2:24:00 AM

0

Hi Dave and Dave,

Thanks for your responses. Between the two I was able to
nut out the problem.

Unfortunately, it is so simple it's very embarassing to
reveal.


-> The table name needs to be qualified with the
library/environment.

ie: not "from myTable", but "from myLibrary.myTable",
since DB2 allows multiple tables in the same database with
the same name, and gets confused sending back nothing.
(Mind you, I would've expected an error in this case,
but...)


Again, thanks guys. You each provided a clue.

Simon.


>-----Original Message-----
>Hi guys,
>
>I'm using VB.NET with ODBC to access a DB2 database
>running on an AS400. I have a system DSN set up through
>the control panel using a DB2 provider from IBM.
>
>Setting up DB2 through the Server Explorer option, I can
>right-click the table and choose "Retrieve Data from
>Table" successfully.
>
>However, if I use code then no dataset is returned.
>Peforming the ODBCDataReader.Read() function to return
the
>first row results in nothing.
>
>Any ideas??
>
>I have posted the code below for anyone who is interested.
>
>Simon.
>
>Module FileProcessor
>
> Dim myConnection As New OdbcConnection
>("Provider=MSDASQL.1;DSN=myDB2")
>
> Public Function TestODBC()
> Dim myCommand As OdbcCommand
> Dim myReader As OdbcDataReader
> Dim RetVal As Object
> Try
> myConnection.Open()
> myCommand = myConnection.CreateCommand()
> myCommand.CommandText = "Select * from
>Accounts"
> myReader = myCommand.ExecuteReader
> MsgBox(myReader.FieldCount) ' this works
> RetVal = 0
> While myReader.Read() ' count no. of rows
> RetVal = RetVal + 1 ' never get here
> End While
> MsgBox(RetVal)
> myConnection.Close()
> Catch err As Exception
> MsgBox(err.Message)
> End Try
> End Function
>End Module
>
>
>.
>