locked
Cant read SQL checkbox RRS feed

  • Question

  • User1390435587 posted

    No idea what I'm doing wrong here, I have searched but cant find an answer online. I have a SQL database and I'm using VS 2015 Community edition I want to read the bit value of the database.

    In this example some people can tow, some cannot. Previously this has been saved in the database by selection of a checkbox. So the database has three possible states, True, False and NULL

    Gridview1 correctly shows a 'tick' in the correct box when previously saved as such. In this code example I have selected row 3 because I know that is checked. But the Messagebox shows empty "", it never shows True, false or even 0 or 1, it always shows a null value. I need to get the value of this gridview cell for further processing.

    Here is the code -

    Note  - I have reduced the code to the relevant section (no Using, stop using etc)

      Dim FD = From Person_data_Table In db.Person_data_tables, Date_Table In db.Date_Tables
                         Select Person_data_Table.MK, Person_data_Table.Can_Tow, Person_data_Table.Can_Tow1 Distinct
    
                GridView1.DataSource = FD
                GridView1.DataBind()
    
                MsgBox(GridView1.Rows(3).Cells(1).Text)

    If I select Cell 0  (any row) it correctly reads the MK value so the DB is valid and working etc,. -  What I need is something like this, but right now it always answers with 'Nothing here

    Dim test as string (0r something else?)  = Gridview1.Rows(3).Cells(1).text

    If test = "" Then MsgBox("nothing here") ElseIf test = "True" Then MsgBox("Ticked") Else MsgBox("not ticked") End If

    Any help much appreciated as ever

    regards

    Peter

    Saturday, October 29, 2016 7:53 AM

Answers

  • User-2057865890 posted

    Hi Peter,

    Html Markup

    <div>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                    <Columns>
                        <asp:BoundField DataField="MK" HeaderText="MK" ReadOnly="True" />
                        <asp:TemplateField HeaderText="CB_Field">
                            <ItemTemplate>
                                <asp:Label ID="Label2" runat="server" Text='<%#Boolean.Parse(Eval("CB_Field").ToString())?"True":"False" %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
    
                </asp:GridView>
                <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
            </div>

    Code behind

    protected void Page_Load(object sender, EventArgs e)
            {
                this.BindGrid();
                Label1.Text = ((Label)GridView1.Rows[0].FindControl("Label2")).Text;
            }
            private void BindGrid()
            {
                string constr = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT MK,CB_Field FROM MyTestTable"))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                            }
                        }
                    }
                }
            }

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 10:01 AM

