Another set of eyes would be appreciated...Lots to look at.

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Trying to execute the below C# from an ASPX to SQL 2008 SP. The below
Error is produced:

Msg 201, Level 16, State 4, Procedure Cornerstone_NEWPOST, Line 0
Procedure or function 'Cornerstone_NEWPOST' expects parameter
'@ApplicationID', which was not supplied.

(1 row(s) affected)


Lots to look at if you're can. Thanks!

/////////////////////////////////////////////////////////////////////////////////
C# from ASPX:///////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
SqlConnection dbConn = null;
string strConnection;
SqlCommand dcmd = null;
try
{
string uI = User.Identity.Name.ToString();
if (IsPostBack)
{
strConnection = ConfigurationManager.ConnectionStrings
["CornerstoneConnectionString"].ConnectionString;
dbConn = new SqlConnection(strConnection);
dbConn.Open();

dcmd = new SqlCommand();
dcmd.CommandText = "Cornerstone_NEWPOST";

dcmd.Parameters.Add(new SqlParameter("@ApplicationID",
SqlDbType.Int));
dcmd.Parameters["@ApplicationID"].Value =
ApplicationDropDownList.SelectedValue.ToString();
dcmd.Parameters.Add(new SqlParameter
("@ApplicationGroupingID", SqlDbType.Int));
dcmd.Parameters["@ApplicationGroupingID"].Value =
GroupsDropDownList.SelectedValue.ToString();
dcmd.Parameters.Add(new SqlParameter("@ImpactID",
SqlDbType.Int));
dcmd.Parameters["@ImpactID"].Value =
ImpactDropDownList.SelectedValue.ToString();
dcmd.Parameters.Add(new SqlParameter("@TypeID",
SqlDbType.TinyInt));
dcmd.Parameters["@TypeID"].Value =
TypeDropDownList.SelectedValue.ToString();
dcmd.Parameters.Add(new SqlParameter("@Header",
SqlDbType.VarChar, 100));
dcmd.Parameters["@Header"].Value =
HeaderTextBox.Text.ToString();
dcmd.Parameters.Add(new SqlParameter
("@CornerstoneBrickBody", SqlDbType.VarChar));
dcmd.Parameters["@CornerstoneBrickBody"].Value =
DescriptionTextBox.Text.ToString();
dcmd.Parameters.Add(new SqlParameter
("@HelpdeskTicketNo", SqlDbType.VarChar, 30));
dcmd.Parameters["@HelpdeskTicketNo"].Value =
HelpdeskTextBox.Text.ToString();
dcmd.Parameters.Add(new SqlParameter("@Filename",
SqlDbType.VarChar, 255));
dcmd.Parameters["@Filename"].Value = DBNull.Value;
dcmd.Parameters.Add(new SqlParameter
("@FileDescription", SqlDbType.VarChar,500));
dcmd.Parameters["@FileDescription"].Value =
DBNull.Value;
dcmd.Parameters.Add(new SqlParameter("@Filesize",
SqlDbType.VarChar,50));
dcmd.Parameters["@Filesize"].Value = DBNull.Value;
dcmd.Parameters.Add(new SqlParameter("@FileData",
SqlDbType.VarBinary));
dcmd.Parameters["@FileData"].Value = DBNull.Value;
dcmd.Parameters.Add(new SqlParameter("@MIMEType",
SqlDbType.VarChar,50));
dcmd.Parameters["@MIMEType"].Value = DBNull.Value;
dcmd.Parameters.Add(new SqlParameter("@FileType",
SqlDbType.VarChar,5));
dcmd.Parameters["@FileType"].Value = DBNull.Value;

if (FileUpload1.HasFile)
{
string mimeType;
string fileExtension = Path.GetExtension
(FileUpload1.PostedFile.FileName).ToLower();

switch (fileExtension)
{
case ".htm":
case ".html":
case ".log":
mimeType = "text/HTML";
break;
case ".txt":
mimeType = "text/plain";
break;
case ".doc":
case ".docx":
mimeType = "application/ms-word";
break;
case ".tiff":
case ".tif":
mimeType = "image/tiff";
break;
case ".asf":
mimeType = "video/x-ms-asf";
break;
case ".avi":
mimeType = "video/avi";
break;
case ".zip":
mimeType = "application/zip";
break;
case ".xls":
case ".xlsx":
case ".csv":
mimeType = "application/vnd.ms-excel";
break;
case ".gif":
mimeType = "image/gif";
break;
case ".jpg":
case "jpeg":
mimeType = "image/jpeg";
break;
case ".bmp":
mimeType = "image/bmp";
break;
case ".wav":
mimeType = "audio/wav";
break;
case ".mp3":
mimeType = "audio/mpeg3";
break;
case ".mpg":
case "mpeg":
mimeType = "video/mpeg";
break;
case ".rtf":
mimeType = "application/rtf";
break;
case ".asp":
mimeType = "text/asp";
break;
case ".pdf":
mimeType = "application/pdf";
break;
case ".fdf":
mimeType = "application/vnd.fdf";
break;
case ".ppt":
case ".pptx":
mimeType = "application/mspowerpoint";
break;
case ".dwg":
mimeType = "image/vnd.dwg";
break;
case ".msg":
mimeType = "application/msoutlook";
break;
case ".xml":
case ".sdxl":
mimeType = "application/xml";
break;
case ".xdp":
mimeType = "application/vnd.adobe.xdp
+xml";
break;
default:
mimeType = "application/octet-stream";
break;
}
dcmd.Parameters["@Filename"].Value =
FileUpload1.FileName;
dcmd.Parameters["@FileDesc"].Value =
FileDescTextBox.Text.ToString();
dcmd.Parameters["@Filesize"].Value =
FileUpload1.PostedFile.ContentLength;
dcmd.Parameters["@FileData"].Value =
FileUpload1.FileBytes;
dcmd.Parameters["@MIMEType"].Value = mimeType;
dcmd.Parameters["@FileType"].Value =
fileExtension.Substring(1, fileExtension.Length - 1);
}

dcmd.Parameters.Add(new SqlParameter("@eBy",
SqlDbType.VarChar));
dcmd.Parameters["@eBy"].Value = uI.ToString();
dcmd.Parameters.Add(new SqlParameter("@modBy",
SqlDbType.VarChar));
dcmd.Parameters["@modBy"].Value = uI.ToString();
dcmd.Parameters.Add(new SqlParameter("@CornerstoneID",
SqlDbType.Int));
dcmd.Parameters["@CornerstoneID"].Direction =
ParameterDirection.Output;
dcmd.Parameters.Add(new SqlParameter("@ErrorCode",
SqlDbType.Int));
dcmd.Parameters["@ErrorCode"].Direction =
ParameterDirection.Output;
dcmd.Parameters.Add(new SqlParameter("@ErrorMessage",
SqlDbType.VarChar, 250));
dcmd.Parameters["@ErrorMessage"].Direction =
ParameterDirection.Output;


dcmd.CommandTimeout = 60000;
dcmd.Connection = dbConn;

dcmd.ExecuteNonQuery();
FileDescTextBox.Text = "";
}
}
catch (Exception ex)
{
ReportError(ex.Message);
}
finally
{
if (dbConn != null)
{
dcmd.Dispose();
dbConn.Close();
}
}


