Why update query not work?

Question

0.00/5 (No votes)

See more:

Hi all,

Iam creating one application where if use enter valid unique_no then his sales is count , code is executed fine but when i see table the field is not updated
database im created in access

Tables schema is

User_ID AutoNumber
User_name Text
Password Memo
Unique_No Number
Sales_count Number
To_Date Date/Time

What I have tried:

C#

private void button1_Click_1(object sender, EventArgs e)
       {
           this.txtinput.MaxLength = 4;


           cmd = new OleDbCommand("update Login set Sales_count=Sales_count+1 where [Unique_No]=@Unique_No", con);
               cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);


               con.Open();
               int n = cmd.ExecuteNonQuery();

               if (n==0)
               {
                   MessageBox.Show("Invalid Unique No. pls try again later");
                   this.DialogResult = DialogResult.Cancel;
               }
               else
               {
                   this.DialogResult = DialogResult.OK;
               }


               con.Close();
           }

its working fine but data is not updated into table
Notes : right now Sales_count field is blank
---------------------------------------------------------------

also when i tried below code

C#

update Login set Sales_count=Sales_count+1 where [Unique_No]=@Unique_No and [To_Date]='" + DateTime.Now.ToShortDateString() + "'", con

the debgugger when to below line

C#

if (n==0)
           {
               MessageBox.Show("Invalid Unique No. pls try again later");
              this.DialogResult = DialogResult.Cancel;
           }

Posted 24-Jul-16 8:50am

Atul Rokade

Updated 25-Jul-16 0:50am

Add a Solution

Comments

Richard Deeming 25-Jul-16 7:33am

... and [To_Date]='" + DateTime.Now.ToShortDateString() + "'"

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

You already know how to use parameters from your previous example.

3 solutions

Solution 1

Hi,

I think the code can be improved by separating UI, business logic and database queries.

Regarding input validations from the UI on input (validations on client side such as with JavaScript/jQuery) and on button click (validations on server side such as with C#) you should validate the input as txtinput.Text may not match field type in database.

From the table schema, it's unclear which is the PK...there is User_ID and Unique_No

Seems like you're missing connection to dbcommand:
OleDbCommand.Connection Property (System.Data.OleDb)[^]

C#

cmd.Connection = con;

Also might be better to use try catch finally to check for any errors. The error may not always be invalid unique no.

C#

try
{
   ...database related code
}
catch(Exception ex)
{
   //handle Exception here
}
finally{
   con.Close(); //this always ensures to close the connection
}

Posted 24-Jul-16 10:56am

P_Z

Solution 3

C#

cmd = new OleDbCommand("update Login set Sales_count= 
case when Sales_count='' then (select Sales_count from Login where [Unique_No]=@Unique_No )+1 else (select Sales_count from Login where [Unique_No]=@Unique_No )+1 end
 where [Unique_No]=@Unique_No", con);

Posted 25-Jul-16 0:50am

Member 10261484

Comments

Atul Rokade 25-Jul-16 9:55am

Hi Member 10261484 : thnx for reply but its giving me Syntax error (missing operator) in query expression 'case when Sales_count='' then (select Sales_count from Login where [Unique_No]=@Unique_No )+1 else (select Sales_count from Login where [Unique_No]=@Unique_No )+1 end'.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2016-07-24T13:12:00

Solution 2

The problem with your code is this part: you wrote "right now Sales_count field is blank." What is blank +1? blank. Without seeing your database blank is most likely a null. Null +1 is still null. Hence no errors but also no data seems to be changing.

A very simple fix:

SQL

set Sales_count=IsNull(Sales_count, 0)+1

This means, if Sales_count is null then IsNull will return 0 so that adding 1 will then work.

Posted 24-Jul-16 13:12pm

ZurdoDev

Comments

Atul Rokade 25-Jul-16 9:54am

Hi @RyanDev : its giving me exception Wrong number of arguments used with function in query expression 'IsNull(Sales_count, 0)+'. note : im using ms access as database Provider=Microsoft.ACE.OLEDB.12.0

ZurdoDev 25-Jul-16 10:00am

You need to change to use IIF.

Something like Set Sales_count = IIF(IsNull(Sales_count), 0, Sales_count) + 1

Atul Rokade 25-Jul-16 10:07am

@RyanDev : sir its working fine sir :) only one help i need how i enter system date into access database i use [To_Date]='" + DateTime.Now.ToShortDateString() + in query but its giving me invalid error how can i put system date in access database while updating this?

ZurdoDev 25-Jul-16 10:12am

I believe Access wants # around dates, not single quotes. If that doesn't work, just do a quick google search for how to put dates into msaccess.

Atul Rokade 25-Jul-16 10:14am

thnx RyanDev for your support

ZurdoDev 25-Jul-16 10:18am

You're welcome.