[RESOLVED] Running different SQL queries based on user selection

[ConfusedAgain]

I want to run a different SQL statement depending what the user types into a text box. So if the user selects "Sales" it runs the Sales Query, if the user selects "Accounts" it runs the accounts query etc etc.
I have tried a IF statement as shown below but i get an error message " The path is not a legal form"

Dim ConnectionString As String = WebConfigurationManager.ConnectionStrings("con").ConnectionString

        Dim soundPath As String
        Dim selectSQL As String

        If txtDept.Text = "Accounts" Then
            selectSQL = "SELECT * FROM vAccounts"

        End If

        If txtDept.Text = "Sales" Then
            selectSQL = "SELECT * FROM vSales"
        End If

        If txtDept.Text = "Logistics" Then
            selectSQL = "SELECT * FROM vLogistics"
        End If



        Dim myconnection As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(selectSQL, myconnection)
        Dim reader As SqlDataReader


        Try

            myconnection.Open()
            reader = cmd.ExecuteReader()
            reader.Read()

            'Fill the controls

            txtDept.Text = reader("Dept").ToString()
            txtStaffID.Text = reader("StaffID").ToString()


            reader.Close()


        Catch ex As Exception
         lblError.Text = Err.Description
        Finally
            myconnection.Close()

        End Try

[techgnome]

On what line? It would help to know where the error happens.

-tg

[ConfusedAgain]

Not too sure as this appears in the lblError for catching any errors. However, what I have noticed is that in the

Dim cmd As New SqlCommand(selectSQL, myconnection)

selectSQL is squigily underlined and if I hover the mouse over it reads.
"Variable SelectSQL is used before it is assigned a value."

Also, just noticed, the correct SQL query runs and populates the fields as it should. But I am a little worried what this error message means!

[sapator]

Put a breakpoint in the beginning to track down the line it gives the error.

For your second question about the underlined problem.
This is happening because you assign the value inside the IF statement.There is a chance that the value will never be assigned.P.E. If you mistakenly type "saless" in the textbox you won't assign a value.
May i suggest that you use a control that will have fixed values,as p.e., a combobox.That way you will always assign a correct value and be more secure from sql injections, speaking of which i urge you not to use the queries this way but at least put some parameters in your code especially if you are going to do something more than select * from table.

[gep13]

Hey,

Are you familiar with how to debug your application? As sap says, you should set a breakpoint in your code, and then step through it to see exactly what is assigned to the selectSQL variable, and what line throws the error.

In addition, it looks to me like the query you are assigning is the same in each case. Is that what you intended?

Would it not make more sense for you to use a parameter within your SQL query, which you could set, based on what the user puts into the textbox.

Gary

[ConfusedAgain]

Thanks for you resposes:
sapator:
I should have said I have set a breakpoint and stepped through the code. Yet no error shows. The only error that shows is in the label that I have assigned the err.description to.

The fields also get populated with the data it should but I am worried that something will go wrong as this message does come up.

gep: The reason I have done it like this is that it picks up a different View depending on what is selected. I had posted the wrong SQL originally, have amended, apologies.

[ConfusedAgain]

Can anyone throw any light on this?
I have got this error coming up now: "Execute reader: CommandText property has not been initilized."

[ConfusedAgain]

Something wrong with the SQL so have wipe out everything and started again and it now works. Trouble is I can't see what is different so I can't really say how I did it.

Thanks all anyways for your help again.

[gep13]

Hey,

The is a similar situation that a lot of people fall into. They create the code and the SQL queries to go with it, and when things don't work the way they expect it, they think that the error lies in their code. In order to isolate the problem, you should always go back to the lowest common denominator. Take the SQL query, and execute it directly against the database. If it works as you expect it, then you know there is something in your code that isn't quite right. On the other hand, if the query doesn't work as you expect it, you know you have isolated your problem.

Gary