Can't Insert Row using OleDbCommand

I modified the table in the query here to include a required field called Password.  After I modified the table I changed this code to reflect the table change.  Since having made those changes I now am unable to do an Insert which I could before.  It comes back with a message saying error in INSERT statment.  I don't see how any of this code would casue an error.

Does anyone know why??

objCmd = new OleDbCommand("INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) " +
             "VALUES (@FirstName, @LastName, @Handle, @Password)", objConn);

         objCmd.Parameters.Add("@FirstName", txtFirstName.Text);
         objCmd.Parameters.Add("@LastName", txtLastName.Text);
         objCmd.Parameters.Add("@Handle", txtHandle.Text);
         objCmd.Parameters.Add("@Password", txtPassword.Text);

         objConn.Open();
         objCmd.ExecuteNonQuery();
         objConn.Close();


THanks,

PT
ptrennumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DanielSchafferCommented:
Does the error get any more specific? Please post the exact error message.

Also, check to make sure that the data type for your password field is correct. Sounds stupid I know, but we've all done it. Well, at least I have. ;)
Jeff CertainCommented:
If you're generating the SQL dynamically, try this:

System.IO.StringBuilder sb;
sb = new System.IO.StringBuilder(256);
sb.Append("INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) VALUES (");
sb.Append(txtFirstName.Text + ",");
sb.Append(txtLastName.Text + ",");
sb.Append(txtHandle.Text + ",");
sb.Append(txtPassword.Text + ")");
objCmd = new OleDbCommand(sb.ToString(), objConn);
DanielSchafferCommented:
Another note... if you're using a database that supports stored procedures, using a SP would be preferable to having the SQL code generated in the server side code.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Jeff CertainCommented:
Good poins, DS.... I generally recommend the same, but assumed he's using Access since he's using OleDb objects. :)
DanielSchafferCommented:
I figured the same, but hey, you never know :)
ptrennumAuthor Commented:
Yes I'm using access and there's an error with

System.IO.StringBuilder sb;


THanks,
PT
ptrennumAuthor Commented:
THIS IS THE ERROR AS REPORTED


Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:


Line 32:
Line 33:          objConn.Open();
Line 34:          objCmd.ExecuteNonQuery();
Line 35:          objConn.Close();
Line 36:
 

Source File:newHunter.aspx    Line: 34

Stack Trace:


[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +122
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +67
   ASP.newHunter_aspx.SubmitBugHunter(Object s, EventArgs e) in D:\Projects\BugHunter\Website\newHunter.aspx:34
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1292

 
Jeff CertainCommented:
whoops...try:

System.Text.StringBuilder sb;

Jeff CertainCommented:
also... the problem may be with your parameters... you might want to check, but I think you need to declare a variable type for each parameter...
Jeff CertainCommented:
And... since you're trying to send text, let's add some single quotes...

System.IO.Text sb;
sb = new System.IO.StringBuilder(256);
sb.Append("INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) VALUES ('");
sb.Append(txtFirstName.Text + "','");
sb.Append(txtLastName.Text + "','");
sb.Append(txtHandle.Text + "','");
sb.Append(txtPassword.Text + "')");
objCmd = new OleDbCommand(sb.ToString(), objConn);
ptrennumAuthor Commented:
The StringBuilder refernece is still wrong it errors on all variations you have given me.

I can't believe this is causing me so much grief.  If I remove the password parameter it works but if I add it it doesn't.

PT
DanielSchafferCommented:
if you can't get StringBuilder to work, just do this....


objCmd.CommandText = "INSERT INTO tblBugHunter (FirstName, LastName, Handle, Password) " +
"VALUES('" + txtFirstName.Text + "','" + txtLastName.Text + "','" + txtHandle.Text + "','" + txtPassword.Text + "')";

....
Jeff CertainCommented:
okay... I use stringbuilder all the time in VB, but let's get rid of it for now...

string sb;
sb = "INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) VALUES ('";
sb = sb + txtFirstName.Text + "','";
sb = sb + txtLastName.Text + "','";
sb = sb + txtHandle.Text + "','";
sb = sb + txtPassword.Text + "'";
objCmd = new OleDbCommand(sb, objConn);
ptrennumAuthor Commented:
Ok - the string that is built looks like this:

INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) VALUES ('gf','fg','fg','fg')

I submit it with :

objConn.Open();
         objCmd.ExecuteNonQuery();
         objConn.Close();

and I still get the original error!!

However if I copy that string that is created into the query builder and run it from access it inserts the row????

Wonder Why??

Jeff CertainCommented:
Hmmmm... it may be some pickiness on Access's part... let's try without extra spaces:

string sb;
sb = "INSERT INTO tblBugHunter(FirstName,LastName,Handle,Password) VALUES('";
sb = sb + txtFirstName.Text + "','";
sb = sb + txtLastName.Text + "','";
sb = sb + txtHandle.Text + "','";
sb = sb + txtPassword.Text + "'";
objCmd = new OleDbCommand(sb, objConn);

