rrhandle8
asked on
Update record using parameterized query in Asp.net VB
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.
I want to update the fields "Address" and "Phone" who's values come from txtAddress.text and txtPhone.text
Please supply code in VB.
ASKER
sameer_goyal
That is just a standard SQL update command. I am trying to do it with a parameterized query like:
cmd.Parameters.AddWithValu e("@Phone" , txtPhone.text)
That is just a standard SQL update command. I am trying to do it with a parameterized query like:
cmd.Parameters.AddWithValu
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.AddWithValu e("@Addres s", txtAddress.Text.Trim())
cmd.Parameters.AddWithValu e("@Phone" , txtPhone.Text.Trim())
Dim strConnString As String = System.Configuration.Confi gurationMa nager.Conn ectionStri ngs("conSt ring").Con nectionStr ing;
Dim con As New SqlConnection(strConnStrin g)
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Response.Write(ex.Message)
Return False
Finally
con.Close()
con.Dispose()
End Try
This is it.
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.AddWithValu
cmd.Parameters.AddWithValu
Dim strConnString As String = System.Configuration.Confi
Dim con As New SqlConnection(strConnStrin
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Response.Write(ex.Message)
Return False
Finally
con.Close()
con.Dispose()
End Try
This is it.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good answer, and thank you for the code!
Dim updateSql As String
updateSql = "Update dbo.<replace you table name here> set Address = '" & txtAddress.Text.Trim().Rep
hope it helps
Note:
txtAddress.Text.Trim().Rep
In the replace for phone, I am just removing ' because it is not required in a phone number