Avatar of pclarke7
pclarke7 asked on

Updating a MS SQL database

Hello,
I am having problems updating my MS SQL database from my C# application. I can read from the database but my updates do not work. Below is an example


I have a value of 'AAA' in field BM_BadgeID. When I run the select statement below it returns 'AAA' as expected to variable LastTran. Then when the Update statement runs it would seem to update the value to 'TEST-7' - however when I inquire on the database it is still set to 'AAA'

If I run this code a 2nd time then the select returns TEST-7 to field LastTran (as though it were updated on the database) . However if I end the application and restart the value is back to 'AAA'.

I don't understand what I am doing incorrectly. Any help/advise would be appreciated.

regards
Pat
     
            //Basic SELECT method to populate a DataSet from a SqlDataAdapter
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            SqlConnection sqlConn = new SqlConnection(ConnectionStr);
            SqlDataAdapter sqlAdapt = new SqlDataAdapter("SELECT * FROM badge_master  WHERE BM_BadgeID='123123'",sqlConn);
            SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
            DataSet sqlSet = new DataSet();
            sqlAdapt.Fill(sqlSet, "badge_master");
            DataRow dRow = sqlSet.Tables["badge_master"].Rows[0];
            string name = dRow.ItemArray.GetValue(1).ToString();            // Badge Name
            string LastTran = dRow.ItemArray.GetValue(11).ToString();       // Last Transaction name
            sqlConn.Close();



            //Basic UPDATE method with Parameters
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            SqlCommand sqlComm = new SqlCommand();
            sqlComm = sqlConn.CreateCommand();
            sqlComm.CommandText = "Update badge_master set BM_LastTranName='TEST-7' where BM_BadgeID='123123'";
            sqlConn.Open();
            sqlComm.ExecuteNonQuery();
            sqlConn.Close();
C#ASP.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
pclarke7

8/22/2022 - Mon
Dmitry G

Do you have any transaction mechanism used?
arnold

You have a typo in the code that seemingly does not connection

You use sqlComm as the variable to establish a connection before the update, but you copied the data from the earlier query that used sqlConn for the connection string and sqlComm as the connected reference.
Try the following
 //Basic UPDATE method with Parameters
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            SqlCommand sqlConn= new SqlCommand(); //<=this is where your code uses sqlComm//
            sqlComm = sqlConn.CreateCommand();
            sqlComm.CommandText = "Update badge_master set BM_LastTranName='TEST-7' where BM_BadgeID='123123'";
            sqlConn.Open();
            sqlComm.ExecuteNonQuery();
            sqlConn.Close();

Open in new window

ASKER
pclarke7

Hi Anaraki,
not sure what you mean by "Do you have any transaction mechanism used". Can you explain ?

Hi Arnold,
sqlcomm is the command whilst sqlconn is the connection. I don't believe that I have a typeo. If I make the change you suggest I just get an error on every subsequent sqlComm as it is no longer defined.

Any further suggestions would be welcome as I can't move forward until I have figured out how to updated my database. Is there anything else external to the code that could be preventing the update ?

Pat
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
arnold

The issue is likely the absence of the connection when you try to execute the command.

In the way your code was initially reference, sqlconn was closed by the earlier query.

  //Basic SELECT method to populate a DataSet from a SqlDataAdapter
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            SqlConnection sqlConn = new SqlConnection(ConnectionStr);
            SqlDataAdapter sqlAdapt = new SqlDataAdapter("SELECT * FROM badge_master  WHERE BM_BadgeID='123123'",sqlConn);
            SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
            DataSet sqlSet = new DataSet();
            sqlAdapt.Fill(sqlSet, "badge_master");
            DataRow dRow = sqlSet.Tables["badge_master"].Rows[0];
            string name = dRow.ItemArray.GetValue(1).ToString();            // Badge Name
            string LastTran = dRow.ItemArray.GetValue(11).ToString();       // Last Transaction name
            sqlConn.Close();



            //Basic UPDATE method with Parameters
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            SqlCommand sqlComm = new SqlCommand(); //<<=== here you define sqlComm as the new sqlcommand
            sqlComm = sqlConn.CreateCommand();   //<<== Here you redefine sqlComm using sqlconn that was closed before this section.
            sqlComm.CommandText = "Update badge_master set BM_LastTranName='TEST-7' where BM_BadgeID='123123'";
            sqlConn.Open();
            sqlComm.ExecuteNonQuery();
            sqlConn.Close();

You are not checking on whether what you are doing is getting through or not i.e. no error check from commands you execute.
note sure whether you need to run sqlConn.connect rather than open.
arnold

Why the C? The issue was with your implementation typos and improper use of variables.
ASKER
pclarke7

Hi Arnold,
the issue was that I was unable to update the database and the issue still remains. If I had got an answer to the problem then I would have marked accordingly.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

If you need help, you need to provide information.
Check for errors versus blindly assuming whatever you send is being executed.
The DSN/login into sql you use might not have update/write rights.

Whenever you code anything that interacts with another system, always use error detection.
One to make sure the connection is present. The request you sent was successfully executed. If any check reflects an error recording/reporting the error will help you diagnose and solve the problem.  Making the error notification detailed reflecting of where the error occurred helps. I.e. update section. Update attempt returned an error. Error message. Access denied, invalid data expected integer, string was ..... Etc.

