none
call stored procedure in C# RRS feed

  • Question

  • This is as far as i could get but i am trying to call a stored procedure from my C# application and use a textbox to pass the parameter to then i want to click the button and it uses whats in the textbox as the @OrderID which is the parameter i need to pass it then i want to add the items it returns based on what i enter in the textbox to my listBox1.Items.Add("data".ToString());....any help where i should go????

     

     

    string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];

    SqlConnection scn = new SqlConnection(connect);

    SqlCommand spcmd = new SqlCommand("CustOrdersDetail", scn);

    spcmd.CommandType = System.Data.CommandType.StoredProcedure;

    spcmd.CommandText = "CustOrdersDetail";

     

     

    Thursday, August 10, 2006 7:19 PM

Answers

  • should really be:

     


    try
    {
     
    string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];
    SqlConnection scn = new SqlConnection(connect);
    string sp = "CustOrdersDetail";

     SqlCommand spcmd = new SqlCommand(sp, scn);

    spcmd.CommandType = CommandType.StoredProcedure

     
    SqlParameter theOrderID = new SqlParameter("@OrderID", SqlDbType.Int);
    theOrderID = Convert.ToInt32(this.txt1.Text);

    SqlDataReader dr;

    spcmd.Parameters.Add(theOrderID);


    MessageBox.Show(spcmd.Parameters["@OrderID"].Value.ToString());
     
    scn.Open();

    dr = spcmd.ExecuteReader();
     

    while (dr.Read())
    {
    listBox2.Items.Add(dr.GetValue(0).ToString());
    }

    scn.Close();
    dr.Close();
    }
    catch (SqlException x)
    {
    MessageBox.Show(x.Message.ToString());
    }
    }
    }
    }


     

     

     

    if there are any errors, try to post the entire exception message (including inner exception)

    Thursday, August 10, 2006 10:07 PM

