Click here to Skip to main content
15,908,673 members
Please Sign up or sign in to vote.
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
Updated 9-Jan-14 9:04am
v2
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.

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.
 
Share this answer
 
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.
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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900