All replies

  • User753101303 posted

    Hi,

    Put a breakpoint and use the immediate window to inspect the grid? Maybe you are not looking at the right row (0 is the header) and you are really looking at a line where this value is null.

    It"s niot directly related but you may want to consider changing your programming style.. Controls are just a way to render data and code that renders data to a ui and read back then data from the ui for various kind of things tends to be hard to read and error prone (you have to look at another line to understand what Cells[1], what if you had another column later  etc...). Instead try to work directly on your data rather than on the ui that shows your data (for example here you would just access to a Persona_Data obbect at position 2 in a list and would just use its Can_Tow property while hre you have to wonder how to read back the data you want from your UI).

    Saturday, October 29, 2016 10:06 AM
  • User1390435587 posted

    Hi and thanks for your time.

    The output (GridView1) looks like this below. (You have to use your imagination I'm afraid, the word tick indicates I'm seeing a tick in the box.). To be clear what your seeing here is a gridview with 3 columns and 5 rows (including the header). The final row has a tick under Can_Tow and Can_Tow1. This is exactly what is in the database and is being correctly displayed by GV1

    MK   Can_tow   Can_tow1

    5602

    5603

    5604

    5605     Tick           Tick

    If I try to 'see' the value of row 3 column 1 I see nothing, its empty. (the same for row 4 as well I tried)

    This is not the answer, but I have a concern about the header being row 0. If I run the code below the message box does display 5602, and not the word MK as you are suggesting. Which indicates that the first row of data is row 0, and not the header?

     Dim FD = From Person_data_Table In db.Person_data_tables, Date_Table In db.Date_Tables
                         Select Person_data_Table.MK, Person_data_Table.Can_Tow, Person_data_Table.Can_Tow1 Distinct
    
                GridView1.DataSource = FD
                GridView1.DataBind()
    
                MsgBox(GridView1.Rows(0).Cells(0).Text)

    Saturday, October 29, 2016 12:12 PM
  • User753101303 posted

    Ok so if you look at the first cell you see "5605" ? It would allow to first make 100% sure you are on the right line. If you still don't see the expected value which control are you using? Could it be that this cell contains a child control?

    I would really put a breakpoint here and would use the "immediate window" to show various expression an understand the strructure of the cell I'm trying to get at. At last if using this approach. Not sure what you'll do then with this value but actually I would read the data directly from the datasource rather than from the gridview.

    Saturday, October 29, 2016 2:39 PM
  • User1390435587 posted

    Many thanks, I'm definitely on the right row, I changed the first line data so that both boxes were ticked, still nothing. 

    I'm using Bootstrap on the page, I dont know whether thats effecting it in some way. I have done the same thing on previous sites I have built and never had a problem. Its so odd that GV1 can 'see' that the box should be ticked, yet I cant read that information myself. When I examine the DB its shown as 'True', yet whenever I look at the cell I see nothing at all.

    I shall continue to monkey with it, see if can find an answer

    best 

    Peter

    Sunday, October 30, 2016 12:44 PM
  • User-2057865890 posted

    Hi Peter,

    If you want to display checked when it has Value and Unchecked when it doesn't have a value, you could use GridView.RowDataBound Event.

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
         if (e.Row.RowType == DataControlRowType.DataRow)
         {
              if (e.Row.Cells[1].Text != null)
              {
                  //Removes the Previous Controls
                  e.Row.Cells[1].Controls.Clear();
    
                  /Adds a checkbox with tick
                  CheckBox cb = new CheckBox();
                  cb.ID = "cb1";
                  cb.Checked = true;
                  e.Row.Cells[1].Controls.Add(cb);
    
              }
         }
    }

    reference: https://forums.asp.net/post/5633318.aspx 

    Best Regards,

    Chris

    Wednesday, November 2, 2016 7:46 AM
  • User1390435587 posted

    Hello Chris

    Thanks for this I really appreciate you taking the time to help. This is a really crazy problem, I cant understand what I'm doing wrong. I have made the whole thing as simple as I can to try to identify the issue but it just does not work.

    I appreciate your a busy guy, but if you have any time at all can I ask you to try something for me?

    Create a SQL database with two fields, a master key (MK) and a 'bit' field (CB_Field). Enter some random data to create say 4 rows. 

    Lets say

    MK  CB_Field

    1       TRUE

    2       TRUE

    3       FALSE

    4       TRUE

    Bind the data above to a gridview (GV1) as I have done above.

    Then run this code

    Dim test as string = gv1.rows(0).cells(1).text

    Msgbox (test)

    When I do the above the message box is always totally empty, what I was expecting was it to return 'true'

    Thanks in advance

    regards

    Peter

    Wednesday, November 2, 2016 8:04 AM
  • User-2057865890 posted

    Hi Peter,

    Html Markup

    <div>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                    <Columns>
                        <asp:BoundField DataField="MK" HeaderText="MK" ReadOnly="True" />
                        <asp:TemplateField HeaderText="CB_Field">
                            <ItemTemplate>
                                <asp:Label ID="Label2" runat="server" Text='<%#Boolean.Parse(Eval("CB_Field").ToString())?"True":"False" %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
    
                </asp:GridView>
                <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
            </div>

    Code behind

    protected void Page_Load(object sender, EventArgs e)
            {
                this.BindGrid();
                Label1.Text = ((Label)GridView1.Rows[0].FindControl("Label2")).Text;
            }
            private void BindGrid()
            {
                string constr = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT MK,CB_Field FROM MyTestTable"))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                            }
                        }
                    }
                }
            }

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 2, 2016 10:01 AM
  • User1390435587 posted

    Thank you so much Chris.

    I'm at work now so can't adopt this solution yet, but I will have a go tonight. I very much appreciate you taking the time to do the above for me, it was really good of you.

    All the very best

    Peter 

    Wednesday, November 2, 2016 10:48 AM
  • User-2057865890 posted

    Hi Peter,

    I edit my reply with screenshot.

    Best Regards,

    Chris

    Wednesday, November 2, 2016 10:53 AM