VB.net and SQL DataSet fill. HELP!!

[chicago75]

I have a problem...

Whenever this code executes, the Dataset fills with the entire DB. It seems to ignore the SQL SELECT statement. Can anyone see what I am doing wrong?

Thx in advance..

 

[Robby]

Which code?

 

[chicago75]

That is what 14 hours of coding will do to a my forgetfullness...Here is the code...

Public Sub custqry()
        If custfn.Text <> "" Or custln.Text <> "" Then
            Dim SQLCN As New SqlClient.SqlConnection("Server=ICS-SVR1;User ID=sa;Password=;Database=ICS-DISPATCH")
            Dim SQLCmd As New SqlCommand
            SQLCN.Close()
            SQLCN.Open()
            Me.custlv1.Items.Clear()
            custlvds.Clear()
            SQLCmd.CommandType = CommandType.Text
            SQLCmd.Connection = SQLCN
            SQLCmd.CommandText = "SELECT * from CUSTDB WHERE custfn LIKE " & custfn.Text & "% and custln LIKE " & custln.Text & "% and custcn like %" & custcn.Text & " and custph like %" & custph.Text & ""
            SqlDataAdapter1.Fill(custlvds, "custdb")
            SQLCN.Close()
            Dim dr As DataRow
            Dim oItem As ListViewItem
            Dim osItem As ListViewItem.ListViewSubItem
            If Not custlvds Is Nothing Then
                For Each dr In custlvds.Tables(0).Rows
                    oItem = New ListViewItem
                    oItem.Tag = dr("CID")
                    osItem = New ListViewItem.ListViewSubItem
                    oItem.Text = dr("custfn")
                    osItem.Text = dr("custln") & ""
                    oItem.SubItems.Add(osItem)
                    osItem = New ListViewItem.ListViewSubItem
                    osItem.Text = dr("custcn") & ""
                    oItem.SubItems.Add(osItem)
                    osItem = New ListViewItem.ListViewSubItem
                    osItem.Text = dr("custph") & ""
                    oItem.SubItems.Add(osItem)
                    Me.custlv1().Items.Add(oItem)

                Next
            End If
        ElseIf custfn.Text <> "" Then
            Exit Sub
        ElseIf custln.Text <> "" Then
            Exit Sub
        Else
            Me.custlv1.Items.Clear()
        End If
    End Sub

 

[Robby]

Where are you declaring the SqlDataAdapter1?

You can pass the Select and Connection String in there...

Dim SqlDataAdapter1 As New SqlDataAdapter(sMySqlStatement, sMyConnectionString)

 

[Nerseus]

You didnt show us where you set the SQL command for the SqlDataAdapter1 object...

Your SQLCmd object has a SQL command, but youre not using it in this code...

Is that your problem, or just a cut-n-paste mistake?

-Nerseus

 

[Sebb77]

If u created the dataadapter using the wizard, u must reassign the select statement for it:

sqldataadapter1.SelectCommand.CommandText="Select bla, bla, bla"

 

[chicago75]

I have added the following line

Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT * from CUSTDB WHERE custfn LIKE " & custfn.Text & "% and custln LIKE " & custln.Text & "% and custph like %" & custph.Text & " and custcn like %" & custcn.Text & "", SQLCN)


I WORKS!!!!!!! Thanks guys....