I have a SP .......

Results 1 to 5 of 5

Thread: I have a SP .......

  1. #1
    Join Date
    Dec 1969

    Default I have a SP .......

    I have written a stored procedure to update tickets. I also wrote if then statement incase a field was not updated.<BR>if strRealCompDate = "" Or IsNull(strRealCompDate) Then<BR> strRealCompDate = Null<BR>end if <BR> <BR>The error I get is still expecting empty field to return something.<BR>The error message I get is this:<BR>Microsoft OLE DB Provider for SQL Server (0x80040E10)<BR>Procedure &#039;ticketUpdate_SP&#039; expects parameter &#039;@RealCompDate&#039;, which was not supplied.<BR>I understand what the error is asking for but I thought I had covered the issue if the variable was returned unchanged. All help and advice is truly appreciated.<BR>Thank you.<BR><BR>

  2. #2
    Steve Cimino Guest

    Default RE: I have a SP .......

    No such thing as OPTIONAL in SQL. You could fake it:<BR><BR>CREATE PROCEDURE myProcedure<BR>@myname varchar (10)<BR>AS<BR>IF (@myname = "")<BR>BEGIN<BR>--nothing here, do whatever<BR>END<BR>ELSE<BR>BEGIN<BR>--ya got a value<BR>END<BR><BR><BR>Either way, you ALWAYS have to pass it the parameters it is expecting.

  3. #3
    Yike Guest

    Default RE: I have a SP .......

    I understand what you are saying but the odd thing is that, I did this for another field and it accepted an a parameter that was not updated by using the if null type statement.<BR><BR>So in the actual stored procedure I need to make the condition as well?<BR>Thanks again.

  4. #4
    Steve Cimino Guest

    Default RE: I have a SP .......

    Paste the code where you&#039;re calling the SP. If you have access to the SP, post that also.<BR><BR>Gotta help out fellow ticket sellers ;)

  5. #5
    Yike Guest

    Default Thanks Steve here is the code and SP(I wrote)

    Here is the Stored Proc:<BR><BR>CREATE PROCEDURE ticketUpdate_SP <BR><BR>@TicketID int,<BR>@Description text,<BR>@CustomerID int,<BR>@ReqCompDate smalldatetime,<BR>@RealCompDate smalldatetime,<BR>@StatusID int,<BR>@Assigned_By int,<BR>@Assigned_To int,<BR>@Assigned_DateTime smalldatetime,<BR>@PriorityID int,<BR>@Close_DateTime smalldatetime<BR><BR><BR>WITH RECOMPILE<BR>AS<BR><BR>UPDATE Tickets<BR> SET Description = @Description,<BR> CustomerID = @CustomerID,<BR> ReqCompDate = @ReqCompDate,<BR> RealCompDate = @RealCompDate,<BR> StatusID = @StatusID,<BR> Assigned_By = @Assigned_By,<BR> Assigned_To = @Assigned_To,<BR> Assigned_DateTime = @Assigned_DateTime,<BR> PriorityID = @PriorityID,<BR> Close_DateTime = @Close_Datetime <BR> where TicketID = @TicketID<BR><BR>Here is the exe page:<BR><BR>&#060;%@Language=VBScript%&#062;<BR>& #060;!-- #include virtual="/PMIS/adovbs.inc" --&#062;<BR>&#060;%<BR>&#039;On Error Resume Next<BR><BR> strDescription = Request("txtDescription")<BR> strCustomerID = Request("txtCustomerID")<BR> strReqCompDate = Request("txtReqCompDate")<BR> strRealCompDate = Request("txtRealCompDate")<BR> strStatusID = Request("txtStatusID")<BR> strAssigned_By = Request("txtAssigned_By")<BR> strAssigned_To = Request("txtAssigned_To")<BR> strAssigned_DateTime = Request("txtAssigned_DateTime")<BR> strPriorityID = Request("txtPriorityID")<BR> strClose_DateTime = Request("txtClose_DateTime")<BR> <BR><BR>if strReqCompDate = "" Or IsNull(strReqCompDate) Then<BR> strReqCompDate = Null<BR>end if <BR><BR>if strRealCompDate = "" Or IsNull(strRealCompDate) Then<BR> strRealCompDate = Null<BR>end if<BR><BR>Set Con = Server.CreateObject("ADODB.Connection")<BR>Con.Ope n application("pmisconn")<BR><BR>Set rcd = Server.CreateObject("ADODB.Command")<BR>rcd.Active Connection = Con<BR>rcd.CommandType = adCmdStoredProc<BR>rcd.CommandText = "ticketUpdate_SP"<BR>rcd.Parameters.Append rcd.CreateParameter("TicketID", adInteger, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("Description", adLongVarChar, adParamInput,16000)<BR>rcd.Parameters.Append rcd.CreateParameter("CustomerID", adInteger, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("ReqCompDate", adDate, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("RealCompDate", adDate, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("StatusID", adInteger, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("Assigned_By", adInteger, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("Assigned_To", adInteger, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("Assigned_DateTime", adDBTimeStamp, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("PriorityID", adInteger, adParamInput)<BR>rcd.Parameters.Append rcd.CreateParameter("Close_DateTime", adDBTimeStamp, adParamInput)<BR><BR>rcd.Parameters("TicketID") = Request.Form("TicketID")<BR>rcd.Parameters("Descri ption")=strDescription<BR>rcd.Parameters("Customer ID")=strCustomerID<BR>rcd.Parameters("ReqCompDate" )=strReqCompDate<BR>rcd.Parameters("RealCompDate") =trRealCompDate<BR>rcd.Parameters("StatusID")=strS tatusID<BR>rcd.Parameters("Assigned_By")=strAssign ed_By<BR>rcd.Parameters("Assigned_To")=strAssigned _To<BR>rcd.Parameters("Assigned_DateTime")=strAssi gned_DateTime<BR>rcd.Parameters("PriorityID")=strP riorityID<BR>rcd.Parameters("Close_DateTime")=strC lose_DateTime<BR><BR><BR>rcd.Execute()<BR>set rcd = Nothing<BR>Con.Close<BR>Set Con = Nothing<BR><BR>&#039;Response.Redirect("index.asp" )<BR>%&#062;<BR>You are the greatest. Thank you so much!!!!<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts