locked
input parameters ignored when called by ,NET RRS feed

  • Question

  • I have a very simple stored procedure with 1 string or 2  inputs. When I execute with SQL management server the values I type in are taken.  But when I use .NET, the input parameters appear ignored.  What am I missing ?  Thanks.  Code for .NET and the stored procedure are below.  With .NET only the time is recorded & the behavior is as if I submitted empty strings. But then why does the interactive MGMT Studio allow my input & more importantly what do I need to do to fix it.  Many thanks

    System.Data.SqlClient.SqlConnection conn = new SqlConnection(recordingDB);
                    System.Data.SqlClient.SqlCommand command = new SqlCommand(storedProcName, conn);
                    conn.Open();
                    command.CommandText = storedProcName;
                    SqlParameter s1 = new SqlParameter("@cnxtring", outageServerConnection);
                    command.Parameters.Add(s1);
                    int numRows = command.ExecuteNonQuery();// returns
                    conn.Close();

    USE [MiniTest]
    GO
    /****** Object:  StoredProcedure [dbo].[Usp_SQLStartofOutage]    Script Date: 09/11/2011 20:33:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[Usp_SQLStartofOutage]
     -- Add the parameters for the stored procedure here
     @cnxtring nvarchar(120)='',
     @outageInfo nvarchar(max)=''
    AS
    BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
       SET NOCOUNT ON;
       DECLARE @SQLOutageStartTime  DateTime
       SET @SQLOutageStartTime=GETDATE()

     INSERT INTO [MiniTest].[dbo].[SQLMonitorState]
               ([OutageStartTIme]
               ,[ServerDatabase]
               ,[Info])
         VALUES
               (@SQLOutageStartTIme,
                @cnxtring,
                @outageInfo)
     
    END


    andrew
    Monday, September 12, 2011 3:57 AM

Answers

  • Ah... my code was at fault - I neglected to set this line below ( brought to my attention by the C# forum... )

    command.CommandType = System.Data.

    CommandType.StoredProcedure;

    command.CommandText = storedProcName;

     


    andrew
    Monday, September 12, 2011 5:57 AM

All replies

  • What if you set the Value of the Parameter to Nothing in VB.NET or null in C#.?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 12, 2011 5:32 AM
    Answerer
  • If from management studio I just continue on the execute command then I get an empty string ( not null ) in the table field.  It looks just like the same result when I try to load the parameter from  C# in my application code.


    andrew
    Monday, September 12, 2011 5:53 AM
  • Ah... my code was at fault - I neglected to set this line below ( brought to my attention by the C# forum... )

    command.CommandType = System.Data.

    CommandType.StoredProcedure;

    command.CommandText = storedProcName;

     


    andrew
    Monday, September 12, 2011 5:57 AM