query for two table in database

Question

5.00/5 (1 vote)

See more:

 SqlCommand cmd3 = new SqlCommand("select id from tbl_history where contactby='"+ddlcontact_type.SelectedValue+"'", cnn);
               SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
               DataSet ds3 = new DataSet();
               da3.Fill(ds3);

               SqlCommand cmd4 = new SqlCommand("select * from call_details where id="+ds3.Tables[0]+"and enquiry_date between '" + txt_datefrom.Text + "' and '" + txt_dateto.Text + "'", cnn);
               SqlDataAdapter da4 = new SqlDataAdapter(cmd4);
               DataSet ds4 = new DataSet();
               da4.Fill(ds4);
GridView1.DataSource = ds4.Tables[0];
               GridView1.DataBind();

i have to take id from table history (there is more then one id) ,and i want to display data in gridview using second query (query is : select data where id is fetched by first query and also within datetime reng)

I have search that in that case nesting of grideview concept is used ,but i can't able to do this.Using join this query is not solve in any way.So i have to use nesting of gridview.

Posted 7-Jan-13 20:58pm

Member 9511889

Updated 8-Jan-13 18:04pm

v3

Add a Solution

7 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)

OriginalGriff · Answer 1 · 2013-01-07T21:06:00

Solution 1

Spaces.
You need to add one between the id value and the word "and":

C#

SqlCommand cmd4 = new SqlCommand("select * from call_details where id="+ds3.Tables[0]+"and enquiry_date between '" + txt_datefrom.Text + "' and '" + txt_dateto.Text + "'", cnn);

Becomes:

C#

SqlCommand cmd4 = new SqlCommand("select * from call_details where id="+ds3.Tables[0]+" and enquiry_date between '" + txt_datefrom.Text + "' and '" + txt_dateto.Text + "'", cnn);

"i have to fetch not only single id,but from first query multiple id's comming .And that id's i have to use in second query."

Right. Ok...that's a different problem altogether.

You can't just provide a datatable to a query and expect it to sort it all out - the syntax just isn't there. For one thing, SQL has no concept of an array which makes it all rather difficult.

There are two ways to do it:

1) Convert the ID's to comma separated values, and use the IN clause in your second query:

SQL

SELECT * FROM MyTable WHERE id IN (1, 2, 3, 4)

This works fine for small numbers of ID, but can get to be a problem with larger ones (as you start to spend more time processing the SQL command than accessing the database!)

XML

List<string> ids = new List<string>();
foreach (DataRow row in dt.Rows)
    {
    ids.Add(row.ItemArray[0].ToString());
    }
string s = String.Join(",", ids);

Will generate the CSV data.
2) Use a JOIN query. This looks up the IDs in one table, and uses them in the other.
Depending on how far you are in your course, you may not have covered these yet, so I'm reluctant to introduce them. (But they are a much better solution)

Posted 7-Jan-13 21:06pm

OriginalGriff

Updated 8-Jan-13 0:02am

v2

Comments

Christian Graus 8-Jan-13 3:16am

But, it's still a table, right ? Tables[0] won't give him a single number value ? Will it even compile ?

OriginalGriff 8-Jan-13 5:44am

Oh yes - it compiles because it's string concatenation, which calls the DateTable.ToString override. Which returns the table name, rather than the ID he seems to expect...I have suggested he uses ExecuteScalar instead of messing with adapters and so forth.

Christian Graus 8-Jan-13 14:09pm

I was scared that might be the case :) At least it returns a name, I was expecting it might return "DataTable". I've seen classes that act that way.

OriginalGriff 8-Jan-13 14:20pm

