INSERT sql script

[CookieNZ]

I am tring to use an inser sql to update the database.

insertSQL = "INSERT INTO Supportcall(ID,Contact,Description)" & " VALUES(txtcallnumber.text,txtcallcontact.text,txtcalldescription.text)"

where txtcallnumber.text are cells in the form.

When I run the app, the exception message is No Value Given for one or more parameters.

The access 2k table only has the 3 fields outlined above

If I 'manually' insert the record it works fine.

insertSQL = "INSERT INTO Supportcall(ID,Contact,Description)" & " VALUES('2','Fred Atkins','This is the description')"

Furthermore, I have a declared the fields as
dr = dssupport.Tables("Supportcall&quot.NewRow()
dr("ID&quot = txtcallnumber.Text
dr("Description&quot = txtcalldescription.Text
dr("Contact&quot = txtcallcontact.Text

 

[link9]

You have to extract the values from your objects... what you've done there is pass in their names -- notice the quotes, indicating a string literal rather than an evaluated statement.

insertSQL = "INSERT INTO Supportcall(ID,Contact,Description)" & " VALUES('" & txtcallnumber.text & "','" & txtcallcontact.text & "','" & txtcalldescription.text & "')"

But check out the FAQ on string concatenation in vb.net. There's a better (read - faster) way to skin this cat.

faq855-1882


paul

 

[CookieNZ]

Brilliant, thanks very much.

I have read the FAQ, but since I only need to insert once, I will set the string manually.

sqlscript = "INSERT INTO Supportcall(ID,Description,Date,Status,Operator,Action,Customer,Contact)" & " VALUES('" & txtcallnumber.Text & "','" & txtcalldescription.Text & "','" & txtdate.Text & "','" & txtstatus.Text & "','" & txtoperator.Text & "','" & txtaction.Text & "','" & cmbcustomer.Text & "','" & txtcallcontact.Text & "')"

This still gives me an error when I run the application. Any ideas?

 

[link9]

Probably have a single tick around a field that isn't supposed to be text. That'd be my guess.

But as for building your string, you really should use the stringbuilder. Doing it that way abuses your memory. But it's your app. ;-)

 

[krisbrixon]

I will assume you are using SQL Server.

Some things to try:

1. You are using reserved words for column names so surround them with []
i.e. sqlscript = "INSERT INTO Supportcall([ID],[Description],[Date],[Status],[Operator],[Action],[Customer],[Contact])...."

2. MSSQL is a pain about spacing so try ) VALUES ( instead of )VALUES(

3. If you have Query Analyzer then make the script work there first then move it to code.

4. If any of those columns are numbers then they do not need the '' around the value.

Other than those things the script looks like it should work. I hope one of these things solves your problem.