?
Solved

Search in Database

Posted on 2006-05-06
10
Medium Priority
?
163 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:Supporter
  • 5
  • 5
10 Comments
 
LVL 5

Accepted Solution

by:
maralans earned 2000 total points
ID: 16623150
 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
 

Author Comment

by:Supporter
ID: 16623860
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
 

Author Comment

by:Supporter
ID: 16623979
And how to show the searched data in to datagrid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:maralans
ID: 16624277
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
 

Author Comment

by:Supporter
ID: 16624364
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
 
LVL 5

Expert Comment

by:maralans
ID: 16624913
I don't have VB in front of me but you might could try:

textbox3.visible=false
windatepicker4.visible=true
0
 
LVL 5

Expert Comment

by:maralans
ID: 16624941
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
 

Author Comment

by:Supporter
ID: 16624964
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
 
LVL 5

Expert Comment

by:maralans
ID: 16625205
You may want to close this question out and then ask a new one if your original question has been answerd.
0
 

Author Comment

by:Supporter
ID: 16625670
OK, I understand. Thanks you for somuch.
I will post my question again in other topic
Thanks you
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question