如何从SQL db中检索asp：CheckBox值？ [英] How to retrieve asp:CheckBox value from SQL db?

问题描述

我有一个CheckBoxes表，它作为1和0插入到SQL数据库中。但是，我想用load事件再次检索这些值，但我无法得到它们。这是我的代码：

  private   void  getAuditChecklist（）
 {
 SqlCommand cmd =  null ; 
  string  conn = ConfigurationManager.ConnectionStrings [ 的ConnectionString]的ConnectionString。 
  string  queryString =  @  SELECT Mount， Braker，Access，Conn_Net，Log_Book，Pictures，Floor，Cb_Lenght，Channel FROM AUDITOR_CHECKLIST + 
   WHERE SITE_ID = @SiteID; 
 
 使用（SqlConnection连接= 
  new  SqlConnection（conn） ）
 {
 SqlCommand command = 
  new  SqlCommand（queryString，connection）; 
 connection.Open（）; 
 cmd =  new  SqlCommand（queryString）; 
 cmd.Connection = connection; 
 
 cmd.Parameters.Add（ new  SqlParameter（  @ SiteID， //  要映射的参数的名称 
 System.Data.SqlDbType.NVarChar， //   SqlDbType value  
 < span class =code-digit> 20 ， //  参数的宽度 
   SITE_ID））;  //  列源名称 
  //  使用检索的值填充参数 
  //  来自文本字段 
 cmd.Parameters [  @ SiteID ]。Value = foo.Site_ID; 
 
 SqlDataReader reader = cmd.ExecuteReader（）; 
 
 
  while （reader.Read（））
 {
 CheckBox1.Checked =（读者。 GetBoolean（reader.GetOrdinal（  Mount）））; 
 CheckBox2.Checked =（reader.GetBoolean（reader.GetOrdinal（  Braker）） ）; 
 CheckBox3.Checked =（reader.GetBoolean（reader.GetOrdinal（  Access）） ）; 
 CheckBox4.Checked =（reader.GetBoolean（reader.GetOrdinal（  Conn_Net）） ）; 
 CheckBox5.Checked =（reader.GetBoolean（reader.GetOrdinal（  Log_Book）） ）; 
 CheckBox6.Checked =（reader.GetBoolean（reader.GetOrdinal（ 图片）） ）; 
 CheckBox8.Checked =（reader.GetBoolean（reader.GetOrdinal（  Floor）） ）; 
 CheckBox9.Checked =（reader.GetBoolean（reader.GetOrdinal（  Cb_lenght）） ）; 
 CheckBox10.Checked =（reader.GetBoolean（reader.GetOrdinal（  Channel）） ）; 
} 
 reader.Close（）; 
} 
} 

