SQL Like command with multiple conditions

[takwirira]

Im having some trouble using the SQL Like command with multiple conditions

where as the first line of code works with one condition the second one doesnt even thought it doesnt return any errors

Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM chdetails WHERE chname LIKE '%" & txtFRname.Text & "%'", myConnection)

Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM chdetails WHERE (chname LIKE '%" & txtFRname.Text & "%') AND (chsurname LIKE '%" & txtFRsname.Text & "%') AND (chDOB = '%" & txtFRdob.Text & "%')", myConnection)


Full Code is below

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" & Server.MapPath("db2.mdb")
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter

Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM chdetails WHERE (chname LIKE '%" & txtFRname.Text & "%') AND (chsurname LIKE '%" & txtFRsname.Text & "%') AND (chDOB = '%" & txtFRdob.Text & "%')", myConnection)

' create a new dataset

Dim ds As DataSet = New DataSet
' fill dataset

da.Fill(ds, "chdetails")
' Attach DataSet to DataGrid

GridView1.DataSource = ds
GridView1.DataBind()

MultiView1.SetActiveView(child)

Where am I going wrong

 

[InertiaM]

Where am I going wrong

1. Not using CODE boxes within your posts to make code easier to read for the rest of us

2. Not using parameters to build your queries - read this post

3. Probably not sending a correctly formatted value to a date field (chDOB)?

 

[takwirira]

Thank you for the information. Im still new to programming and this forum I dont know how to use code boxes so if you could assist that would be a great help. Anyway the code works now as below but I am intrigued about using parameters it definately sound more secure. I have enclosed the code I used to insert and retrieve.If you could help me convert the code to use parameters I would be more than grateful

            Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" & Server.MapPath("db2.mdb")
            Dim myConnection As OleDbConnection = New OleDbConnection
            myConnection.ConnectionString = connString
            ' create a data adapter

            Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT name,surname,add1,add2,town,province,postcode,country,tel,mobile,email FROM details WHERE (chname LIKE '%" & txtFRname.Text & "%') AND (chsurname LIKE '%" & txtFRsname.Text & "%') AND (chDOB = '" & txtFRdob.Text & "')", myConnection)

            ' create a new dataset

            Dim ds As DataSet = New DataSet
            ' fill dataset

            da.Fill(ds, "details")
            ' Attach DataSet to DataGrid

            GridView1.DataSource = ds
            GridView1.DataBind()

            MultiView1.SetActiveView(child)

        Dim conn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim sConnString As String

        sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
     Server.MapPath("db2.mdb")
        'open the connection
        conn.Open(sConnString)
        cmd.ActiveConnection = conn

        cmd.CommandText = "INSERT INTO details (name,surname,add1,add2,town,province,postcode,country,tel,mobile,email)" & _
           "VALUES " & _
            "('" & mtxtname.Text & "'," & _
            "'" & mtxtsurname.Text & "'," & _
            "'" & mtxtadd1.Text & "'," & _
            "'" & mtxtadd2.Text & "'," & _
            "'" & mtxttown.Text & "'," & _
            "'" & mtxtprovince.Text & "'," & _
            "'" & mtxtpostcode.Text & "'," & _
            "'" & mtxtcountry.Text & "'," & _
            "'" & mtxttel.Text & "'," & _
            "'" & mtxtmobile.Text & "'," & _
            "'" & mtxtemail.Text & "'," & _
            "'" & mtxtchname.Text & "'," & _
            "'" & mtxtchsurname.Text & "'," & _
            "'" & mtxtchDOB.Text & "')"

        cmd.Execute()

        cmd = Nothing
        conn.Close()
        conn = Nothing

 

[InertiaM]

Havent used ADODB for ages so my apologies if this syntax is not correct, but you'll get the idea

        cmd.CommandText = "INSERT INTO details (surname,add1,add2) VALUES (@SURNAME, @ADD1, @ADD2)"

        cmd.Parameters.Append("@SURNAME")
        cmd.Parameters("@SURNAME").Type = ADODB.DataTypeEnum.adVarChar
        cmd.Parameters("@SURNAME").Size = 30 'characters (for example)

        cmd.Parameters("@SURNAME").Value = "SIMPSON"

        cmd.Execute()

 

[cjard]

takwirira, read the DW2 link in my signature, section "Creating a Simple Data App"