ASP.NET C# Connect to database and execute Stored procedure which has input parameter

I am using C# 2005 and SQL server 2005.
I already have default.aspx and web.config file in my application. i just want to connec to database and execute my stored procedure.
Can some one tell me how to do Database connection using Web.config file?
I have created a stored procedure in sql 2005, it takes in 2 input paramenters and return one output parameter. only one value with be return everytime as an output parameter.  Just want to display data in a Gridview control based off the results of the stored proc

I have included the ASP.NET, C#, and web.config information

Thanks for you help in advance.
ASP.NET Code****************
 
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" Debug="true"  %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    <asp:GridView ID="GridView1" runat="server">
  <Columns>
    <asp:BoundField DataField="id"
HeaderText="id" SortExpression="id" />
    <asp:BoundField DataField="Name"
HeaderText="Name" SortExpression="Name"/>
<asp:BoundField DataField="CreateDate"
HeaderText="CreateDate" SortExpression="CreateDate"/>
<asp:BoundField DataField="UserName"
HeaderText="UserName" SortExpression="UserName"/>
<asp:BoundField DataField="Queue"
HeaderText="Queue" SortExpression="Queue"/>
  </Columns>
 
    </asp:GridView>
    </form>
</body>
</html>
 
END ASP>NET CODE************************
 
C# .cs file CODE************************************
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Data;
 
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
 
        PopulateGrid();
      
    }
 
 
    public void PopulateGrid()
        {
 
          
          
 
          DataSet objDs = new DataSet();
          //SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString);
          SqlConnection conn = new SqlConnection("Server=myservername;Database=mytable;Trusted_Connection=True;");
          SqlDataAdapter mCommand;
          mCommand = new SqlDataAdapter("MYStoredProcedure", conn);
 
          mCommand.SelectCommand.  
 
            mCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
            
            conn.Open();
            mCommand.Fill(objDs);
 
                  
         
          GridView1.DataSource = objDs;
          GridView1.DataBind();
 
        }
 
}
 
********END C# Code .cs file
 
******  Web.config File
<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration>
 
 
 
    <appSettings/>
    <connectionStrings/>
    <system.web>
        <!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
        -->
        <compilation debug="true">
 
        </compilation>
        <!--
            The <authentication> section enables configuration 
            of the security authentication mode used by 
            ASP.NET to identify an incoming user. 
        -->
        <authentication mode="Windows" />
        <!--
            The <customErrors> section enables configuration 
            of what to do if/when an unhandled error occurs 
            during the execution of a request. Specifically, 
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.
 
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
 
 
 
    </system.web>
 
</configuration>

Open in new window

jedwards_2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

silemoneCommented:
do you mean connectionstring in Web.Config?
0
jedwards_2Author Commented:
Yep
0
silemoneCommented:
http://www.connectionstrings.com/?carrier=sqlserver2005 is how to construct connection strings...

you place inside web.Config within the Configuration node...


i.e.


<configuration>
      <connectionStrings>
            <add name="CNTSTR" connectionString="Data Source=PROG2;Initial Catalog=Tasks;Integrated Security=True" providerName="System.Data.SqlClient"/>
      </connectionStrings>

you give it whatever name you wish...i chose CNTSTR for connect string, but better to call it a name for which application/project will use it...
      
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

silemoneCommented:
in your code, you use  

ConfigurationManager.ConnectionStrings["CNTSTR"].ToString() to retrieve it...this static class is from namespace System.Configuration so you must  place as one of your libraries:  using System.Configuration;
0
silemoneCommented:
you can place your connection string you retrieved in a string variable or just pass it in method where string connectString is needed...i.e.


SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString()); <--c# way


