Error in Insert Command

Question

1.00/5 (1 vote)

See more:

I just wanna ask about something in my code
the scenario in this code is adding a data in the database with a lot of fields, its almost 55 fields to be inserted with data.

When I run this command and type improper words I notice that it will save the data but when i I proper and right words for each fields i get an error of

OledbException was Unhandled

Syntax Error(Missing Operator) in Query Expression

This is My Code

Dim importantmessage As String
importantmessage = MsgBox("Filling Up Complete, Are you sure you want to save this?", vbYesNo)
If importantmessage = vbYes Then
cmdInsert.CommandText = "INSERT INTO personinfo (SurName,FirstName,MiddleName,Present_CurrentAddress,ProvincialAddress,Noofchildren,Height,weight,birthdate,age,birtrhplace,gender,civilstatus,educationalattain,schoolname,course,yearsofstudy,collegestatus,trainingschoolname,positiontrained,yearstrained,coursetrained,detachment,Region,effdate,workstatus,empnumber,positionemployed,yearsofemployed,sssnumber,tinnumber,nbi,nbidate,pnp,pnpdate,priorexp,gdexp,guardposition,uniformdate,millitary,LOS,previousemp,licno,licdate,licexp,dateofemp,badgeno,neurocenter,neuroresult,neurodate,drugcenter,marijuana,shabu,drugresult,histories) VALUES ('" & txtsurname.Text & "','" & txtfirstname.Text & "','" & txtmiddlename.Text & "','" & txtaddress.Text & "','" & txtprovince.Text & "','" & txtchildren.Text & "','" & txtheight.Text & "','" & txtweight.Text & "','" & datepicker1.Text & "','" & txtage.Text & "','" & txtbplace.Text & "','" & cmbgender.Text & "','" & cmbcivil.Text & "','" & ComboBox4.Text & "','" & TextBox11.Text & "','" & TextBox10.Text & "','" & TextBox9.Text & "','" & TextBox8.Text & "','" & TextBox6.Text & "','" & ComboBox3.Text & "','" & TextBox7.Text & "','" & TextBox1.Text & "','" & ComboBox2.Text & "','" & ComboBox7.Text & "','" & MaskedTextBox3.Text & "','" & ComboBox1.Text & "','" & TextBox29.Text & "','" & TextBox30.Text & "','" & TextBox31.Text & "','" & TextBox5.Text & "','" & TextBox4.Text & "','" & TextBox3.Text & "','" & MaskedTextBox4.Text & "','" & TextBox2.Text & "','" & MaskedTextBox5.Text & "','" & TextBox24.Text & "','" & TextBox25.Text & "','" & ComboBox11.Text & "','" & MaskedTextBox6.Text & "','" & TextBox26.Text & "','" & TextBox28.Text & "','" & TextBox27.Text & "','" & TextBox32.Text & "','" & MaskedTextBox2.Text & "','" & MaskedTextBox1.Text & "','" & MaskedTextBox7.Text & "','" & TextBox12.Text & "','" & TextBox14.Text & "','" & ComboBox6.Text & "','" & MaskedTextBox8.Text & "','" & TextBox15.Text & "','" & ComboBox5.Text & "','" & ComboBox12.Text & "','" & MaskedTextBox9.Text & "','" & RichTextBox1.Text & "');"
cmdInsert.CommandType = CommandType.Text
cmdInsert.Connection = cnnOLEDB
cmdInsert.ExecuteNonQuery()
End If
If importantmessage = vbNo Then
Me.Show()
End If

I hope you will find the error Thank You.

Posted 3-Jun-13 16:24pm

Crixalis Paul

Add a Solution

Comments

Ron Beyer 3-Jun-13 22:38pm

That statement is a nightmare, you should look at how to execute SQL statements with parameters, it will help you a LOT, nobody is going to go through that and find your error.

Crixalis Paul 3-Jun-13 22:42pm

Ron Beyer, your Right its really a nightmare and i also know parameters that your talking about and if i do that code by parameter, it will be the my cause of death :) just kidding I hope someone will help me further, anyway thank you for your suggestion. I appreciate it

1 solution

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)

Sergey Alexandrovich Kryukov · Accepted Answer · 2013-06-03T16:39:00

Solution 1

This code does not actually deserve funding a bug. To start with, you should rewrite it from scratch get rid of string concatenation with string data from UI. This is very inefficient, and this text can be anything, including… fragment of SQL code. It makes your application wide open to the well-know exploit called SQL injection: http://en.wikipedia.org/wiki/SQL_injection[^].

This is a good illustration of it: http://xkcd.com/327/[^].

What to do? Please see my past answers:
hi name is not displaying in name?[^],
EROR IN UPATE in com.ExecuteNonQuery();[^].

Use: http://msdn.microsoft.com/en-us/library/ff648339.aspx[^].

And then, if you use SQL properly, you bug may go. If not, you will have to address this problem again, but at least you won't need to do useless double work.

—SA

Posted 3-Jun-13 16:39pm

Sergey Alexandrovich Kryukov

Comments

Crixalis Paul 3-Jun-13 22:48pm

Thank you for your solution, Is it fine if I don't use Parameters because the system i will make will only implement in a little business. or I just really need to use parameters

Sergey Alexandrovich Kryukov 3-Jun-13 22:56pm

It's not OK. Fist do parameters, to even have something to talk about. Small business or not, things should be done properly, otherwise you will get sunk in problems.
—SA

Crixalis Paul 3-Jun-13 22:59pm

Thanks for what you had said. Is this the example of parameters you where talking about?

cmdInsert.Parameters.Add(cmdInsert.CreateParameter).ParameterName = "surname"
cmdInsert.Parameters.Item("surname").Value = TextBox1.Text

if not, can you give an example?

Sergey Alexandrovich Kryukov 3-Jun-13 23:02pm

You will find some examples here:
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

—SA

Crixalis Paul 3-Jun-13 23:04pm

Sir. Thank you for your time discussing it with me. I will do what you have suggested. Its a great honor meeting you.

Sergey Alexandrovich Kryukov 3-Jun-13 23:07pm

Great.
And you are very welcome. Good luck, call again.
—SA