Update record using parameterized query in VB

Posted on 2012-09-21
Last Modified: 2012-09-21
I have a record that I want to update in a SQL Server database.  I want to use a parameterized query to reduce the chances of SQL Injection attack, but I just can't get the syntax correct.

I want to update the fields "Address" and "Phone" who's values come from txtAddress.text and txtPhone.text

Please supply code in VB.
Question by:rrhandle8
    LVL 5

    Expert Comment

    Considering you have the DB connection/etc code in place, here's what you can do

    Dim updateSql As String

    updateSql = "Update dbo.<replace you table name here> set Address = '" & txtAddress.Text.Trim().Replace("'", "''") & "', Phone = '" & txtPhone.Text.Trim().Replace("'", "") & "' WHERE <use condition clause if any>"

    hope it helps

    txtAddress.Text.Trim().Replace("'", "''") : the first parameter in Replace is a single quote mark and the second one is 2 single quotes within the quotations. This is to prevent exception due to an appostrophe which can be part of the text. This will also prevent Sql Injection

    In the replace for phone, I am just removing ' because it is not required in a phone number

    Author Comment


    That is just a standard SQL update command.  I am trying to do it with a parameterized query like:

    cmd.Parameters.AddWithValue("@Phone", txtPhone.text)
    LVL 5

    Expert Comment

    Here you go

    Dim updateSql As String
    Dim cmd As SqlCommand
    strQuery = "update <tableName> set Address=@Address, phone = @Phone where <you condition>"
    cmd = New SqlCommand(updateSql)
    cmd.Parameters.AddWithValue("@Address", txtAddress.Text.Trim())
    cmd.Parameters.AddWithValue("@Phone", txtPhone.Text.Trim())

    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString;
            Dim con As New SqlConnection(strConnString)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
                Return True
            Catch ex As Exception
                Return False
            End Try

    This is it.

    Author Comment

    That works with 1 correction: strQuery should be UpdateQuery
    strQuery = "update <tableName> set Address=@Address, phone = @Phone where <you condition>"

    One question: Shouldn't the datatype be stated some place like SqlDbType.NVarChar?  The whole reason I am trying to do this with parameterized queries is to avoid SQL injection attacks.  Does doing it the way you have it written help prevent attacks?
    LVL 5

    Accepted Solution

    No, the datatype is not required for parameterized queries. However, when working with Stored procs, you will need to define the datatype.

    Using parameterized queries help prevent sql injection to a large extent but i suggest you use stored procs instead of queries. That way, you can handle illegitimate characters, if any, in the params more gracefully and handle it within the database,

    For instance, you can examine the value in the parameter in more detail inside a stored procedure but that possibility is rather rare when working with queries.

    let me know if that helps.

    Author Comment

    Good answer, and thank you for the code!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now