CODEWITHSUNDEEP
sqlvisual-studio-2010variables
Green Developer
Green Developer

Reputation: 187

Insert Variable Into SQL Query Visual Studio 2010

I have a SQL query that looks like this;

Dim query As String = "SELECT ID, " & Environment.NewLine & _
                      "month_of_sale, " & Environment.NewLine & _
                      "client_last_name, " & Environment.NewLine & _
                      "client_first_name, " & Environment.NewLine & _
                      "deal_number, " & Environment.NewLine & _
                      "stock_number, " & Environment.NewLine & _
                      "delivery_date, " & Environment.NewLine & _
                      "finance_manager, " & Environment.NewLine & _
                      "finance_income, " & Environment.NewLine & _
                      "record_entered " & Environment.NewLine & _
                      "FROM(dodge_records)" & Environment.NewLine & _
                      "WHERE month_of_sale = @month_of_sale AND client_last_name = @client_last_name" & Environment.NewLine & _
                      "ORDER BY client_last_name"

I am trying to insert a variable into the SQL query but I don't see it happening when I debug the website. Here is the rest of the code so that it makes more sense.

Case "Last Name"
                    Try
                        Dim query As String = "SELECT ID, " & Environment.NewLine & _
                            "month_of_sale, " & Environment.NewLine & _
                            "client_last_name, " & Environment.NewLine & _
                            "client_first_name, " & Environment.NewLine & _
                            "deal_number, " & Environment.NewLine & _
                            "stock_number, " & Environment.NewLine & _
                            "delivery_date, " & Environment.NewLine & _
                            "finance_manager, " & Environment.NewLine & _
                            "finance_income, " & Environment.NewLine & _
                            "record_entered " & Environment.NewLine & _
                            "FROM(dodge_records)" & Environment.NewLine & _
                            "WHERE month_of_sale = @month_of_sale" & Environment.NewLine & _
                            "WHERE client_last_name = @client_last_name" & Environment.NewLine & _
                            "ORDER BY client_last_name"

                        Using Connection = New MySqlConnection(connStr)
                            Using da = New MySqlDataAdapter(query, Connection)
                                da.SelectCommand.Parameters.AddWithValue("@month_of_sale", CurrentMonth)
                                da.SelectCommand.Parameters.AddWithValue("@client_last_name", ValueBoxText)
                                Dim DS As New DataSet
                                da.Fill(DS)
                                DodgeSearchGridView.DataSource = DS
                                DodgeSearchGridView.DataBind()
                                Connection.Close()
                            End Using
                        End Using
                    Catch ex As Exception
                    End Try

CurrentMonth & ValueBoxText are strings that I need inserted into the SQL query. Right now this isn't working how I want it to. I don't get an error message or anything, the query doesn't return anything and I have double checked the database to make sure there is data in it that would be returned by this query. Can anyone help me error check the code so I can find out how to make this work? Thanks!

Upvotes: 0

Views: 1968

Answers (2)

Eric
Eric

Reputation: 1068

Step through the code in the VS debugger and paste the query generated from the command into SQL Server Management studio and try it there.

Upvotes: 1

kheld
kheld

Reputation: 792

Your SQL is incorrect. You have used WHERE twice. The second condition should use AND ie
AND client_last_name

Upvotes: 2

Related Questions