SQL Update not working 1

[patrickstrijdonck]

Hello,

Im trying to update a record with a new value,
short explanation: Ive got a table tblItems, which contains all my items which are on stock. when i send 1 product, the amount must decrease by the amount im sending in the transaction.

this part is working, ive made a textbox visible on the form where i can see what the amount is after the transaction. in table, amount is 2, im sending 1 and the textbox shows, 1 so thats OK

now i want to update the table and update the value with the value thats currently in the textbox.

                        string connstrr = Properties.Settings.Default.IT4STOCKConnectionString;
                        string cmddstrr = "UPDATE tblItems SET AantalDC = aantal WHERE Productnr = productnumber";
                                        



                        OleDbConnection connr = new OleDbConnection(connstrr);
                        OleDbCommand commr = new OleDbCommand(cmddstrr, connr);
                        connr.Open();
                        commr.Parameters.AddWithValue("productnumber", textBox1.Text);
                        commr.Parameters.AddWithValue("aantal", textBox9.Text);
                        commr.ExecuteNonQuery(); 
                        connr.Close();

Textbox 1 contains the productnumber
Textbox 9 contains the new value.

when i try a transaction there is NO error, BUT the new value is not updated in the table, it remains on 2 (if it was 2)

I placed a messagebox in the code to see if the code is really executed, the message shows so the code is executed.

ive tried alot of things to get the table to update, but it just wont!

Am i missing something???

 

[jmeckley]

1. if this is the actual sql you are using the sql statement is missing parameters. depending on the database you are connecting to it should look something like this

UPDATE tblItems SET AantalDC = @aantal WHERE Productnr = @productnumber

2. this code is error prone. here are just a few issues I see.
1. connection management
2. lack of error handling
3. connection & command are never disposed
4. updating the order and amount in stock should be part of the same transaction. it's an all or nothing operation.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

Hi Jason,

Order and stock are 2 different things in my database,

Orders just keeps track on order and which product are going to be sended.

Items keeps track of all Items.

So that is OK.

I've added a simple error handling, but there is no error when executing.

I've also tried adding the '@' before the parameters, like the piece of code you gave, still not working.

What do you mean with Connection Management?

Also added the Dispose to the code.

 

[jmeckley]

What do you mean with Connection Management?

Also added the Dispose to the code.

read the faq I have in my signature. this deals with web applications specifically, but the concept applies no matter what GUI the system has.

Order and stock are 2 different things in my database,

Orders just keeps track on order and which product are going to be sended.

Items keeps track of all Items.

So that is OK.

if an order is placed the stock is adjusted correct? so either both operations execute or neither should, correct? you ensure this by placing both operations into the same unit of work. logically it would look like this (not necessarily the code).
[tt]
try
open connection
begin transaction
insert order
update stock
commit
catch sql exception
rollback transaction
finally
dispose of connection
[/tt]

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

Ive changed my code to this now:

                        string connectionupdate = Properties.Settings.Default.IT4STOCKConnectionString;
                        string sqlupdate = "UPDATE tblItems SET AantalDC = @aantaler WHERE Hba1 = @hba";
                        OleDbConnection conupdate = new OleDbConnection(connectionupdate);
                        OleDbCommand comupdate = new OleDbCommand(sqlupdate, conupdate);
                        try
                        {
                            conupdate.Open();
                            comupdate.Parameters.AddWithValue("@hbar", SqlDbType.VarChar);
                            comupdate.Parameters["@hbar"].Value = textBox1.Text;
                            comupdate.Parameters.AddWithValue("@aantaler", textBox9.Text);
                            comupdate.ExecuteNonQuery();
                            if (comupdate.ExecuteNonQuery() > 0)
                            {
                                MessageBox.Show("OK");
                            }
                            else
                            {
                                MessageBox.Show("Failure");
                            }


                            conupdate.Close();
                        }
                        catch
                        {
                            MessageBox.Show("Error");
                        }

but this is still NOT working, the messagebox Failure shows up saying that there are no records changed / updated
but there is no error on the try/catch method so the SQL should be OK.

