How do I parameterized the queries of VB.NET code

Question

1.00/5 (1 vote)

See more:

I need to parameterized my queries for safety purposes.

What I have tried:

 Private Sub Save_Click(sender As Object, e As EventArgs) Handles Save.Click
       
        MyNonQuery(String.Format("insert into boyscout_pos.tblproducts (Purchase_Invoice,Product_Code,Product_Name,Category,Size,Product_Price,Selling_Price,Qty_Stock) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", txtpurchaseinvoice.Text, txtproductcode.Text, txtproductname.Text, txtproductcategory.Text, TextBoxSize.Text, txtproductprice.Text, txtsellingprice.Text, txtproductquantity.Text))

        MessageBox.Show("Successfully Saved")

        TxtSearchCode.Clear()
        cboproductsize.Items.Clear()
        txtproductcode.Clear()
        txtproductname.Clear()
        txtproductcategory.Clear()
        txtproductprice.Clear()
        txtsellingprice.Clear()
        TextBoxSize.Clear()
        cboproductsize.Text = ""
        txtproductquantity.Clear()
End Sub
    
Public Sub MyNonQuery(ByVal SQCommand As String)
       Dim conn As New 
       MySqlConnection("server=localhost;userid=root;password=;database=boyscout_pos")
       Dim SQLCMD As New MySqlCommand(SQCommand, conn)
       conn.Open()
       SQLCMD.ExecuteNonQuery()
       conn.Close()
End Sub

Posted 15-Aug-17 15:49pm

Member 13264296

Updated 15-Aug-17 19:42pm

v2

Add a Solution

Comments

Patrice T 15-Aug-17 23:35pm

A,d what says Google in your place ?

Dave Kreskowiak 15-Aug-17 23:46pm

Type "vb.net parameterize queries mysql" into Google and you'll see this has been documented many, many times.

2 solutions

Solution 2

If you're using Visual Studio 2015 or higher, and targeting .NET 4.6 or later, something like this will work:

VB.NET

Public Shared Function CreateCommand(ByVal connection As IDbConnection, ByVal commandText As FormattableString, Optional ByVal commandType As CommandType = CommandType.Text) As IDbCommand
    Dim result As IDbCommand = connection.CreateCommand()
    
    Dim parameterNames As New List(Of String)(commandText.ArgumentCount)
    For Each parameter As Object In commandText.GetArguments()
        Dim parameterName As String = "@p" & parameterNames.Count
        parameterNames.Add(parameterName)
        
        Dim p As IDbDataParameter = result.CreateParameter()
        p.ParameterName = parameterName
        p.Value = parameter
        result.Parameters.Add(p)
    Next
    
    result.CommandText = String.Format(commandText.Format, parameterNames.ToArray())
    result.CommandType = commandType
    Return result
End Function

Public Sub MyNonQuery(ByVal commandText As FormattableString, Optional ByVal commandType As CommandType = CommandType.Text)
    Using connection As New MySqlConnection("server=localhost;userid=root;password=;database=boyscout_pos")
        Using command As IDbCommand = CreateCommand(connection, commandText, commandType)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

...

MyNonQuery($"insert into boyscout_pos.tblproducts (Purchase_Invoice, Product_Code, Product_Name, Category, Size, Product_Price, Selling_Price, Qty_Stock) values ({txtpurchaseinvoice.Text}, {txtproductcode.Text}, {txtproductname.Text}, {txtproductcategory.Text}, {TextBoxSize.Text}, {txtproductprice.Text}, {txtsellingprice.Text}, {txtproductquantity.Text})")

This uses string interpolation[^] to pass the command text and parameters as one object. By passing it as a FormattableString[^], it is able to extract the parameter values and pass them properly, rather than concatenating them into the command text.

(NB: I didn't invent this idea - I saw it in an article, which I think was somewhere on CodeProject. Unfortunately, I didn't keep the link, and I can't find it now. If the author spots this and wants me to add a link, let me know.)

Posted 17-Aug-17 6:22am

Richard Deeming

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-08-15T19:42:00

You want to end up with code like this:

VB

Using con As New MySqlConnection(strConnect)
	con.Open()
	Using com As New MySqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con)
		com.Parameters.AddWithValue("@C1", myValueForColumn1)
		com.Parameters.AddWithValue("@C2", myValueForColumn2)
		com.ExecuteNonQuery()
	End Using
End Using

Which means that the way you are doing it will not work - you can't just pass a string to a "Generic" function.