Insert Access SQL Statement Not working in C#

Question

I have C# program. My code generated the SQL statement like this:

INSERT INTO [TWEET_RESULT] ([SearchKeyword], [TweetID], [RetweetCount], [URL], [Body], [PostedTime], [Sentiment])
VALUES ("BVN", "tag:search.twitter.com,2005:528481176659697664", "1", "http://twitter.com/austin_ebi/statuses/528481176659697664", "Pls what is BVN going to be used for? Why can't every Nigerian just have 1 National Insurance number to be used for all purposes?", "2014-11-01T09:38:25.000Z", "NEUTRAL")

When I execute this SQL statement in Access database, it works by inserting the record correctly.

However, when I run the same query in my C# code to insert the record. It does not do anything.

The reason I used double quote to enclose the field value is because some of the field values can contain special characters. The same query, when using only single quote to enclose the field value, it worked before.

When I check the exception message in C# code. It says:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

I dont thin the issue is your query. Try a simple select query and see if that works. Select 1 record and 1 from a table and see if that works. — CodingYoshi
– CodingYoshi, Commented Dec 18, 2016 at 2:34
Sorry, I don't understand what you mean by doing select 1 record. It appears that insert sql statement will work when I run it inside of Microsoft Access. When my C# code try to execute the same sql query, it runs into exception. — rvpals
– rvpals, Commented Dec 18, 2016 at 2:37
How to enclose a field value that contains special characters like \, comma etc. I can't use single quote because of special character. If I use double quote, it bombs out from C# code. Strange. — rvpals
– rvpals, Commented Dec 18, 2016 at 2:38
i am saying you have shown us your insert query but the error has nothing to do with your insert query. Even if you do a select it will fail — CodingYoshi
– CodingYoshi, Commented Dec 18, 2016 at 2:39
First try with this qry then check whether its query issue or ur code.INSERT INTO [TWEET_RESULT] ([SearchKeyword], [TweetID], [RetweetCount], [URL], [Body], [PostedTime], [Sentiment]) VALUES ("BVN", "test", "1", "test", "test, "2014-11-01T09:38:25.000Z", "NEUTRAL") — Kushal Patil
– Kushal Patil, Commented Dec 18, 2016 at 3:21

Parfait · Accepted Answer · 2016-12-19 16:30:07Z

2

Consider a parameterized query which avoids any need for quote enclosure or escaping:

OdbcConnection conn = new OdbcConnection(connString);

String strSQL = "INSERT INTO [TWEET_RESULT] ([SearchKeyword], [TweetID], [RetweetCount], [URL], [Body], [PostedTime], [Sentiment]) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?);"

OdbcCommand cmd = new OdbcCommand(strSQL, conn);
cmd.Parameters.Add("Search", OdbcType.Varchar).Value = "BVN";
cmd.Parameters.Add("TweetID", OdbcType.Varchar).Value = "tag:search.twitter.com,2005:528481176659697664";
cmd.Parameters.Add("Retweet", OdbcType.Varchar).Value = "1";
cmd.Parameters.Add("URL", OdbcType.Varchar).Value = "http://twitter.com/austin_ebi/statuses/528481176659697664";
cmd.Parameters.Add("Body", OdbcType.Varchar).Value = "Pls what is BVN going to be used for? Why can't every Nigerian just have 1 National Insurance number to be used for all purposes?";
cmd.Parameters.Add("PostedTime", OdbcType.Varchar).Value = "2014-11-01T09:38:25.000Z";
cmd.Parameters.Add("Sentiment", OdbcType.Varchar).Value = "NEUTRAL";

try
{
   conn.Open();

   Int affectedRows = cmd.ExecuteNonQuery();    
   Console.WriteLine("Affected Rows: {0}", affectedRows);
}
catch (Exception ex)
{
   Console.WriteLine(ex.Message);
}

edited Dec 19, 2016 at 16:30

answered Dec 18, 2016 at 17:02

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

8 Comments

rvpals Over a year ago

Thanks. I'm going to try this one with parameterized query.

rvpals Over a year ago

I tried the parameterized query, I instead got this exception: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 7. I verified that it's entered 7 values as the code does, but it says too few parameter.

Parfait Over a year ago

Seems no param value is being passed. I'm not a C# expert and grabbed this from a tutorial site. Are you using an OleDbConnection or OdbcConnection with MS Access? Named params may not be allowed. Try using question marks as placeholders (only in SQL statement) and remove @ in .AddWithValue.

rvpals Over a year ago

I'm using ODBC connection with Microsoft Access.

rvpals Over a year ago

I'm replacing @ with ? in my query. Now get the exception: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 14.

|

Collectives™ on Stack Overflow

Insert Access SQL Statement Not working in C#

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related