在使用C＃更新数据库之前，如何使用while循环更新多个值 [英] How to update mutiple values with while loop before updating the databse with C#

问题描述

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Text;

public partial class Nampak_AddFal : System.Web.UI.Page
{
    string selectSQL, selectSQL1, selectSQL5;
    string updateSQL, updateSQL1, updateSQL5, updateSQL6, updateSQL2;
    SqlCommand cmd = new SqlCommand();
    SqlCommand cmd1 = new SqlCommand();
    SqlCommand cmd2 = new SqlCommand();
    SqlCommand cmd3 = new SqlCommand();
    SqlCommand cmd5 = new SqlCommand();
    SqlCommand cmd6 = new SqlCommand();
    SqlCommand cmd9 = new SqlCommand();
    SqlConnection dbConn = new SqlConnection();
    SqlConnection dbConn1 = new SqlConnection();
    SqlConnection dbConn2 = new SqlConnection();
    SqlConnection dbConn3 = new SqlConnection();
    SqlConnection dbConn4 = new SqlConnection();
    SqlConnection dbConn5 = new SqlConnection();
    SqlConnection dbConn6 = new SqlConnection();
    SqlDataReader dr, dr1, dr2, dr3, dr5, dr8;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void SetSS_Click(object sender, EventArgs e)
    {
        Session["YY"] = DropDownList1.SelectedItem.Text;
        int Q = Convert.ToInt32(TextBox1.Text);
        


        selectSQL = "select id, RTicket from Staff WHERE Jobtype ='Contract'";
        dbConn.ConnectionString = "Data Source=LAPTOP\\SQLEXPRESS; Initial         Catalog=Namh;Integrated Security=True;";
        cmd.Connection = dbConn;
        cmd.CommandText = selectSQL;
        cmd.CommandType = CommandType.Text;

        try
        {
            dbConn.Open();
         
            dr = cmd.ExecuteReader();
           while (dr.Read())
            {
                Session["FT"] = dr["Rticket"].ToString();
                 int W = Convert.ToInt32(Session["FT"]);
                int S = W + Q;
                string P = S.ToString();
                Session["PP"] = S.ToString();
             


              updateSQL1 = "update Staff set  Rticket='" + Session["PP"].ToString() + "' where  Jobtype ='Contract' ";
              // Response.Write(updateSQL1);
               dbConn1.ConnectionString = "Data Source=LAPTOP\\SQLEXPRESS; Initial Catalog=Namh;Integrated Security=True;";

               cmd1.Connection = dbConn1;
               cmd1.CommandText = updateSQL1;
               cmd1.CommandType = CommandType.Text;
               try
               {

                   dbConn1.Open();
                   int updated1 = cmd1.ExecuteNonQuery();
                   if (updated1 == 1)
                   {
                      Response.Write("well done");
                       


                   }
                   else
                   {
                     Response.Write("double");
                       


                       
                       } 
                       
                      
                   
               }
               catch (Exception err)
               {
                   Label6.Text = "Error  Logging in 2 ";
                   Label6.Text += err.ToString();
               }
               finally
               {
                   dbConn1.Close();
               }

            }
           
            dr.Close();
        }
        catch (Exception err)
        {
            Label6.Text = "Error  Logging in 2 ";
            Label6.Text += err.ToString();
        }
        finally
        {
            dbConn.Close();
        }



    }
}

我的问题是我能够在添加值之前读取值并最后在数据库中更新它，例如



20

34

56

67



但它会将所有值更新为数据库中的相同值，例如



98

98

98

98



而不是说int Q = 4



24

38

60


数据库，请帮助



我尝试过：



i尝试使用更新语句

my problem is that i am able to read values before adding a value and lastly updating it in the database e.g

20
34
56
67

but it updates all the values to same values in the database e.g

98
98
98
98

instead of say int Q =4

24
38
60
71
in the database ,please help

What I have tried:

i have tried using the update statement

推荐答案

这是您的SQL语句：

update Staff set  Rticket='" + Session["PP"].ToString() + "' where  Jobtype ='Contract'

因此，很明显它会更新Staff表中具有Contract类型的任何记录。因此，如果您不想更新所有合同记录，则需要在WHERE子句中添加一些内容。



另外，一些指示：

1.您没有显示所有代码，但是您有许多单独的sql连接和sql命令。这可能是一种糟糕的做事方式。你在标题中提到了一个while循环，但你不需要它。看起来你正在提取一个值，更改它然后写回数据库。在此过程和单个sql命令中只使用一个连接。



2.将控件命名为有意义的东西。看起来您在名为Label6的控件中显示消息。将其命名为lblMessages或lblError或类似名称，以便每次尝试找出该控件时都不必切换到表单设计。

So, it is pretty clear that it will update any record in the Staff table that has a jobtype of Contract. So, if you don't want to update all the Contract records you need to add something to your WHERE clause.

Also, some pointers:
1. You did not show all your code but you have lots of separate sql connections and sql commands. This is likely a bad way of doing things. You mention in your title about a while loop but you don't need it. It looks like you are pulling out a value, changing it and then writing back to the db. Use only a single connection for this process and a single sql command.

2. Name your controls something that makes sense. It looks like you show messages in a control named Label6. Name it lblMessages or lblError or something like that so that you don't have to switch over to your form design every time you try and figure out what that control is.

这篇关于在使用C＃更新数据库之前，如何使用while循环更新多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！