MySQL/VB.NET(Winform) - How to use correctly @var?

Question

I am developing a sequence in MySQL & VB.NET in which, when deleting a record, the number that identifies it, is deleted and put a sequence of 1 to 'n', the sequence is as follows.

this is the instruccion for MySQL.

SET @rownum=0;
UPDATE id_line t, (SELECT @rownum:=@rownum+1 rownum, id_line.* FROM id_line WHERE id_line.line_name=59999 and id_line.line_no<>0) r 
SET t.line_no = r.rownum 
WHERE (t.id_line_b = r.id_line_b)

in VB.net I use this

cmdB = New MySqlCommand("SET @rownum=0 UPDATE id_line t, (SELECT @rownum=:@rownum+1 rownum, id_line.* FROM id_line WHERE id_line.line_name='" & TextBox1.Text & "' and id_line.line_no<>0) r ) SET t.line_no = r.rownum WHERE(t.id_line_b = r.id_line_b)", conn)

but VB sends this error

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 'UPDATE id_line t, (SELECT @rownum=:@rownum+1 rownum, id_line.* FROM id_lin' at line 1

Could you help me please in this error?

Updated 1: This is my string connection.

Public conString As String = "Data Source=server_one;port=3306;Initial Catalog=test_db;User Id=root;password=root;Allow User Variables=True"

For a start, I see a semi-colon at the end of SET @rownum=0; but it doesn't appear in the string in the VB.NET code. — Andrew Morton
– Andrew Morton, Commented Mar 25, 2019 at 18:38
Yes, the first part of the code is for the database engine, add the semicolon in VB.NET, but the error persists. — LARC
– LARC, Commented Mar 25, 2019 at 19:34

Rajesh · Accepted Answer · 2019-03-25 19:42:52Z

1

other better way for create mysql procedure for delete mysql data query

https://www.tutorialspoint.com/What-is-stored-procedure-and-how-can-we-create-MySQL-stored-procedures

answered Mar 25, 2019 at 19:42

Rajesh

694 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LARC Over a year ago

Thank you for your contribution, but the error is in VB, in MySQL engine is working fine, anyway I'll try whit the method you propose. Thank you again. Regards.

LARC · Accepted Answer · 2019-03-26 00:00:01Z

0

I found the error, a parenthesis, it was misplaced, this is the correct code.

cmdB = New MySqlCommand("SET @rownum:=0; UPDATE id_line t, (SELECT @rownum:=@rownum+1 rownum, id_line.* FROM id_line WHERE id_line.line_name='" & TextBox1.Text & "' and id_line.line_no<>0) r SET t.line_no = r.rownum WHERE(t.id_line_b = r.id_line_b)", conn)

Thank you everyone for your attention and support.

Regards.

answered Mar 26, 2019 at 0:00

LARC

1191 silver badge14 bronze badges

2 Comments

Mary Over a year ago

I am glad you found your error but please use parameters. That TextBox1.Text can cause damage to your database. It needs to be sanitized by setting it as the value of a parameter which will be treated as a value not executable code by the database engine.

LARC Over a year ago

OK, I understand, the code was updated whit a variable before the sentence to do the MySQLCommand, is working too!. Thank you so much for your contribution too.

Collectives™ on Stack Overflow

MySQL/VB.NET(Winform) - How to use correctly @var?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related