locked
how to access database through stored procedure RRS feed

  • Question

  • User-1751137359 posted

    i want to display database result of "userid" using stored procedure.i have done the following

    // THIS CODE IS UNDER "NI_AD_USER_MST2.CS(BLAYER)"
    
    using System;
    using System;
    using System.Data;
    using System.Configuration;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    namespace NIPL.TASK
    {
        class NI_AD_USER_MST
        {
            SqlConnection con;
            DBFunctions HTMLREPORTS;
            SqlDataAdapter da;
            public void HTMLREPORT()
            {
                HTMLREPORTS = new DBFunctions();
            }
            public bool NI_AD_USER_MST(int userid, out DataSet ds)
            {
    
    
                con = HTMLREPORTS.GetConnection();
                da = new SqlDataAdapter("[nipl].[USER_MST_SP]", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@NI_ADUM_USER_ID", userid);
                DataSet dsout = new DataSet();
    
                da.Fill(dsout, "[nipl].[USER_MST_SP]");
                ds = dsout;
                con = HTMLREPORTS.CloseConnection();
    
                return true;
            }
        }
    }
    
    // THIS CODE IS UNDER NI_AD_HTML_REPORTS.ASPX.CS
    
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    using System.Net;
    using NIPL.TASK;
    
    namespace NIPL.NIPL.TASK
    {
       
        public partial class NI_AD_HTML_REPORTS : System.Web.UI.Page
        {
    
            NI_AD_USER_MST hr = new NI_AD_USER_MST();
            
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPLOYEEConnectionString"].ToString());
            
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    try
                    {
                        con.Open();
                        string NI_QUERY = "";
                        string NI_USER_ID = Request.QueryString["UserId"];
                        string NI_Entity_ID = Request.QueryString["EntityId"];
                        string NI_ENTITY_ID = Session[NI_SessionClass.NI_SS_ENTITYID].ToString();
                        SqlCommand cmd;
                        SqlDataReader ad;
                       
                        //Response.Write("</br>");
                        //Response.Write("</br>");
                        //Response.Write("</br>");
                        //Response.Write("</br>");
                        //Response.Write("</br>");
                        Response.Write("<TABLE border=1 cellspacing='0' cellpadding='0' width='100%'>");
                        Response.Write(" <th> User Name</th>");
                        Response.Write("<th> Email Id </th>");
                        Response.Write("<th> User Pan </th>");
                        Response.Write("<th> User Dept Id </th>");
                        Response.Write("<th> Dept Name </th>");
                        Response.Write("<th> Registration Date </th>");
                        Response.Write("<tr valign='top'>");
                        DataSet ds = new DataSet();
                        hr.NI_AD_USER_MST(NI_USER_ID, out ds);
                        cmd = new SqlCommand(NI_QUERY,con);
                        ad = cmd.ExecuteReader();
                        while (ad.Read())
                        {
                           
                           
                            Response.Write("<tr>");
                           
                            
                            Response.Write("<td width=\"15%\" align=\"left\">" + ad.GetValue(0).ToString() + "</td>");
                            
                            
                            Response.Write("<td width=\"15%\" align=\"left\">" + ad.GetValue(1).ToString() + "</td>");
                            
                            
                            Response.Write("<td width=\"15%\" align=\"left\">" + ad.GetValue(2).ToString() + "</td>");
                          
                        
                            Response.Write("<td width=\"10%\" align=\"left\">" + ad.GetValue(3).ToString() + "</td>");
                            
                          
                            Response.Write("<td width=\"20%\" align=\"left\">" + ad.GetValue(4).ToString() + "</td>");
                            
                            
                            Response.Write("<td width=\"25%\" align=\"left\" >" + ad.GetValue(5).ToString() + "</td>");
                          
                            
                            Response.Write("</tr>");
                          
    
    
    
                        }
                        ad.Close();
                    }
                    catch (Exception eNI)
                    {
                        Response.Write(eNI.Message);
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
        }
    }
    
    //THIS IS MY STORED PROCEDURE
    
    USE [EMPLOYEE]
    GO
    
    /****** Object:  StoredProcedure [nipl].[USER_MST_SP]    Script Date: 01/01/2013 17:36:13 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [nipl].[USER_MST_SP](
    @NI_ADUM_USER_ID INT)
    AS
    BEGIN
    SELECT * FROM nipl.NI_AD_USER_MST WHERE NI_ADUM_USER_ID = @NI_ADUM_USER_ID
    END 
    
    GO

    Friday, October 11, 2013 8:08 AM

All replies

  • User753101303 posted

    Hi,

    Use the last "Insert Code" button and copy/paste in the popup window. Your code should then be much more readable. Also it's always best to tell what happens when the code you shown runs. Do you see have error ? If yes, what is the message and where does it happen ?

    For now it's unrelated but with web forms you usually avoid to use Response.Write. Instead you can bind data sources to control (for example the asp.GridView control) and this control will render the HTML markup at its place in the page for you.

    Friday, October 11, 2013 8:23 AM
  • User1751268424 posted

    Hi,

    Can you repost your code please!

    You can use the right most button of this editor: "Insert Code" button, just after small "HTML" button, paste the code after click the "Insert Code" button.

    That will make your code much easier to read.

    Have fun

    Friday, October 11, 2013 8:30 AM
  • User-1751137359 posted

    CODE UPDATED

    Friday, October 11, 2013 8:48 AM
  • User-1751137359 posted

    CODE UPDATED

    Friday, October 11, 2013 8:49 AM
  • User1508394307 posted

    You need to understand the code you do.

    Let's take a piece of it

    string NI_QUERY = "";
    
    ...
     
    hr.NI_AD_USER_MST(NI_USER_ID, out ds);
    cmd = new SqlCommand(NI_QUERY,con);

    What is this for?

    Line #1 sets NI_QUERY = "". Where do you use it? You use it in cmd = new SqlCommand(NI_QUERY,con); - you supplied empty query to SqlCommand. Will never work.

    Next line is with hr.NI_AD_USER_MST(NI_USER_ID, out ds); What is this for? Where do you use ds after that? 

    Friday, October 11, 2013 9:57 AM
  • User-1751137359 posted

    then please tell me how can i display the "userid" records by using the stored procedure.As i am a beginner please tell me the appropiate code.

      thanks in advance

    Sunday, October 13, 2013 2:59 AM
  • User1508394307 posted

    You can try this

    protected void Page_Load(object sender, EventArgs e)
    {
      using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EMPLOYEEConnectionString"].ToString()))
      {
        con.Open();
    
        string NI_USER_ID = Request.QueryString["UserId"];
    
        // html stuff deleted
        
        SqlCommand cmd = new SqlCommand("nipl.USER_MST_SP", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@NI_ADUM_USER_ID", NI_USER_ID);
        
        SqlDataReader ad = cmd.ExecuteReader();
        
        while (ad.Read())
        {
            Response.Write("<tr>"); 
            Response.Write("<td width=\"15%\" align=\"left\">" + ad.GetValue(0).ToString() + "</td>");    
            Response.Write("<td width=\"15%\" align=\"left\">" + ad.GetValue(1).ToString() + "</td>");
            Response.Write("<td width=\"15%\" align=\"left\">" + ad.GetValue(2).ToString() + "</td>");
            Response.Write("<td width=\"10%\" align=\"left\">" + ad.GetValue(3).ToString() + "</td>");
            Response.Write("<td width=\"20%\" align=\"left\">" + ad.GetValue(4).ToString() + "</td>");
            Response.Write("<td width=\"25%\" align=\"left\">" + ad.GetValue(5).ToString() + "</td>");
            Response.Write("</tr>");
        }
      }
    }

    It's not tested and you might need to create a new page for the test, to keep your old code.

    Hope this helps.

    Sunday, October 13, 2013 4:20 AM
  • User-1751137359 posted

    thanks and also this is not working and finally i get the results by own.

    Monday, October 14, 2013 7:28 AM
  • User1508394307 posted

    Why it is not working?

    Monday, October 14, 2013 7:51 AM