Ive tried to create a complete new clean form with 1 button and this code (offcourse, the parameters are adjusted to match a record in the table)

but still nothing is updated in the table, ive checked the parameters, checked the table and the access database, but everything is OK, Getting data from de database works perfect, but updating......

ive tried to create a new DB with that table, but that also doesnt work.

Can someone please help me?

 

[jmeckley]

we're moving in the right direction. 3 problems I see though:
1. you're not disposing of the ado.net objects. this is critical for memory management.
2. you are swallowing the exception so you never know what failed, only that it did.
3. by calling comupdate.ExecuteNonQuery() you are executing the command twice.
with some tweaks you should be able to get this working

using(var conupdate = new OleDbConnection(connectionupdate))
using(var comupdate = new OleDbCommand(sqlupdate, conupdate))
{
    try
    {
        conupdate.Open();
        comupdate.Parameters.AddWithValue("@hbar", textBox1.Text);
        comupdate.Parameters.AddWithValue("@aantaler", textBox9.Text);
        
		var rowsModified = comupdate.ExecuteNonQuery();
		MessageBox.Show(string.Format("Modified {0} row(s)", rowsModifed));
        
		conupdate.Close();
    }
    catch(Exception exception)
    {
        MessageBox.Show(exception.ToString());
    }
}

I also cleaned up the parameter definations.
I would also recommend naming your variables something meaningful.
sqlupdate, conupdate, textBox1, textBox9 doesn't tell you much when you return to the code weeks/months/years from now.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

Disposing is done by adding this code:

conupdate.dispose();
comupdate.dispose();

Can i just place this after conupdate.close(); ??? or must i place this somewhere else?

Concerning the piece of code you provided, Ive tried is and the message i get is: Modified "0" Rows

 

[jmeckley]

Disposing is done by adding this code:

conupdate.dispose();
comupdate.dispose();

Can i just place this after conupdate.close(); ??? or must i place this somewhere else?

if you did it would only work when there are no exceptions. this defeats the purpose of the try/catch block.
this code. a picture (code) is worth 1000 words, so...

using(IDisposable x = new ADisposableClass())
{
}

is sugar syntax for

IDisposable x 
try
{
   x = new ADisposableClass();
}
finally
{
   if(x != null)
   {
      x.Dispose();
   }
}

which is what you need to do, if you are not using the [tt]using[/tt] keyword.

I would spend an hour or two researching exception throwing, IDisposable and try/catch/finally. in a nut shell when an exception is thrown all processing stops and the exception bubbles up the call stack. (this is how the stack trace is populated on an exception object.) The try/catch/finally blocks allow you to manage how exceptions are handled.
the try{} block is the work you expect to happen without error.
the catch{} block is what you want to do when an exception occurs. this code should be a simple as possible. there is usually 1 of 3 actions here:
1. log message.

catch(Exception e)
{
    log.Error(e);
}

2. do some other action and rethrow

catch(Exception e)
{
    DoSomeCleanupWork();
    throw;
}

3. wrap the exception and throw

catch(Exception e)
{
    throw new MyException("message you want to show", exception);
}

the finally{} block will always occur, even if there is an exception. this block is used for resource clean up, like disposing disposable objects.
....

back to the original question.

Concerning the piece of code you provided, Ive tried is and the message i get is: Modified "0" Rows

ok, so the sql statement is executing, but not updating any rows. now you need to figure out why.


Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

back to the original question.

Quote:Concerning the piece of code you provided, Ive tried is and the message i get is: Modified "0" Rows

ok, so the sql statement is executing, but not updating any rows. now you need to figure out why.

thats currently the problem which i cannot find out.

What i did to test and see a couple of things:

See if textBox1 and textBox9 contains a value:
I did this by adding a messagebox after the SQL code, turns out that both contains the correct value.

@hbar and @aantaler should have the correct values.

Tried updating a row with a fixed value:
Instead of using @hbar at the WHERE condition, i used a fixed value(offcourse i checked if that value exist in the table). Still no update.

