DB Insert Syntax Issues? (MS Access)

J

joe_pool_is

Well-known member
Joined
Jan 18, 2004
Messages
451
Location
Texas
My code:
Code: 
strSql = 
"INSERT INTO people 
(Name, Email, Username, Password, Website, Dated) " 
+ "VALUES (\"" +
strName + "\", \"" + strEmail + "\", \"" + strUserId + "\", \""
+ strPassword + "\", \"" + strUrl + "\", \"" + strDate + "\");";
Command1 = new OleDbCommand(strSql, Connection1);
try 
{
Connection1.Open();
Command1.ExecuteNonQuery();
} 
catch (Exception ex) 
{
lblCreateMsg.Text = "Insert Database error:<br />";
lblCreateMsg.Text += ex.Message + "<br />" + strSql;
return;
} finally {
Connection1.Close();
}
produces this SQL statement:
Code: 
INSERT INTO people
(Name, Email, Username, Password, Website, Dated)
VALUES 
("John Doe", "[email="jdoe123@domain.com"]jdoe123@domain.com[/email]", "jdoe123",
"6184D6847D594EC75C4C07514D4BB490D5E166DF", "", 
"9/17/2005 2:07:06 PM");
but catches this error that is written to lblCreateMsg:
Code: 
Syntax error in INSERT INTO statement.
Am I using a reserved word? What would cause this problem?
 
Your string names need to be:

VALUES(Joe)

not

VALUES("Joe")

Also, you are leave yourself wide open for SQL Injection attacks. If you use parameters (and you can use them on in-line SQL too; not just stored procedures), you wont have to worry about these syntaxtical errors or SQL injection attacks.
 
bri189a said:
Your string names need to be:

VALUES(Joe)

not

VALUES("Joe")
Click to expand...
Ah! Simple stuff. It so often turns out that way, too. Thanks.

bri189a said:
Also, you are leave yourself wide open for SQL Injection attacks. If you use parameters (and you can use them on in-line SQL too; not just stored procedures), you wont have to worry about these syntaxtical errors or SQL injection attacks.
Click to expand...
I didnt know Access used parameters. Are they stored in the Access database, or constructed "on the fly" in my code? If you know of any good sites that teach this, Id be grateful.
 
bri189a said:
Your string names need to be:

VALUES(Joe)

not

VALUES("Joe")
Click to expand...
Poo-doo! This time I got:
Code: 
Insert Database error:
Syntax error in INSERT INTO statement.
INSERT INTO people (Name, Email, Username, Password, Website, Dated) VALUES (John Doe, jdoe123@domain.com, jdoe123, 
6184D6847D594EC75C4C07514D4BB490D5E166DF, , 9/17/2005 4:27:29 PM);
 
Put brackets around the password and name fields.
This defines them as fields, in case they are actually keywords

Make sure the password data can actually fit into the field.

And Access takes a different default date format than the one you are
displaying. Either change the format of the column in Access or the
format you are inserting into the database.

Search these forums for a little tutorial on using parameters to create sql commands. I believe plausiblydamp wrote the post.
 
And dont catch System.Exceptions!

Or at least catch a more specific exception before it

try
{
}
catch (System.Data.Oledb.OledbException dbe)
{
}
catch (Exception e)
{
}
 
Back
Top