6 Replies - 4292 Views - Last Post: 01 February 2011 - 01:04 PM Rate Topic: -----

#1 wbwalsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 09-September 09

C# SQL Select Statement With 29 CheckBoxes

Posted 31 January 2011 - 08:40 PM

Hi guys,

I have been going around in circles trying to make a simple select statement into a monster.

I have a form with a listbox, picturebox and 29 checkboxes. When a name is selected in the listbox the image linked to the image path stored in the database record is displayed in the picturebox and all 29 checkboxes are filled by the true or false stored in the database record. The checkboxes are attributes for the person displayed in the picturebox: male, female, racearabic, raceasian, raceblack, racehispanic, raceother, racewhite, haircolorlight, haircolordark, etc., etc. etc.

I built forms to load the "filler" images and other forms that allow the program to funciton for the user.

Where is my current problem. There is a button on the form that shows the picture and attribute checkboxes. When the button is clicked I want to pass the checkbox values in a select statement to pull the records corresonding to the picture being shown.

The reason for this program is to create a photo lineup of a suspect and 5 filler images. When the suspect is selected from the listbox and his/her picture is in the picturebox the 29 checkboxes will be populated with the suspects attributes. When the button is clicked the sql select statement will pull from the filler table to locate filler images that match the suspect's checkbox attributes.