/////////////////////////////////////////////////////////////////////////////////
SQL Trace:////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
declare @p18 int
set @p18=NULL
declare @p19 int
set @p19=NULL
declare @p20 varchar(250)
set @p20=NULL
exec sp_executesql N'Cornerstone_NEWPOST'
,N'@ApplicationID int
,@ApplicationGroupingID int
,@ImpactID int
,@TypeID tinyint
,@Header varchar(100)
,@CornerstoneBrickBody varchar(4)
,@HelpdeskTicketNo varchar(30)
,@Filename varchar(255)
,@FileDescription varchar(500)
,@Filesize varchar(50)
,@FileData varbinary(8000)
,@MIMEType varchar(50)
,@FileType varchar(5)
,@eBy varchar(14)
,@modBy varchar(14)
,@CornerstoneID int output
,@ErrorCode int output
,@ErrorMessage varchar(250) output'
,@ApplicationID=18
,@ApplicationGroupingID=16
,@ImpactID=5
,@TypeID=4
,@Header='Test'
,@CornerstoneBrickBody='Test'
,@HelpdeskTicketNo=''
,@Filename=NULL
,@FileDescription=NULL
,@Filesize=NULL
,@FileData=NULL
,@MIMEType=NULL
,@FileType=NULL
,@eBy='CALEXAN11\Dave'
,@modBy='CALEXAN11\Dave'
,@CornerstoneID=@p18 output
,@ErrorCode=@p19 output
,@ErrorMessage=@p20 output
select @p18, @p19, @p20


