I want to make a query like this.
SELECT @rownum:=@rownum+1 'rank' FROM (SELECT @rownum:=0) r
When I test it on my mysql query browser, it works well.
Now, I want to execute it in VB with mysqlCommand. of course @rownum is defined as a parameter and waiting for its value. How can I execute that query in VB?
You will need to add the variable to the command's parameters:
command.Parameters.AddWithValue("@rownum", rownum)
Some documentation is here: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlparametercollection.html
You must add this key to your connection string: 'Allow User Variables=True;'
dim conn As New MySqlConnection <br>
dim comm As New MySqlCommand<br><br>
dim dAdapter As New MySqlDataAdapter<br>
dim ddata As New DataSet<br>
conn.ConnectionString = "DATABASE=" & ddatabase & ";SERVER=" & Trim(TextBox1.Text) & ";user id=dev;password=revolution;port=" & TextBox3.Text & " ;charset=utf8;<b>Allow User Variables=True" </b><br>
conn.Open() <br>
<br>
comm.Connection = conn<br>
comm.CommandText = "set @rownum = 10"<br>
comm.ExecuteNonQuery() <br>
<br>
ddata.Clear()<br>
ddata.Tables.Clear()<br>
comm.Connection = conn<br>
comm.CommandText = "select @rownum rownum"<br>
dAdapter.SelectCommand = comm <br>
dAdapter.Fill(ddata)<br>
<br>
msgbox(ddata.Tables(0).Rows(0).Item("rownum")) <br>
