查询数据库中的两个表 [英] query for two table in database

问题描述

 SqlCommand cmd3 = new SqlCommand（select t 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必须从表历史中获取id（还有一个以上的id），我想使用第二个查询在gridview中显示数据（查询是：选择数据，其中id由第一个查询获取，也在datetime reng内）



我搜索在那种情况下使用grideview概念的嵌套，但我可以''能够做到这一点。使用连接这个查询不会以任何方式解决。所以我必须使用gridview的嵌套。

解决方案

空格。

您需要在id值和单词and之间添加一个：

 SqlCommand cmd4 =  new  SqlCommand（  select * from call_details，其中id = + ds3.Tables [ 0 ] +  和' + txt_datefrom之间的enquiry_date .Text +   '和' + txt_dateto.Text +  '，cnn）; 
 

成为：

 SqlCommand cmd4 =  new  SqlCommand（< span class =code-string>  select * from call_details where id = + ds3.Tables [ 0 ] +  和' + txt_datefrom.Text +  '和' + txt_dateto.Text +  '，cnn）; 
 

我不仅要获取单个ID，还要从首先查询多个id'的提交。并且id''si必须在第二个查询中使用。





对。好吧......这完全是一个不同的问题。



你不能只为查询提供一个数据表，并期望它将它全部排序 - 语法就不存在了。首先，SQL没有数组的概念，这使得一切都相当困难。



有两种方法可以做到：



1）将ID转换为逗号分隔值，并在第二个查询中使用IN子句：

  SELECT  *  FROM  MyTable  WHERE  id  IN （ 1 ， 2 ， 3 ， 4 ）

这适用于少量ID，但可能会成为较大的问题（开始花费更多时间处理SQL命令而不是访问数据库！）

列表<   string  >  ids = new List <   string  > （）; 
 foreach（dt.Rows中的DataRow行）
 {
 ids.Add（row.ItemArray [0] .ToString（））; 
} 
 string s = String.Join（，，ids）; 

将生成CSV数据。

2）使用JOIN查询。这会在一个表中查找ID，并在另一个表中使用它们。

根据您在课程中的距离，您可能还没有涵盖这些，所以我不愿意介绍他们。 （但它们是一个更好的解决方案）

尝试将第二个查询放入：

 SqlCommand cmd4 =  new  SqlCommand（  select * from call_details其中id = + ds3.Tables [ 0 ]。行[ 0 ] [< span class =code-string>  id] +  和enquiry_date在' + DateTime.Parse（txt_datefrom.Text）+  '和' + DateTime.Parse（txt_dateto.Text）+  ' ，cnn）; 

id从第一个数据表中提取并且日期应该是 datetime 类型（如果数据库中的数据类型是datetime。

试试这个：

 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 ; 

 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.

解决方案

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

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:

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:

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

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)

Try putting the second query as:

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.

Try this:

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";

这篇关于查询数据库中的两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！