Tried to bypass all parameters:
Instead of using @hbar and @aantaler, i used fixed values to update. still....... no update

Tried using the textbox.text in the SQL:

string sqlupdate = "UPDATE tblItems SET AantalDC = @aantaler WHERE Hba1 =" + textBox1.Text;

Still no rows modified......

Tried using the same SQL code in Access self:
Seems that this IS working,

UPDATE tblItems SET AantalDC = "2" WHERE Hba1 = "DT15423"

Running this, is updating the table correctly, so i think access is not the problem here.

I was thinking, Is it possible to let access run the query instead of the SQL Update???

To test and try my question above,
I changed my code into this:

string cmdstr = Properties.Settings.Default.IT4STOCKConnectionString;
                        OleDbConnection conupdate = new OleDbConnection(cmdstr);
                        OleDbCommand comupdate = new OleDbCommand();
                        comupdate.Connection = conupdate;
                        comupdate.CommandText = "Query1";            //Query1 is the name of the Access Query    
                        comupdate.CommandType = CommandType.StoredProcedure;
                        { 
                            try 
                            { conupdate.Open();
                            comupdate.Parameters.Add("hbar", OleDbType.Char);
                            comupdate.Parameters["hbar"].Value = textBox1.Text;
                            comupdate.Parameters.Add("aantaler", OleDbType.Char);
                            comupdate.Parameters["aantaler"].Value = textBox9.Text;
                                var rowsModified = comupdate.ExecuteNonQuery();
                                MessageBox.Show(string.Format("Modified {0} row(s)", rowsModified)); 
                                conupdate.Close(); 
                            } 
                            catch (Exception exception) 
                            { 
                                MessageBox.Show(exception.ToString()); 

                            } 
                        }

But that just gives no update in the table, maybe this piece of code is wrong, i just came up with it, so i will double check it, but there is no error. just the message that 0 rows have been updated.

I also tried Parameters.AddWithValue but that didnt work either.

Filling in the values when running the query in access works fine.

Ive tried really ALOT of things, i hope this is enough information for you to see whats going on.

Thanks already for the help, really appriciate it

 

[jmeckley]

you're connecting to Access? that changes things. Access doesn't behave like a typical RDBMS. a quick search might shed some light on the subject. This looks like a common problem. And others are solving the problem using ordinal parameters rather than named parameters.

var sql = "UPDATE tblItems SET AantalDC = ? WHERE Hba1 = ?";
command.Parameters.AddWithValue("AantalDC", textBox1.Text);
command.Parameters.AddWithValue("Hba1", textBox9.Text);

Looks like ado.net parameter ordering is critical when connecting to an Access database.
so much for abstraction

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[patrickstrijdonck]

Can you use OLEDB connection for something else then access??

Sorry for the confusion, should have told that i was using access.

Anyway, thanks, this is now working, the row is updating. Strange because when selecting data from the database, those parameters ARE working......

Well whatever it was, it is fixed by using the ?.

Thanks alot, because of this fault, i learned alot and thats important

 

[jmeckley]

9 times out of 10 .net developers are connecting to MS SQL. so most queries are structured using MS TSQL syntax.

OleDb can be used to access data store which abides by ODBC or the Jet Engine.
Access
dBase
FoxPro
MySql
Sybase
Excel
Active Directory

Unfortunately each ado.net doesn't support all features for all databases. for example, most of these don't support transactional support. Access as we found is temperamental with parameters. Active Directory doesn't handle parameterized queries at all nor table joining. The list goes on.

Depending on your needs you can work around these issues though and for simple reads, having ado.net access to these different data stores is advantageous.

ado.net provides the building blocks for access relational databases. some developers code directly against ado.net. Others use data access frameworks that abstract ado.net away from the core functionality of there code. the Data Access Block from MS is geared toward DataSets and stored procs. There is also the concept of Object Relational Mappers (LLBL, ActiveRecord, NHibernate, Linq2Sql, EnityFramework). The idea behind ORM is to map domain objects (not datasets) to a relational database.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259