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();
LVL 1
pclarke7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anarki_jimbelSenior DeveloperCommented:
Do you have any transaction mechanism used?
0
arnoldCommented:
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

0
pclarke7Author Commented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

arnoldCommented:
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.
0
arnoldCommented:
Why the C? The issue was with your implementation typos and improper use of variables.
0
pclarke7Author Commented:
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.
0
arnoldCommented:
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.
0
pclarke7Author Commented:
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
0
arnoldCommented:
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*
0
pclarke7Author Commented:
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
0
arnoldCommented:
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.
0
pclarke7Author Commented:
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";
0
arnoldCommented:
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);
0
pclarke7Author Commented:
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
0
arnoldCommented:
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?
0
pclarke7Author Commented:
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"
0
arnoldCommented:
Could you try your code when the database is attached in sql?
I think it is likely something simple that the parameters settings when the file is "attached" is that you have to commit for the update you made to take effect.
Try the following.
Use a loop of 1-10 update with the loop count as the appended string, and then select the same row. And see whether each change is reflected in subsequent request.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pclarke7Author Commented:
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
0
pclarke7Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.