Help with SQL in VB.NET

[m.tompkinsnz]

Hey Guys,

I'm working on a practice Project at the moment where I'm recalling user information from a database, placing it into a form with several textBoxes, comboBoxes, etc and then proceeding to update the information in the database when the user clicks save. I understand this is simple, however snippets I've seen (and I probably haven't searched properly) show how to update one column in the table. I'm using VB.NET 2010 and SQL CE

An example of what I've found is below, which replaces a single entry.

Dim SQLCmd As New SqlCommand("UPDATE tblQuestion SET Answer = 'Like this' Where Question = 'How to use SQL?'")

I guess what I'm needing is to know how to write an SQL Command that updates several columns, on the same row with variables (or even directly from the control) pulled from the form. Can you set multiple columns, and how would I change only the user entry labelled 'John' for example?

For the purposes of this exercise, the table is called 'Users', columns called 'Name', 'Age', 'Address', each user on a different row. Only one user is called into the form, of which I've got the read side of things sorted with a try, while and if to match my inputbox to an entry with sqlDataReader.Item .

It's a silly question I know but I can't for the life of me get in the right headspace to work it out ^_^

Cheers in advance,

Michael

[m.tompkinsnz]

I realise one way would be calling the information into a dataset then updating the entire dataset, but how could I do it without using a dataset and writing straight back from the controls?

[M.Waqas Aslam]

try something like this

dim con as new sqlconnection("connection string")
dim cmd as new sqlcommand
con.open()
cmd.connection = con 
cmd.commandtext = "update table1 set col1=@col1,col2=@col2,col3=@col3,col4=@col4 where recid = @recid"
cmd.paramerter.addwithvalues("@col1",txt1.text)
cmd.paramerter.addwithvalues("@col2",txt2.text)
cmd.paramerter.addwithvalues("@col3",txt3.text)
cmd.paramerter.addwithvalues("@col4",txt4.text)
cmd.paramerter.addwithvalues("@recid",txtRecId.text)
cmd.ExecutenonQuery()
con.close()

i typed it here so may be there are some spelling mistakes in it ,hope this will solve your prob :)

Regards

[m.tompkinsnz]

You'll have to excuse my misunderstanding, but which part of the commandtext do i put the name of my columns in? or do i just refer to the column number.

For example could it be

con.open()
cmd.connection = con
cmd.commandtext = "update Users set age=@age,address=@address where name = @name"
cmd.parameters.addwithvalues("@name", txtName.text)
cmd.parameters.addwithvalues("@age", txtAge.text)
cmd.parameters.addwithvalues("@address", txtAddress.text)
cmd.ExecutenonQuery()
con.close()

I'm a bit of a beginner so any help is good help :)

[m.tompkinsnz]

Awesome! Have worked it out thanks to your help :)

Issues now with how I'm processing my data but I can handle that!

Thanks again

[m.tompkinsnz]

Actually, could I potentially replace update with insert to add a new row entry? Obviously I'd need to take off the where at the end but is this do-able?

This is the code I've got at the moment, which is throwing up an exception: "There was an error parsing the query. [Token line number = 1, Token line offset = 14, token in error = set]

cmd.Connection = conn
cmd.CommandText = "insert Users set ID=@id,Username=@username,Password=@password,FirstName=@firstname,LastName=@lastname,Employment=@employment,Position=@position,Department=@department,DOB=@dob,StartingDate=@started"
cmd.Parameters.AddWithValue("@id", idCounter)
cmd.Parameters.AddWithValue("@username", txtUsername.Text)
cmd.Parameters.AddWithValue("@password", txtPassword.Text)
cmd.Parameters.AddWithValue("@firstname", txtFirstName.Text)
cmd.Parameters.AddWithValue("@lastname", txtLastName.Text)
cmd.Parameters.AddWithValue("@employment", currentEmploymentStatus)
cmd.Parameters.AddWithValue("@position", currentPosition)
cmd.Parameters.AddWithValue("@department", currentDepartment)
cmd.Parameters.AddWithValue("@dob", Me.dtpDateOfBirth.Value)
cmd.Parameters.AddWithValue("@started", Me.dtpStartingDate.Value)
cmd.ExecuteNonQuery()

Any ideas?

[G_Waddell]

Hi you're on the right path but the syntax for INSERT is different instead of
ColumnName = Value you do it like this:

INSERT INTO TableName(Column1,Column2)
VALUES (@Column1, @Column2)

[M.Waqas Aslam]

and also you can use delete command in place of update and insert command.
Regards