Insert querry errors

Question

2.33/5 (2 votes)

See more:

I am getting error in insert statement. this querry is executing at backend in access.but from front end I am getting error like "syntax error in insert into statement" plz help me.
the datatype of all coloumns are text only.

C#

OleDbCommand cmd1 = new OleDbCommand("INSERT into " + main1.text + "marketdetails(comame,date1,open,low,high,close,vol)values('"+strticker+"','" + strdte + "'," + strOpen + "," + strLow + "," + strHigh + "," + strClose + "," + strVol + ")", conn);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();

Posted 9-Jan-14 9:00am

hussain548

Updated 9-Jan-14 9:04am

bowlturner

v2

Add a Solution

Comments

ZurdoDev 9-Jan-14 15:08pm

You'll need to fix the syntax errors. Where are you stuck?

1. This will end up being INSERT INTO <whatever is="" in="" main1.text="">marketdetails. Is that right?
2. Dates in Access need # around them, if I recall correctly.

bowlturner 9-Jan-14 15:09pm

What is main1.text? also after strdte you are not adding "'" in the strings any more.

2 solutions

Solution 2

I thing query open and close brakes not specified correctly . pls try use this code temporary

"INSERT into " + main1 + "marketdetails(comame,date1,open,low,high,close,vol)values('" + strticker + "','" + strdte + "','" + strOpen + "','" + strLow + "','" + strHigh + "','" + strClose + "','" + strVol + "')"

But, always use OleDbCommand with Parameter

Posted 9-Jan-14 10:13am

Paramaa

Updated 9-Jan-14 10:21am

v2

Comments

Dave Kreskowiak 9-Jan-14 16:47pm

I wouldn't even suggest using this code AT ALL as it doesn't solve the problem of building an SQL query using string concatenation. Better to learn to do it the correct way in the first place than to go back and have to rework the code later.

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)

OriginalGriff · Accepted Answer · 2014-01-09T09:22:00

Solution 1

There are a couple of things here, that make this a bad move, and cause your problem.
The immediate problem is that your string may be wrong:

"INSERT into " + main1.text + "marketdetails(...

Assume main1.Text contains "MyTable" and concatenate the string together and yoy get the string

"INSERT into MyTablemarketdetails(...

This may be a problem depending on what the user types.
The second is that your query is very dangerous: it is wide open to SQL Injection attack. You should never build SQL commands by concatenating strings - always use parametrized queries instead.

C#

OleDbCommand cmd1 = new OleDbCommand("INSERT INTO " + main1.text + "marketdetails(comame,date1,open,low,high,close,vol) VALUES(@TKR, @DAT, @OPN, @LOW, @HGH, @CLS, @VOL)", conn);
cmd1.Parameters.AddWithValue("@TKR", strticker);
cmd1.Parameters.AddWithValue("@DAT", strdte);
cmd1.Parameters.AddWithValue("@OPN", strOpen);
cmd1.Parameters.AddWithValue("@LOW", strLow);
cmd1.Parameters.AddWithValue("@HGH", strHigh);
cmd1.Parameters.AddWithValue("@CLS", strClose);
cmd1.Parameters.AddWithValue("@VOL", strVol);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();

Personally, I would find a way to eliminate the main1.Text as well, but that may cure your problem for now.

Posted 9-Jan-14 9:22am

OriginalGriff

Comments

Matt T Heffron 9-Jan-14 15:26pm

In your code, you missed the space in the "marketdetails...." string that you mentioned!

OriginalGriff 9-Jan-14 15:38pm

I'm assuming that he has a range of tables called "MyTablemarketdetails", "YourTablemarketdetails" and so forth, and he uses the main1.text to select which he is going to use. A space between them would cause an SQL syntax error because you can't have two table names in that close a proximity!

Matt T Heffron 9-Jan-14 15:40pm

Fair enough!

hussain548 9-Jan-14 15:52pm

I appreciate your help sir. its working.actually I am inserting from txt file which consist of million of lines so for inserting it is taking a lot of time, how can I reduce the time of inserting.

hussain548 9-Jan-14 15:55pm

By the way that code is working sir, I need to insert fastly as soon as possible.

Ron Beyer 9-Jan-14 16:22pm

Don't use text files... They aren't meant to be databases.

hussain548 9-Jan-14 17:39pm

i want to insert from txt file to access database but it is taking a lot time. so there is any way to reduce the time.

OriginalGriff 10-Jan-14 4:20am

There is no bulk insert for Access DB's, so you have to get creative.
The first thing to do is find out where it's slow: reading the input or adding to the DB.
Then you can look at speeding things up. So start with the Stopwatch class and get some numbers that you can use to tell both where your bottleneck is, and how much you are improving it each time. Remember to time a number of runs so you remove the effects of any caching / other system operations.
Then you can start working out how to speed things up - but we can't help that much, certainly without your code and data we can give little except advice. So get some numbers and see where it's taking time, and how much time it's taking.

Yvan Rodrigues 9-Jan-14 19:52pm

+5 for using a prepared statement