CODEWITHSUNDEEP
c#sqlms-access
rvpals
rvpals

Reputation: 189

Insert Access SQL Statement Not working in C#

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.

Upvotes: 0

Views: 742

Answers (1)

Parfait
Parfait

Reputation: 107567

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);
}

Upvotes: 2

Related Questions