Parameterized query for asp.net in VB for MySQL

[dejswa]

Sorry guys for possibly repeating an issue that may be answered here before, but I have searched and searched and just cannot get it right. Maybe you can offer a quick solution.

I am using VS 2008 for a .aspx web page in asp.net 3.5
My coding is in VB and I am using MySQL latest version, connecting through a system DSN connection on a virtual server running MS server 2008.

I keep finding solutions that are specific to SQL server or use other types of connections. Don't know if I should be putting '?' or '@' in my query strings to do parameterization. Can you show me specifically what I need to do to translate this to parameterized code? Let's say I want to pass the data 'fielddata1'.

My typical code for a query looks like:

Dim conx As New System.Data.Odbc.OdbcConnection("DSN=mydb")
Dim strsql As String
Dim mycmd As System.Data.Odbc.OdbcCommand
conn.Open()
strsql = "UPDATE tbljunk SET field1=" & field1data & "WHERE pkjunk=1"
mycmd = New System.Data.Odbc.OdbcCommand(strsql, conn)
mycmd.ExecuteNonQuery()


Thanks for reading!

DJ

 

[dejswa]

FYI, this is one thing I have tried which didn't work:

strsql = "UPDATE tbljunk SET myfield=@myfield"
mycmd = New System.Data.Odbc.OdbcCommand(strsql, conn)
mycmd .Parameters.Add("@myfield", System.Data.Odbc.OdbcType.VarChar, "hello")
mycmd .ExecuteNonQuery()

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Line 25: mycmd .Parameters.Add("@myfield", System.Data.Odbc.OdbcType.VarChar, "hello")

Nor does anything like this work:

mycmd .Parameters(1).Value = "hello"

 

[dejswa]

But wait . . . this is not giving an error, but don't know yet if it is working . . .

cmd.Parameters.Add("@myfield", "hello");

well, it is not generating an error at runtime, but the specified field, which is VARCHAR(30) is changed from its prior string value to NULL.

So, I guess it basically doing nothing to the query string.

Still no luck . . .

 

[jmcilhinney]

The Connector/Net ADO.NET provider from MySQL should be your first option for connecting to MySQL. It supports both ? and @ as prefixes for parameters. If you can't use that for some reason then you must use ? as a parameter prefix, which is a MySQL standard.

If you want to add a parameter and set its value then you should be using AddWithValue, not Add:

myCommand.Parameters.AddWithValue("?MyParameter", parameterValue)

 

[dejswa]

Thanks for the feedback.

When I use the '?',

strsql = "UPDATE tbljunk SET myfield=?myfield where id=1"
mycmd = New System.Data.Odbc.OdbcCommand(strsql, conn)
mycmd.Parameters.AddWithValue("?myfield", "hello")
mycmd.ExecuteNonQuery()
​

I get the following error:

Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.47-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myfield where id=1' at line 1​

When I use the '@', I don't get an error, however, a NULL is written to the database field rather than 'hello'

Do I have to install any special MySQL component for asp.net or ado.net? I don't think I have done that.

Also, Add vs AddWithValue? Apparently Add does a type conversion from the input to the desired type, whereas AddWithValue simply passes its input to the database and lets the db handle any conversion. Is this right?

Thanks for the feedback.

 

[jmcilhinney]

I've never actually used MySQL so I'm only going by the snippets that I've read here and there. In the SQL code, try using just ? alone.

As for Add and AddWithValue, Add creates a parameter without a value, so you must set the Value property separately, while AddWithValue infers all other properties, like data type and size, from the value. As such, you must make sure that the value you pass to AddWithValue is the correct type. You'd always make sure everything was the correct type anyway though.