Update SQL Server tables using textboxes

Question

I'm programming an education website using asp.net vb.net and SQL Server. I have 4 stackholders, if any body log in in his profile he will see his information

If he wants to update them he just change the textboxes then click update

My problem is how to update.

I wrote a method to update but it always show me a syntax error in the query. I made sure there is no problem. I'm updating two tables and I made to sql statements!

My qustion is can I Insert instead of update?

If not: how to upade one record based on the session I have?

please help me

this my code

' Private Sub UpdateInfo(ByVal USER_ID As Integer)

'Dim User As Integer = USER_ID
'Dim sql1 As String = "UPDATE AdminCoordinatorInformation  SET   MobileNumber =" + tbmobile.Text + ", HomeNumber=" + tbhome.Text + ", AltRelation = " + DDLRelationShip.SelectedValue + ", AlTitle = " + DDLTitle.SelectedValue + ", AltName = " + tbemname.Text + ", AltMobile = " + tbemmobile.Text + " WHERE USER_ID = User)"
'Dim sql2 As String = "UPDATE DIP_USERS SET USER_Email=" + tbEmail.Text.Trim + "  WHERE USER_ID = User)"


'  Try
' Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("mydbConnectionString").ConnectionString)
' Dim cmd1 As New SqlCommand(sql1, conn)
' Dim cmd2 As New SqlCommand(sql2, conn)
' cmd2.Parameters.AddWithValue("@FirstName", tbname.Text)
' cmd2.Parameters.AddWithValue("@USER_PASSWORD", tbnewpassword.Text)
' cmd2.Parameters.AddWithValue("@USER_Email", tbEmail.Text)
' cmd1.Parameters.AddWithValue("@MobileNumber", tbmobile.Text)
' cmd1.Parameters.AddWithValue("@HomeNumber", tbhome.Text)
' cmd1.Parameters.AddWithValue("@AltRelation", DDLRelationShip.SelectedValue)
' cmd1.Parameters.AddWithValue("@AlTitle", DDLTitle.SelectedValue)
' cmd1.Parameters.AddWithValue("@AltName", tbemname.Text)
' cmd1.Parameters.AddWithValue("@AltMobile", tbemmobile.Text)
' conn.Open()


'Dim ra As Integer = cmd1.ExecuteNonQuery()

'Dim ra1 As Integer = cmd2.ExecuteNonQuery()

'cmd1.Dispose()
'cmd2.Dispose()


'   conn.Close()
'   Catch ex As Exception
' MsgBox(ex.Message)
' End Try


'End Sub

Manish Mishra · Accepted Answer · 2013-04-17 08:34:49Z

4

you have not specified your parameters in your query, you're directly concatenating the values of controls inside your query. And still you have added parameters.

Firstly, do not concatenate your sql query like that, its prone to SQL Injection.

construct your query like this:

Dim sql1 As String = "UPDATE AdminCoordinatorInformation  SET   
                        MobileNumber =@MobileNumber, 
                        HomeNumber=@HomeNumber, 
                        AltRelation = @AltRelation, 
                        AlTitle = @AlTitle, 
                        AltName =@AltName, 
                        AltMobile = @AltMobile 
                        WHERE USER_ID = @User"
Dim sql2 As String = "UPDATE DIP_USERS SET 
                       USER_Email=@USER_Email 
                       WHERE USER_ID = @User"

and also, add this parameter too

 cmd1.Parameters.AddWithValue("@User", USER_ID)
 cmd2.Parameters.AddWithValue("@User", USER_ID)

And one very important thing. you need to assign proper datatype to your columns in the query i.e. remember these things.

txtBox.Text returns String value, you might need to convert it to Int32 using Convert.Int32 or you need to wrap it in single quote, based totally upon datatype of your column

edited Apr 17, 2013 at 8:34

answered Apr 17, 2013 at 8:26

Manish Mishra

12.4k5 gold badges40 silver badges65 bronze badges

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

1 Comment

Ali M Alramil Over a year ago

when I try to type it it show that it's not belong to the String

Fabio · Accepted Answer · 2013-04-17 08:31:17Z

1

Put parameters which you declare in your SQL Command query:

"UPDATE AdminCoordinatorInformation SET MobileNumber=@MobileNumber,HomeNumber=@homeNumber...

You get syntax error because your string data in sql query must be wrapped with "'".

"UPDATE AdminCoordinatorInformation SET MobileNumber='0987654321',....

Note: creating sql queries by concating query with user inputs ("...SET MobileNumber='" + txtbox.Text + "',...") is not good/dangerous practice because of SQL Injection

answered Apr 17, 2013 at 8:31

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

1 Comment

Ali M Alramil Over a year ago

thanks but I did it is between "" and the I did the first syntax before but the same

Collectives™ on Stack Overflow

Update SQL Server tables using textboxes

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related