Search in Database

Now, I have a database which have 2 table : candidates and marks
I want to search the candidate infomation by candidate_code, candidate names, candidate DOB in candidate tables. How can I do it. Help me the code
I created a combobox which search by candidate_code, candidate names, candidate DOB
a textbox to enter value
a button to search
i'm using sqlserver and vb.net 2003
Thanks so much
SupporterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

maralansCommented:
 Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim dv As DataView = yourDataSet.Tables(0).DefaultView

        If tbSearch.Text = "" Then
            MessageBox.Show("Enter Search Criteria", "yourProject", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        If combobox1= "candidate_code" Then
            dv.RowFilter = " code LIKE '%" + tbSearch.Text + "%' "
        ElseIf combobox1= "candidate names" Then
            dv.RowFilter = " names LIKE '%" + tbSearch.Text + "%' "
        ElseIf combobox1 =  "candidate DOB" Then
            dv.RowFilter = " DOB LIKE '%" + tbSearch.Text + "%' "
               End If

        If dv.Count < 1 Then
            MessageBox.Show("No records found.", "yourProject", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            dv.Sort = "names "
        End If
   End Sub


 Private Sub tbSearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles tbSearch.KeyPress
        If Convert.ToInt32(e.KeyChar) = Keys.Enter Then
            Call btnSearch_Click(Me, e)
            e.Handled = True
        End If
    End Sub
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SupporterAuthor Commented:
Thanks maralans
But I don't understand

If combobox1= "candidate_code" Then
            dv.RowFilter = " code LIKE '%" + tbSearch.Text + "%' "
        ElseIf combobox1= "candidate names" Then
            dv.RowFilter = " names LIKE '%" + tbSearch.Text + "%' "
        ElseIf combobox1 =  "candidate DOB" Then
            dv.RowFilter = " DOB LIKE '%" + tbSearch.Text + "%' "
               End If
you have 3 variable
code, names, DOB, is it the field in my database

Dim dv As DataView = yourDataSet.Tables(0).DefaultView <~~~ yourDataset <~~~ how I can generat an dataset without using wizard
I often using dataadapter wizard to generat a dataset
here is my code: but I don't know how to generate an dataset

Dim sqlconn As New System.Data.SqlClient.SqlConnection("Data Source=BIP-PCWPUJ7QJPW;Integrated Security=SSPI;Initial Catalog=MCSM;")
        Dim sqlcmd As New System.Data.SqlClient.SqlCommand("SELECT * FROM TblCandidates WHERE Candidate_Code = @Candidate_Code", sqlconn)
        sqlcmd.Parameters.Add("@Candidate_Code", TextBox3.Text)
0
SupporterAuthor Commented:
And how to show the searched data in to datagrid
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

maralansCommented:
First you cannot search a dataset directly, you need to create a dataview.  You can either populate your dataset by code or use the wizzard if you find that easier.

 Dim dv As DataView = yourDataSet.Tables(0).DefaultView

Then you need to filter your dataview with your values in the combobox and the textbox.  For example, if your combobox value is DOB and your textbox is 1980 then it would show all records with a DOB of 1980.

If combobox1= "candidate_code" Then
            dv.RowFilter = " code  LIKE '%" + tbSearch.Text + "%' "
        ElseIf combobox1= "candidate names" Then
            dv.RowFilter = " names LIKE '%" + tbSearch.Text + "%' "
        ElseIf combobox1 =  "candidate DOB" Then
            dv.RowFilter = " DOB LIKE '%" + tbSearch.Text + "%' "
End If

And, finally you can sort the dataview.

 If dv.Count < 1 Then
            MessageBox.Show("No records found.", "yourProject", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            dv.Sort = "names "
        End If
   End Sub
0
SupporterAuthor Commented:
Yeah. I've done. Thanks so much.
But now, If I choose Candidate DOB to search. I want the search box is datepicker box. Whenever I choose Candidate DOB,  the datepicker box will show and the tbSearch will hide and whenever I choose Candidate Name, the tbSearch will show, and the datepicker will hide. How can I do it

Here is my code, but it doesn't work each time I load my application
        If WinComboBox1.Text = "Candidate Code" Then
            TextBox3.Show()
            WinDatePicker4.Hide()
        End If
        If WinComboBox1.Text = "Candidate Name" Then
            TextBox3.Show()
            WinDatePicker4.Hide()
        End If
        If WinComboBox1.Text = "Candidate DOB" Then
            TextBox3.Hide()
            WinDatePicker4.Show()
        End If
0
maralansCommented:
I don't have VB in front of me but you might could try:

textbox3.visible=false
windatepicker4.visible=true
0
maralansCommented:
Private Sub WinComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WinComboBox1.SelectedIndexChanged
        If ComboBox1.Text = "Candidate DOB" Then
            DateTimePicker1.Visible = True
            TextBox1.Visible = False
        Else
            DateTimePicker1.Visible = False
            TextBox1.Visible = True
        End If
    End Sub
0
SupporterAuthor Commented:
You are Great maralans :)
I've done. I'm now trying to make a report but a made a trouble
the excersise require me create a report that must display the following information
Candidate_Code
Candidate_Name
DOB
Sex
The average mark of each candidate
The average mark of Candidate on each region

another require me create a report that must display the following information
Candidate_Code
Candidate_Name
DOB
Sex
The ratio of Candidates who pass the exam on each region
The ratios of Candidates who pass the exam on all regions

I wonder how to make relationship betwenn tblCandidates and tblMarks in sql server so that I can make report.
Can you help me to build a complete code. I need it to reference
Now I have 2 table in MCSM database
tblCandidates consist of Candidate_Code, Candidate_Name, DOB, Sex, ......
tblMarks consist of Exam_paper_Code, Subject_Code, Candidate_Code, Mark
0
maralansCommented:
You may want to close this question out and then ask a new one if your original question has been answerd.
0
SupporterAuthor Commented:
OK, I understand. Thanks you for somuch.
I will post my question again in other topic
Thanks you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.