/////////////////////////////////////////////////////////////////////////////////
SQL 2008 Stored Procedure////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
ALTER PROCEDURE [dbo].[Cornerstone_NEWPOST]
@ApplicationID int
,@ApplicationGroupingID int
,@ImpactID int
,@TypeID tinyint
,@Header varchar(100)
,@CornerstoneBrickBody varchar(MAX)
,@HelpdeskTicketNo varchar(30)
,@FileName varchar(255)
,@FileDescription varchar(500)
,@FileSize varchar(50)
,@FileData varbinary(max)
,@MIMEType varchar(50)
,@FileType varchar(5)
,@eBy varchar(30)
,@modBy varchar(30)
,@CornerstoneID INT OUTPUT
,@ErrorCode INT OUTPUT
,@ErrorMessage VARCHAR(250) OUTPUT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
SET LOCK_TIMEOUT 5000

SET @ErrorCode = 0
SET @ErrorMessage = ''

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO
[Cornerstone].[dbo].[CornerstoneTable]
(
[ApplicationID]
,[ApplicationGroupingID]
,[ImpactID]
,[TypeID]
,[Header]
,[eBy]
,[eDate]
,[modBy]
,[modDate]
)
VALUES
(
@ApplicationID
,@ApplicationGroupingID
,@ImpactID
,@TypeID
,@Header
,@eBy
,GETDATE()
,@modBy
,GETDATE()
)
SET @CornerstoneID = @@IDENTITY
INSERT INTO
[Cornerstone].[dbo].[CornerstoneBrickTable]
(
[CornerstoneID]
,[SequenceID]
,[CornerstoneBrickBody]
,[HelpdeskTicketNo]
,[FileName]
,[FileDescription]
,[FileSize]
,[FileData]
,[MIMEType]
,[FileType]
,[eBy]
,[eDate]
,[modBy]
,[modDate]
)
VALUES
(
@CornerstoneID
,1
,@CornerstoneBrickBody
,@HelpdeskTicketNo
,@FileName
,@FileDescription
,@FileSize
,@FileData
,@MIMEType
,@FileType
,@eBy
,GETDATE()
,@modBy
,GETDATE()
)
COMMIT TRANSACTION
END TRY

BEGIN CATCH
-- exec ut_LogError(@eBy) This procedure will log the
actual SQL error message
SET @ErrorCode = 99
SET @ErrorMessage = ERROR_MESSAGE()
SET @CornerstoneID = -1
ROLLBACK TRANSACTION
END CATCH
 
Hanging my head low....

Missing:
dcmd.CommandType = CommandType.StoredProcedure;









Trying to execute the below C# from an ASPX to SQL 2008 SP.  The below
Error is produced:

Msg 201, Level 16, State 4, Procedure Cornerstone_NEWPOST, Line 0
Procedure or function 'Cornerstone_NEWPOST' expects parameter
'@ApplicationID', which was not supplied.

(1 row(s) affected)