Dim objConn As New SqlConnection((ConfigurationManager.ConnectionStrings["CNTSTR"].ToString())
0
jedwards_2Author Commented:
OK, so How would I call the stored proc and pass values to load data to gridview
0
jedwards_2Author Commented:
I am only using C#
0
silemoneCommented:
here's the code: to make a db call:

          Helper dbHelper = new Helper();
          string strQuery = "Select * from Makes";
          objDT = dbHelper.getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          rpt.DataSource = objDT;
          rpt.DataBind();


I have a helper.cs class dbHelper that I create an instance of...

In my helper.cs Class I have the method:

 public DataTable getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objDT = null;

          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objDT = new DataTable();
               objDA.Fill(objDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion

          return objDT;
     }




         


0
silemoneCommented:
oops..my example is using a repeater, but it's actually the same process...all you would have to do is change rpt to grid name
0
silemoneCommented:
now if you don't want to use a help.cs class (helper class is just something I created because since I use these methods so often, its sort of a little library where I can just call the method and pass  couple of params instead always writing code over), you can do this...


in page load (in both examples I should have mentioned this part goes in page load before...also objDT is a global DataTable in both these examples...)


DataTable objDT =  null;

in pageLoad method...
 
          string strQuery = "Select * from Makes";
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          rpt.DataSource = objDT;
          rpt.DataBind();


in same page, make a method called getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objDT = null;

          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objDT = new DataTable();
               objDA.Fill(objDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion

          return objDT;
     }
0
silemoneCommented:
finally this is the namespace for SQL objects...

using System.Data.SqlClient;
0
jedwards_2Author Commented:
I like that way.  Need a little assistance.  Getting some errors in my code.  See my snippet below.  i added a GridView on my ASP page and got rif of one of the erros.  Can you dump the code into VS and take a look at the errors that I am getting.  I am using ASP.NET
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    protected void Page_Load(object sender, EventArgs e)
    {
 
        string strQuery = "Select * from Makes";
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public void getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objDT = new DataTable();
               objDA.Fill(objDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objDT;
     }
 
 
}

Open in new window

0
jedwards_2Author Commented:
I also am using a stored proc with input parameters instead of hard coded SQL String.
0
silemoneCommented:
1) ok...you can't use my selection statement...I have a table called Makes...so you need to add your DataBase table there instead...
2)  objDT, you didn't make it global as I stated...

second, what error are you getting?

anyway, here is corrected code as far as errors I can see right now...
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT = null;
 
    protected void Page_Load(object sender, EventArgs e)
    {
       
  
          string strQuery = "Select * from Makes";   //<--- Put your DataBase Table there instead of my table Makes
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public void getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objTempDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objDT = new DataTable();
               objDA.Fill(objTempDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objTempDT;
     }
 
 
}

Open in new window

0
silemoneCommented:
one more correction...
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT;
 
    protected void Page_Load(object sender, EventArgs e)
    {
       
  
          string strQuery = "Select * from Makes";   //<--- Put your DataBase Table there instead of my table Makes
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public void getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objTempDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objDT = new DataTable();
               objDA.Fill(objTempDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objTempDT;
     }
 
 
}

Open in new window

0
silemoneCommented:
one more correction...press enter before ready...
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT = null;
 
    protected void Page_Load(object sender, EventArgs e)
    {
       
  
          string strQuery = "Select * from Makes";   //<--- Put your DataBase Table there instead of my table Makes
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public void getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objTempDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objTempDT = new DataTable();
               objDA.Fill(objTempDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objTempDT;
     }
 
 
}

Open in new window

0
silemoneCommented:
last but not least....
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT;
 
    protected void Page_Load(object sender, EventArgs e)
    {
       
  
          string strQuery = "Select * from Makes";   //<--- Put your DataBase Table there instead of my table Makes
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public void getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objTempDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objTempDT = new DataTable();
               objDA.Fill(objTempDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objTempDT;
     }
 
 
}

Open in new window

0
silemoneCommented:
ok...for stored procedure...just put "EXEC SPROCNAME"  where SPROCNAME is Store procedure...

so make:
string strQuery = "Select * from Makes";

string strQuery = "EXEC SPROCNAME parameter1 parameter2 ";  //if there are parameters...otherwise don't use parameters
0
jedwards_2Author Commented:
Here are the erros that I am receiving.

