Solved

how to use cmd.ExecuteReader

Posted on 2011-03-08
12
793 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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 …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

758 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

20 Experts available now in Live!

Get 1:1 Help Now