Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

problem caling an SQL Server stored procedure from C# code

Hello everyone,

I'm using this code to test a small stored procedure.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;

namespace Import_Settlement
{
      /// <summary>
      /// Summary description for Class1.
      /// </summary>
      class Class1
      {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                  string Connection = System.Configuration.ConfigurationSettings.AppSettings["ConnectionStringSQL"];
                  SqlConnection SettlementConnect = new SqlConnection(Connection);

                  SettlementConnect.Open();

                  SqlCommand Insert = SettlementConnect.CreateCommand();
                  Insert.CommandType = CommandType.StoredProcedure;
                  Insert.CommandText = "sp_DST";

                  SqlParameter DST = new SqlParameter();

                  DST = Insert.Parameters.Add(new SqlParameter("@SiteID", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@SiteID"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@RetailerID", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@RetailerID"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@NPOID", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@NPOID"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@CardAccountNumber", SqlDbType.VarChar, 14));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@CardAccountNumber"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@Product1Code", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Product1Code"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@Product1Qty", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Product1Qty"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@Product2Code", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Product2Code"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@Product2Qty", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Product2Qty"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@Product3Code", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Product3Code"].Value = 11;

                  DST = Insert.Parameters.Add(new SqlParameter("@Product3Qty", SqlDbType.Int));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Product3Qty"].Value = 11;
                  
                  DST = Insert.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Money));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@Amount"].Value = 11;
                  
                  DST = Insert.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 10));
                  DST.Direction = ParameterDirection.Input;
                  Insert.Parameters["@tname"].Value = "DST_030309";

                  try
                  {
                        Insert.ExecuteNonQuery();
                  }
                  
                  catch (Exception e)
                  {
                        Console.WriteLine(e);
                  }

                  SettlementConnect.Close();
            }
      }
}

The tablename is passes to the stored procedure as a variable. This is the procedure itself:

CREATE PROCEDURE sp_DST

@SiteID int,
@RetailerID int,
@NPOID int,
@CardAccountNumber varchar(14),
@Product1Code int,
@Product1Qty int,
@Product2Code int,
@Product2Qty int,
@Product3Code int,
@Product3Qty int,
@Amount money,
@tname varchar (10)
 
AS
 
EXEC ( 'INSERT INTO '+ @tname + ' (SiteID,RetailerID,' +
      'NPOID,CardAccountNumber,Product1Code,'+
      'Product1Qty,Product2Code,Product2Qty,'+
      'Product3Code,Product3Qty,Amount)  VALUES' +
      '(' + @SiteID +','  +
        @RetailerID + ',' + @NPOID +','  +
        @CardAccountNumber + ',' + @Product1Code +','  +
        @Product1Qty + ',' + @Product2Code +','  +
        @Product2Qty + ',' + @Product3Code +','  +
        @Product3Qty + ',' + @Amount +')' )
GO

When I run the program, an exception pops up saying: "Implicit conversion from data type money to datatype nvarchar is not allowed. Use the CONVERT function to use this query."

I'm assuming the string I use for the stored proc insert statement is not accepting the money datatype fo some reason. Or maybe I'm mistaken...

Help please....

0
schatz
Asked:
schatz
1 Solution
 
polluxaxlCommented:
Hi Schatz:

The error you're experiencing is very self-explanatory: You are trying to run a SQL sentence assembled on-the-spot while executing SQL, which happens to be different that using each parameter for simple assignment. In order to make it work, you should convert every parameter different from varchar or nvarchar to varchar by using CONVERT, in order to form a valid string, so you could do something like:

DECLARE
   @SQLString   varchar(2048)

SET @SQLString = 'INSERT INTO '+ @tname +
' (SiteID,RetailerID,' +
'NPOID,CardAccountNumber,Product1Code,'+
'Product1Qty,Product2Code,Product2Qty,'+
'Product3Code,Product3Qty,Amount) VALUES' +
'(' + CONVERT(varchar, @SiteID) +
',' + CONVERT(varchar, @RetailerID) +
',' + CONVERT(varchar, @NPOID) +
',' + @CardAccountNumber +
',' + CONVERT(varchar, @Product1Code) +
',' + CONVERT(varchar, @Product1Qty) +
',' + CONVERT(varchar, @Product2Code) +
',' + CONVERT(varchar, @Product2Qty) +
',' + CONVERT(varchar, @Product3Code) +
',' + CONVERT(varchar, @Product3Qty) +
',' + CONVERT(varchar, @Amount) +')'

EXEC ( @SQLString )

'Keep in mind that you can also do a Print @SQLString command, sometimes helpful for debugging purposes

You should take care of all conversions, not only Amount, otherwise, it would be pointing out errors for every unconverted var...

Hope it helps !
0
 
schatzAuthor Commented:
Thanks polluxaxl,

Works perfectly!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now