0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rvpals
  • 177
  • 1
  • 3
  • 15
  • 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 Dec 18 '16 at 02: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 Dec 18 '16 at 02: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 Dec 18 '16 at 02: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 Dec 18 '16 at 02:39
  • 1
    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 Dec 18 '16 at 03:21
  • Thanks, Kushal. I'm sure your simplified query would work. I'm dealing with query that insert values that contains special characters. – rvpals Dec 18 '16 at 17:31

1 Answers1

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);
}
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks. I'm going to try this one with parameterized query. – rvpals Dec 18 '16 at 17:30
  • 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. – rvpals Dec 18 '16 at 20:52
  • 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`. – Parfait Dec 19 '16 at 00:50
  • I'm using ODBC connection with Microsoft Access. – rvpals Dec 19 '16 at 01:15
  • I'm replacing @ with ? in my query. Now get the exception: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 14. – rvpals Dec 19 '16 at 01:20
  • Interestingly, it's now expecting 14 parameters. What's going on here. I'm sick of using Access to handle data storage. :) – rvpals Dec 19 '16 at 01:21
  • See updated code using `OdbcConnection` method and updated parameters collection. If it worked for this [OP's question](http://stackoverflow.com/a/1536062/1422451), it should yours! – Parfait Dec 19 '16 at 16:32
  • Unfortunately, I have no more time to mess with access, I ended up just export everything I got to MySQL. So far it works out pretty well. Instead I use my time in business logic instead of low level storage function. Thanks for all the comments though. – rvpals Feb 15 '17 at 13:58