SQL Insert Into Syntax Error using C# 2013

Question

string Connstr = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Shahrivar92\Documents\Safa_co.accdb");
OleDbConnection oleconn = new OleDbConnection(Connstr);
OleDbCommand olecmd = new OleDbCommand();
olecmd.Connection = oleconn;         
olecmd.CommandText = string.Format("insert into info-ghrardad(gh-note,gh-tarikh,gh-mablagh,gh-shomareh,gh-modat,today-date)values(@gh-note,@gh-tarikh,@gh-mablagh,@gh-shomareh,@gh-modat,@today-date)");

oleconn.Open();
olecmd.Parameters.AddWithValue("@gh-note", Gh_name.Text);
olecmd.Parameters.AddWithValue("@gh-tarikh", Gh_date.Text);
olecmd.Parameters.AddWithValue("@gh-mablagh", Gh_mablagh.Text);
olecmd.Parameters.AddWithValue("@gh-shomareh", Gh_number.Text);
olecmd.Parameters.AddWithValue("@gh-modat", Gh_modat.Text);
olecmd.Parameters.AddWithValue("@today-date", TodayDate.Text);
olecmd.ExecuteNonQuery();          
oleconn.Close();

System.Data.OleDb.OleDbException was unhandled
  HResult=-2147217900
  Message=Syntax error in INSERT INTO statement.

OleDB doesn't support named parameters, as far as I remember - you need to use just ? as the placeholder in your SQL statement, and you can use just about any name for the parameter name - it only depends on the position of the parameters (the first one added matches the first question mark, the second parameter the second question mark and so on) — marc_s
– marc_s, Commented Dec 15, 2014 at 21:41

DRapp · Accepted Answer · 2014-12-18 04:07:59Z

2

Aside from marc_s comment about named parameters and using "?" as place-holder, the table an column names having "-" as the name is IMO a really bad naming convention. It is like you are telling the system to subtract two values.

That said, it may be that you need to both change the insert values as "?" place holders and add them in same sequence (which you already have), but also may need to wrap your column names within brackets, tick marks, whatever, such as

insert into [info-ghrardad] (
   [gh-note], [gh-tarikh], [gh-mablagh], [gh-shomareh], [gh-modat], [today-date] )
   values( ?, ?, ?, ?, ?, ?)");

then your parameters...

Additionally your date field might be part of the crash if you are putting in as just text instead of an actual date or date/time data type value if that is what the actual structure has. Similarly if amounts should be numeric and you are grabbing simple text, make sure it is in proper expected format for the columns.

answered Dec 18, 2014 at 4:07

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gord Thompson Over a year ago

Yes, the table and field names must be enclosed in square brackets because of the hyphens ("minus signs") they contain. Converting from @parameterName to ? placeholders is recommended but is not absolutely required, provided that the parameters are defined in the order in which their placeholders appear in the command text.

Collectives™ on Stack Overflow

SQL Insert Into Syntax Error using C# 2013

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related