[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

Using Stored Procedure and trying to use it with ASP

Experts, please help me.
I am a beginner of MSSql and ASP.
I do not know what seems to be to problem.
I used to created several Procedure and used it in ASP coding and it worked well, but I am getting an error message on this one and I think it has to do with  using Numeric, Decimal and Money Data type. It's the first time I am using these datatype. I have tested Procedure in MS SQL and it worked well, but there is an error when I use ASP.
Pleae help me. THhnk you so Much!

----------------------------------------------Stored Procedure-------------------
Create Proc CreateNewProduct (
      @CategoryID            INT = null
,      @SubCategoryID      INT = null
,      @ProductName      nvarchar(50)=null
,      @RetailPrice      MONEY=null
,      @SalePrice            MONEY = null
,      @Stock                  Numeric(8,0) =null
,      @Size                  nvarchar(50) = null
,      @Weight                  decimal(6,2)= null
,      @Note                  nvarchar(100)=null
,      @Description      nvarchar(4000) =NULL
,      @Image1                  nvarchar(30) = null
,      @Image2                  nvarchar(30)= null
,      @Image3                  nvarchar(30)= null
,      @VendorID            INT = null
,      @ReferenceURL      nvarchar(300) = null
,      @BottomPrice      MONEY = NULL
,      @TopPrice            MONEY = NULL
,      @AdminNote            nvarchar(4000) = NULL
,      @Viewed                  Numeric(10,0)= NULL
,      @Hot                  BIT = null
,      @RegisterDate      DATETIME = null
,      @ModifyDate            DATETIME =null
,      @AdminID            INT=null
Set Nocount On

Declare @ProductID int

Insert Product (
,      RetailPrice
,      SalePrice
,      Stock
,      Size
,      Weight
,      Note
,      Description
,      Image1
,      Image2
,      Image3
,      VendorID
,      ReferenceURL
,      BottomPrice
,      TopPrice
,      AdminNote
,      Viewed
,      Hot
,      RegisterDate
,      ModifyDate
,      AdminID
Values (
,      @RetailPrice
,      @SalePrice
,      @Stock
,      @Size
,      @Weight
,      @Note
,      @Description
,      @Image1
,      @Image2
,      @Image3
,      @VendorID
,      @ReferenceURL
,      @BottomPrice
,      @TopPrice
,      @AdminNote
,      @Viewed
,      @Hot
,      @RegisterDate
,      @ModifyDate
,      @AdminID

Insert ProductCategory (ProductID,SubCategoryID,CategoryID)
Values (@ProductID,@SubCategoryID,@CategoryID)


----------------------------------ASP Coding (I just uploaded the Parameter code)----------
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
      With cmd
    .ActiveConnection = db
    .Commandtext = "CreateNewCategory"
    .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@CategoryID", adInteger,adParamInput)
      .Parameters.Append .CreateParameter("@SubCategoryID", adInteger,adParamInput)
      .Parameters.Append .CreateParameter("@ProductName", advarchar,adParamInput,50)
      .Parameters.Append .CreateParameter("@RetailPrice", adCurrency,adParamInput)
      .Parameters.Append .CreateParameter("@SalePrice", adCurrency,adParamInput)
      .Parameters.Append .CreateParameter("@Stock", adNumeric,adParamInput,8,0)
      .Parameters.Append .CreateParameter("@Size", advarchar,adParamInput,50)
      .Parameters.Append .CreateParameter("@Weight", adDecimal,adParamInput,6,2)
      .Parameters.Append .CreateParameter("@Note", advarchar,adParamInput,100)
      .Parameters.Append .CreateParameter("@Description", advarchar,adParamInput,4000)
      .Parameters.Append .CreateParameter("@Image1", advarchar,adParamInput,30)
      .Parameters.Append .CreateParameter("@Image2", advarchar,adParamInput,30)
      .Parameters.Append .CreateParameter("@Image3", advarchar,adParamInput,30)
      .Parameters.Append .CreateParameter("@VendorID", adInteger,adParamInput)
      .Parameters.Append .CreateParameter("@ReferenceURL", advarchar,adParamInput,300)
      .Parameters.Append .CreateParameter("@BottomPrice", adCurrency,adParamInput)
      .Parameters.Append .CreateParameter("@TopPrice", adCurrency,adParamInput,30)
      .Parameters.Append .CreateParameter("@AdminNote", advarchar,adParamInput,4000)
      .Parameters.Append .CreateParameter("@Viewed", adNumeric,adParamInput,10,0)
      .Parameters.Append .CreateParameter("@Hot", adInteger,adParamInput)
      .Parameters.Append .CreateParameter("@RegisterDate", adDate,adParamInput)
      .Parameters.Append .CreateParameter("@ModifyDate", adDate,adParamInput)
      .Parameters.Append .CreateParameter("@AdminID", adInteger,adParamInput)

    .Parameters("@CategoryID").Value = CategoryID
      .Parameters("@SubCategoryID").Value = SubCategoryID
      .Parameters("@ProductName").Value = ProductName
'if abc.item("retailprice") = "" then
'      .Parameters("@RetailPrice").Value = Null
      .Parameters("@RetailPrice").Value = RetailPrice
end if

if abc.item("saleprice") = "" then
      .Parameters("@SalePrice").Value = Null
      .Parameters("@SalePrice").Value = SalePrice
end if

if abc.item("stock") = "" then
      .Parameters("@Stock").Value = Null
      .Parameters("@Stock").Value = Stock
end if
      .Parameters("@Size").Value = Size
if abc.item("@weight") = "" then
      .Parameters("@Weight").Value = Null
      .Parameters("@Weight").Value = Weight
end if

      .Parameters("@Note").Value = Note
      .Parameters("@Description").Value = Description
If abc.item("Image1") <> "" then
      .Parameters("@Image1").Value = strFileName1
      .Parameters("@Image1").Value = Null
End if

If abc.item("Image2") <> "" then
      .Parameters("@Image2").Value = strFileName2
      .Parameters("@Image2").Value = Null
End if

If abc.item("Image3") <> "" then
      .Parameters("@Image3").Value = strFileName3
      .Parameters("@Image3").Value = Null
End if

      .Parameters("@VendorID").Value = VendorID
      .Parameters("@ReferenceURL").Value = ReferenceURL
if abc.item("bottomprice") = "" then
      .Parameters("@BottomPrice").Value = null
      .Parameters("@BottomPrice").Value = BottomPrice
end if

if abc.item("topprice") = "" then
      .Parameters("@TopPrice").Value = Null
      .Parameters("@BottomPrice").Value = TopPrice
end if

      .Parameters("@AdminNote").Value = AdminNote
if abc.item("viewed") = "" then
      .Parameters("@Viewed").Value = 1
      .Parameters("@Viewed").Value = Viewed
end if

      .Parameters("@Hot").Value = Hot
      .Parameters("@RegisterDate").Value = RegisterDate
      .Parameters("@ModifyDate").Value = ModifyDate
      .Parameters("@AdminID").Value = AdminID

    .Execute , , adExecuteNoRecords
End with

Set cmd = Nothing
  • 4
  • 4
2 Solutions
Jai STech ArchCommented:
can you telll what the error message is ?
erin027Author Commented:
Microsoft OLE DB Provider for SQL Server error '80004005'

The precision is invalid.
Jai STech ArchCommented:
.Parameters.Append .CreateParameter("@Viewed", adNumeric,adParamInput,10,0)
.Parameters.Append .CreateParameter("@Stock", adNumeric,adParamInput,8,0)

can you change this to just
.Parameters.Append .CreateParameter("@Viewed", adNumeric,adParamInput)
.Parameters.Append .CreateParameter("@Stock", adNumeric,adParamInput)
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

erin027Author Commented:
It seems like the datatype Numeric, Decimal, and Money is causing the problem...
When I submit the form with few empty values except the ones that doesn't accept Null, it give me that error message. I have tried several different things but I am at a dead end.
I was tying to solve this problem for 2 days... ^^;;;;
erin027Author Commented:
I tried it but it still gives me the same error message.
Jai STech ArchCommented:
is it possible that you pass EMPTY STRING("") or 0 instead of NULL values...does your business accpet it ?
Jai STech ArchCommented:
and one more thing is that ...since you already have a DEFAULT NULL value in your SP...i think that you dont need to manuall set your fields to NULL
just change the checking from this
If abc.item("Image1") <> "" then
      .Parameters("@Image1").Value = strFileName1
      .Parameters("@Image1").Value = Null
End if
If abc.item("Image1") <> "" then .Parameters("@Image1").Value = strFileName1
Have you checked the values you assign to the parameters? Maybe one of the values you assign to the parameters is not a number. Plus, I am not sure if that is a problem but it seems that the values you get out of abc.item("<name>") are string values. I usually try to match the variable type I pass to the variable type of the parameter which means you could try and convert the value to a numeric variable if you assign a value to a parameter that expects a numeric value.
erin027Author Commented:
I've got it.
I was using different Stored Procedure Name on ASP coding. Sorry, my mistake.
Thanks guys

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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