为什么更新查询不起作用？ [英] Why update query not work?

问题描述

大家好，



我创建了一个应用程序，如果使用输入有效的unique_no然后他的销售额是计数，代码执行正常但是当我看到表格时，字段是尚未更新

数据库我在访问时创建



表架构是



User_ID自动编号

用户名文字

密码备忘录

Unique_No编号

Sales_count编号

To_Date日期/时间



我尝试过：

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:

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();
           }

工作正常，但数据未更新到表中

注意：此时Sales_count字段为空白

---------------------------------- -----------------------------



也时我尝试下面的代码

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

also when i tried below code

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

debgugger何时到达下线

the debgugger when to below line

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

推荐答案

我认为可以通过分离UI，业务逻辑和数据库查询来改进代码。



关于输入的UI输入验证（客户端的验证，例如JavaScript / jQuery）和按钮点击（服务器端的验证，如C＃）你应该将输入验证为txtinput.Text可能与数据库中的字段类型不匹配。



从表模式中，不清楚哪个是PK ...有User_ID和Unique_No



好​​像你缺少与dbcommand的连接：

OleDbCommand.Connection Property（ System.Data.OleDb） [ ^ ]

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)[^]

cmd.Connection = con;

也可能最好使用try catch最终检查任何错误。错误可能并非总是无效的唯一编号。

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

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

代码的问题在于这一部分：你写的现在Sales_count字段是空白的。什么是空白+1？空白。没有看到您的数据库空白很可能是空的。空+1仍为空。因此没有错误，但也没有数据似乎在变化。



一个非常简单的修复：

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:

set Sales_count=IsNull(Sales_count, 0)+1

这意味着，如果Sales_count为null，则IsNull将返回0，以便添加1将起作用。

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

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);

这篇关于为什么更新查询不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！