Get same dataset while using multiple search control criteria

[Frustrated Developer via DotNetMonster.com]

I have developed a form that would allow the user to load and search a
database several ways, by data range using two combo boxes, by specific
number entered in a text box or all database entries. I'm able to use the
combo box selection method multiple times with no problem However, when I try
to type in a specific drawing number in the txtDrawingNum.text field and
click btnLoad I get the
same dataset that I previously had from the combo boxes. Any ideas?

The data bindings are all to text boxes and one checkbox. It is an MS ACCESS
database I am connecting to.

Imports System.Data
Imports System.Data.OleDb

+ Windows Form Designer generated code

Dim dataset1 As New DataSet1
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source = C:\AutoCAD Project\AutoCAD_Files.mdb; User ID=Admin"
Dim Connection1 As OleDbConnection = New OleDbConnection(ConnectionString)


Dim IDbinding As New Binding("Text", dataset1, "AutoCAD_Files.ID")
Dim FileNamebinding As New Binding("Text", dataset1, "AutoCAD_Files.
FILE_NAME")
Dim FileTitlebinding As New Binding("Text", dataset1, "AutoCAD_Files.
FILE_TITLE")
Dim FileDescbinding As New Binding("Text", dataset1, "AutoCAD_Files.
FILE_DESCRIPTION")
Dim RevLetterbinding As New Binding("Text", dataset1, "AutoCAD_Files.
REV_LETTER")
Dim OrigDatebinding As New Binding("Text", dataset1, "AutoCAD_Files.
ORIGINATION_DATE")
Dim DateModifiedbinding As New Binding("Text", dataset1, "AutoCAD_Files.
DATE_MODIFIED")

Dim daAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
Dim daAdapter2 As OleDbDataAdapter = New OleDbDataAdapter
Dim daAdapter3 As OleDbDataAdapter = New OleDbDataAdapter

Public Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.
EventArgs) Handles btnLoad.Click
Dim Command1 As OleDbCommand = New OleDbCommand("SELECT * FROM
AutoCAD_Files", Connection1)

Connection1.Open()
Command1.Connection = Connection1

' Check if a drawing number range is selected
If ComboBox1.SelectedItem <> "" And ComboBox2.SelectedItem <> "" Then
Dim FromRange As String
Dim ToRange As String
Dim SQLRange As String
FromRange = Trim(UCase(ComboBox1.SelectedItem))
ToRange = Trim(UCase(ComboBox2.SelectedItem))
SQLRange = "SELECT * FROM AutoCAD_Files WHERE FILE_NAME BETWEEN
""" & FromRange & """ AND """ & ToRange & """"
Dim Command3 As OleDbCommand = New OleDbCommand(SQLRange,
Connection1)
daAdapter3.SelectCommand = Command3
DataGrid1.Visible = True
daAdapter3.Fill(dataset1, "AutoCAD_Files")
DataGrid1.SetDataBinding(dataset1, "AutoCAD_Files")
editID.DataBindings.Clear()
editID.DataBindings.Add(IDbinding)
editFILE_NAME.DataBindings.Clear()
editFILE_NAME.DataBindings.Add(FileNamebinding)
editFILE_TITLE.DataBindings.Clear()
editFILE_TITLE.DataBindings.Add(FileTitlebinding)
editFILE_DESCRIPTION.DataBindings.Clear()
editFILE_DESCRIPTION.DataBindings.Add(FileDescbinding)
editREV_LETTER.DataBindings.Clear()
editREV_LETTER.DataBindings.Add(RevLetterbinding)
editORIGINATION_DATE.DataBindings.Clear()
editORIGINATION_DATE.DataBindings.Add(OrigDatebinding)
editDATE_MODIFIED.DataBindings.Clear()
editDATE_MODIFIED.DataBindings.Add(DateModifiedbinding)
Me.lblNavLocation.Text = (((Me.BindingContext(dataset1,
"AutoCAD_Files").Position + 1).ToString + " of ") _
+ Me.BindingContext(dataset1, "AutoCAD_Files").Count.
ToString)
Connection1.Close()
Exit Sub
End If

' Check if a drawing number is specified
If txtDrawingNum.Text <> "" Then
Dim SearchString As String
Dim SQL As String
SearchString = Trim(UCase(txtDrawingNum.Text))
SQL = "SELECT * FROM AutoCAD_Files WHERE FILE_NAME LIKE """ &
SearchString & """"
Dim command2 As OleDbCommand = New OleDbCommand(SQL, Connection1)
daAdapter2.SelectCommand = command2
DataGrid1.Visible = True
daAdapter2.Fill(dataset1, "AutoCAD_Files")
DataGrid1.SetDataBinding(dataset1, "AutoCAD_Files")
editID.DataBindings.Clear()
editID.DataBindings.Add(IDbinding)
editFILE_NAME.DataBindings.Clear()
editFILE_NAME.DataBindings.Add(FileNamebinding)
editFILE_TITLE.DataBindings.Clear()
editFILE_TITLE.DataBindings.Add(FileTitlebinding)
editFILE_DESCRIPTION.DataBindings.Clear()
editFILE_DESCRIPTION.DataBindings.Add(FileDescbinding)
editREV_LETTER.DataBindings.Clear()
editREV_LETTER.DataBindings.Add(RevLetterbinding)
editORIGINATION_DATE.DataBindings.Clear()
editORIGINATION_DATE.DataBindings.Add(OrigDatebinding)
editDATE_MODIFIED.DataBindings.Clear()
editDATE_MODIFIED.DataBindings.Add(DateModifiedbinding)
Me.lblNavLocation.Text = (((Me.BindingContext(dataset1,
"AutoCAD_Files").Position + 1).ToString + " of ") _
+ Me.BindingContext(dataset1, "AutoCAD_Files").Count.
ToString)
Connection1.Close()
Exit Sub
End If

daAdapter1.SelectCommand = Command1
DataGrid1.Visible = True
daAdapter1.Fill(dataset1, "AutoCAD_Files")
DataGrid1.SetDataBinding(dataset1, "AutoCAD_Files")

editID.DataBindings.Clear()
editID.DataBindings.Add(IDbinding)
editFILE_NAME.DataBindings.Clear()
editFILE_NAME.DataBindings.Add(FileNamebinding)
editFILE_TITLE.DataBindings.Clear()
editFILE_TITLE.DataBindings.Add(FileTitlebinding)
editFILE_DESCRIPTION.DataBindings.Clear()
editFILE_DESCRIPTION.DataBindings.Add(FileDescbinding)
editREV_LETTER.DataBindings.Clear()
editREV_LETTER.DataBindings.Add(RevLetterbinding)
editORIGINATION_DATE.DataBindings.Clear()
editORIGINATION_DATE.DataBindings.Add(OrigDatebinding)
editDATE_MODIFIED.DataBindings.Clear()
editDATE_MODIFIED.DataBindings.Add(DateModifiedbinding)

Me.lblNavLocation.Text = (((Me.BindingContext(dataset1,
"AutoCAD_Files").Position + 1).ToString + " of ") _
+ Me.BindingContext(dataset1, "AutoCAD_Files").Count.
ToString)
Connection1.Close()
Exit Sub
End Sub

Private Sub btnClearForm_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnClearForm.Click
dataset1.Clear()
DataGrid1.Hide()
lblNavLocation.Text = ""
txtNumber_of_Drawings.Text = ""
txtDrawingNum.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
End Sub

 

[Cor Ligthert [MVP]]

Hi,

That is a lot of code, however know that a fill does not renew a dataset. It
adds what it gets new.

If this is not the answer, than you would to make it possible to help you in
my opinion bring it first back to for us as well understandable code
(probably you find it than yourself)

I hope this helps something,

Cor