Lots to look at if you're can.  Thanks!
///////////////////////////////////////////////////////////////////////////­//////
C# from ASPX:///////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////­//////
        SqlConnection dbConn = null;
        string strConnection;
        SqlCommand dcmd = null;
        try
        {
            string uI = User.Identity.Name.ToString();
            if (IsPostBack)
            {
                strConnection = ConfigurationManager.ConnectionStrings
["CornerstoneConnectionString"].ConnectionString;
                dbConn = new SqlConnection(strConnection);
                dbConn.Open();

                dcmd = new SqlCommand();
                dcmd.CommandText = "Cornerstone_NEWPOST";

                dcmd.Parameters.Add(new SqlParameter("@ApplicationID",
SqlDbType.Int));
                dcmd.Parameters["@ApplicationID"].Value =
ApplicationDropDownList.SelectedValue.ToString();
                dcmd.Parameters.Add(new SqlParameter
("@ApplicationGroupingID", SqlDbType.Int));
                dcmd.Parameters["@ApplicationGroupingID"]..Value =
GroupsDropDownList.SelectedValue.ToString();
                dcmd.Parameters.Add(new SqlParameter("@ImpactID",
SqlDbType.Int));
                dcmd.Parameters["@ImpactID"].Value =
ImpactDropDownList.SelectedValue.ToString();
                dcmd.Parameters.Add(new SqlParameter("@TypeID",
SqlDbType.TinyInt));
                dcmd.Parameters["@TypeID"].Value =
TypeDropDownList.SelectedValue.ToString();
                dcmd.Parameters.Add(new SqlParameter("@Header",
SqlDbType.VarChar, 100));
                dcmd.Parameters["@Header"].Value =
HeaderTextBox.Text.ToString();
                dcmd.Parameters.Add(new SqlParameter
("@CornerstoneBrickBody", SqlDbType.VarChar));
                dcmd.Parameters["@CornerstoneBrickBody"].Value =
DescriptionTextBox.Text.ToString();
                dcmd.Parameters.Add(new SqlParameter
("@HelpdeskTicketNo", SqlDbType.VarChar, 30));
                dcmd.Parameters["@HelpdeskTicketNo"].Value =
HelpdeskTextBox.Text.ToString();
                dcmd.Parameters.Add(new SqlParameter("@Filename",
SqlDbType.VarChar, 255));
                dcmd.Parameters["@Filename"].Value = DBNull.Value;
                dcmd.Parameters.Add(new SqlParameter
("@FileDescription", SqlDbType.VarChar,500));
                dcmd.Parameters["@FileDescription"].Value=
DBNull.Value;
                dcmd.Parameters.Add(new SqlParameter("@Filesize",
SqlDbType.VarChar,50));
                dcmd.Parameters["@Filesize"].Value = DBNull.Value;
                dcmd.Parameters.Add(new SqlParameter("@FileData",
SqlDbType.VarBinary));
                dcmd.Parameters["@FileData"].Value = DBNull.Value;
                dcmd.Parameters.Add(new SqlParameter("@MIMEType",
SqlDbType.VarChar,50));
                dcmd.Parameters["@MIMEType"].Value = DBNull.Value;
                dcmd.Parameters.Add(new SqlParameter("@FileType",
SqlDbType.VarChar,5));
                dcmd.Parameters["@FileType"].Value = DBNull.Value;

                if (FileUpload1.HasFile)
                {
                    string mimeType;
                    string fileExtension = Path.GetExtension
(FileUpload1.PostedFile.FileName).ToLower();

                    switch (fileExtension)
                    {
                        case ".htm":
                        case ".html":
                        case ".log":
                            mimeType = "text/HTML";
                            break;
                        case ".txt":
                            mimeType = "text/plain";
                            break;
                        case ".doc":
                        case ".docx":
                            mimeType = "application/ms-word";
                            break;
                        case ".tiff":
                        case ".tif":
                            mimeType = "image/tiff";
                            break;
                        case ".asf":
                            mimeType = "video/x-ms-asf";
                            break;
                        case ".avi":
                            mimeType = "video/avi";
                            break;
                        case ".zip":
                            mimeType = "application/zip";
                            break;
                        case ".xls":
                        case ".xlsx":
                        case ".csv":
                            mimeType = "application/vnd.ms-excel";
                            break;
                        case ".gif":
                            mimeType = "image/gif";
                            break;
                        case ".jpg":
                        case "jpeg":
                            mimeType = "image/jpeg";
                            break;
                        case ".bmp":
                            mimeType = "image/bmp";
                            break;
                        case ".wav":
                            mimeType = "audio/wav";
                            break;
                        case ".mp3":
                            mimeType = "audio/mpeg3";
                            break;
                        case ".mpg":
                        case "mpeg":
                            mimeType = "video/mpeg";
                            break;
                        case ".rtf":
                            mimeType = "application/rtf";
                            break;
                        case ".asp":
                            mimeType = "text/asp";
                            break;
                        case ".pdf":
                            mimeType = "application/pdf";
                            break;
                        case ".fdf":
                            mimeType = "application/vnd.fdf";
                            break;
                        case ".ppt":
                        case ".pptx":
                            mimeType = "application/mspowerpoint";
                            break;
                        case ".dwg":
                            mimeType = "image/vnd.dwg";
                            break;
                        case ".msg":
                            mimeType = "application/msoutlook";
                            break;
                        case ".xml":
                        case ".sdxl":
                            mimeType = "application/xml";
                            break;
                        case ".xdp":
                            mimeType = "application/vnd.adobe.xdp
+xml";
                            break;
                        default:
                            mimeType = "application/octet-stream";
                            break;
                    }
                    dcmd.Parameters["@Filename"].Value =
FileUpload1.FileName;
                    dcmd.Parameters["@FileDesc"].Value =
FileDescTextBox.Text.ToString();
                    dcmd.Parameters["@Filesize"].Value =
FileUpload1.PostedFile.ContentLength;
                    dcmd.Parameters["@FileData"].Value =
FileUpload1.FileBytes;
                    dcmd.Parameters["@MIMEType"].Value = mimeType;
                    dcmd.Parameters["@FileType"].Value =
fileExtension.Substring(1, fileExtension.Length - 1);
                }

                dcmd.Parameters.Add(new SqlParameter("@eBy",
SqlDbType.VarChar));
                dcmd.Parameters["@eBy"].Value = uI.ToString();
                dcmd.Parameters.Add(new SqlParameter("@modBy",
SqlDbType.VarChar));
                dcmd.Parameters["@modBy"].Value = uI.ToString();
                dcmd.Parameters.Add(new SqlParameter("@CornerstoneID",
SqlDbType.Int));
                dcmd.Parameters["@CornerstoneID"].Direction =
ParameterDirection.Output;
                dcmd.Parameters.Add(new SqlParameter("@ErrorCode",
SqlDbType.Int));
                dcmd.Parameters["@ErrorCode"].Direction =
ParameterDirection.Output;
                dcmd.Parameters.Add(new SqlParameter("@ErrorMessage",
SqlDbType.VarChar, 250));
                dcmd.Parameters["@ErrorMessage"].Direction =
ParameterDirection.Output;

                dcmd.CommandTimeout = 60000;
                dcmd.Connection = dbConn;

                dcmd.ExecuteNonQuery();
                FileDescTextBox.Text = "";
            }
        }
        catch (Exception ex)
        {
            ReportError(ex.Message);
        }
        finally
        {
            if (dbConn != null)
            {
                dcmd.Dispose();
                dbConn.Close();
            }
        }

