By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Can't figure out where clause in sql statement.

P: n/a
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(ByVal Table As String, Optional ByVal
SearchCrit As String = "")

If Table = "CustomerData" Then
'Build Data Adapter with Select Statement
Dim MySelect As String = "SELECT * FROM CustomerData"
DataAdapter = New OleDb.OleDbDataAdapter(MySelect,
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.OleDbDataAdapter(MySelect,
DataConnection)
End If

'Build the Insert, Delete, and Update statements
Dim cb As New OleDb.OleDbCommandBuilder(DataAdapter)

Debug.WriteLine("SELECT Command: " &
DataAdapter.SelectCommand.CommandText)

End Sub

'Now fill the dataset
DataAdapter.Fill(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=15293505
15293505 is found in the dataset. This seems so simple.
Anyway, Thanks for your time.
Carl Summers

Nov 21 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi,

You clear the second dataset before filling it.

mdsBracelets.Clear
DataAdapter.Fill(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(ByVal Table As String, Optional ByVal
SearchCrit As String = "")

If Table = "CustomerData" Then
'Build Data Adapter with Select Statement
Dim MySelect As String = "SELECT * FROM CustomerData"
DataAdapter = New OleDb.OleDbDataAdapter(MySelect,
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.OleDbDataAdapter(MySelect,
DataConnection)
End If

'Build the Insert, Delete, and Update statements
Dim cb As New OleDb.OleDbCommandBuilder(DataAdapter)

Debug.WriteLine("SELECT Command: " &
DataAdapter.SelectCommand.CommandText)

End Sub

'Now fill the dataset
DataAdapter.Fill(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=15293505
15293505 is found in the dataset. This seems so simple.
Anyway, Thanks for your time.
Carl Summers

Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.