MySQL + dataAdapters

[mike55]

Hi all,

 

Am trying to execute a simple sql command and input the result into a dataAdapter. Will then take the data in the Adapter and put it in a dataset. Unfortunately when i attempt to fill the dataAdapter, an exeception is thrown.

 

The code that i am using is:

 

MyCommand.CommandText = "Select CategoryID, CategoryName, Comments from categories where CategoryID >0 order by CategoryName"

daDataAdapter = MyCommand.ExecuteScalar()

daDataAdapter.Fill(dsDataSet)

MyConnection.Close()

Return daDataAdapter

[wessamzeidan]

'daDataAdapter = MyCommand.ExecuteScalar()' I dont think you can do that..

[mike55]

Any suggestions to get around problem. Can't use a datareader as you can't pass a datareader between classes or projects.

 

Michael

[PlausiblyDamp]

MyCommand.CommandText = "Select CategoryID, CategoryName, Comments from categories where CategoryID >0 order by CategoryName"
daDataAdapter.SelectCommand = MyCommand
daDataAdapter.Fill(dsDataSet)
MyConnection.Close()
Return daDataAdapter

 

assuming you have set the connection up properly and assigned it to the command object the above code should work.

[wessamzeidan]

what type of exception are you getting??

[mike55]

Ok, my connection code is as follows:

 

Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _

"SERVER=localhost;" & "DATABASE=ivertecproject;" & _

"UID=root;" & "PASSWORD=;" & _

"OPTION=3"

MyConnection = New OdbcConnection(connString)

MyConnection.Open()

MyCommand.Connection = MyConnection

 

The exception raised is a null reference exception. As a precaution, i changed my selection command to do a select * from the table, same exception is raised.

 

I know from other sql command that i am using that the connection string is ok and working properly.

 

Mike

[PlausiblyDamp]

Have you created the command object before you reference it?

Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & "DATABASE=ivertecproject;" & _
"UID=root;" & "PASSWORD=;" & _
"OPTION=3"
MyConnection = New OdbcConnection(connString)
MyConnection.Open()
MyCommand = new OdbcCommand()     'I think.
MyCommand.Connection = MyConnection

[wessamzeidan]

also check your dataset

[mike55]

Have you created the command object before you reference it?

Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & "DATABASE=ivertecproject;" & _
"UID=root;" & "PASSWORD=;" & _
"OPTION=3"
MyConnection = New OdbcConnection(connString)
MyConnection.Open()
MyCommand = new OdbcCommand()     'I think.
MyCommand.Connection = MyConnection

 

 

Yea i created it, it is declared as a global variable at the top of the page.

Dim MyCommand As New OdbcCommand

[mike55]

also check your dataset

 

Is there anything in particular that i should be looking for??

[mike55]

Hi guys,

 

Right the dataset is working when passing large amounts of data between classes and modules. The problem is that how to pass the dataset that i have from a web service back to the asp.net project, have tried to pass the dataset and have tried to pass other variables as-well. Am going to try and use a dataAdapter to pass the selected data back and forth, however if anyone has any better suggestion would appreciate it.

 

Mike55

[PlausiblyDamp]

You should be able to return a dataset from a webmethod - I've done it myself several times. If it isn't working could you post your code or more details on how it is failing?

[huby]

executeScalar doesn't return a dataset. it returns a single value.

 

if you're only getting data from 1 table, why not use a dataReader to populate your dataset, adn then return your dataset ? (i'm not that advanced, so don't take my word for it ...)

 

luck,

 

Huby.

[mike55]

executeScalar doesn't return a dataset. it returns a single value.

 

if you're only getting data from 1 table, why not use a dataReader to populate your dataset, adn then return your dataset ? (i'm not that advanced, so don't take my word for it ...)

 

luck,

 

Huby.

 

Thanks for the suggestion, managed to get the dataset working properly...Think it may have been a bug on the machine i had been working on.