Sign in to follow this  

Problem Updating MS SQL table with C#

This topic is 4093 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm trying to update an MS SQL 2000 database from a program I've written in C# 2003 Pro. I can read from the database just fine, but if I try to write to the database it seems that nothing is happening. In the program I've got a data set called ds_CurQtr1 which gets populated from the database containing the table. I'm using a data adapter called sqlDataAdapter_CurQtr. Here's the code snippet where I think the problem lies:
int TempQtr = ds_CurQtr1.data_Cur_Qtr[0].Qtr;
int TempYear = ds_CurQtr1.data_Cur_Qtr[0].Year;
try {
sqlConnection1.Open ();
int NumRows = sqlDataAdapter_CurQtr.Update ( ds_CurQtr1 );
MessageBox.Show (
    "Updated " + NumRows + " rows. \n\n" + "TempQtr=" + TempQtr +"\n"
    + "TempYear=" + TempYear, "Info", MessageBoxButtons.OK,
    MessageBoxIcon.Information );
sqlConnection1.Close ();
ds_CurQtr1.AcceptChanges (); 
}
catch (Exception err)
{
	MessageBox.Show ( "Error: \n" + err.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}

When it runs I get a message box that says 'Updated 0 rows' and gives the original data. The SQL data adapter has the following code in the Update parameters:
UPDATE dbo.data_Cur_Qtr SET Qtr = 'QtrVar', Year = 'YearVar'
I've tried it without the parameters command present(which is the default from VS2003) and with this command. Also tried it with QtrVar and YearVar variables defined and not. And basically every combination you could have. From everything I've read on the internet this should be all that's needed to update the database, but it doesn't seem to work. I've also tried the dozens of different methods for updating SQL databases I've found on different sites but nothing works. I should also note that I had it working sporatically by not using the sql data adapter and just manually entering the commands, but it wasn't reliable - it would only update 1 time in 10 so I changed the code. Now even putting the code back to that way doesn't seem to work. If anyone has any ideas I'd be grateful. Also if you need more code posted I have no problem with that, but I have to warn you it's pretty ugly. I'm just learning C# and SQL programming.

Share this post


Link to post
Share on other sites
Hi, JimboC:

Your code looks not completed for us to help you out!
However, my experience wrote the C# code for access MS SQL server is list as follows for you reference:

int TempQtr = ds_CurQtr1.data_Cur_Qtr[0].Qtr;
int TempYear = ds_CurQtr1.data_Cur_Qtr[0].Year;

string strConnectionString="Data Source=(local);Initial Catalog=MyDBName;User ID=sa;Password=xxxx";
string strTable="dbo.data_Cur_Qtr";
string strSQL="UPDATE "+strTable+" SET Qtr = '"+TempQtr.ToString()+"', Year = '"+TempYear.ToString()+"';

SqlConnection conn = new SqlConnection();
conn.ConnectionString = strConnectionString;
SqlCommand cmd = new SqlCommand(strQuery, conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}

Hope this is good for you.

CHIHLIN

Share this post


Link to post
Share on other sites

This topic is 4093 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this