Here is my current code after many, many, many revisions. Help if you can. Thanks.


        private void btnLineupLoadFillerPhotos_Click(object sender, EventArgs e)
        {

            try
            {
            int qGenderMale = 0;
            int qGenderFemale = 0;
            int qRaceArabic = 0;
            int qRaceAsian = 0;
            int qRaceBlack = 0;
            int qRaceHispanic = 0;
            int qRaceOther = 0;
            int qRaceWhite = 0;
            int qHairBald = 0;
            int qHairHat = 0;
            int qHairShort = 0;
            int qHairLong = 0;
            int qHairDreadlocks = 0;
            int qHairColorLight = 0;
            int qHairColorDark = 0;
            int qFaceAcne = 0;
            int qFaceClean = 0;
            int qFaceBeard = 0;
            int qFaceGoatee = 0;
            int qFaceGoldTeeth = 0;
            int qFaceGlasses = 0;
            int qFaceMustache = 0;
            int qFacePiercing = 0;
            int qBodyTypeThin = 0;
            int qBodyTypeAverage = 0;
            int qBodyTypeHeavy = 0;
            int qAgeElderly = 0;
            int qAgeMiddle = 0;
            int qAgeYoung = 0;

                if (cbxLineupGenderFemale.Checked)
                {
                    qGenderFemale = 1;
                }
                if (cbxLineupGenderMale.Checked)
                {
                    qGenderMale = 1;
                }
                if (cbxLineupRaceArabic.Checked)
                {
                    qRaceArabic = 1;
                }
                  if (cbxLineupRaceAsian.Checked)
                {
                    qRaceAsian = 1;
                }
                 if (cbxLineupRaceBlack.Checked)
                {
                    qRaceBlack = 1;
                }
                 if (cbxLineupRaceHispanic.Checked)
                {
                    qRaceHispanic = 1;
                }
                if (cbxLineupRaceOther.Checked)
                {
                    qRaceOther = 1;
                }
                 if (cbxLineupRaceWhite.Checked)
                {
                    qRaceWhite = 1;
                }
                if (cbxLineupHairColorLight.Checked)
                {
                    qHairColorLight  = 1;
                }
                if (cbxLineupHairColorDark.Checked)
                {
                    qHairColorDark = 1;
                }
                if (cbxLineupHairBald.Checked)
                {
                    qHairBald = 1;
                }
                if (cbxLineupHairDreadlocks.Checked)
                {
                    qHairDreadlocks = 1;
                }
                if (cbxLineupHairHat.Checked)
                {
                    qHairHat = 1;
                }
                if (cbxLineupHairShort.Checked)
                {
                    qHairShort = 1;
                }
                if (cbxLineupHairLong.Checked)
                {
                    qHairLong = 1;
                }
                if (cbxLineupFaceAcne.Checked)
                {
                    qFaceAcne = 1;
                }
                if (cbxLineupFaceClean.Checked)
                {
                    qFaceClean = 1;
                }
                if (cbxLineupFaceBeard.Checked)
                {
                    qFaceBeard = 1;
                }
                if (cbxLineupFaceGoatee.Checked)
                {
                    qFaceGoatee = 1;
                }
                if (cbxLineupFaceGoldTeeth.Checked)
                {
                    qFaceGoldTeeth = 1;
                }
                if (cbxLineupFaceGlasses.Checked)
                {
                    qFaceGlasses = 1;
                }
                if (cbxLineupFaceMustache.Checked)
                {
                    qFaceMustache = 1;
                }
                if (cbxLineupFacePiercing.Checked)
                {
                    qFacePiercing = 1;
                }
                if (cbxLineupBodyTypeThin.Checked)
                {
                    qBodyTypeThin = 1;
                }
                if (cbxLineupBodyTypeAverage.Checked)
                {
                    qBodyTypeAverage = 1;
                }
                if (cbxLineupBodyTypeHeavy.Checked)
                {
                    qBodyTypeHeavy = 1;
                }
                if (cbxLineupAgeElderly.Checked)
                {
                    qAgeElderly = 1;
                }
                if (cbxLineupAgeMiddle.Checked)
                {
                    qAgeMiddle = 1;
                }
                if (cbxLineupAgeYoung.Checked)
                {
                    qAgeYoung = 1;
                }
                string connectionString = "Data Source=walsh_02\\sqlexpress;Database=PPDOne_db;Integrated Security=True;";
                SqlConnection conn = new SqlConnection(connectionString);
                string commandString = ("SELECT GenderMale, GenderFemale, RaceArabic, RaceAsian, RaceBlack, RaceHispanic, RaceOther, RaceWhite FROM tblFiller WHERE GenderMale = "+ qGenderMale +" AND GenderFemale = "+ qGenderFemale +" AND RaceArabic = "+ qRaceArabic +" AND RaceAsian = "+ qRaceAsian +" AND RaceBlack = "+ qRaceBlack +" AND RaceHispanic = "+ qRaceHispanic +" AND RaceOther = "+ qRaceOther +" AND RaceWhite = "+ qRaceWhite+"");
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(commandString, conn);
                da.Fill(ds);
    
                //Show Message If Select Statement Successful To View Record Count
                [quote][/quote]MessageBox.Show("Number Of Records Returned: " + ds.Tables[0].Rows.Count, "Message Center", MessageBoxButtons.OK, MessageBoxIcon.Information);
                ResetLineupQuery();
                conn.Close();
                ds.Clear();
                ds.Reset();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Filler Photos Unable To Loade. Check Program Manager Settings To Correct The Following Error: " + ex.Message, "Message Center", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
     }



This post has been edited by wbwalsh: 31 January 2011 - 08:42 PM


Is This A Good Question/Topic? 0
  • +

Replies To: C# SQL Select Statement With 29 CheckBoxes

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: C# SQL Select Statement With 29 CheckBoxes

Posted 01 February 2011 - 03:53 AM

1. don't build your query like
string commandString = ("SELECT GenderMale, GenderFemale, RaceArabic, RaceAsian, RaceBlack, RaceHispanic, RaceOther, RaceWhite FROM tblFiller WHERE GenderMale = "+ qGenderMale +" AND GenderFemale = "+ qGenderFemale +" AND RaceArabic = "+ qRaceArabic +" AND RaceAsian = "+ qRaceAsian +" AND RaceBlack = "+ qRaceBlack +" AND RaceHispanic = "+ qRaceHispanic +" AND RaceOther = "+ qRaceOther +" AND RaceWhite = "+ qRaceWhite+""); 


this way you open your code to SQL injection. Use parameterized queries
2. Even if you use this code, qGenderMale(and all others variables) is defined as int, so you have to pass a string to commandString. use
qGenderMale.toString();


3. What doesn't work in your code?
4. This topic should be moved to C# section

This post has been edited by Ionut: 01 February 2011 - 03:54 AM

Was This Post Helpful? 2
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: C# SQL Select Statement With 29 CheckBoxes

Posted 01 February 2011 - 04:41 AM

I'm confused, you have booleans, but shouldn't something like cbxLineupGenderFemale and cbxLineupGenderMale be a single drop down or radio button? Does it make sense to check both? Neither?

You don't need to fear SQL injection here, because your input is controlled by, well, the control.

If you wanted to just build SQL, you could do:
string commandString = "SELECT GenderMale, GenderFemale, RaceArabic, RaceAsian, RaceBlack, RaceHispanic, RaceOther, RaceWhite"
	+ " FROM tblFiller"
	+ " WHERE";

commandString += " GenderFemale=" + (cbxLineupGenderFemale.Checked ? "1" : "0");
commandString += " AND GenderMale=" + (cbxLineupGenderMale.Checked ? "1" : "0");
//...



Prefered would be a parameterized query, though. If not for safety, merely for speed.
SqlCommand cmd = conn.CreateCommand()
cmd.CommandText = "SELECT GenderMale, GenderFemale, RaceArabic, RaceAsian, RaceBlack, RaceHispanic, RaceOther, RaceWhite"
	+ " FROM tblFiller"
	+ " WHERE GenderMale=@GenderMale"
	+ " AND GenderFemale=@GenderFemale"
	;

cmd.Parameters.Add("@GenderMale", SqlDbType.Bit);
cmd.Parameters.Add("@GenderFemale", SqlDbType.Bit);
//...
cmd.Parameters["@GenderMale"].Value = cbxLineupGenderMale.Checked;
cmd.Parameters["@GenderFemale"].Value = cbxLineupGenderFemale.Checked;
//...
DataSet ds = new DataSet();
new SqlDataAdapter(cmd).Fill(ds);



Edit: And moved topic. Thanks for dropping by, though.

This post has been edited by baavgai: 01 February 2011 - 04:43 AM

Was This Post Helpful? 2
  • +
  • -

#4 wbwalsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 09-September 09

Re: C# SQL Select Statement With 29 CheckBoxes

Posted 01 February 2011 - 09:05 AM

Yes looking at GenderMale and GenderFemale they could be radio buttons. It becomes more complex with the remaining 27 checkboxes. A person could be classified as RaceWhite, RaceHispanic and RaceOther depending on how they look and the Face checkboxes have Acne, Clean, Glasses, Hat and a few more so a record could have multiple boxes checked. The checkboxes are held inside groupboxes.

The screne that allows the user to create the photo lineup shows the suspect photo and displays their 29 checkboxes filled as saved in the databases. The user has the option to check or uncheck additional checkboxes to include or exclude more/less query results so the ability to query the database looking at all 29 checkboxes should be made available.

I am going to try to build my select as you have shown. Being self taught I have never coded a database app that gets this complex... I am pushing my skills and learning as I go.

Will try to send a screen shot later today but the form I am working on has the a suspect photo box and 5 filler photo boxes with another 10 smaller filler photo boxes and the checkboxes. When the user hit the control to fill the filler photo boxes the system should randomly select 5 photos for the 5 larger boxes and the remaining 10 boxes are filled. There are forward, backward, first and last buttons to scroll through the returned images in the smaller 10 boxes. The user can replace one or more of the 5 larger filler photos with one of the smaller 10 photos.

Anyway... Thanks again and will post my progress... will probable need more help!

This post has been edited by wbwalsh: 01 February 2011 - 09:06 AM

Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: C# SQL Select Statement With 29 CheckBoxes

Posted 01 February 2011 - 09:58 AM

Are the fields in your database setup as 'bit' fields? or 'int' fields?

Hoping they are 'bit' fields, there is no need to convert the Checked/Unchecked value to an integer. You can use the parameterized query that baavgai posted to just use the boolean value from the checkbox. It will cut down on the lines of code, and make it easier to create the query.
Was This Post Helpful? 0
  • +
  • -

#6 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: C# SQL Select Statement With 29 CheckBoxes

Posted 01 February 2011 - 10:21 AM

Your database design needs some work.

For example:

int qRaceArabic = 0;
int qRaceAsian = 0;
int qRaceBlack = 0;
int qRaceHispanic = 0;
int qRaceOther = 0;
int qRaceWhite = 0;

I'm guessing you have those columns in your table, correct? A better solution would be to have something like:

create table Race
(
RaceID int primary key identity(1,1),
Description nvarchar(64)
);

create table Person
(
PersonID int primary key identity(1,1),
RaceID int foreign key references Race(RaceID)
);


Why would you want to do this? It's easier to fetch a collection of People from a certain race. You can do the same for say, hair styles. Have a HairType table and fill them in. Then in a comboBox load the different types.

I suggest you read about database normalization, it's a solid investment of your time:
http://www.devshed.c...e-Normalization
Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5642
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: C# SQL Select Statement With 29 CheckBoxes

Posted 01 February 2011 - 01:04 PM

Simplest form: just tag it.

Database could be:
create table Person (
	PersonId int primary key identity(1,1),
	FullName nvarchar(100) not null
);

create table PersonTag (
	PersonId int,
	Tag varchar(20)
);



Make a list of valid tags and you're off.

If you want everyone with a collection of tags, you just join on a list.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1