Thread: C# and MSSQL 2008

We are having problems with a bit of C# after upgrading to MSSQL 2008 (from MSSQL 2005 Express)

I have stripped out the un-required code to focus on the problem code.

C#/VB.NET Code:

DateTime shippedTime = DateTime.Now.AddDays(-5);
using (SqlConnection dbconn = new SqlConnection(DB.GetDBConn())) {
dbconn.Open();
using (IDataReader rs = DB.GetRS(String.Format("select OrderNumber, FirstName, Email from Orders where ShippedOn < '{0}' and SentReviewEmail = 0", shippedTime.ToString("d",CultureInfo.CreateSpecificCulture("en-us"))))) {
int orderNu = 0;
while (rs.Read()) {
orderNu = rs.GetInt32(0);
// PROBLEM CODE
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "UPDATE Orders set SentReviewEmail = 1 WHERE OrderNumber = " + orderNu;
cmd.Connection = dbconn;
cmd.ExecuteNonQuery();
// END PROBLEM CODE
}}
dbconn.Close();
} 


The code normally returns 20-30 orders and uses their ordernumber for finding out what products they brought and for updating we have sent them an email.

However the problem is this code that updates the database to say we have sent the email does not work. I have tested this script without the database update and the email works fine.

How can I update the database? Why does this code no longer work?
I have written the code in VS2008 and it shows no issues or problems.

Did you do a straight upgrade or did you install sql 2008 and then move your data into it? The reason I ask is because SentReviewEmail is not a field out of the box in the Orders table which may be causing you this issue.

Do you have the exact error you are getting if this is not the problem?

Moved the data over. SentReviewEmail is a custom field I created which does exist.

Sadly I do not know the error message as I can not replicate the issue on my local (local is running 2005 express)

All the SQL statements work it just seems cmd.ExecuteNonQuery(); is causing me problems.

Could you try using just CommandType.Text; instead of System.Data.CommandType.Text; ? Also, are you doing a cmd.Dispose() somewhere?

Ah yes, sloppy coding. Change to CommandType.Text;

I am not using cmd.Dispose() any where. I just attempted with dispose after the execute query with no joy.

It should, however it works. If I cut out the bit of code highlighted as problem code the script runs fine and emails the customer. However if I do not update that I have emailed them the whole process is pointless, they will just get endless emails.

I included the other parts of the code in case there was a known issue with 2008 and the method I was using.

Like I said the code works fine on 2005 express but not on 2008.

FIXED!

The problem was we had customers with a blank email. We just had to wrap the email and update code in a try catch. Got the code working and I just had to update the database for two customers.

Thanks for your help Chris!