Error      1      Cannot implicitly convert type 'void' to 'System.Data.DataTable'      
Error      2      Since 'Default2.getDataTable(string, string)' returns void, a return keyword must not be followed by an object expression      
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT;
 
    protected void Page_Load(object sender, EventArgs e)
    {
       
  
          string strQuery = "Select * from Makes";   //<--- Put your DataBase Table there instead of my table Makes
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public void getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objTempDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objTempDT = new DataTable();
               objDA.Fill(objTempDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objTempDT;
     }
 
 
} 

Open in new window

0
silemoneCommented:
Easy fix...in fact they derive from same problem...did i put 'void getDataTable' or 'DataTable getDataTable'.  my apologies..

All we have to do is change void getDataTable...to DataTable getDataTable so that this method returns a dataTable...void means it returns nothing so we wouldn't be able to do this:  


objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);

the reason we are doing this is because we're returning a DataTable...so

 public DataTable getDataTable(string connectString, string queryString)    <----DataTable in this says at the end of the method
                                                                                                                   we must have 'return objDT;'
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT;
 
    protected void Page_Load(object sender, EventArgs e)
    {
       
  
          string strQuery = "Select * from Makes";   //<--- Put your DataBase Table there instead of my table Makes
          objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
          GridView1.DataSource = objDT;
          GridView1.DataBind();
          
 
    }
 
 
    public DataTable getDataTable(string connectString, string queryString)
     {
          SqlConnection objConn = null;
          SqlCommand objComm = null;
          SqlDataAdapter objDA = null;
          DataTable objTempDT = null;
 
          try
          {
               objConn = new SqlConnection(connectString);
               objComm = new SqlCommand(queryString, objConn);              
               objDA = new SqlDataAdapter(objComm);
               objTempDT = new DataTable();
               objDA.Fill(objTempDT);              
          }
          catch (Exception ex)
          {
               //write to a log file all errors
               string error = ex.Message;
          }
          #region explicit disposal of objects
          finally
          {
               if(objConn.State.Equals(ConnectionState.Open))
               {
                    objConn.Close();                    
               }
               if (objConn != null)
               {
                    objConn.Dispose();
               }
               if (objComm != null)
               {
                    objComm.Dispose();
               }
               if (objDA != null)
               {
                    objDA.Dispose();
               }
          }
          #endregion
 
          return objTempDT;
     }
 
 
} 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jedwards_2Author Commented:
OK.....   I got everything working and connected.  Grid populates with data.  One last question.  Based off the code, how do you pass multiple parameters this way.  I see a lot of examples where you can do param.add, but the example you provided is a little bit different.  
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataTable objDT;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        string strQuery = "EXEC TagByTouchTime @vRouting = 'QE In Work', @vShortName = IS NOT NULL, @ vDept = IS NOT NULL";
        objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
        GridView1.DataSource = objDT;
        GridView1.DataBind();
 
 
    }
 
 
    public DataTable getDataTable(string connectString, string queryString)
    {
        SqlConnection objConn = null;
        SqlCommand objComm = null;
        SqlDataAdapter objDA = null;
        DataTable objTempDT = null;
 
        try
        {
            objConn = new SqlConnection(connectString);
            objComm = new SqlCommand(queryString, objConn);
            objDA = new SqlDataAdapter(objComm);
            objTempDT = new DataTable();
            objDA.Fill(objTempDT);
        }
        catch (Exception ex)
        {
            //write to a log file all errors
            string error = ex.Message;
        }
        #region explicit disposal of objects
        finally
        {
            if (objConn.State.Equals(ConnectionState.Open))
            {
                objConn.Close();
            }
            if (objConn != null)
            {
                objConn.Dispose();
            }
            if (objComm != null)
            {
                objComm.Dispose();
            }
            if (objDA != null)
            {
                objDA.Dispose();
            }
        }
        #endregion
 
        return objTempDT;
    }
}

Open in new window

