Using Stored Procedure and trying to use it with ASP

Posted on 2007-10-14
Last Modified: 2012-05-05
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
Question by:erin027
    LVL 14

    Expert Comment

    can you telll what the error message is ?

    Author Comment

    Microsoft OLE DB Provider for SQL Server error '80004005'

    The precision is invalid.
    LVL 14

    Expert Comment

    .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)

    Author Comment

    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... ^^;;;;

    Author Comment

    I tried it but it still gives me the same error message.
    LVL 14

    Expert Comment

    is it possible that you pass EMPTY STRING("") or 0 instead of NULL values...does your business accpet it ?
    LVL 14

    Accepted Solution

    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
    LVL 5

    Assisted Solution

    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.

    Author Comment

    I've got it.
    I was using different Stored Procedure Name on ASP coding. Sorry, my mistake.
    Thanks guys

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL Query 18 67
    report c# 9 60
    Delete from table 6 27
    Can a SQL Server database be recovered from a non-sql aware server image? 6 14
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now