All replies

  • sorry I dont quite follow...

    do you want to get a list of results back from SQL based on the parameter you give to a stored procedure, which the stored procedure will give you back the results based on the query?

    Thursday, August 10, 2006 8:04 PM
  • Yea i want to get a result set back based on the parameter i give the stored procedure but i want to enter the value for the parameter in a textbox like....

    textBox1.Text = @OrderID

    Excute the Stored procedure

    return results in listBox1.Items.Add("result returned from procedure")

    i would normally just do it like this

    string smd = "Select Employess Where fname = " + textBox1.Text + ";

    Sqlcommand cmd = New SqlCommand(smd, connection);

    i just want to use a Stored procedure to do it now to avoid injection attacks.

    Thursday, August 10, 2006 8:10 PM
  • good - your learning the proper way ;-)

     

    right, fairly simple.

    you take whatever you need from the inputs and add them to your parameter collection.

    Now there are different ways of getting data back from SQL, probably something you maybe familiar with - SqlDataAdapter, filling a dataset with results returned back, then binding them to textboxes/comboboxes/datagridview etc...

    Example:

     



    ..
    ..
    SqlParameter theID = new SqlParameter("@theCustomerID", Sqldbtype.Int);
    theID.Value = this.textBox1.Text
    theSqlCommand.Parameters.Add(theID);
    theDataAdapter.Fill(theDataSet); //data filled in the dataset
    theSqlCommand.Connection.Open();
     
    theSqlCommand.Connection.Close();
    this.theDataGridView.DataSource = theDataSet.Tables[0].DefaultView;

     

     

    does this help?

     

    you can also read data using a SqlDataReader (which is faster since its a forward-based reader only) if you like, but best stick with the SqlDataAdapter for now, since everything is pretty much done for you.

     

    the way you execute commands from C# (client) to SQL Server is pretty much the same as you would from SQL Injection and the same thing for retrieving data - no difference except setting up the correct approach to execute the command (stored procedures) and creating the procedure on SQL Server

     

    does this help?

    Thursday, August 10, 2006 8:36 PM
  • I am using VS.Net 2005 .Net 2.0 and my compiler is teling me that SqlDbtype.Int is not supported in the context I am using.....Also i wanted to know if i am executing it right by using the SqlDataReader???

    "The name 'SqlDbtype' does not exist in the current context"

    also how do i add that special block of text that keeps the code together when i post in the forumn instead of showing the code like i have it????

     

    string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];

    SqlConnection scn = new SqlConnection(connect);

    string sp = "CustOrderDetails";

    SqlCommand spcmd = new SqlCommand(sp, scn);

    SqlParameter pm = new SqlParameter("@OrderID", SqlDbtype.Int);

    SqlDataReader dr;

    pm.Value = this.txt1.Text;

    spcmd.Parameters.Add(pm);

    scn.Open();

    dr = spcmd.ExecuteReader();

    if (dr.Read())

    {

    while (dr.Read())

    {

    listBox2.Items.Add(dr.GetValue(1).ToString());

    }

    }

    scn.Close();

    dr.Close();

    }

    }

    }

    Thursday, August 10, 2006 8:54 PM
  • for the forum tags, :

     

    [ code language="c#" ]

     

    [ /code ]

    altogether

     

    the SqlDbType.Int is a parameter, be sure it shows up in intellisense rather than copying and pasting.

    you seem to be using the dataReader correctly but take out the if statement, there is no need for it unless you are doing a conditional statement (if there is no data then..blah blah)

    you maybe better if you are going to be using several columns for data analysis etc... to fill a dataset with results, then if you need to bind a UI control like a listbox/combobox, to give it the datasource and displaymember:

     



    this.theListBox.Datasource = theDataSet.Tables[0].DefaultView;
    this.theListBox.DisplayMember = "fieldname";

     

     

    this will automatically populate the listbox with the appropriate data from the field name given

     

    does this help?

    Thursday, August 10, 2006 9:00 PM
  • Well I am going to try and load it with a dataset but i want to get this way working first....I am using the NorthWind database in SQL server 2000 and i keep getting "Incorrect Syntax near CustOrdersDetail" but i dont know why...I am trying to see what its sending to the server but the MessageBox.Show only return System.SqlClient.SqlCommand....kinda lost check my code out

     


    try

    {
     
    string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];
    SqlConnection scn = new SqlConnection(connect);
    string sp = "CustOrdersDetail";

     
    SqlCommand spcmd = new SqlCommand(sp, scn);
    string pm = "@OrderID";

    SqlDataReader dr;
    spcmd.Parameters.AddWithValue(txt1.Text, pm);
    spcmd.Parameters.Add(pm, System.Data.
    SqlDbType.Int);

     
    scn.Open();
    MessageBox.Show(spcmd.ToString());
    dr = spcmd.ExecuteReader();
     
    if (dr.Read())
    {
    while (dr.Read())
    {
    listBox2.Items.Add(dr.GetValue(0).ToString());
    }
    }
    scn.Close();
    dr.Close();
    }
    catch (SqlException x)
    {
    MessageBox.Show(x.Message.ToString());
    }
    }
    }
    }

     

    Thursday, August 10, 2006 9:52 PM
  • should really be:

     


    try
    {
     
    string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];
    SqlConnection scn = new SqlConnection(connect);
    string sp = "CustOrdersDetail";

     SqlCommand spcmd = new SqlCommand(sp, scn);

    spcmd.CommandType = CommandType.StoredProcedure

     
    SqlParameter theOrderID = new SqlParameter("@OrderID", SqlDbType.Int);
    theOrderID = Convert.ToInt32(this.txt1.Text);

    SqlDataReader dr;

    spcmd.Parameters.Add(theOrderID);


    MessageBox.Show(spcmd.Parameters["@OrderID"].Value.ToString());
     
    scn.Open();

    dr = spcmd.ExecuteReader();
     

    while (dr.Read())
    {
    listBox2.Items.Add(dr.GetValue(0).ToString());
    }

    scn.Close();
    dr.Close();
    }
    catch (SqlException x)
    {
    MessageBox.Show(x.Message.ToString());
    }
    }
    }
    }


     

     

     

    if there are any errors, try to post the entire exception message (including inner exception)

    Thursday, August 10, 2006 10:07 PM
  • Friday, August 11, 2006 1:09 AM
  • You can't cast theOrderId to int for that "theOrderID" variable - it's a SqlParameter, not an int.  You get an illegal cast error.

    If you're trying to fill txt1 with the OrderID, and OrderID is an output parameter of a Stored Procedure, do it like this:

     

     

            try
    
            {
    
    		SqlConnection sqlConn = null;
    
            	ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings["OurConnectionString"];
    
            string connString = cs.ConnectionString;
    
                    // Open our connection
    
    		if (sqlConn != null)
    
    		{
    
    			if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); }
    
    		}
    
    		sqlConn = new SqlConnection(connString);
    
    		sqlConn.Open();
    
    		string sp = "MyStoredProcedure";
    
    		SqlCommand  dbCommand = new SqlCommand(sp, sqlConn);
    
            	dbCommand.CommandType = CommandType.StoredProcedure;
    
    		SqlParameter orderID = new SqlParameter();
    
            	orderID = dbCommand.Parameters.Add(new SqlParameter("OrderID", SqlDbType.Int, 10));
    
            	userID.Direction = ParameterDirection.Output;
    
    		dbCommand.ExecuteNonQuery();	
    
    		txt1.Text = userID.Value.ToString();
    
    	}
    
    	catch(Exception ex)
    
    	{
    
    		Response.Write(ex.ToString());
    
    	}
    
    	finally { sqlConn.Close(); }
    

     

    If you are trying to update the database using a Stored Procedure that has OrderID as an input parameter that comes from txt1.Text, do it this way:

     

            try
    
            {
    
    		SqlConnection sqlConn = null;
    
            	ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings["OurConnectionString"];
    
            string connString = cs.ConnectionString;
    
                    // Open our connection
    
    		if (sqlConn != null)
    
    		{
    
    			if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); }
    
    		}
    
    		sqlConn = new SqlConnection(connString);
    
    		sqlConn.Open();
    
    		string sp = "MyStoredProcedure";
    
    		SqlCommand  dbCommand = new SqlCommand(sp, sqlConn);
    
            	dbCommand.CommandType = CommandType.StoredProcedure;
    
                	dbCommand.Parameters.Add("OrderID", SqlDbType.Int, 10).Value = int.Parse(txt1.Text);
    
    		dbCommand.ExecuteNonQuery();	
    
    	}
    
    	catch(Exception ex)
    
    	{
    
    		Response.Write(ex.ToString());
    
    	}
    
    	finally { sqlConn.Close(); }
    

    This last method assumes there are no output parameters and that OrderID is the only required input parameter to the Stored Procedure (it likely won't be).

    • Proposed as answer by navyjax2 Monday, November 28, 2011 9:15 AM
    Monday, November 28, 2011 9:14 AM