Vb.net/Mysql throws fatal error when @variable is added to mysql query

Question

Hi I'm using the following query/code to fill the datagrid

Private Sub btnSave_frmCreateInvoice_Click(sender As Object, e As EventArgs) Handles btnSave_frmCreateInvoice.Click
 Dim qryGetInvoiceItems as string = "select  @s:=@s+1 'SLNO', ini_item_details 'Item Description', ini_item_quantity 'Quantity', ini_item_rate 'Rate', round(ini_item_quantity*ini_item_rate,2) 'Item Amount' from tbl_invoice_items, (SELECT @s:=0) AS s where ini_invoice_no=" & txtInvoiceNumber_frmCreateInvoice.Text & " and ini_status=1 order by ini_id ASC"
 fill_datagrid(dgInvoiceItems_frmCreateInvoice, qryGetInvoiceItems)
 End Sub

Public Sub fill_datagrid(ByVal datagrid As DataGridView, ByVal query As String)
    Using conn As New MySqlConnection
        Try
            conn.ConnectionString = constr
            conn.Open()
            Dim sqlcommand As New MySqlCommand(query, conn)
            Dim sqlDataAdapter As New MySqlDataAdapter
            Dim dt As New DataTable
            Dim bSource As New BindingSource
            sqlDataAdapter.SelectCommand = sqlcommand
            sqlDataAdapter.Fill(dt)
            bSource.DataSource = dt
            datagrid.DataSource = bSource
            sqlDataAdapter.Dispose()
        Finally
            If conn IsNot Nothing Then
                conn.Dispose()
            End If
        End Try
    End Using
End Sub

In mysql query, i'm using @s to get SLNO for each item. The query is running perfectly as desired in MYSQL Console but it is throwing fatal error when run in the application

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: Fatal error encountered during command execution.

If I remove @s:=@s+1 and (SELECT @s:=0) AS s, then the application works fine.

Please suggest.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

There are several ways to form the SQL query, but there is another step required. Using this SQL:

Dim SQL = "SET @s=0; SELECT Id, Name, Description, A, B, C, D, @s:=@s+1 As SLNO FROM Alpha"
Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(SQL, dbcon)

        dbcon.Open()
        dtSample = New DataTable

        dtSample.Load(cmd.ExecuteReader)
    End Using
End Using
dgv2.DataSource = dtSample

Note that you do not need a DataAdapter just to fill a DataTable; there are meant for a bit more heavy lifting than that. This SQL also works:

Dim SQL = <sql>
        SELECT Id, Name, Description, A, B, C, D, @s:=@s+1 As SLNO 
            FROM Alpha, (SELECT @s :=0) ss
      </sql>.Value

The XML literal is just to eliminate scroll. The (SELECT @s :=0) ss portion acts as inline initialization and does the same as the separate statement in the first ( SET @s=0; ).

This is the trick

By default, MySQL does not allow user variables ( @s ), so you have to enable them to avoid the fatal error exception. If you View Detail you should see {"Parameter '@s' must be defined."}. User vars are enabled via the connection string:

"allowuservariables=True;server=...;database=xxx;user id=uuu;password=pppp;port=3306"

MySQL allows for a large number of options you can enable as needed in the connection string. If you use more than one or two you may want to use MySqlConnectionStringBuilder to create it to avoid formatting errors.

Once user variables are enabled, the results are the same with either SQL (MySQL 6.9.8.0):

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 1, 2016 at 15:57

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges66 silver badges194 bronze badges

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

1 Comment

Iqlas Uddin Over a year ago

Thanks Plutonix, for your time and detailed explanation.. You saved my work, I just added this part to the connection string : allowuservariables=True; and it's awesome!

Rahul · Accepted Answer · 2016-05-01 08:35:29Z

1

Try changing your query to look like

select  ini_item_details as 'Item Description', 
ini_item_quantity as 'Quantity', 
ini_item_rate as 'Rate', 
round(ini_item_quantity*ini_item_rate,2) as 'Item Amount',
@rownum := @rownum + 1 as 'SLNO' 
from tbl_invoice_items, 
(SELECT @rownum := 0) s 
where ini_invoice_no=" & txtInvoiceNumber_frmCreateInvoice.Text & " 
and ini_status=1 
order by ini_id;

answered May 1, 2016 at 8:35

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

Vb.net/Mysql throws fatal error when @variable is added to mysql query

2 Answers 2

This is the trick

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

This is the trick

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related