从sql db获取复选标记我缺少什么？下面是插入sql的方法：

 private void SaveAuditChecklist（）
 {
 if（auditChecklist！= null）
 {
 SqlCommand cmd = null; 
 string conn = ConfigurationManager.ConnectionStrings [ConnectionString]。ConnectionString; 
 
 string queryString = @INSERT INTO AUDITOR_CHECKLIST VALUES（+ 
@SiteID，@ Mount，@ Braker，@ Access，@ ConnNet，@ LogBook，@ Pict，@ Floor，@ CbLenght，@ Channel; 
 
 using（SqlConnection connection = 
 new SqlConnection（conn））
 {
 SqlCommand command = 
 new SqlCommand（queryString，connection）; 
 connection.Open（）; 
 cmd = new SqlCommand（queryString）; 
 cmd.Connection = connection; 
 cmd.Parameters.Add（new SqlParameter（
@SiteID，//要映射的参数名称
 System.Data.SqlDbType.NVarChar，// SqlDbType value 
 20，//参数
Site_ID的宽度））; //列源的名称
 
 //使用从文本字段
 cmd.Parameters [@ SiteID]中检索到的值
 //填充参数。 = foo.Site_ID; 
 cmd.Parameters.Add（new SqlParameter（@ Mount，SqlDbType.Bit））; 
 cmd.Parameters [@ Mount]。Value = CheckBox1.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ Braker，SqlDbType.Bit））; 
 cmd.Parameters [@ Braker]。Value = CheckBox2.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ Access，SqlDbType.Bit））; 
 cmd.Parameters [@ Access]。Value = CheckBox3.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ ConnNet，SqlDbType.Bit））; 
 cmd.Parameters [@ ConnNet]。Value = CheckBox4.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ LogBook，SqlDbType.Bit））; 
 cmd.Parameters [@ LogBook]。Value = CheckBox5.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ Pictures，SqlDbType.Bit））; 
 cmd.Parameters [@ Pictures]。Value = CheckBox6.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ Floor，SqlDbType.Bit））; 
 cmd.Parameters [@ Floor]。Value = CheckBox8.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ CbLenght，SqlDbType.Bit））; 
 cmd.Parameters [@ CbLenght]。Value = CheckBox9.Checked; 
 cmd.Parameters.Add（new SqlParameter（@ Channel，SqlDbType.Bit））; 
 cmd.Parameters [@ Channel]。Value = CheckBox10.Checked; 
 cmd.ExecuteReader（）; 
} 
} 
} 
 

解决方案

你的问题始于

  string  queryString =  @  SELECT @SiteID，@ Many FROM AUDITOR_CHECKLIST + 
   WHERE SITE_ID = @SiteID; 

我认为这应该是

  string  queryString =  @  SELECT  SITE_ID，MOUNT  FROM AUDITOR_CHECKLIST + 
   WHERE SITE_ID = @SiteID; 

即返回数据库中的列！



你还没有说明你的复选框表与这个表有什么关系 - 我猜测SITE_ID会指示哪个复选框到更新，MOUNT是0/1指标



所以你的问题继续...

  if （CheckBox1.Checked ==  true ）
 {
  while （reader.Read（））
 {
 cmd.Parameters.Add（ new  SqlParameter（   @ Mount，SqlDbType.Bit））; 
 cmd.Parameters [  @ Mount]。Value =  1 ; 
} 
} 
  else  
 {
 cmd.Parameters.Add（ new  SqlParameter（  @ Mount，SqlDbType.Bit））; 
 cmd.Parameters [  @ Mount]。Value =  0 ; 
} 

首先，你试图在运行之后向sql添加参数。如果CheckBox1已经已经检查，那么你只运行查询。而你并没有试图设置任何复选框的值。



我怀疑你真的想要类似的东西（注意我还没有测试过这个也没有我放入了所有正确的try-catch，测试null等等）

 while（reader.Read（））
 {
 int i = reader.GetOrdinal（SITE_ID）; 
 int cbNum = reader.GetInt32（i）; 
 //找到我们要更新的复选框 - 我正在使用winform，所以你需要改变这个位以适应asp 
 CheckBox c =（CheckBox）this.Controls.Find（ CheckBox+ cbNum.ToString（），True）; 
 i = reader.GetOrdinal（MOUNT）; 
 
 if（reader.GetByte（i）== 1）
 c.Checked = reader.GetByte（i）== 1？真假; 
} 

尝试用

替换你的sql

 SELECT Mount，Braker，Access，ConnNet，LogBook，Pictures，Floor，CbLenght，Channel FROM AUDITOR_CHECKLIST WHERE SITE_ID = @SiteID; 

注意唯一的@符号用于SiteID 参数

然后你可以使用

<$来读取数据p $ p> while（reader.Read（））
{
CheckBox1.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Mount）））
CheckBox2.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Braker）））;
CheckBox3.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Access）））;
CheckBox4.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（ConnNet）））;
CheckBox5.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（LogBook）））;
CheckBox6.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Pictures）））;
CheckBox8.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Floor）））;
CheckBox9.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Cblenght）））;
CheckBox10.Checked = Convert.ToBoolean（reader.GetByte（reader.GetOrdinal（Channel）））;
}