One other possibility... wrap it in a try/catch block and see if you can catch a more specific error...
DanielSchafferCommented:
Heh, I've had things like this happen to me before. Access can be tricky like this. Did you check to see what objCmd.CommandText is set to? Also, I'm not sure how you were doing it before so check to make sure that objCmd.CommandType = CommandType.Text.
ptrennumAuthor Commented:
This is waht is returned from obj.CommandText:

INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) VALUES (@FirstName, @LastName, @Handle, @Password)

This line throws an error:

objCmd.CommandType = CommandType.Text

PT
DanielSchafferCommented:
Where did those SQL variables come from? Set objCmd.CommandText to sb. If that line you mention throws an error, then take it out... CommandType defaults to CommandType.Text, just wanted to make sure you hadn't changed it.
ptrennumAuthor Commented:
Here is all my code for this insert.  


OleDbConnection objConn;
         OleDbCommand objCmd;

         objConn = new OleDbConnection(
         "Provider=Microsoft.Jet.OLEDB.4.0;" +
         "Data Source=D:\\Projects\\BugHunter\\BugHunterDB.mdb");

objConn.Open();
 objCmd = new OleDbCommand("INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) " +
             "VALUES (@FirstName, @LastName, @Handle, @Password)", objConn);
objCmd.Parameters.Add("@FirstName", txtFirstName.Text);
         objCmd.Parameters.Add("@LastName", txtLastName.Text);
         objCmd.Parameters.Add("@Handle", txtHandle.Text);
         objCmd.Parameters.Add("@Password", txtPassword.Text);

Response.Write(objCmd.CommandText);
         //objCmd.ExecuteNonQuery();
         objConn.Close();
DanielSchafferCommented:
Okay, get rid of this:

objCmd = new OleDbCommand("INSERT INTO tblBugHunter (FirstName,LastName,Handle,Password) " +
             "VALUES (@FirstName, @LastName, @Handle, @Password)", objConn);
objCmd.Parameters.Add("@FirstName", txtFirstName.Text);
         objCmd.Parameters.Add("@LastName", txtLastName.Text);
         objCmd.Parameters.Add("@Handle", txtHandle.Text);
         objCmd.Parameters.Add("@Password", txtPassword.Text);

and replace it with Choasian's code:

string sb;
sb = "INSERT INTO tblBugHunter(FirstName,LastName,Handle,Password) VALUES('";
sb = sb + txtFirstName.Text + "','";
sb = sb + txtLastName.Text + "','";
sb = sb + txtHandle.Text + "','";
sb = sb + txtPassword.Text + "'";
objCmd = new OleDbCommand(sb, objConn);
ptrennumAuthor Commented:
I did that already and I still get the insert error - but if I just copy the query string (sb) into access and run it from access It works.

PT
Jeff CertainCommented:
There's a missing )... (I hate not being able to debug code before I post it...)

string sb;
sb = "INSERT INTO tblBugHunter(FirstName,LastName,Handle,Password) VALUES('";
sb = sb + txtFirstName.Text + "','";
sb = sb + txtLastName.Text + "','";
sb = sb + txtHandle.Text + "','";
sb = sb + txtPassword.Text + "')";
objCmd = new OleDbCommand(sb, objConn);
ptrennumAuthor Commented:
yes I noticed that and changed it.

PT
Jeff CertainCommented:
Out of curiousity, are you able to connect to the database elsewhere in your code? This looks suspicious to me... in the past, I've used \ not \\
"Data Source=D:\\Projects\\BugHunter\\BugHunterDB.mdb");
Jeff CertainCommented:
also.... your new password field... does it have a maximum length shorter than the text you're trying to submit?
DanielSchafferCommented:
\\ is the C# escape sequence for using a \ in a string.
Jeff CertainCommented:
Change your password field name to pwd and try again...
http://support.microsoft.com/default.aspx?scid=kb;en-us;892608

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptrennumAuthor Commented:
the password field does not have a max length shorter then the string.

I just deleted the table and re created it also still no change yet when I run the origianl code without the new password field it submits just fine.


PT

Jeff CertainCommented:
Alternately, use [Password] as the field reference in the query string. (At least, this trick works in SQL server... and I think it works in Access, too... since I've used it to get around field names like [First Name]...)

string sb;
sb = "INSERT INTO tblBugHunter(FirstName,LastName,Handle,[Password]) VALUES('";
sb = sb + txtFirstName.Text + "','";
sb = sb + txtLastName.Text + "','";
sb = sb + txtHandle.Text + "','";
sb = sb + txtPassword.Text + "')";
objCmd = new OleDbCommand(sb, objConn);
DanielSchafferCommented:
Alternatively, it might also work if you reference it by using [Password]...

Good find Chaosian... seems so obvious now that I think about it.
Jeff CertainCommented:
Daniel....

Yeah, I thought it was obvious too.... right after smacking myself in the head and muttering "Doh!" :)
ptrennumAuthor Commented:
You guys rock - You're both A+ in my book!!  Points for all!!

Thanks
PT
Jeff CertainCommented:
Hey, I'm just glad we finally saw the obvious!! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.