CODEWITHSUNDEEP
asp.netsqlsql-servervb.net
Ali M Alramil
Ali M Alramil

Reputation: 39

Update SQL Server tables using textboxes

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

Upvotes: 1

Views: 1858

Answers (2)

Manish Mishra
Manish Mishra

Reputation: 12375

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

Upvotes: 4

Fabio
Fabio

Reputation: 32445

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

Upvotes: 1

Related Questions