ADO.Net & MySQL: OleDbCommand object with partameters fails to execute

[boolean]

Yeah... have been trying to use ADO.NEt with MySQL and i havent really been very successful!
Heres what i do...

' Dimension them all....
Dim OleDbCmd As New OleDb.OleDbCommand()
Dim OleDbCnn As New OleDb.OleDbConnection()
Dim _SqlText, _ConnectionString As String
Dim objDatadrd As IDataReader
Dim _ReturnStatus As Integer
' The connection string...
_ConnectionString = "Provider=MySqlProv;Data Source=The source;Integrated Security=;Password=Thepassword;User ID=TheUID;Location=SERVER;Extended Properties="
OleDbCmd.Connection = OleDbCnn
OleDbCmd.Connection.ConnectionString = _ConnectionString
' The SQL STring...
_SqlText = "select * from agency where agency_id = ?"
' This string executes just fine because there arent any parameters...
' _SqlText = "select * from agency where agency_id = 1"
OleDbCmd.Parameters.Add("@AgencyId", 1)
OleDbCmd.CommandText = _SqlText
OleDbCmd.CommandType = CommandType.Text
' The connection opens just fine...
OleDbCmd.Connection.Open()
' This is what fails... command object with parameters fails...
objDatadrd = OleDbCmd.ExecuteReader(CommandBehavior.CloseConnection)
If objDatadrd.Read Then
MsgBox(objDatadrd("agency_code&quot)
End If

Thanks for the help...
Sham Sham aka boolean... be practical/be straight... true/false?!

 

[boolean]

Guys... I had even tried downloading the native MySql Data provider from CoreLab! same problem... But this time around I get a
[
An unhandled exception of type 'CoreLab.MySql.MySqlException' occurred in corelab.mysql.dll

Additional information: You have an error in your SQL syntax near '?' at line 1
] Sham aka boolean... be practical/be straight... true/false?!

 

[link9]

It's a SQL error, not a data provider error. I have used coreLab, and that's the error message that it returns when you have an error in your sql statement...

What does that statement return when executed directly against the database?

The answer to getting answered -- faq855-2992

 

[boolean]

the query when run directly in mysqlfront would throw an error... no doubt about that...
but how do you pass parameters to the SQL statement in MySQL?
say... in my screen... i have to pick up the details of a particular user from the db... normally in MSSQL or ORACLE, i would either write a SP or an inline query with a parameter like:
SELECT * FROM USERS WHERE USER_ID = ?
and using the OledbParameter class... i would add the parameters to my OldedbCommandand then execute the query... this runs fine in MSSQL and ORACLE...

yeah? any ideas? Sham aka boolean... be practical/be straight... true/false?!

 

[link9]

Ahh...

commandObject.SelectCommand = "SELECT something FROM myTable WHERE fieldName = @value";
commandObject.Parameters.Add("@value",value);

-paul

The answer to getting answered -- faq855-2992

 

[link9]

Sorry for that quick and dirty answer there....

The Parameters collection's .Add method has many overloads (which you can look up in the help under sqlCommand -- the corelab mySQL object mirrors it)...

You can add it quick and dirty like I have there, or you can specify type, size, direction, etc... if your application needs more information about the parameter.

Like I said, though, if you check the help files under sqlCommand, you'll get all the information you could need.

hth!
paul

The answer to getting answered -- faq855-2992

 

[boolean]

hey paul... thanks for the time...

but then... i cant really use named parameters!
Essentially, we are into an application that is being customized to work on 3 engines - MSSQL, ORACLE and MySQL.

We have crafted a middle-layer between the business logic and data access that would make the application run based on the selected driver. If MSSQL is the database selected, the middle-layer would configure the usage of the MSSQL.Net data provider, else the 'OleDB'.Net data provider would be used. But all the forthcoming logic (be it any dataprovider) would be the same.

I hope you understand what the problem is... Do pass on your comments/suggestions as to how we can go about it...
Thanks again... but then... is paul the only enlightened one here? Sham aka boolean... be practical/be straight... true/false?!