Can't Insert Row using OleDbCommand

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. ;)

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);

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.

Good poins, DS.... I generally recommend the same, but assumed he's using Access since he's using OleDb objects. :)

I figured the same, but hey, you never know :)

Yes I'm using access and there's an error with

System.IO.StringBuilder sb;


THanks,
PT

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

 

whoops...try:

System.Text.StringBuilder sb;

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...

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);

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

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 + "')";

....

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);

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??

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...

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.

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

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.

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();

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);

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

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);

yes I noticed that and changed it.

PT

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");

also.... your new password field... does it have a maximum length shorter than the text you're trying to submit?

\\ is the C# escape sequence for using a \ in a string.

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

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);

Daniel....

Yeah, I thought it was obvious too.... right after smacking myself in the head and muttering "Doh!" :)

You guys rock - You're both A+ in my book!!  Points for all!!

Thanks
PT

Hey, I'm just glad we finally saw the obvious!! :)