C#Net2008 Stored Procedures


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: C#Net2008 Stored Procedures

  1. #1
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319

    [RESOLVED] C#Net2008 Stored Procedures


    Hi Csharp Good Guys

    I am encountering another interesting problem using C#.
    I am writing coding to pass parameters to SQL SERVER Stored Procedure to updata the TABLE but instead it generated 3 error messages on compilation.

    The main error seems to be caused by this coding statement which add value to the parameters that generate ERROR 1 of the 3 error messages:

    sqlCmd.Parameters <---- cause ERROR 1
    -------------------------------------
    Here are the 3 error messages:
    Error 1 Non-invocable member 'System.Data.SqlClient.SqlCommand.Parameters' cannot be used like a method.
    Error 2 Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?)
    Error 3 Operator '==' cannot be applied to operands of type 'string' and 'System.DBNull'

    -------------------------------------------------

    Here are the coding:
    Code:
    private void FUpdateCreateRecOrders() 
     
           {
               sqlconn = new SqlConnection(connstr);
               sqlcmd = new SqlCommand();
               sqlcmd.Connection = sqlconn;
    
                // CALL STORE PROCEDURE
                sqlcmd.CommandText = "SPMaintTestOrdersTBL";
                sqlcmd.CommandType = CommandType.StoredProcedure;
             
                 //create  input parameter direction and define SQL SERVER DataType
                 sqlcmd.Parameters.Add("@OrderID", SqlDbType.Int, 4).Direction = ParameterDirection.Input;                
                 sqlcmd.Parameters.Add("@OrderDate",SqlDbType.DateTime,8).Direction = ParameterDirection.Input;
                 sqlcmd.Parameters.Add("@ShipVa",SqlDbType.Int,4).Direction = ParameterDirection.Input;
                 sqlcmd.Parameters.Add("@Freight",SqlDbType.Money,8).Direction = ParameterDirection.Input;
                     
                 //parameter values                  
                 sqlcmd.Parameters ("@OrderID").Value= Convert.ToInt32(intPOrderID);                   
                 sqlcmd.Parameters ("@OrderDate").Value = this.txtOrderDate.TextLength == 0? String.Empty: DateTime.Parse(this.txtOrderDate.Text);
                 sqlcmd.Parameters  ("@ShipVia").Value = cboTransport.Text == System.DBNull.Value ? string.Empty: this.cboTransport.SelectedValue;
                 sqlcmd.Parameters  ("@Freight").value = txtFreight.TextLength ==0? 0.00: (Double) txtFreight.Text;  
                     
           }
    Last edited by Lennie; 04-15-2010 at 10:49 PM. Reason: resolved
    Cheers,
    Lennie

  2. #2
    Join Date
    Jan 2005
    Location
    New Zealand
    Posts
    319
    Hi Csharp NewBies

    I would like to share with you the coding which is working now. Here is the brief description of the coding function
    .

    The textbox controls data are being retrieve and used as parameters to update the SQL SREVER table using STORED PROCEDURES within the SQL SERVER. The transfer of parameters are based on SQLCMD.PARAMETERS.ADD function.

    private void FUpdateCreateRecOrders()

    {
    sqlconn = new SqlConnection(connstr);
    sqlcmd = new SqlCommand();
    sqlcmd.Connection = sqlconn;

    // CALL STORE PROCEDURE
    sqlcmd.CommandText = "SPMaintOrdersTBL";
    sqlcmd.CommandType = CommandType.StoredProcedure;

    //create input parameter direction and define SQL SERVER DataType
    sqlcmd.Parameters.Add("@OrderID", SqlDbType.Int, 4).Direction = ParameterDirection.Input;
    sqlcmd.Parameters.Add("@CustID", SqlDbType.nchart, 4).Direction = ParameterDirection.Input;
    sqlcmd.Parameters.Add("@OrderDate",SqlDbType.DateTime,8).Direction = ParameterDirection.Input;
    sqlcmd.Parameters.Add("@ShipVa",SqlDbType.Int,4).Direction = ParameterDirection.Input;
    sqlcmd.Parameters.Add("@Freight",SqlDbType.Money,8).Direction = ParameterDirection.Input;

    //parameter values

    sqlcmd.Parameters ["@OrderID"].Value= Convert.ToInt32(intPOrderID);
    sqlcmd.Parameters ["@CustID"].Value= Convert.string(txtCustID.text);
    sqlcmd.Parameters ["@OrderDate"].Value = this.txtOrderDate.TextLength == 0? String.Empty: DateTime.Parse(this.txtOrderDate.Text);
    sqlcmd.Parameters ["@ShipVia"].Value = cboTransport.Text == System.DBNull.Value ? string.Empty: this.cboTransport.SelectedValue;
    sqlcmd.Parameters ["@Freight"].value = txtFreight.TextLength ==0? 0.00: (Double) txtFreight.Text;

    }

    Here is the Stored Procedure:
    Create Procedure SPMainOrdersTBL
    @OrderID int,
    @CustID nchar(5),
    @OrderDate datatime,
    @ShipVia int,
    @Freight money,
    as
    begin
    Update Orders
    set CustomerID = @CustID,
    OrderDate = @OrderDate,
    ShipVia = @shipVia,
    Freight = @Freight

    Where OrderID = @OrderID
    end
    Go
    Cheers,
    Lennie

Similar Threads

  1. Replies: 0
    Last Post: 11-24-2006, 08:11 AM
  2. DB Independent Application..What abt Stored Procedures
    By Richard Hsu in forum VB Classic
    Replies: 0
    Last Post: 12-05-2001, 05:26 AM
  3. Stored procedures and multiple databases
    By Col in forum VB Classic
    Replies: 0
    Last Post: 10-25-2001, 09:34 PM
  4. Replies: 3
    Last Post: 03-01-2001, 11:49 PM
  5. Stored procedures and Active X Objects
    By Nicole Hagler in forum Database
    Replies: 0
    Last Post: 09-06-2000, 10:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center