• 0

[VB.net] MYSQL Queries HELP


Question

I cannot make a mysql query with a question mark as the value of the string or the program freezes and says "Parameter '*string with the question mark in it*' must be defined" and it highlights "cmd.ExecuteNonQuery()". This the block of code:

Dim cmd As New MySqlClient.MySqlCommand

Dim connect As New MySqlClient.MySqlConnection("Database=*database name*;Data Source=tehplanet.net;User Id=*my username*;Password=*my passwod*")

Dim the_time As String = System.DateTime.Now

Dim WC As New System.Net.WebClient

Dim the_ip As String

the_ip = System.Text.Encoding.ASCII.GetString((WC.DownloadData("http://whatismyip.com/automation/n09230945.asp")))

WC.Dispose()

If string_to_process.Text <> Nothing Then

cmd.CommandText = "INSERT INTO `table name`(string, ip, time) VALUES (?" & string_to_process.Text & "?, ?" & the_ip & "?, ?" & the_time & "?)"

cmd.Connection = connect

connect.Open()

cmd.ExecuteNonQuery()

connect.Close()

E[i][/i]code]

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Because you are not escaping the value of "string_to_process.Text", the MySql provider will interpret "?" as a parameter. I recommend using the parameter pattern when defining Sql statements with ADO.NET:

Dim command As MySqlCommand = New MySqlCommand()

command.Parameters.AddWithValue("?String", string_to_process.Text)
command.Parameters.AddWithValue("?IPAddress", the_ip)
command.Parameters.AddWithValue("?Time", the_time)

command.CommandText = "INSERT INTO 'table_name' (string, ip, time) VALUES (?String, ?IPAddress, ?Time)"

'Do other work here...

The MySql ADO.NET provider (MySql Connector.NET) will automatically identify the types, and apply the value in the statement for you. This also helps against sql-injection attacks.

Also, I noticed that you are not disposing of your objects, I make it a personal preference to make sure I call Dispose() on objects when I no longer need them. VB.NET doesn't have a C# style "using" construct that will automatically disposing of objects in the using block, but you should look here for a VB.NET alternative:

http://weblogs.asp.net/psteele/articles/9603.aspx

Link to comment
Share on other sites

  • 0
So essentially they are the same thing?

yes, ado.net is the framework other providers can make connectors for :). The mssqlclient is automatically included with .net, you use the mysql client. They share the same base classes and their adapters result in the same objects, namely datasets and datatables.

Link to comment
Share on other sites

  • 0
yes, ado.net is the framework other providers can make connectors for :). The mssqlclient is automatically included with .net, you use the mysql client. They share the same base classes and their adapters result in the same objects, namely datasets and datatables.

Thanks there mate! =]

Link to comment
Share on other sites

This topic is now closed to further replies.