Can't figure out where clause in sql statement.

[Locke]

I know I'm doing something wrong, and it's something simple but I can't
figure it out. I'm new at this and it's only a little more than a
hobby so please bear with me.

I'm writing a VB shell around an Access database. I have an object
that handles the communication of data between my main form and the
database. It creates a oledb data connection, - no problems. It then
builds an adapter and fills a dataset of customer information, - no
problems. However, it then recreates the adapter and fills a dataset
with information from a second table. There's actually no problem with
this except when I try to filter the data being populated into the
second dataset. Here's the most relevent code of the project:

'Build Data Adapter
Private Sub BuildAdapter(By Val Table As String, Optional ByVal
SearchCrit As String = "")

If Table = "CustomerDa ta" Then
'Build Data Adapter with Select Statement
Dim MySelect As String = "SELECT * FROM CustomerData"
DataAdapter = New OleDb.OleDbData Adapter(MySelec t,
DataConnection)
ElseIf Table = "Bracelets" Then
Dim MySelect As String
If SearchCrit = "" Then
MySelect = "SELECT * FROM Bracelets"
Else
MySelect = "SELECT * FROM Bracelets WHERE CustNum=" &
SearchCrit
End If
DataAdapter = New OleDb.OleDbData Adapter(MySelec t,
DataConnection)
End If

'Build the Insert, Delete, and Update statements
Dim cb As New OleDb.OleDbComm andBuilder(Data Adapter)

Debug.WriteLine ("SELECT Command: " &
DataAdapter.Sel ectCommand.Comm andText)

End Sub

'Now fill the dataset
DataAdapter.Fil l(mdsBracelets, "Bracelets" )

The value passed to build adapter for searchcrit is a number ie; 12345
, no quotes. The output of the debug.writeline is: SELECT Command:
SELECT * FROM Bracelets WHERE CustNum=1529350 5
15293505 is found in the dataset. This seems so simple.
Anyway, Thanks for your time.
Carl Summers

[Ken Tucker [MVP]]

Hi,

You clear the second dataset before filling it.

mdsBracelets.Cl ear
DataAdapter.Fil l(mdsBracelets, "Bracelets" )

Ken
----------------------------
"Locke" wrote:

I know I'm doing something wrong, and it's something simple but I can't
figure it out. I'm new at this and it's only a little more than a
hobby so please bear with me.

I'm writing a VB shell around an Access database. I have an object
that handles the communication of data between my main form and the
database. It creates a oledb data connection, - no problems. It then
builds an adapter and fills a dataset of customer information, - no
problems. However, it then recreates the adapter and fills a dataset
with information from a second table. There's actually no problem with
this except when I try to filter the data being populated into the
second dataset. Here's the most relevent code of the project:

'Build Data Adapter
Private Sub BuildAdapter(By Val Table As String, Optional ByVal
SearchCrit As String = "")

If Table = "CustomerDa ta" Then
'Build Data Adapter with Select Statement
Dim MySelect As String = "SELECT * FROM CustomerData"
DataAdapter = New OleDb.OleDbData Adapter(MySelec t,
DataConnection)
ElseIf Table = "Bracelets" Then
Dim MySelect As String
If SearchCrit = "" Then
MySelect = "SELECT * FROM Bracelets"
Else
MySelect = "SELECT * FROM Bracelets WHERE CustNum=" &
SearchCrit
End If
DataAdapter = New OleDb.OleDbData Adapter(MySelec t,
DataConnection)
End If

'Build the Insert, Delete, and Update statements
Dim cb As New OleDb.OleDbComm andBuilder(Data Adapter)

Debug.WriteLine ("SELECT Command: " &
DataAdapter.Sel ectCommand.Comm andText)

End Sub

'Now fill the dataset
DataAdapter.Fil l(mdsBracelets, "Bracelets" )

The value passed to build adapter for searchcrit is a number ie; 12345
, no quotes. The output of the debug.writeline is: SELECT Command:
SELECT * FROM Bracelets WHERE CustNum=1529350 5
15293505 is found in the dataset. This seems so simple.
Anyway, Thanks for your time.
Carl Summers