SQL Errors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get a syntax error every time I try to use an INSERT statement. My statement is correct. Strings seem to be the problem. The primary key is a number and that's the only field I can insert

Doesn't Work

db.InsertCommand.CommandText = "INSERT INTO Person(ID, First, MI, Last, Address, Phone, Parents_Names, School, Grade, Comments, Type) VALUES (9, '" + name.Text + "', '" + mi.Text + "', '" + last.Text + "', '" + address.Text + "', '" + phone.Text + "', '" + parents.Text + "', '" + school.Text + "', '" + grade.Text + "', '" + comments.Text + "', 'S')"

Doesn't Wor

db.InsertCommand.CommandText = "INSERT INTO Person(Last) VALUES('Smith')"

Works

db.InsertCommand.CommandText = "INSERT INTO Person(ID) VALUES(3)";
 
If you set the commandtext and print it to the debug window, then put it
into Query Analyzer or whatever tool you use to fire queries against your
DB, will the first one work? Anything that won't work in QA for instance
isn't going to work via an ADO.NET command, so that's the first place I'd
check. In your example with Smith that doesn't work, you aren't giving the
PK a value (unless Last is the PK) and you can't insert nulls into a PK
field. If the PK field is an autoincrement field or identity, send in a
negative number and see if that doesn't fix it.

BTW, which specific error are you getting? Does it say Primary Key
violation for isntance, or concurrency exeption or what? That'll definitely
help you track it down.

Also, I'd REALLY recommend not concatenating your command text and use
Parameters instead.
db.InsertCommand.CommandText = "INSERT INTO Person(ID, First, MI, Last,
Click to expand...
Address, Phone, Parents_Names, School, Grade, Comments, Type) VALUES (@ID,
@FirstName, @MI, @Last, @Address, @Phone,@Parents,@School, @Grade,
@Commands, @WhateverFieldSRepresents)

cmd.Parameters.Clear();

cmd.Parameters.Add("@ID", SqlDBType.Int).Value = 9;
//do the same for the rest of the param values.

This will give you a much cleaner implementation, get you around the issue
of people having an apostrophe in their name and minize the risks of
Injection attacks. Furthermore, I've been told that there is a performance
difference. While a query like this will probably execute pretty quickly
(especially if you are only firing it once) either way, over time user bases
grow....things change, and saving resources becomes necessary not just a
luxury.

You may also want to consider making this thing a stored proc (That'd
definitely be worth doing if at all possible), and just passing in the
params. This way, you can test your proc in QA, verify that it works, and
be done with it.

HTH,

Bill

Eric said:
I get a syntax error every time I try to use an INSERT statement. My
Click to expand...
statement is correct. Strings seem to be the problem. The primary key is a
number and that's the only field I can insert.
Doesn't Work:

db.InsertCommand.CommandText = "INSERT INTO Person(ID, First, MI, Last,
Click to expand...
Address, Phone, Parents_Names, School, Grade, Comments, Type) VALUES (9, '"
+ name.Text + "', '" + mi.Text + "', '" + last.Text + "', '" + address.Text
+ "', '" + phone.Text + "', '" + parents.Text + "', '" + school.Text + "',
'" + grade.Text + "', '" + comments.Text + "', 'S')";
 
The Error I'm getting when I print the exception is Syntax Error in INSERT INTO statement. "Last" is a last name field in my database. I've compared what I did with other projects and everything is exactly the same. I don't get it at all

btw, the DB is Access if that helps any.
 
Eric:

I made a table with those fields (just guessed at the types) in Access and
didn't have a problem, so I'm wondering if a DataType conflict is happening
or one of a few other things.

Does it fail on every record or is it a specific one? If an Apostrohpe was
used in this case, like O'Flynn, that would cause a syntax error.

If the command text taken from the debug window will run in Access, and you
are getting a syntax error, that is usually the use of a reserved word as a
column name that can cause the problem, but First and Last aren't reserved
so that's not it. I wasn't sure from your last post, but if you use the
commandtext from the debug and put it in Access, will it work? For
instance, use the same text and right before you fire executenonquery...use
Debug.WritleLine(myCommand.CommandText);

Then if it fails, copy and paste the command text into access and see if it
runs.

If not, can you post the exact line from the Debug.WriteLine? I can try
running it here and see if it works b/c so far it does...just wondering if
the data may be a problem.

Bill




Eric said:
The Error I'm getting when I print the exception is Syntax Error in INSERT
Click to expand...
INTO statement. "Last" is a last name field in my database. I've compared
what I did with other projects and everything is exactly the same. I don't
get it at all.
 
Back
Top