?
Solved

problem caling an SQL Server stored procedure from C# code

Posted on 2003-03-09
2
Medium Priority
?
234 Views
Last Modified: 2010-04-15
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
Comment
Question by:schatz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Accepted Solution

by:
polluxaxl earned 400 total points
ID: 8101093
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
 

Author Comment

by:schatz
ID: 8102068
Thanks polluxaxl,

Works perfectly!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question