Use value from one database query to select second query

 Hi,

 I have the following code, which looks up the value 'comment_id' from a database table (tblPage), then uses that value to look up values from the comments table (tblComment):

 

 

protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Server=my.server.ip;Database=db;User ID=myusername;Password=mypassword");
        conn.Open();
       
        //Page
        SqlCommand comm_page = new SqlCommand("SELECT TOP 1 page_title, page_keywords, page_description, page_content, comment_id FROM tblPage WHERE page_id = 1;", conn);
        SqlDataReader reader_page = comm_page.ExecuteReader();
        while (reader_page.Read())
        {
            metaTitle.Text = reader_page["page_title"].ToString();
            metaKeywords.Content = reader_page["page_keywords"].ToString();
            metaDescription.Content = reader_page["page_description"].ToString();
            string comment_id = reader_page["comment_id"].ToString();
        }
        reader_page.Close();
       
        //Comment
        SqlCommand comm_comment = new SqlCommand("SELECT TOP 1 comment_comment, comment_person FROM tblComment WHERE comment_id = " + comment_id + ";", conn);
        SqlDataReader reader_comment = comm_comment.ExecuteReader();
        while (reader_comment.Read())
        {
            commentComment.Text = reader_comment["comment_comment"].ToString();
            commentPerson.Text = reader_comment["comment_person"].ToString();
        }
        reader_comment.Close();
        conn.Close();

    }

 

 

However, running this, I get a compilation error: "The name 'comment_id' does not exist in the current context"

I'm aware I could join the two tables in a single query, but I really want to understand how to do the above, should I need to in future.

Thanks for any help folks...

 

0
danwednesday
2/5/2009 8:52:18 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

6 Replies
791 Views

Similar Articles

[PageSpeed] 59

danwednesday:
  while (reader_page.Read())
        {
            metaTitle.Text = reader_page["page_title"].ToString();
            metaKeywords.Content = reader_page["page_keywords"].ToString();
            metaDescription.Content = reader_page["page_description"].ToString();
            string comment_id = reader_page["comment_id"].ToString();
        }

the scope of your comment_id is your while loop:

so declare your comment_id before the loop

string comment_id ;

  while (reader_page.Read())
        {
            metaTitle.Text = reader_page["page_title"].ToString();
            metaKeywords.Content = reader_page["page_keywords"].ToString();
            metaDescription.Content = reader_page["page_description"].ToString();
           comment_id = reader_page["comment_id"].ToString();
        }
        reader_page.Close();
       
        //Comment
        SqlCommand comm_comment = new SqlCommand("SELECT TOP 1 comment_comment, comment_person FROM tblComment WHERE comment_id = " + comment_id + ";", conn);
        SqlDataReader reader_comment = comm_comment.ExecuteReader();
        while (reader_comment.Read())
        {
            commentComment.Text = reader_comment["comment_comment"].ToString();
            commentPerson.Text = reader_comment["comment_person"].ToString();
        }
        reader_comment.Close();
        conn.Close();


Sri
Please mark as answer if it helped you
0
jsriharsha
2/5/2009 9:45:11 PM

You need to declare your variable outside the while loop, e..g.

string Comment_ID ;

assign it in the loop

your query will only use the last value of comment_ID. Is it the intent?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/5/2009 9:49:31 PM

Hi,danwednesday

Make Ur Code LIke This

I Have dont Make Ur Input Parameters To Parameterized Query

So I will Suggest U MAke ur Query Parameterized SO As to get Prevention From Sql injection

I have Shown the changes in bold

 

protected void Page_Load(object sender, EventArgs e)

{

SqlConnection conn = new SqlConnection("Server=my.server.ip;Database=db;User ID=myusername;Password=mypassword");

 

//Page

SqlCommand comm_page = new SqlCommand("SELECT TOP 1 page_title, page_keywords, page_description, page_content, comment_id FROM tblPage WHERE page_id = 1;", conn);

conn.Open();

SqlDataReader reader_page = comm_page.ExecuteReader();

string comment_id;

if (reader_page.HasRows)

{

while (reader_page.Read())

{

metaTitle.Text = reader_page[
"page_title"].ToString();

metaKeywords.Content = reader_page["page_keywords"].ToString();

metaDescription.Content = reader_page["page_description"].ToString();comment_id = reader_page["comment_id"].ToString();

}

}

reader_page.Close();

//Comment

SqlCommand comm_comment = new SqlCommand("SELECT TOP 1 comment_comment, comment_person FROM tblComment WHERE comment_id = " + comment_id + ";", conn);SqlDataReader reader_comment = comm_comment.ExecuteReader();

 

if (reader_comment.HasRows)

{

while (reader_comment.Read())

{

commentComment.Text = reader_comment["comment_comment"].ToString();

commentPerson.Text = reader_comment["comment_person"].ToString();

}

}

reader_comment.Close();

conn.Close();

}

 

 

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
2/6/2009 3:19:09 AM

 Thanks folks, but it's not working.  My code looks like this:

 

protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Server=mp.server.ip;Database=db;User ID=username;Password=password");
        conn.Open();
       
        //Declare variables
        string comment_id;

        //Page
        SqlCommand comm_page = new SqlCommand("SELECT TOP 1 page_title, page_keywords, page_description, page_content, comment_id FROM tblPage WHERE page_id = 1;", conn);
        SqlDataReader reader_page = comm_page.ExecuteReader();
        while (reader_page.Read())
        {
            metaTitle.Text = reader_page["page_title"].ToString();
            metaKeywords.Content = reader_page["page_keywords"].ToString();
            metaDescription.Content = reader_page["page_description"].ToString();
            pageContent.Text = reader_page["page_content"].ToString();
            comment_id = reader_page["comment_id"].ToString();
        }
        reader_page.Close();
       
        //Comment
        SqlCommand comm_comment = new SqlCommand("SELECT TOP 1 comment_comment, comment_person FROM tblComment WHERE comment_id = " + comment_id + ";", conn);
        SqlDataReader reader_comment = comm_comment.ExecuteReader();
        while (reader_comment.Read())
        {
            commentComment.Text = reader_comment["comment_comment"].ToString();
            commentPerson.Text = reader_comment["comment_person"].ToString();
        }
        reader_comment.Close();
        conn.Close();

    }

 

 When I run this though, I get an error on this line:

SqlCommand comm_comment = new SqlCommand("SELECT TOP 1 comment_comment, comment_person FROM tblComment WHERE comment_id = " + comment_id + ";", conn);
Saying, Compilation error: "CS0165: Use of unassigned local variable 'comment_id'" 
 

Any ideas?

0
danwednesday
2/6/2009 10:10:00 AM

danwednesday:
Saying, Compilation error: "CS0165: Use of unassigned local variable 'comment_id'"
 

If that is coming U can put 

string comment_id=""; in this portion by replacing string comment_id;

danwednesday:
        SqlConnection conn = new SqlConnection("Server=mp.server.ip;Database=db;User ID=username;Password=password");
        conn.Open();
       
        //Declare variables
        string comment_id;

        //Page

 


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
2/6/2009 10:27:30 AM

Is your comment_ID an integer in the database?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
2/6/2009 1:15:58 PM
Reply:

Similar Artilces:

Extracting values from one table and query the values as a query for second table
Dear Perl DBI users I have two tables: currency_acc and amount_type. I would like to extract Names from the first table who have SGD currency and keep as a query for the second table Table 1 currency_acc +--------------------------------------- ID Name currency + +--------------------------------------- 23 RAM SGD 32 GOP GBP 45 RAN SGD 67 KRI USD 78 YUR SGD +------------------------------------ Table 2 amount_type +------------------------------------------------- ACC RAN KRI YUR RAM + +---------------------...

Results of One Query Used In A Second Query
I don't know the correct approach to this problem. Nested Queries perhaps? I have users enter stock symbols into a textbox. The symbols are then written in a table that has 3 columns named UserID, Portfolio_Name and Portfolio_Symbols. In order to look the symbols up (Portfolio_Symbols), I search by UserID and Portfolio_Name; this returns a string consisting of the symbols. What I need, however are the results of (another) query based on these symbols(Portfolio_Symbols)--the result would be various pieces of data for each company (symbol)being returned to a dataset that will be read...

Using selected values from a Multi Select Listbox in a SQL query
I have seen a few post concerning this but don't really understand fully as I am new to this.  I would appreciate any help 1) First I loop through the Listbox control and get the selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End If Next Label5.Text = strPC The resulting string looks something like: 101, 102, 100 ,    I get rid of the last space and "," with the following statement Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) 2) I would then like to ...

Select Where Database Query Using a Query String Variable
Hi, i'm using a variable called ID which is getting passed through a querystring, it is converted into an integer and i am trying to use it within a select statement. Dim ID As String = Request.QueryString("DVD_ID") Dim dvdid as Integer = Convert.ToInt32(ID) For some reason it just refuses to work, my code is like this below: ("Select * From DVDs Where DVD_ID='+dvdid'", MyConnection) When i process the code, i get "Data type mismatch in criteria expression", but when change it to for example, the value  to 2, it works perfectly! I am assuming my syntax is wrong, i'm using...

Srored procedure, sql query not using values in the query
ISSUE: I am attempting to construct a stored procedure that will select select information between two time periods. Well I have tried a couple of queries to find out what would happen to performance. What I have found is the query where the range of dates is entered, hard coded, the Optimizer uses the index. If I use a variable or a calculation I get a table scan and the dates are not used at all in the query. Why is this happening, forcing the index has no effect, see last example. I am using ASE 11.92 on a SUN server, Sun_srv4, sybase build 1031. below are the results of query ...

how access records one by one fetched using Select query in sql server 2005
Hi Friends, I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist  as shown below Declare @tolist varchar(8000) set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '') FROM awc_register WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist  set @tolist = substring(@tolist, 2, len(@tolist))  and I passed this @tolist to another procedure which should send mails for email...