///////////////////////////////////////////////////////////////////////////­//////
SQL Trace:////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////­//////
declare @p18 int
set @p18=NULL
declare @p19 int
set @p19=NULL
declare @p20 varchar(250)
set @p20=NULL
exec sp_executesql N'Cornerstone_NEWPOST'
,N'@ApplicationID int
,@ApplicationGroupingID int
,@ImpactID int
,@TypeID tinyint
,@Header varchar(100)
,@CornerstoneBrickBody varchar(4)
,@HelpdeskTicketNo varchar(30)
,@Filename varchar(255)
,@FileDescription varchar(500)
,@Filesize varchar(50)
,@FileData varbinary(8000)
,@MIMEType varchar(50)
,@FileType varchar(5)
,@eBy varchar(14)
,@modBy varchar(14)
,@CornerstoneID int output
,@ErrorCode int output
,@ErrorMessage varchar(250) output'
,@ApplicationID=18
,@ApplicationGroupingID=16
,@ImpactID=5
,@TypeID=4
,@Header='Test'
,@CornerstoneBrickBody='Test'
,@HelpdeskTicketNo=''
,@Filename=NULL
,@FileDescription=NULL
,@Filesize=NULL
,@FileData=NULL
,@MIMEType=NULL
,@FileType=NULL
,@eBy='CALEXAN11\Dave'
,@modBy='CALEXAN11\Dave'
,@CornerstoneID=@p18 output
,@ErrorCode=@p19 output
,@ErrorMessage=@p20 output
select @p18, @p19, @p20