NB我已经使用了与 Cblenght 相同的拼写 - 它可能是数据库中的Cblength。

I have a table of CheckBoxes that are inserted into a SQL db as ''1'' and ''0''. However, I would like to retrieve those values again with a load event, but I''m not able to get them. This is my code:

    private void getAuditChecklist()
    {
        SqlCommand cmd = null;
        string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string queryString = @"SELECT Mount, Braker, Access, Conn_Net, Log_Book, Pictures, Floor, Cb_Lenght, Channel FROM AUDITOR_CHECKLIST " +
            "WHERE SITE_ID = @SiteID";        

        using (SqlConnection connection =
                   new SqlConnection(conn))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();
            cmd = new SqlCommand(queryString);
            cmd.Connection = connection;

            cmd.Parameters.Add(new SqlParameter("@SiteID", //the name of the parameter to map
                  System.Data.SqlDbType.NVarChar, //SqlDbType value
                  20, //The width of the parameter
                  "SITE_ID")); //The name of the column source
            //Fill the parameter with the value retrieved
            //from the text field
            cmd.Parameters["@SiteID"].Value = foo.Site_ID;

            SqlDataReader reader = cmd.ExecuteReader();

            
while (reader.Read())
       {                    
       CheckBox1.Checked = (reader.GetBoolean(reader.GetOrdinal("Mount")));
       CheckBox2.Checked = (reader.GetBoolean(reader.GetOrdinal("Braker")));
       CheckBox3.Checked = (reader.GetBoolean(reader.GetOrdinal("Access")));
       CheckBox4.Checked = (reader.GetBoolean(reader.GetOrdinal("Conn_Net")));
       CheckBox5.Checked = (reader.GetBoolean(reader.GetOrdinal("Log_Book")));
       CheckBox6.Checked = (reader.GetBoolean(reader.GetOrdinal("Pictures")));
       CheckBox8.Checked = (reader.GetBoolean(reader.GetOrdinal("Floor")));
       CheckBox9.Checked = (reader.GetBoolean(reader.GetOrdinal("Cb_lenght")));
       CheckBox10.Checked = (reader.GetBoolean(reader.GetOrdinal("Channel")));
       } 
       reader.Close();
        }        
    }

What am I missing to get the checkmark from the sql db? Below is how insert into sql:

private void SaveAuditChecklist()
{
    if (auditChecklist != null)
    {
        SqlCommand cmd = null;
        string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        string queryString = @"INSERT INTO AUDITOR_CHECKLIST VALUES(" +
            "@SiteID, @Mount, @Braker, @Access, @ConnNet, @LogBook, @Pictures, @Floor, @CbLenght, @Channel) ";

        using (SqlConnection connection =
                   new SqlConnection(conn))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();
            cmd = new SqlCommand(queryString);
            cmd.Connection = connection;
            cmd.Parameters.Add(new SqlParameter(
                "@SiteID",                        //the name of the parameter to map
                System.Data.SqlDbType.NVarChar,   //SqlDbType value
                20,                               //The width of the parameter
                "Site_ID"));                      //The name of the column source

            //Fill the parameter with the value retrieved
            //from the text field
            cmd.Parameters["@SiteID"].Value = foo.Site_ID;
            cmd.Parameters.Add(new SqlParameter("@Mount", SqlDbType.Bit));
            cmd.Parameters["@Mount"].Value = CheckBox1.Checked;
            cmd.Parameters.Add(new SqlParameter("@Braker", SqlDbType.Bit));
            cmd.Parameters["@Braker"].Value = CheckBox2.Checked;
            cmd.Parameters.Add(new SqlParameter("@Access", SqlDbType.Bit));
            cmd.Parameters["@Access"].Value = CheckBox3.Checked;
            cmd.Parameters.Add(new SqlParameter("@ConnNet", SqlDbType.Bit));
            cmd.Parameters["@ConnNet"].Value = CheckBox4.Checked;
            cmd.Parameters.Add(new SqlParameter("@LogBook", SqlDbType.Bit));
            cmd.Parameters["@LogBook"].Value = CheckBox5.Checked;
            cmd.Parameters.Add(new SqlParameter("@Pictures", SqlDbType.Bit));
            cmd.Parameters["@Pictures"].Value = CheckBox6.Checked;
            cmd.Parameters.Add(new SqlParameter("@Floor", SqlDbType.Bit));
            cmd.Parameters["@Floor"].Value = CheckBox8.Checked;
            cmd.Parameters.Add(new SqlParameter("@CbLenght", SqlDbType.Bit));
            cmd.Parameters["@CbLenght"].Value = CheckBox9.Checked;
            cmd.Parameters.Add(new SqlParameter("@Channel", SqlDbType.Bit));
            cmd.Parameters["@Channel"].Value = CheckBox10.Checked;
            cmd.ExecuteReader();
        }
    }
}