SqlDatasource using SQL database query as datasource problem
I have a SqlDatasource that uses a SQL 2005 query as its datasource. I cant get the UPDATE/INSERT/DELETE radio button to enable. The query is made up of 3 relational tables. I have tried including all the primary keys from the tables but to no avail. What am i doing wrong and how do i get the update to work Thanks in advance  thegent38,You can use stored procedures for select, insert and update  <asp:SqlDataSource ID="sdsRadnici" runat="server"           ConnectionString="<%$ conStr %>&qu...

using value from a drop down list to select database name for query
Thanks in advance to anyone who can help me out. I've been at this one for a little while now and am getting nowehere fast.  I have a drop down list available for my coworkers to select a database for which they want to view the stored procedures. That select statement selects the db names as "dbname" In my statement to display the procedures for the db that has been selected I have:  <asp:SqlDataSource ID="sdsAdmin" runat="server" ConnectionString="<%$ ConnectionStrings:masterConnectionString %>" SelectComman...

how to add a query to sql datasource using a value from code behind?
 I have a value in my web form which is received from previous page and i have a sqldatasource in which i have written the query through the wizard.In this query i want to use where clause which tallies the cavlue from code behind :the code in code behind is:value1 = Request("abc")SqlDataSource1.SelectParameters("fid").DefaultValue = value1and in source:  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CoursePortalConnectionString %>"       &nb...

sql query based on one or many dropdownlist values selected
I'm trying to write flexible dynamic sql that will allow the user to select one or many dropdown value to be an 'AND' clause in any part of a sql select statement. DropDowns all have the following in common: AppendDataBoundItems="true"><asp:ListItem Text="<Select>" Value=" " /> Currently the if statement immediately following requires that the  DropDownListSRForeman be selected first because there is no and operator before the comparison. How could I write the if statement and following sql to allow the user to s...

How to use value calcuated in query in subsequent query, all based on value in querystring?
I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string.  But, I don't know how to get a value returned from one sql statement into a second.  Here's what I'm trying to do... From the QueryString, we know that the MatterID = 4  ( xxx.aspx?MatterID=4) Knowing that the Matterid=4, I query the database to get the OfficeId for that MID  (Select OfficeID from tMatter where Mid=4)   ~This returns an OfficeID of 6 So, then I need to do another query to get the employees w...

All Listed Datagrid Results from one database used in a datasource query for another Datagrid.
I have two separate databases with separate connection strings.   DataGrid (1) list a series of names from database (1).   How can the second DataGrid (2) datasource (2) use the DataGrid (1) LISTed values in it's query?  The query input requires the entire list.  Consequently, gridview1.selectedvalue does not appear to be an option. Thanks for your time,  Sincerely, Unhistoric   I'm not sure if I follow your statement above, but if you want to use the first DataSource as your Grid 2 DataSource then y...

How do I use a SQL query as my report datasource instead of just picking the tables in the database expert?
A colleague of mine has been creating our reports for our application and so far he's been just selecting the tables and setting up the relationships using the database expert (I think). We need to change this so that we can filter the data so we're only reporting on a single account instead of every record in the database. What do I need to change to accomplish this? Can it be done in the report or will I need to create a data source of some kind in the application and pass it into the report somehow? We're new to Crystal Reports so this may be a simple answer. Thanks! You can pa...

Getting a value from another query to use it in other queries
I have a datasource query in which i take some values.I am using a listbox so that to show these values.I want to select from the listbox and use this value in other queries as a table to choose from.So in other datasource i am trying to use the syntax SelectCommand="SELECT bla FROM '<%DropDownList1.SelectedValue%>' " but it doesnt seem to work.How can I use the table value from the other query in different way or how can i manage this using the command above ?????Thanx  see it is SELECT bla FROM '<% ListBox1.SelectedValue%>'Jai Ganesh. J , GSD ,I...

Web resources about - Use value from one database query to select second query - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

Donald Trump walks back Muslim database comments - Videos - CBS News
Republican candidate Donald Trump stepped back from comments that appear to suggest that he wants to register all of the Muslims in the United ...

Trump: ‘I Didn’t Suggest A Database’ For Registering Muslims
Trump: ‘I Didn’t Suggest A Database’ For Registering Muslims

Trump refuses to say how his Muslim database plan differs from Nazi Germany
... blurt out outrageous ideas like forcing American Muslims to register and carry special ID and then back down. No, he sticks with them. A database ...

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

WATCH 'TROJAN HORSE' Trump wants database for Syrian refugees in US
WATCH 'TROJAN HORSE' Trump wants database for Syrian refugees in US

Donald Trump is Under Media Assault for Suggesting ‘Muslim Database,’ But Here’s What He Says About It ...
"Donald Trump's plan for a Muslim database draws comparison to Nazi Germany"...

Trump is getting pummeled by his political opponents over Muslim database
WASHINGTON — Republican presidential rivals rushed Friday to condemn Donald Trump's support for a government database to track Muslims in the ...

Resources last updated: 11/22/2015 8:09:32 AM