Check checkbox based on database query

Question

I need to populate a checkbox in asp C# based on a value of 0 or 1 from my database.

 SqlCommand Comm1 = new SqlCommand("Select Active from dbo.SQLInfrastructure WHERE [Instance] LIKE  '" + label1.Text + "%'", connn);
 connn.Open();
 SqlDataReader DR1 = Comm1.ExecuteReader();


 if (Comm1==1)
 {
     this.activeCheckBox.Checked = false;
 }
 else
 {
     this.activeCheckBox.Checked = true;
 }

 connn.Close();

I believe I am close, this is on the page_load event. This code does not work but conveys what I am trying to accomplish.

if you are using a dataReader you need to loop through it in a while loop `while(DR1.Read()) { }` do a google search on the topic — MethodMan, Sep 15 '16 at 21:35
I would suggest looking at a library to do all the syntactic sugar for you - Dapper is a good one. — Nathan White, Sep 15 '16 at 22:11

score 0 · Answer 1 · answered Sep 15 '16 at 21:18

0

Have a look at this SqlDataReader example

Essentially, before you can use the content of the reader you have to move it to the first record

Also (further to STLDeveloper's comment)... consider using parameterised queries instead of direct values

answered Sep 15 '16 at 21:18

Scott Perham

2,410
1
10
20

I will absolutely use parameterised queries. I wanted to oversimplify it so I could get the code working. Thanks for the comments tho – PoopaSaurasRex Sep 15 '16 at 21:22

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

SqlCommand Comm1 = new SqlCommand("Select Active from dbo.SQLInfrastructure WHERE [Instance] LIKE  '" + label1.Text + "%'", connn);
connn.Open();
SqlDataReader DR1 = Comm1.ExecuteReader();

this.activeCheckBox.Checked = DR1.Read() && DR.GetBoolean(0);

connn.Close();

Also,

This is very much wrong to what you have done over here,

if (Comm1==1){}

since, the SqlCommand object Comm1 cannot have an integer value in it as said by LarsTech in the comments.

edited May 23 '17 at 12:19

Community

1
1

answered Sep 15 '16 at 21:23

Raktim Biswas

4,011
5
27
32

How is "The ExecuteNonQuery()returns the number of rows affected by an INSERT, UPDATE or DELETE statement." useful when he's `selecting`? – Scott Perham Sep 15 '16 at 21:25
Cool... except that _still_ doesn't really help... what if the returned value for `Active` is false? – Scott Perham Sep 15 '16 at 21:35
The `else` part...?? – Raktim Biswas Sep 15 '16 at 21:38
1

You can't just check if there is any record returned... you have to check the value as well (I've edited your code to reflect this) – Scott Perham Sep 15 '16 at 21:39

score 0 · Answer 3 · answered Sep 15 '16 at 21:39

0

DataAdapter might be an other solution,

SqlDataAdapter adp = new SqlDataAdapter("command",connection);
DataTable dt = new DataTable();
adp.Fill(dt);

if((int)dt.AsEnumerable().First()["Active"] == 1) {
    this.activeCheckBox.Checked = false;
}
else{
    this.activeCheckBox.Checked = true;
}

Hope helps,

answered Sep 15 '16 at 21:39

Berkay Yaylacı

4,383
2
20
37

I get an access violation when I use this example. Thanks for your input – PoopaSaurasRex Sep 16 '16 at 11:35
I assumed that "Active" has an integer datatype at sql? If there is a missmatch, it is normal to get access violation. @PoopaSaurasRex – Berkay Yaylacı Sep 16 '16 at 11:40
Thank you! it works.Yes the issue was at the database level, I changed it to int. – PoopaSaurasRex Sep 16 '16 at 11:48
Your welcome, (don't forget to upvote if it is the answer :)). @PoopaSaurasRex – Berkay Yaylacı Sep 16 '16 at 11:49

score 0 · Answer 4 · edited May 23 '17 at 12:08

0

Take a look at the ExecuteScalar() method of the SqlCommand class:

this.activeCheckBox.Checked = (bool)Comm1.ExecuteScalar;

Also, as STLDeveloper indicates, you should really be using SqlParameters to pass user input into your SQL statement.

edited May 23 '17 at 12:08

Community

1
1

answered Sep 15 '16 at 22:06

ssis_ssiSucks

1,476
1
12
11

Check checkbox based on database query

4 Answers4