Saving data in sql server 2008 through vb.net

[dilse4sk]

hi every one i am trying to save data from vb.net form in sql server 2008 below is the code which i am using but the only problem is . i am not getting any error instead the data which is being saved in sql server in my First Name , Last Name and Address fields is not the one i insert into textboxes, when i check the backend it shows me my field values as first name , last name and address.

Dim FirstName As String
Dim LastName As String
Dim Address As String

FirstName = txtFirstname.Text
LastName = txtLastname.Text
Address = txtAddress.Text

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = ("Data Source=SAAD-PC\SQLEXPRESS;Initial Catalog=photoshoot;Integrated Security=True")
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO staff(FirstName, LastName,Address) VALUES(@p1,@p2,@p3)"
cmd.Parameters.AddWithValue("@p1", FirstName)
cmd.Parameters.AddWithValue("@p2", LastName)
cmd.Parameters.AddWithValue("@p3", Address)
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
Finally
con.Close()
End Try

[toomutch]

I would use a different sql command:

cmd.commandtext = "insert into staff (firstname, lastname, address) values ('" _
& firstname &"', '" & lastname & "', '" & address & "')"

you can then remove the 'cmd.parameters...' commands

Toomutch

[toomutch]

just doing something similar myself, and have thought of something else that may help in the long run. I don't know if you've stripped down your code to make it simpler for this forum, but as it stands you may have a problem. My suggestion above will fail if the user enters text with an apostrophe in it! (e.g. if the user enters a last name of "O'Shea" for example). The sql command uses apostrophes to separate input items so falls over if it finds an extra one.
Before you issue the 'insert' sql command you will need to remove unwanted characters from each string. Suggest you have a look at "Regular Expressions".
toomutch

[Reverend Jim]

Please refer to the excellent point raised by jbennet here regarding why you want to use substituted parameters.

[M.Waqas Aslam]

hello !
here is an error in your code

cmd.Parameters.AddWithValue("@p1", FirstName)'---error
cmd.Parameters.AddWithValue("@p2", LastName)'---error
cmd.Parameters.AddWithValue("@p3", Address)'---error
'==================================================
'check this code
cmd.Parameters.AddWithValue("@p1", txtFirstName.text)
cmd.Parameters.AddWithValue("@p2", txtLastName.text)
cmd.Parameters.AddWithValue("@p3", txtAddress.text)

Hope this will work fine :)
if your prob is solved please mark this thread solved and vote me up :)

Regards

[toomutch]

Hadn't thought of the SQL injection loophole. Thanks for the info - guess I'll have to go and rewrite LOADS of code!