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

 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

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)

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 
   

 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? 

 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 
   

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)