问题：我有两个选择查询两个单独的数据表？ [英] problem: i have two select queries for two separate data tables?

问题描述

解释



i有两个数据表



数据表1包括 - Id（int）

来源（varchar（max））

目的地（varchar（max））



datatable2包括：UsercommanetId（Int ）

Id（int）外键

UserComments



除此之外还有两个gui文本框。其中包括用户输入源和目的地。



所以我根据那个

制作代码如果用户输入源和目的地在数据表中可用1

因此它将获得id数据表1

并且用户可以添加注释。

当他们添加注释时它将根据特定情况存储id

所以用户可以随时评论，它会逐一添加到表格中

以便代码可以使用



所以现在我想把这些评论放到一个文本框或列表框中





所以我为select和id做了代码destination等于来自database1的userinputs

并选择所有与该特定用户ID相等的usercomment



所以我的代码不能用于此...

它给出了执行错误

explanation

i have two data tables

datatable 1 includes - Id(int)
Source(varchar(max))
Destination(varchar(max))

datatable2 includes : UsercommanetId(Int)
Id(int)foreign key
UserComments

apart from that there is two gui textboxes. which includes user input source and destination.

so i made the code according to that
if user input source and destinations are available in datatable 1
so it will get id form datatable 1
and user can add comments.
when they add comments it will store according to the particular id
so user can comments anytime and it will add one by one to the table
so that code is okay

so now i want to get those comments into one textbox or listbox


so i made code for select id where source and destination equals to userinputs from database1
and select all the usercomment which equals to that particular user Id

so my code is not working for that...
it gives executenonquery error

// when user select view comment button comment box will visible to everyone
        string textnval = UserComment.Text;
        UserComment.Visible = true;

        SqlCommand scmd1 = null;
        
        //Add comment Button
        //Create new Sql Connection
        SqlConnection conn2 = new SqlConnection();
        //Create new Connection String
        conn2.ConnectionString = "Data Source=PROMOD-PC;Initial Catalog=travel_Directions;Integrated Security=True";
        //open new connection

        //String Query
        String selQuery = "SELECT Id FROM MapDataImage WHERE Source='" + Source_Box.Text + "' AND Destination='" + Distance_Box.Text + "'";
        {
            // create new sql command to run the query through it.
            SqlCommand scmd = new SqlCommand(selQuery, conn2);

            // open connection
            conn2.Open();

            //create a sql data reader to read the query through sql command
            SqlDataReader sqldread = scmd.ExecuteReader();

            // untill sql data reader read the query
            while (sqldread.Read())
            {
                //retrieve id from the sql mapImagedata table
                int Dbid = (int)sqldread["Id"];
                

                // this will check is there any valid id or not
                if (Dbid != null)
                {
                    String QueryStr = "SELECT User_Comments FROM Add_Comment WHERE Id='" + Dbid + "'";
                    scmd1 = new SqlCommand(QueryStr, conn2);

                }
            }

            sqldread.Dispose();
            
            scmd1.ExecuteNonQuery();


            conn2.Close();
        }

i认为我的第二个选择查询中有错误或错误..



请帮帮我。



executenonquery错误.............

i think there is a mistake or error in my second select query..

please help me.

executenonquery error.............

推荐答案

在while循环中，您正在设置一个选择查询但不执行它 - 您将只获得使用sqldrread中最后一个值的查询结果。但是，如果您的第一个查询只返回一行，这不是问题。



其次，您（最终）使用ExecuteNonQuery运行查询 - 您通常会使用该方法对于插入/更新/删除命令，它将返回受影响的行数。如果你想得到一个选择的结果然后使用一个返回datareader的其他选项 - 就像你第一次查询一样。



警告！构建查询的方式是让您接触SQL注入 - 请参阅此文章 http://msdn.microsoft.com/en-us/library/ms161953(v = sql.105）.aspx [ ^ ]。即使应用程序只是在内部或私人使用，你应该养成构造安全的sql命令的习惯。



最后，而不是说我的代码不起作用告诉我们实际上是什么问题......抛出任何异常的细节，或者实际结果与你期望的结果有什么不同。

Within your while loop you are setting up a select query but not executing it - you will only ever get the results of the query that uses the last value from sqldrread. However, this is not a problem if your first query only returns one row.

Secondly you are (eventually) running a query using ExecuteNonQuery - you would normally use that method for Insert/Update/Delete commands and it will return the number of rows that were affected. If you want to get at the results of a select then use one of the other options that returns a datareader - just as you have done with your first query.

Caution! The way you are constructing your queries is leaving you exposed to SQL Injection - see this article http://msdn.microsoft.com/en-us/library/ms161953(v=sql.105).aspx[^]. Even if the application is only ever to be used in-house or privately you should get into the habit of constructing safe sql commands.

Lastly, instead of saying "my code is not working for that" tell us what the problem actually is ... either the details of any exception that is thrown, or how the actual results are different from what you were expecting.

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