Adding Search Function

[AdrianVicencio]

HI guys,

I am having problems putting a search Function in my vb.net program. It is a windows application with SQL 2005 as database. I have a stored procedure written and I want to use that. Problem is, I used dataset because I am not familiar with hard coded stuff.

Ill be posting my entire code here so you could help me out
==========================================================
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

Private Sub SetControls(ByVal valueBoolean As Boolean)
'This sub procedure sets the user interface for the
'BindingNavigator control and bound controls for Edit/Add
'operations

'ReadOnly/Not ReadOnly the bound controls

MiddleNameTextBox.Enabled = Not valueBoolean
LastNameTextBox.Enabled = Not valueBoolean
FirstNameTextBox.Enabled = Not valueBoolean
AddressTextBox.Enabled = Not valueBoolean
SerialTextBox.Enabled = Not valueBoolean
StatusComboBox.Enabled = Not valueBoolean
MeterTextBox.Enabled = Not valueBoolean
NearestTextBox.Enabled = Not valueBoolean


'Make the Move, Position, and Buttons
'(except Save and Cancel) Enable/disable

FirstButton.Enabled = valueBoolean
NextButton.Enabled = valueBoolean
PreviousButton.Enabled = valueBoolean
LastButton.Enabled = valueBoolean

AddButton.Enabled = valueBoolean
EditButton.Enabled = valueBoolean
DeleteButton.Enabled = valueBoolean

'Enable/disable the Save and Cancel Buttons
SaveButton.Enabled = Not valueBoolean
CancelButton.Enabled = Not valueBoolean
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'MeretDataSet2.Meret_main' table. You can move, or remove it, as needed.
Me.Meret_mainTableAdapter1.Fill(Me.MeretDataSet2.Meret_main)
'TODO: This line of code loads data into the 'MeretDataSet1.Meret_sub' table. You can move, or remove it, as needed.
Me.Meret_subTableAdapter.Fill(Me.MeretDataSet1.Meret_sub)

Try
Me.Meret_mainTableAdapter1.Fill(Me.MeretDataSet2.Meret_main)
SetControls(True)
Catch ex As Exception
Dim messageString As String = "DataBase Connection Failed" & ControlChars.NewLine & _
"Report this error to the system developer [09062864264]." & ControlChars.NewLine & ex.Message
Dim titleString As String = "Database Connection Failure"
MessageBox.Show(messageString, titleString, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try


End Sub

Private Sub AddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddButton.Click
SetControls(False)
MeretmainBindingSource1.AddNew()

LastnameTextBox.Focus()
End Sub

Private Sub EditButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditButton.Click
Me.SetControls(False)
End Sub

Private Sub DeleteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteButton.Click
Dim rowNumberInteger As Integer = Me.MeretmainBindingSource1.Position

Try
Dim responseDialogResult As DialogResult = MessageBox.Show("Confirm to delete the student record.", "Delete Y/N?", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
If responseDialogResult = Windows.Forms.DialogResult.Yes Then
'Delete the row by removing the current record,
'ending the edit, and calling the Update method
Me.MeretmainBindingSource1.RemoveCurrent()
Me.MeretmainBindingSource1.EndEdit()
'Me.MeretmainBindingSource.Update(Me.MeretmainDataSet.Meretmain)
Me.Meret_mainTableAdapter1.Update(Me.MeretDataSet2.Meret_main)
End If

Catch exOleDb As OleDb.OleDbException
'The deletion attempt failed due to a relationship
'to existing data rows in the ENROLLMENT table
MessageBox.Show("This student cannot be deleted - the student is enrolled in courses." & _
ControlChars.NewLine & exOleDb.Message, "Delete Operation Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

'Restore the deleted row with the RejectChanges method
Me.MeretDataSet2.RejectChanges()

'Reposition to the row that was deleted
Me.MeretmainBindingSource1.Position = rowNumberInteger

Catch ex As Exception
'Some other exception was triggered
MessageBox.Show("Unexpected error in delete operation: " & ControlChars.NewLine & _
ex.Message, "Delete Operation Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Sub

Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelButton.Click
Me.SetControls(True)

Me.MeretmainBindingSource1.CancelEdit()
Me.MeretDataSet2.RejectChanges()

End Sub

Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click
Try
Me.MeretmainBindingSource1.EndEdit()
Me.Meret_mainTableAdapter1.Update(Me.MeretDataSet2.Meret_main)

Me.SetControls(True)

Catch ex As Exception
Dim messageString As String = "Error saving into Database" & ControlChars.NewLine & ex.Message
Dim TitleString As String = "Error in Operation"

MessageBox.Show(messageString, TitleString, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try
End Sub

Private Sub FirstButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FirstButton.Click
MeretmainBindingSource1.MoveFirst()

End Sub

Private Sub PreviousButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PreviousButton.Click
MeretmainBindingSource1.MovePrevious()

End Sub

Private Sub NextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextButton.Click
MeretmainBindingSource1.MoveNext()

End Sub

Private Sub LastButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LastButton.Click
MeretmainBindingSource1.MoveLast()

End Sub


Private Sub SerialTextBox_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles SerialTextBox.KeyPress

Select Case Asc(e.KeyChar)
Case 8, 48 To 57
e.Handled = False 'Allow the key
Case Else
e.Handled = True 'Ignore the key
End Select
End Sub
Private Sub MeterTextBox_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles MeterTextBox.KeyPress

Select Case Asc(e.KeyChar)
Case 8, 48 To 57
e.Handled = False 'Allow the key
Case Else
e.Handled = True 'Ignore the key
End Select
End Sub

End Class

==========================================================

how do i incorporate the search function on my code?

Thanks a lot

[jmcilhinney]

Open your DataSet in the designer, right click the TableAdapter that corresponds to the table you want to search and add a new query to it. Once configured, this will add a new method to your TableAdapter that you can call to invoke your sproc. If there isn't already a DataTable in your DataSet whose schema matches that of this sproc then you can add a new one.

[AdrianVicencio]

thanks..

ill be trying it out later

so the query will be like a SQL statement like

SELECT LastName from Meret_main where LastName LIKE % textbox8.text %

is my query correct?Meret_main is my main database.

I have a textbox where in you can input the search query

thanks a lot for your help.appreciate it a lot!!

[jmcilhinney]

If you only want a single column from an existing table then you will, as I previously suggested, need to add a new TableAdapter. Each TableAdapter is typed to the schema of the DataTable it's coupled with. That means that every query on a TableAdapter must return the same set of columns.

Now, you said previously that you had a stored procedure, so how can your SQL code possibly contain a reference to a TextBox on your VB form? Your SQL code should contain a parameter. When you add a the query to your TableAdapter a new method will be created. That method will also have a parameter. You will pass the Text of your TextBox to that method parameter when you call the method and, internally, the TableAdapter will pass it on to the sproc.