///////////////////////////////////////////////////////////////////////////­//////
SQL 2008 Stored Procedure////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////­//////
ALTER PROCEDURE [dbo].[Cornerstone_NEWPOST]
        @ApplicationID int
        ,@ApplicationGroupingID int
        ,@ImpactID int
        ,@TypeID tinyint
        ,@Header varchar(100)
        ,@CornerstoneBrickBody varchar(MAX)
        ,@HelpdeskTicketNo varchar(30)
        ,@FileName varchar(255)
        ,@FileDescription varchar(500)
        ,@FileSize varchar(50)
        ,@FileData varbinary(max)
        ,@MIMEType varchar(50)
        ,@FileType varchar(5)
        ,@eBy varchar(30)
        ,@modBy varchar(30)
        ,@CornerstoneID INT OUTPUT
        ,@ErrorCode INT OUTPUT
        ,@ErrorMessage VARCHAR(250) OUTPUT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
SET LOCK_TIMEOUT 5000

SET @ErrorCode = 0
SET @ErrorMessage = ''

BEGIN TRY
   BEGIN TRANSACTION
   INSERT INTO
      [Cornerstone].[dbo].[CornerstoneTable]
      (
       [ApplicationID]
      ,[ApplicationGroupingID]
      ,[ImpactID]
      ,[TypeID]
      ,[Header]
      ,[eBy]
      ,[eDate]
      ,[modBy]
      ,[modDate]
      )
   VALUES
      (
       @ApplicationID
      ,@ApplicationGroupingID
      ,@ImpactID
      ,@TypeID
      ,@Header
      ,@eBy
      ,GETDATE()
      ,@modBy
      ,GETDATE()
      )
   SET @CornerstoneID = @@IDENTITY
   INSERT INTO
        [Cornerstone].[dbo].[CornerstoneBrickTable]
          (
                [CornerstoneID]
                ,[SequenceID]
                ,[CornerstoneBrickBody]
                ,[HelpdeskTicketNo]
                ,[FileName]
                ,[FileDescription]
                ,[FileSize]
                ,[FileData]
                ,[MIMEType]
                ,[FileType]
                ,[eBy]
                ,[eDate]
                ,[modBy]
                ,[modDate]
      )
   VALUES
      (
                @CornerstoneID
                ,1
                ,@CornerstoneBrickBody
                ,@HelpdeskTicketNo
                ,@FileName
                ,@FileDescription
                ,@FileSize
                ,@FileData
                ,@MIMEType
                ,@FileType
                ,@eBy
                ,GETDATE()
                ,@modBy
                ,GETDATE()
      )
   COMMIT TRANSACTION
END TRY

BEGIN CATCH
--          exec ut_LogError(@eBy)   This procedure will log the
actual SQL error message
   SET @ErrorCode = 99
   SET @ErrorMessage = ERROR_MESSAGE()
   SET @CornerstoneID = -1
   ROLLBACK TRANSACTION
END CATCH
Click to expand...
 
Hanging my head low....

Missing:
dcmd.CommandType = CommandType.StoredProcedure;
Click to expand...

That one still gets me from time to time, especially if I have been
cleaning up a site with TableAdapters and have to go back to sprocs. :-)

BTW, psychologically, as soon as you post, you free your mind to find the
answer to the problem, as you feel you have others who will find the
answer. This is why you often get the aha moment shortly after posting.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top