Getting error in insert statement in vb.net and ms access

Question

0.00/5 (No votes)

See more:

Hi
below is my code snapshot where i wanted to insert values in Inwards table, I have stores all variables like command,con in module. I am not getting any error but when i debugged then i got exception message as "Syntax error in INSERT INTO statement." plz help me,

VB

str = "Insert Into Inwards (SerialNumber,LetterNumber,DateOfEntry,Subject,From,LetterType,AssignedTo,BeltNumber,DateOfIssue) values(" + txtSerialNo.Text.Trim + "," + txtLetterNumber.Text.Trim + ",'" + lblDate.Text.Trim + "','" + txtSubject.Text.Trim + "','" + txtFrom.Text.Trim + "','" + cbType.Text.Trim + "','" + txtAssignedTo.Text.Trim + "','" + txtBeltNumber.Text.Trim + "','" + dtDOI.Text.Trim + "')"
               cmd = New OleDb.OleDbCommand(str, con)
               con.Open()
               cmd.ExecuteNonQuery()
               MsgBox("Inserted Successfully new Inward..")

               'data()
               'clearText()
           Catch ex As Exception

           Finally
               con.Close()

           End Try

Posted 4-Jun-13 23:54pm

Shaikh Nadeem

Updated 5-Jun-13 0:07am

Pheonyx

v2

Add a Solution

Comments

Pheonyx 5-Jun-13 6:09am

Question 1... What type are all of the columns? You appear to have some date columns but passing in strings.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-05T00:20:00

Solution 1

If DateOfIssue field is date data type, please, use query like this:

SQL

INSERT INTO Table1 (DateField1)
VALUES(#2013/06/05#)

for text:

SQL

INSERT INTO Table1 (TextField1)
VALUES('any text')

for numeric values:

SQL

INSERT INTO Table1 (NumericField1)
VALUES(1000)

Have a look here:
Add, edit, and delete data (MS Access)[^]
INSERT INTO Statement (Microsoft Access SQL)[^]

Posted 5-Jun-13 0:20am

Maciej Los

Comments

Shaikh Nadeem 5-Jun-13 6:25am

I have taken all datafields as text only still showing same error on debugging.

Maciej Los 5-Jun-13 6:34am

Do you want to tell that all fields in your table are text data type?

Shaikh Nadeem 5-Jun-13 6:42am

yes,But thats not the problem

Maciej Los 5-Jun-13 6:54am

OK, i got it.
Please use brackets: [] to define destination fields, especially: [FROM] ;)

Shaikh Nadeem 5-Jun-13 7:44am

Still not working getting some other error as "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Maciej Los 5-Jun-13 8:08am

Please, read error message carefuly...
Which field is primary key in table? Does it possible that you're trying to insert duplicated data?

Shaikh Nadeem 5-Jun-13 8:23am

no,SerialNumber is primary key in the table and that key is autogenerated not user entered..

Maciej Los 5-Jun-13 8:24am

So, why are you trying to add this values to this field?

Please, use "Reply" widget!!!

Shaikh Nadeem 5-Jun-13 8:27am

bcoz i am directly taking value with disabled text box with code :dst.table("Inwards").rows.count+1

Shaikh Nadeem 5-Jun-13 8:30am

Is there any option to generate numbers automatically in msAccess2010 like we use in sqlserver with identity keyword?

Maciej Los 5-Jun-13 8:49am

Have you seen linked articles?
Please, stop asking, start teaching yourself. Sorry.

Dave Kreskowiak · Answer 2 · 2013-06-05T03:59:00

Google for "SQL Injection Attack" to see why what you're doing is so bad. It's a HUGE security risk.

Then Google for "VB.NET SQL Parameterized Queries" on how to fix it.

Yes, you can make an AutoNumber column in an Access database. If you made the database in Access it would have been staring you right in the face when you picked a field type for the column.