How to design this kind of query in .net using sql database.

Question

0.00/5 (No votes)

See more:

Hello folks..

I am learning .net these days and have a question need a bit help here..

How to achieve this:

I am designing my form here as simple as i can explain you..:

FORM:

Search Name: (User can enter 1 or many names here to search in database seperated by commas)

 -------------------
|                   |      SUBMIT
|                   |
 -------------------

Output Result: (Once user submit the query it will lookup those entered name 1 or many in the 3 different tables for its existance.. If they exist or not then the output will be shown as below..)

Name   |   Class X    |   Class XI   |   Class XII
--------------------------------------------------
First  |    Yes       |    NO        |    NO
Second |    YES       |    YES       |    YES
.
.
.
.
N entry|   ......     |    .....     |   ......

This should be the way the output for N search should display, Now i am stuck how to do that.. I am not that familiar with .net please help with a code explanation or somewhere where i can have this goal achieved. I hope anyone can help me.. Sorry for such easy question.

Please provide me a sample for this function. . :(

Posted 7-Jul-13 19:23pm

Devil9919

Add a Solution

Comments

_Asif_ 8-Jul-13 6:13am

Please share the tables details

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Answer 1 · 2013-07-07T20:32:00

Hello,

You can use GridView web form control to display multiple records. Please refer to following tutorials, those should get you started.

Since you need to check for existance of the record in three different tables you can do soby Fire three different count SQL's using SQLCommand and based on the output (which will be a count of matching records found) you can proceed further to display the result. The code for a single query will be something like shown below.

C#

Int32 intCnt1 = 0;
string sql = "SELECT COUNT(1) FROM your_table WHERE search_col  = @searchTerm";
using (SqlConnection conn = new SqlConnection(connString)) {
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.Parameters.Add("@searchTerm", SqlDbType.VarChar);
    cmd.Parameters["@searchTerm"].Value = your_value;
    try {
        conn.open();
        intCnt1 = (Int32) cmd.ExecuteScalar();
    }
    catch (Exception ex) {
        // Code to handle exception
    }
}

Regards,

ErBhati · Answer 2 · 2013-07-07T19:57:00

Solution 1

try this

http://stackoverflow.com/questions/15976366/trying-to-pass-an-array-of-values-to-search-using-in[^]

or

http://www.williamrobertson.net/documents/comma-separated.html

Posted 7-Jul-13 19:57pm

ErBhati

Basmeh Awad · Answer 3 · 2013-07-08T02:05:00

Solution 4

try this

VB

Dim Arr As String()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim str As String
        Dim dr As OleDbDataReader
        Arr = TextBox1.Text.Split(",")
        For Each name As String In Arr
            con = New OleDbConnection("YourConnectionString")
            con.open()
            str = "select * from YourTableName where YourColumnName like '%" & name & "%'" ''First Table To Search
            cmd = New OleDbCommand(str, con)
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                DataGridView1.Rows.Insert(dr(0), dr(1), dr(2), dr(3)) 'And so on
            End If
            dr.Close()
            str = "select * from YourTableName where YourColumnName like '%" & name & "%'" ''Second Table To Search
            cmd = New OleDbCommand(str, con)
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                DataGridView1.Rows.Insert(dr(0), dr(1), dr(2), dr(3)) 'And so on
            End If
            dr.Close()
        Next

Posted 8-Jul-13 2:05am

Basmeh Awad

Updated 8-Jul-13 2:24am

v2

Comments

Devil9919 8-Jul-13 8:21am

Thanks for the code, can you explain what is happening after creating connection.

Basmeh Awad 8-Jul-13 8:27am

as you can see..we are searching for the name in the table which the user have entered in the search box and if we found it we are displaying it in the DataGridView..

Devil9919 8-Jul-13 8:33am

So exactly this is searching for the name and displaying the name or saying Yes / No kinda ???

Basmeh Awad 8-Jul-13 8:37am

yes..

Devil9919 8-Jul-13 9:00am

OK Thanks mate ! I will have a try on this and will let you know ! :) Is this for SQL right ? as i see OLEDB connection stuffs..

Basmeh Awad 8-Jul-13 9:38am

yes..you can use your stuff there

Devil9919 9-Jul-13 4:10am

Can anyone give me working code for my query stuff.. :(

i am unable to get it work.. :( please help me !

Basmeh Awad 9-Jul-13 4:24am

where you stuck??

Devil9919 9-Jul-13 5:50am

I can't understand and implement the code provided.. :(

Basmeh Awad 9-Jul-13 7:15am

update your question and paste the entire code of your form what you have done..
and reply me lets see