Solved

how to use cmd.ExecuteReader

Posted on 2011-03-08
12
799 Views
Last Modified: 2012-05-11
i have a cript that excute a single row using excutescalar and i want to change to excutereader to have more rows

how do i apply it

// Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                                    object resultObj = cmd.ExecuteScalar();

                                    if (resultObj != null)
                                    {
                                          credit = resultObj.ToString();
                                          balance.Text=string.Format("{0}",credit);
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }
                              }

                              conn.Close();
                        }
0
Comment
Question by:daz1234
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35068510
0
 

Author Comment

by:daz1234
ID: 35068556
so how would i rewrite the syntax above i am no programmer

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35068586
Well, there isn't much in your code to state what database type you are connecting to or what your result set is or what datatypes you are using. But, making some assumptions it would be something like:
// Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                                    SqlDataReader reader = cmd.ExecuteReader();

                                    while (reader.Read())
                                    {
                                          credit = reader.GetDouble(0);
                                          balance.Text=string.Format("{0}",credit);
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }
                              }

                              conn.Close();
                        }

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 35068587
What do you want to do with the multiple results ? Append them in one textbox, show in a datagrid, ... ?

// Create command
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;

SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while(myReader.Read()) 
{
    Console.WriteLine(myReader.GetString(0));
}
myReader.Close();

Open in new window

0
 
LVL 15

Expert Comment

by:angus_young_acdc
ID: 35068604
Try the following
cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;
            SqlDataReader reader = default(SqlDataReader);
            reader = cmd.ExecuteReader();
            balance.Text="Name: N/A";
            while(reader.Read())
            {
                 credit = resultObj.ToString();
                 balance.Text=string.Format("{0}",credit);

            }
            conn.Close();

Open in new window

0
 

Author Comment

by:daz1234
ID: 35068661
want to show the result as text on a webpage as rows

full script below

<%@ Page Language="C#" Title="rate" %>


<script runat="server" type="text/C#">

                  void  Page_Load()
                  {
                        string dialplan = Request.QueryString["dialplan"];
                        string credit = "";
                        string sql = string.Format("select * from zones('{0}')", dialplan);
                        
                        
                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(Web.Configuration.DatabaseConnectionString))
                        {
                              conn.Open();

                              using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                              {
                                    // Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                              
                                    
                                    object resultObj = cmd.ExecuteScalar();

                                    if (resultObj != null)
                                    {
                                          credit = resultObj.ToString();
                                          balance.Text=string.Format("{0}",credit);
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }

                                    
                              }

                              conn.Close();
                        }

                  }
</script>
            
<asp:label id="balance" runat="server"/>
</asp:label>
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 35068694
                  void  Page_Load()
                  {
                        string dialplan = Request.QueryString["dialplan"];
                        string credit = "";
                        string sql = string.Format("select * from zones('{0}')", dialplan);
                        
                        
                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(Web.Configuration.DatabaseConnectionString))
                        {
                              conn.Open();

                              using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                              {
                                    // Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                              
                                    
                                    SqlDataReader reaer = cmd.ExecuteReader();

                                    if (reader.Read())
                                    {
                                         do {
                                          credit = reader.GetDouble(0).ToString();
                                          balance.Text += string.Format("{0}<br />",credit);
                                        } while (reader.Read());
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }

                                    
                              }

                              conn.Close();
                        }

                  }

Open in new window

0
 

Author Comment

by:daz1234
ID: 35068736
carl_tawn

i get an error message

 
Compiler Error Message: CS0246: The type or namespace name 'SqlDataReader' could not be found (are you missing a using directive or an assembly reference?)

Line 24:                              
Line 25:                                    
Line 26:                                     SqlDataReader reaer = cmd.ExecuteReader();
Line 27:
Line 28:                                     if (reader.Read())
 
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 35068768
Change that line to:
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

Open in new window

0
 

Author Comment

by:daz1234
ID: 35068817
now i get

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:


Line 27:                                     {
Line 28:                                          do {
Line 29:                                           credit = reader.GetDouble(0).ToString();
Line 30:                                           balance.Text += string.Format("{0}<br />",credit);
Line 31:                                         } while (reader.Read());
 
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 35068840
Well since we can only guess at the data type of your underlying data, just use:
credit = reader.GetValue(0).ToString();

Open in new window

0
 

Author Closing Comment

by:daz1234
ID: 35068887
fantastic

worked like a treat

how would you excute script from a webpage

where the user enter the dial plan digits and then the submit button would excute the script
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this tutorial I will show you how to provide a dynamic RTF document on your website generated with data from your database. For this tutorial you will need Microsoft Word or WordPad, WhizBase and Microsoft Access. In this tutorial I will show …
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
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

919 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now