Solved

Asp c# sql server: How to pass the result of the first sql field to a c# variable?

Posted on 2009-07-09
14
251 Views
Last Modified: 2012-05-07
I have an aspx page, in c#, with a working sql request.  The sql request returns just 1 row everytime, with just one field, testField.

I can see the correct result in a datagrid.  But, how to pass this to a c# variable?

Thanks!
0
Comment
Question by:ThanksWelcome
14 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 24815671
How are you populating the datagrid, can you post the relevant code?
0
 

Author Comment

by:ThanksWelcome
ID: 24815750
Thanks for your answer.  Yes, I post it below, but please note the data grid is somehow irrelevant to the final result, as I use it just for testing, to make sure all the sql request work.  So it will ultimately be removed.  

The name of the datasource is SqlDataSource1, while the name of the field is testField.  Only one row is expected as a result everytime.  I do not need to implement verification at this point.  Thanks!
    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" 
        EnableModelValidation="True" AutoGenerateColumns="False" 
        BorderStyle="None" style="text-align: center" Width="267px">
        <Columns>
            <asp:BoundField DataField="testField" HeaderText="testField" SortExpression="testField" />
         
        </Columns>
    </asp:GridView>

Open in new window

0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24815833
"but please note the data grid is somehow irrelevant to the final result, as I use it just for testing, to make sure all the sql request work.  So it will ultimately be removed. "

In that case can you let us know what exactly would be the real scenario.
As the code will vary depending on if you are using GridView, sqlDatasource or none of those.

Or do you mean you want to execute a SQL query and retrieve the results in code-behind?
Then you can look into using SqlDataReader like below:

// 1. Instantiate the connection
        SqlConnection conn = new SqlConnection(YourconnectionString);
        SqlDataReader rdr = null;
        try
        {
            // 2. Open the connection
            conn.Open();
            // 3. Pass the connection to a command object
            SqlCommand cmd = new SqlCommand(yourQuery,conn);
            //
            // 4. Use the connection
            //
            // get query results
            rdr = cmd.ExecuteReader();
            // print the CustomerID of each record
            while (rdr.Read())
            {
                string result = rdr["testField"].ToString();
            }
        }
        finally
        {
            // close the reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // 5. Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }

Code Reference:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 22

Assisted Solution

by:Snarf0001
Snarf0001 earned 50 total points
ID: 24816030
For the most part agree with guru_sami, if you're not actually binding to a grid and just want it in a variable, then do the query in code-behind, not in the aspx.

Only thing I would suggest is using cmd.ExecuteScalar() instead of .ExecuteReader().
If your only getting one value back each time, that's what Scalar is inteded to do, return the first column of the first row of the first result set.

ie:

string myVariable = cmd.ExecuteScalar().ToString();
0
 

Author Comment

by:ThanksWelcome
ID: 24816108
Hi!  I am sorry if context was not clear.

I think the best would be to take a look at my attached whole page below.  For the moment, if I could just be able to pass the  field "Domain" (this is the real name for the testField in the real page attached), for the first row, to a c# variable from the connection sqldatasource1 , It'd be fantastic.  :)

Important note1:  please take note, on begining of document, of:  protected override void OnPreRender(EventArgs e)...
Important note2:  Configured this way, I already obtain successufully the row I need.  I just need to pass the result to a variable.

Thank's!   (And I'm sorry if this question has been kind of messy in the description...)
defaultpage.txt
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24816204

Good Point. Thanks.

Just a small question:
So Ideally you don't need this code right?
----Following code is just for test right ....and not required----
 <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
        EnableModelValidation="True" AutoGenerateColumns="False"
        BorderStyle="None" style="text-align: center" Width="267px">
        <Columns>
            <asp:BoundField DataField="Domain" HeaderText="Domain" SortExpression="Domain" />
         
        </Columns>
    </asp:GridView>
   
    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
     <%# Eval("Domain")%>
</ItemTemplate>
</asp:Repeater>
------End of Test Code----------

If so then what exactly are you trying to accomplish?
0
 

Author Comment

by:ThanksWelcome
ID: 24816283
Right.  In fact this code is for testing purposes.  It helped me to realize visually that I really get the result I want from the database.  It almost fills the need I describe, but it prints the result in the web page instead of puting it in a variable.  I really hope I am clear, you are very patient...  thanks!
0
 
LVL 41

Accepted Solution

by:
guru_sami earned 450 total points
ID: 24816434
Ok then you do not need even SqlDataSource and that OnPreRender method as well....
Try something like below:
 protected void Page_Load(object sender, EventArgs e)
    {
 // assuming there is a connectionsting with name "testfirststring" in your web.config.
        string connectionString = ConfigurationManager.ConnectionStrings["testfirststring"].ConnectionString;

// 1. Instantiate the connection
        SqlConnection conn = new SqlConnection(connectionString);
          try
        {
            // 2. Open the connection
            conn.Open();
            // 3. Pass the connection to a command object
// This is the query taken from the code you provided
           string query = String.Format("SELECT Domain FROM table1test WHERE Domain ='{0}'",HttpContext.Current.Request.ServerVariables["HTTP_HOST"]);

            SqlCommand cmd = new SqlCommand(query,conn);
           // get query results
           //Thanks to Snarf0001:
                   string myVariable = cmd.ExecuteScalar().ToString();
              }
        finally
        {          
            // 5. Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
}

Note: Instead of putting this function in .aspx file you can move it to your code behind file i.e. .aspx.cs
0
 

Author Comment

by:ThanksWelcome
ID: 24816945
Hi!  This looks just fantastif, I'm sure it will work!  One problem though:  SqlCommand and SqlConnection generate an error:  The type or namespace name  could not be found, are you missing a using or assembly refenrence?

I tried to add  :
using System.Data;
using System.Data.SqlClient;

But it did not change.  I'm sure we are done after this!  :)  Thanks!!!
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24817039
Where did you add System.Data.SqlClient;?
in your code behind or in .aspx page itself?
if its on .aspx page you should add this at the top of the page after you <@Page..> directive:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
0
 

Author Comment

by:ThanksWelcome
ID: 24817214
Hi!  Import worked the way you gave it... Oh oh:  when I compile, I get this message:  On line:   string myVariable = cmd.ExecuteScalar().ToString();  

System.NullReferenceException

I'm so sorry, can we give 5000 points?  THanks!
0
 

Author Comment

by:ThanksWelcome
ID: 24817407
No problem finally with the null exception:  it is because I did not have result to my query:  I was not online.  Now it works.

Thanks for everything!  

Next step:  I do not know how to access the variable:  it seems the scope is not outside the function?  I tried to do a:

<% Response.Write(myVariable); %>  , from the body, and it did not work because this variable did not exist in the context.  Nice if you had a clue, but, well, thank you very much for your great great help!!!



0
 

Author Closing Comment

by:ThanksWelcome
ID: 31601711
Thanks!!!
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24817850
you can declare a public variable like below:
public string myVariable;
 protected void Page_Load(object sender, EventArgs e)
    { //rest of your code...with minor chage
                myVariable = cmd.ExecuteScalar().ToString();
}
Then you can access it as:
<% Response.Write(this.myVariable); %>
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question