0
jedwards_2Author Commented:
Nevermind....  I figured it all out.  I created a overload and changed the datatable to a dataset.  Want to say thanks for all your help.
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
public partial class Default2 : System.Web.UI.Page
{
 
    DataSet objDT;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        string strQuery = "EXEC TagByTouchTime 'QE In Work'";
        //string strQuery = "TagByTouchTime";
        objDT = getDataTable(ConfigurationManager.ConnectionStrings["CNTSTR"].ToString(), strQuery);
        GridView1.DataSource = objDT;
        GridView1.DataBind();
 
 
    }
 
 
    public DataSet getDataTable(string connectString, string queryString)
    {
        SqlConnection objConn = null;
        SqlCommand objComm = null;
        SqlDataAdapter objDA = null;
        DataSet objTempDT = null;
 
        try
        {
            objConn = new SqlConnection(connectString);
            objComm = new SqlCommand(queryString, objConn);
            objDA = new SqlDataAdapter(objComm);
            objTempDT = new DataSet();
            objDA.Fill(objTempDT);
        }
        catch (Exception ex)
        {
            //write to a log file all errors
            string error = ex.Message;
        }
        #region explicit disposal of objects
        finally
        {
            if (objConn.State.Equals(ConnectionState.Open))
            {
                objConn.Close();
            }
            if (objConn != null)
            {
                objConn.Dispose();
            }
            if (objComm != null)
            {
                objComm.Dispose();
            }
            if (objDA != null)
            {
                objDA.Dispose();
            }
        }
        #endregion
 
        return objTempDT;
    }
 
    public DataSet getDataTable(string connectString, string queryString, string param1)
    {
        SqlConnection objConn = null;
        SqlCommand objComm = null;
        SqlDataAdapter objDA = null;
        DataSet objTempDT = null;
 
        try
        {
            objConn = new SqlConnection(connectString);
            objComm = new SqlCommand(queryString, objConn);
            objComm.CommandType = CommandType.StoredProcedure;
            objComm.Parameters.Add(new SqlParameter("@vRouting", SqlDbType.NVarChar, 75));
            objComm.Parameters["@vRouting"].Value = param1;
            //objComm.Parameters.Add(new SqlParameter("@vShortName", SqlDbType.NVarChar, 75));
            //objComm.Parameters["@vShortName"].Value = "vShortName";
            //objComm.Parameters.Add(new SqlParameter("@vDept", SqlDbType.NVarChar, 75));
            //objComm.Parameters["@vDept"].Value = "vDept";
            objDA = new SqlDataAdapter(objComm);
 
            objTempDT = new DataSet();
            objDA.Fill(objTempDT);
        }
        catch (Exception ex)
        {
            //write to a log file all errors
            string error = ex.Message;
        }
        #region explicit disposal of objects
        finally
        {
            if (objConn.State.Equals(ConnectionState.Open))
            {
                objConn.Close();
            }
            if (objConn != null)
            {
                objConn.Dispose();
            }
            if (objComm != null)
            {
                objComm.Dispose();
            }
            if (objDA != null)
            {
                objDA.Dispose();
            }
        }
        #endregion
 
        return objTempDT;
    }
}

Open in new window

0
silemoneCommented:
ok...just a note:  if you use

objComm.CommandType = CommandType.StoredProcedure; <--and yes when i told you to use "EXEC StoredProcedureName"
                                                                                               it equates to using this...difference is:
                                                                                               if you set command type, you have to remove EXEC and just
                                                                                               pass stored procedure name....

then you should change first version of method with signature
   public DataSet getDataTable(string connectString, string queryString)

to do the same so that you will always just pass StoredProcedureName without EXEC...

Other things:  Change getDataTable to getDataSet so there won't be any confusion
                     Change any object with DT as end letters to DS as in 'objTempDT' the DT stands for DataTable...DS stands for
                     DataSet

Things like this will help later with maintenance so that others won't get confused and neither will yourself...

GOOD JOB!!!

Cheers...

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.