解决方案

Your problems start with

string queryString = @"SELECT @SiteID, @Mount FROM AUDITOR_CHECKLIST " +
            "WHERE SITE_ID = @SiteID";

I think this should be

string queryString = @"SELECT SITE_ID, MOUNT FROM AUDITOR_CHECKLIST " +
            "WHERE SITE_ID = @SiteID";

i.e. return the columns from the database!

You haven''t indicated how your "table of checkboxes" are related to this table - I''m guessing that SITE_ID would indicate which checkbox to update and that MOUNT is the 0/1 indicator

So your problems continue with ...

if (CheckBox1.Checked == true)
            {
                while (reader.Read())
                {
                    cmd.Parameters.Add(new SqlParameter("@Mount", SqlDbType.Bit));
                    cmd.Parameters["@Mount"].Value = 1;
                }
            }
            else
            {
                cmd.Parameters.Add(new SqlParameter("@Mount", SqlDbType.Bit));
                cmd.Parameters["@Mount"].Value = 0;
            }

Firstly, you''re trying to add parameters to the sql after you have run it. and you''re only running the query if CheckBox1 is already checked. And you are not attempting to set the value of any checkbox.

I suspect that you really wanted something like (note I haven''t tested this nor have I put in all of the proper try-catch, tests for null etc etc)

while (reader.Read())
{
    int i = reader.GetOrdinal("SITE_ID");
    int cbNum = reader.GetInt32(i);
    // Find the checkbox we want to update - I''m using winform so you''ll need to change this bit to suit asp
    CheckBox c = (CheckBox)this.Controls.Find("CheckBox" + cbNum.ToString(), True);
    i = reader.GetOrdinal("MOUNT");

    if(reader.GetByte(i) == 1)
        c.Checked = reader.GetByte(i) == 1 ? true : false;    
}

[EDIT - update in response to OP update of question]

Try replacing your sql with

SELECT Mount, Braker, Access, ConnNet, LogBook, Pictures, Floor, CbLenght, Channel FROM AUDITOR_CHECKLIST WHERE SITE_ID = @SiteID;

Note the only "@" symbol is for the SiteID parameter
Then you can read the data using

while (reader.Read())
{
     CheckBox1.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Mount")))
     CheckBox2.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Braker")));
     CheckBox3.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Access")));
     CheckBox4.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("ConnNet")));
     CheckBox5.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("LogBook")));
     CheckBox6.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Pictures")));
     CheckBox8.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Floor")));
     CheckBox9.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Cblenght")));
     CheckBox10.Checked = Convert.ToBoolean(reader.GetByte(reader.GetOrdinal("Channel")));
}

NB I''ve used the same spelling for Cblenght as you have - it might be Cblength on the database.

这篇关于如何从SQL db中检索asp：CheckBox值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！