query with parameters

[curiousvbnet]

Hi,
I try to create parametered queries but i see error messages when i run them.

Here are these queries

 [red]Dim strSQL_recherche As String, obj_recherche As SqlCommand[/red]

 strSQL_recherche = "select DISTINCT Lib_TERME as 'TERMES COMMENCANT PAR """ & txt_critere.Text.Trim.ToUpper & """ ' from TERMES where Lib_TERME like '" & txt_critere.Text.Trim & "%' order by Lib_TERME"

this query is changed in

[blue] strSQL_recherche =  "select DISTINCT Lib_TERME as 'TERMES COMMENCANT PAR ""'  @txtcritere  '""' from TERMES where Lib_TERME like @critere order by Lib_TERME"
obj_recherche = New SqlCommand(strSQL_recherche, objConn)

[/blue]
[green]obj_recherche.Parameters.Add(New SqlParameter("@txtcritere", txt_critere.Text.Trim.ToUpper))

 obj_recherche.Parameters.Add(New SqlParameter("@critere", txt_critere.Text.Trim & "%"))[/green]

                If objConn.State = ConnectionState.Closed then
                    objConn.Open()
                End If
                obj_recherche.ExecuteNonQuery()

could you help me to improve this query to make it possible with parameters.

Thanks a lot for your help.

Regards
Nathalie

 

[NoCoolHandle]

Here is an example that works with the northwind database..
You will notice that there is no need for quoting...
Part of the key is passing the right type of data to the parameter.

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        someadhocproc("VINET", "7/4/1996")
    End Sub

    Sub someadhocproc(ByVal CustomerName As String, ByVal OrderDate As Date)
        Dim c As New SqlClient.SqlConnection("server=.\sql2005demo;database=northwind;integrated security=sspi")
        Dim cmd As New SqlClient.SqlCommand("Select * from orders where customerid=@customerid and orderdate=@orderdate", c)
        cmd.CommandType = CommandType.Text
        Dim dr As SqlClient.SqlDataReader
        With cmd
            .Parameters.Add("@customerid", SqlDbType.VarChar, 5).Value = CustomerName
            .Parameters.Add("@orderdate", SqlDbType.DateTime).Value = OrderDate
        End With
        c.Open()
        dr = cmd.ExecuteReader()


    End Sub

 

[curiousvbnet]

Hi and thanks a lot.

Here is the new request but i receive the error message the variable @critere1 has to be declared what i don't understand.

[blue]strSQL_recherche = "select DISTINCT Lib_TERME as 'TERMES COMMENCANT PAR @txtcritere'  from TERMES where Lib_TERME like @critere1 order by Lib_TERME"[/blue]
               [green] obj_recherche = New SqlCommand(strSQL_recherche, objConn)
                obj_recherche.Parameters.Add(New SqlParameter("@txtcritere", txt_critere.Text.Trim.ToUpper))
                obj_recherche.Parameters.Add(New SqlParameter("@critere1", txt_critere.Text.Trim.ToUpper & "%"))[/green]


If objConn.State = ConnectionState.Closed Then
                    objConn.Open()
                End If
                obj_recherche.ExecuteNonQuery()

                objDA10 = New SqlDataAdapter(strSQL_recherche, objConn)
                [red]objDA10.Fill(objDS10, "RESULTAT_REQUETE")[/red]<- here i receive the error message

i receive the error message i have to declare the variable @critere1 at the red line level.

Thanks a lot again for all your help

Regards.
Nathalie

 

[chrissie1]

I think this

strSQL_recherche = "select DISTINCT Lib_TERME as 'TERMES COMMENCANT PAR @txtcritere'  from TERMES where Lib_TERME like @critere1 order by Lib_TERME"
obj_recherche = New SqlCommand(strSQL_recherche, objConn)
obj_recherche.Parameters.Add(New SqlParameter("@txtcritere", txt_critere.Text.Trim.ToUpper))
obj_recherche.Parameters.Add(New SqlParameter("@critere1", txt_critere.Text.Trim.ToUpper & "%"))

If objConn.State = ConnectionState.Closed Then
  objConn.Open()
End If
objDA10 = New SqlDataAdapter()
objDA10.connection = objconn
objDA10.selectcommand = obj_recherche
objDA10.Fill(objDS10, "RESULTAT_REQUETE")

Christiaan Baes
Belgium

"My old site" - Me