The quick glance pointed to the issue being the connection to the database because you were mixing the sqlComm and sqlConn.
ASKER
pclarke7

Hi Arnold,
Fair point. I have attached a file with details of the issues - if you are willing to have another look at it ? I have put in error handling , but no errors are being thrown. It seems to update the database but the update doesn't happen.

How can I check my Login into SQL for update/write rights ?  

regards
Pat
Example---Unable-to-update-datab.docx
arnold

use SSMS to login into the database, and see whether you can issue a query that updates a row in a table.

In part 2.
You are trying to reuse an existing select connection in the update section.

You need to first check whether sqlconn.open is true. if not, run sqlconn.connect to reestablish the connection or undef and redefine the sqlConn in the second section.
SqlConnection sqlConn = new SqlConnection(ConnectionStr);.
sqlConn.connect;

*Competing site link removed-  MIT 9-13-13*
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
pclarke7

Hi Arnold,
I am authorized to update the table from SSMS. I also checked and verified that the state of sqlconn.open  which was "State: True". Still not updating. I have attached a step by step account of attaching the database and of debugging the program. Hopefully you might spot something obvious that I am doing wrong.

regards
Pat
Failing-to-update---Step-by-step.docx
arnold

How about you take everything related to the select in your code and only use an update.

I think the select/adapter are tying up the socket/connection preventing the update from being sent through.
Usually when using the same connection, you have to complete the first action in your case it is a select, and then start the new one.

You have
: open connection
issue select

update


If you use transactions, you have to commit the transaction.
ASKER
pclarke7

Hi Arnold,
I have changed the code so that only the update is executed. I have also added in a commit statement. But it still does not update the badge_master table. Here is what is now looks like.

regards
Pat

             //Basic UPDATE method with Parameters
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            try
            {
                sqlConn.Open();
               SqlCommand sqlComm = new SqlCommand("Update badge_master set BM_LastTranName= " + "'" + name + "'" + "where BM_BadgeID='123123'");
                sqlComm.Connection = sqlConn;
                sqlComm = sqlConn.CreateCommand();
                sqlComm.CommandType = CommandType.Text;
                sqlComm.CommandText = "Update badge_master set BM_LastTranName= " + "'" + name + "'" + "where BM_BadgeID='123123'";
                SqlTransaction trx = sqlConn.BeginTransaction();
                sqlComm.Transaction = trx;
                sqlComm.ExecuteNonQuery();
                trx.Commit();
                sqlConn.Close();
            }
            catch (Exception ex)
            {
                CatchNo = "10151";
                MessageBox.Show("Error " + CatchNo + " - " + ex);
            }
            return "X";
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

Pat,

you are missing the declaration for sqlconn in the example you posted.  Without it being defined, sqlConn.Open() does nothing the try will capture a c# exception.


            SqlConnection sqlConn = new SqlConnection(ConnectionStr);
ASKER
pclarke7

Tried it again with SqlConnection sqlConn = new SqlConnection(ConnectionStr);  and got the same result - no update.

It looks to me that the dataset is being updated but not the database.  I tried putting in
sqlAdapt.Update(sqlSet, "badge_master");  before the Update but it made no difference.

I am running this application in Debug - I assume that this should have no effect on updating the database table. ?

regards
Pat
arnold

When you run the code, whose credentials are configured in the DSN/connection string?
Your c# seems to be accessing the data not via an SQL server connection, but through a direct access to the database file. Is it correct?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
pclarke7

The database is on my C: The connection string is:

globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Rulesdb2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
ASKER CERTIFIED SOLUTION
arnold

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
pclarke7

Happy days !!!
got it working.  I think the problem was due to the fact that I had a number of versions of MS SQL Server installed on my PC and had at various time alternated between a local copy of the database and a direct link to the database. I backed up the database and then uninstalled all versions of MS SQL SERVER deleted all MS SQL SERVER services and removed all folders linked to MS SQL SERVER. I then installed MS SQL Server 2012 (32 bit) , restored the database and created a direct (non local) connection. The update worked 1st time. In fact I was able to reduce it down to the following:

            string name2 = "TEST01";
            ConnectionStr = globals.MyTrans.p1_RtvConnMSSQLDB_NATIVE;
            SqlConnection con3 = new SqlConnection(ConnectionStr);
            SqlDataAdapter da3 = new SqlDataAdapter();
            SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da3);
            DataSet ds3 = new DataSet();
            try
            {
                SqlCommand cmd3 = new SqlCommand("Update badge_master set BM_LastTranName= " + "'" + name2 + "'" + "where BM_BadgeID='123123'");
                con3.Open();
                cmd3.Connection = con3;
                cmd3.ExecuteNonQuery();
                con3.Close();
            }
            catch (Exception ex)
            {
                CatchNo = "10151";
                MessageBox.Show("Error " + CatchNo + " - " + ex);
            }
            return "X";
        }


A big thank you Arnold for your perseverance , it is much appreciated.

regards
Pat
ASKER
pclarke7

Particularly appreciate the perseverance in trying to resolve this issue. After 6 weeks of banging my head against a wall , I can finally move on
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.