Yep! Unless the class overrides ToString, the default object version is used and that just returns the full class name. (I know you know this, but there are probably people reading who don't).

The worst offender for this is the Image class - I have seen far too many people who assume that concatenating a Bitmap into the SQL command string will save the actual image data into the database. Only to wonder why the code to retrieve it throws a GDI+ exception...:doh:
Of course, by that stage, they have saved hundreds of images to the DB, so that must be working, right? :laugh:

Member 9511889 8-Jan-13 5:11am

your solution also give error.what is problem.

OriginalGriff 8-Jan-13 5:18am

What error does it give?

Member 9511889 8-Jan-13 5:25am

incorect syntex near Table.

OriginalGriff 8-Jan-13 5:42am

That helps - always try to tell us the error message, it makes life a lot easier :laugh:
ds3.Tables[0] is a DataTable, so the DataTable.ToString implementation will be used to build your string. This returns the name of the table rather than any possibly more useful info, like the actual ID you were looking for.
If you are looking for a single ID value to insert here, why not return just the one value via ExecuteScalar from the first query instead of using an Adapter, a Set and a Table anyway? It would be clearer, and easier to read.

Member 9511889 8-Jan-13 5:45am

i have to fetch not only single id,but from first query multiple id's comming .And that id's i have to use in second query.

OriginalGriff 8-Jan-13 6:02am

Answer updated

Rahul Rajat Singh · Answer 2 · 2013-01-07T21:07:00

Try putting the second query as:

C#

SqlCommand cmd4 = new SqlCommand("select * from call_details where id="+ds3.Tables[0].Rows[0]["id"]+"and enquiry_date between '" + DateTime.Parse(txt_datefrom.Text) + "' and '" + DateTime.Parse(txt_dateto.Text) + "'", cnn);

The id has top extracted from the first datatable and the date should be of datetime type(if the datatype in database is datetime.

Kuthuparakkal · Answer 3 · 2013-01-07T21:07:00

Solution 3

Try this:

C#

SqlCommand cmd3 = 
"SELECT * FROM call_details AS CD
INNER JOIN
(
SELECT DISTINCT Id FROM tbl_history WHERE contactby= '"+ddlcontact_type.SelectedValue+"'
) AS TH
ON CD.Id = TH.Id";

Posted 7-Jan-13 21:07pm

Kuthuparakkal

Updated 7-Jan-13 21:08pm

v2

Christian Graus · Answer 4 · 2013-01-07T21:08:00

Well, your SQL is a mess, because it is string mashed in a way that allows SQL injection attacks ( that is, I can erase your DB if I want to ). But, you seem to be very confused.

ds3.Tables[0]

This returns a data table. As such, I expect this code won't even compile. I suspect you wnat to do something like

id in (1,2,5,6)

where the sequence of numbers is built by iterating over ds3.

In future, please tell us the exact error you get, so we can better help you.

Zafar Sultan · Answer 5 · 2013-01-07T21:13:00

Oh dear. Really bad approach to write inline queries. You should learn to use stored procedures instead. Now coming to your problem, use a single stored procedure like:

SQL

Create Procedure GetRecords
(
@contactby int,
@startdate DateTime,
@enddate DateTime 
)
AS
BEGIN
select * from call_details where id in
(
select id from tbl_history where contactby = @contactby
)
and enquiry_date >= @startdate
and enquiry_date <= @enddate
END

Alternatively, if you don't want to use SP convert your inline query to above and try.

spunnam · Answer 6 · 2013-01-07T21:14:00

Try this:

SqlCommand cmd3 = new SqlCommand("select id from tbl_history where contactby='"+ddlcontact_type.SelectedValue+"'", cnn);
SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
DataSet ds3 = new DataSet();
da3.Fill(ds3);

SqlCommand cmd4 = new SqlCommand("select * from call_details where id="+ds3.Tables[0].Rows[0].ItemArray[0]+"and enquiry_date between '" + txt_datefrom.Text + "' and '" + txt_dateto.Text + "'", cnn);
SqlDataAdapter da4 = new SqlDataAdapter(cmd4);
DataSet ds4 = new DataSet();
da4.Fill(ds4);
GridView1.DataSource = ds4.Tables[0];
GridView1.DataBind();

Mycroft Holmes · Answer 7 · 2013-01-07T21:15:00

There are a number of issues with your code.

Do not use strings directly from the User Interface read this article[^] pay especial attention to the Parameterised Queries section it will also help you with the dates.

Then read this article [^]to understand joins.

Which should lead you to something like (your query should use parameters):

Select * from call_details C
inner join tbl_history H on H.ID = C.ID
where H.ID= ID
and enquiry_date between this and that

query for two table in database

7 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Solution 6

Solution 7

Add your solution here

Preview 0