Build query based on two variables

[di.miller]

Hi All,

In my program I have a combo box that the user selects a date from and a list view that the user selects an employee ID from. How do I properly design my database query string to use both the date selected and the employee ID selected? The date is stored in a variable called YearMonth and the employee ID is stored in a variable called EmpID.

Here is what I am trying to do with my query:

        Dim ExactQuery As String = _
           "SELECT * " & _
            "FROM[JobTypeExact_Query] WHERE (EMPID = ?) AND WHERE (YEARMONTH = YM)" 'Define Exact job type query

        ExactQuery = ExactQuery.Replace("?", CStr(EmpID)).Replace("YM", CStr(YearMonth)) 'Constrain to Employee and YearMonth chosen

Is there something wrong with my syntax here and/or is there an easier way to accomplish this?

Thanks in advance for your help,
Diana

[techgnome]

One word: Parameters ... http://www.vbforums.com/showthread.php?t=548787

-tg

[vb_ftw]

y dont u just put the variables in when declaring? y replace?

Dim ExactQuery As String = _
"SELECT * " & _
"FROM[JobTypeExact_Query] WHERE (EMPID = EmpID) AND WHERE (YEARMONTH = YearMonth)"

[vb_ftw]

i meant
Dim ExactQuery As String = _
"SELECT * " & _
"FROM[JobTypeExact_Query] WHERE (EMPID = " & EmpID & ") AND WHERE (YEARMONTH = "& YearMonth & ")" 'Define Exact job type query

[techgnome]

because the query would still be wrong... and it's a bad habit.

-tg

[di.miller]

Well,

That was a lot to read and I am still confused and not sure how to implement using parameters in my query at all. It seems that I would also have to change a lot more than just my query.

Can someone please be more helpful than telling me to read an article?

Thanks!
-Diana

[Hack]

First, in all SQL queries, you only need one WHERE clause

'it would not be
"FROM[JobTypeExact_Query] WHERE (EMPID = ?) AND WHERE (YEARMONTH = YM)"
'but rather
"FROM[JobTypeExact_Query] WHERE (EMPID = ?) AND YEARMONTH = YM"

With that in mind, lets add some parameters. Try this:

Dim ExactQuery As String = _
           "SELECT * " & _
            "FROM[JobTypeExact_Query] WHERE EMPID = @EMPID AND YEARMONTH = @YM" 
Dim command As New SqlCommand(ExactQuery, myConnection) 'here replace myConnection 
                                                        'with your connection object
command.Parameters.AddWithValue("@EMPID", YourListViewName)        
command.Parameters.AddWithValue("@YM", YourComboBoxName)

That is the basics of using parametized queries.

<Pet_Peeve>Do you really need to have every single solitary field in the entire table returned for your query to work? If not, then why are you doiing SELECT *? Your SELECT clause should contain only those fields that you need for that particular query.</Pet_Peeve>