我如何...使用另一个SQL查询获取所有产品详细信息我从第一个SQL查询获得的ID，因为我想要从表中看到包含最多的产品的数据主义者最常见... [英] How Do I...Use Another Sql Query To Get All Product Details By Id That I Have From The First Sql Query As I Want To Do Datalist That Contain Most Seen Product From The Table Most Seen...

问题描述

public partial class mostseen : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aqar;Data Source=HIMA-PC\SQLEXPRESS";
        con.Open();

        string sql = "SELECT TOP 10 id_aqar, COUNT(id_aqar) AS Count FROM mostseen GROUP BY id_aqar ORDER BY COUNT(id_aqar) DESC";


        SqlDataAdapter adpt = new SqlDataAdapter(sql, con);
        DataTable tb = new DataTable();
        adpt.Fill(tb);
        object idx = tb.Rows[0]["id_aqar"].ToString();
        int idfk = int.Parse(idx.ToString());

        string sql2 = "SELECT * FROM addaqar WHERE id_aqar='"+idfk+"'";

        SqlDataAdapter adpt2 = new SqlDataAdapter(sql2,con);
        DataTable tb2 = new DataTable();
        adpt2.Fill(tb2);
        DataList1.DataSource = tb2;
        DataList1.DataBind();

    }
}

推荐答案

您可以在代码中修改一些内容。

1.当您需要数据库中的单个值时，请使用 SqlCommand.ExecuteScalar方法 [ ^ ]如下所示

there are few things you can correct in your code.
1. when you need single value from database, use SqlCommand.ExecuteScalar Method [^] like below

string sql = "SELECT TOP 1 id_aqar FROM mostseen GROUP BY id_aqar ORDER BY COUNT(id_aqar) DESC";
int idfk=-1;
using (SqlConnection conn = new SqlConnection(connString))
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
    conn.Open();
    idfk = (Int32)cmd.ExecuteScalar();
}

2.你已经将参数值设置为字符串，如果列日期时间是整数，则不需要 sql2 中的单引号，请尝试如下

2. you have set parameter value as string, if the column date time is integer you don't need single quotes in your sql2, try as below

string sql2 = "SELECT * FROM addaqar WHERE id_aqar="+idfk;

3.通过连接构建的sql语句是打开sql注入攻击的应用程序，你最好使用下面的SQL参数< br $>

3. Sql statement building by concatenating is opening your application for sql injection attacks, you better use SQL parameters like below

string sql2 = "SELECT * FROM addaqar WHERE id_aqar=@id_aqar";
using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand(sql2, conn))
{
    cmd.Parameters.AddWithValue("@id_aqar",idfk);  
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    {
        DataTable tb2 = new DataTable();
        adapter.Fill(tb2);
        DataList1.DataSource = tb2;
        DataList1.DataBind();
    }
}

4.最后:)我认为您可以使用单个查询并返回您需要的内容如下所示

4. finally :) I think you can use single query and return what you need like below

using(SqlConnection con = new SqlConnection())
{
    con.ConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aqar;Data Source=HIMA-PC\SQLEXPRESS";
    con.Open();
    string sql2 = "SELECT * FROM addaqar WHERE id_aqar=(SELECT TOP 1 id_aqar FROM mostseen GROUP BY id_aqar ORDER BY COUNT(id_aqar) DESC)";
    
    using(SqlDataAdapter adpt2 = new SqlDataAdapter(sql2,con))
    {
        DataTable tb2 = new DataTable();
        adpt2.Fill(tb2);
        DataList1.DataSource = tb2;
        DataList1.DataBind();
    }
}

更新：

update:

using(SqlConnection con = new SqlConnection())
{
    con.ConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aqar;Data Source=HIMA-PC\SQLEXPRESS";
    con.Open();
    string sql2 = "SELECT * FROM addaqar WHERE id_aqar in (SELECT TOP 10 id_aqar FROM mostseen GROUP BY id_aqar ORDER BY COUNT(id_aqar) DESC)";
    
    using(SqlDataAdapter adpt2 = new SqlDataAdapter(sql2,con))
    {
        DataTable tb2 = new DataTable();
        adpt2.Fill(tb2);
        DataList1.DataSource = tb2;
        DataList1.DataBind();
    }
}

假设详细信息在另一个名为addaqar的表中，该表的id_aqar链接到此最常见的id_adqar表，只需要一个使用如下连接的sql：

Let's say the details is in another table called addaqar that has its id_aqar that links to the id_adqar of this mostseen table, just need one sql using join like this:

SELECT TOP 10 addaqar.id_aqar,  [other fields in addaqar table], COUNT(mostseen.id_aqar)
FROM addaqar JOIN mostseen
ON addaqar.id_aqar=mostseen.id_aqar
GROUP BY addaqar.id_aqar,  [other fields in addaqar table], COUNT(mostseen.id_aqar)

这篇关于我如何...使用另一个SQL查询获取所有产品详细信息我从第一个SQL查询获得的ID，因为我想要从表中看到包含最多的产品的数据主义者最常见...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！