Filling a dataset using Stored Proc in MySQL

[ramnujs]

I have a problem on Filling a dataset whose commands on command objects came from stored procedures in MySQL.....

Anyone who can help me please?..........
thanks a lot!

 

[jmcilhinney]

A liitle information about how you're going about it at the moment would have been nice. Basically, you need to set the CommandText of your Command object to the name of the SP and the CommandType to StoredProcedure. You then Add Parameters to your Command that correspond to the parameters of the SP and you can then execute the Command.

 

[Schenz]

I think we need to see what code you are using. I use mySQL 5.0.16, and the mySQL COnnector, and have used stored procedures in MySQL. But, I think it would be helpful to see what you already have that you are having problems with before just throwing raw code at you.

 

[ramnujs]

i am also using mysql 5.0.16

here is the code fragment:


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=localhost;uid=root;password=ramnujs;data source=hotelmsgtsystem")
Try
cn.Open()
Dim cmd As New Odbc.OdbcCommand("spselectguest")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = cn
Dim da As New Odbc.OdbcDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds, "Guests")
Me.DataGrid1.DataSource = ds
Me.DataGrid1.Refresh()
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub

Stored Proc is :
create procedure spSelectGuest()
begin
select * from guests;
end

 

[jmcilhinney]

And what is the actual problem? Do you get an exception thrown? Do any records get returned, which you can check by the Rows.Count property of the DataTable? There's a very good chance that the issue is simply that you aren't displaying the table properly. You should either bind the DataTable to the DataGrid directly or else bind the DataSet as you are and set the DataMember to the name of the DataTable you want to display, i.e. "Guests".

Finally, there are various MySQL-specific data connectors available, as Schenz alluded to. There is a link to a free one from MySQL themselves in my signature. I'd suggest using that rather than the more generic, and thus less efficient, OdbcClient namespace.

 

[Schenz]

jmcilhinney is correct. You should use the provider from MySQL.

Next have you verified that the stored procedures works correctly via Query Browser?

I do not see anything offhand that would give you problems....

 

[ramnujs]

there was an exception thrown and displayed in the message box - i think error # 23000 : something to do with the syntax i supposed.......

maybe you guys are right, maybe i should use MySqlData Providers... thanks for the help anyway!

 

[Schenz]

Download the mySQL Provider, and adjust your code as needed first. Once you have it installed and create a reference to the MySQL.Data.DLL, and have adjusted your code to use the MySQL classes, then post any problems you have. Exact error message, and the exact line any exception occurs on is really needed to track down any sort of exception.

 

[ramnujs]

MySQL Classes are Good!

I have downloaded MySQL Direct from cored lab and all are perfectly working ryt now. the only problem is that i amusing a trial version. Is there any freeware MySQL Data providers?

I really thank for all the helped that have extended by you.......

 

[jmcilhinney]

Maybe, let me think...

Finally, there are various MySQL-specific data connectors available, as Schenz alluded to. There is a link to a free one from MySQL themselves in my signature. I'd suggest using that rather than the more generic, and thus less efficient, OdbcClient namespace.

 

[Schenz]

Here it is as well:
http://dev.mysql.com/downloads/connector/net/1.0.html