[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 654
  • Last Modified:

Datatype in MSSQL 2005

Hello,
I am a beginner developer and I am trying to create a product table in MS SQL 2005.
I have created several columns and I am really confused about datatype, because I am getting a lot of error message saying: "The precision is invalid."
And I guess I am getting that because I am not using datatype correctly.
These are the columns that I have created:

,      ProductName      char(50)
,      RetailPrice      money
,      SalePrice            money
,      Stock                  int
,      Size                  char(50)
,      Weight                  decimal(6,2)
,      Note                  nvarchar(100)
,      Description      nvarchar(4000)
,      Image1                  char(30)
,      Image2                  char(30)
,      Image3                  char(30)
,      VendorID            int
,      ReferenceURL      nvarchar(300)
,      BottomPrice      money
,      TopPrice            money
,      AdminNote            nvarchar(4000)
,      Viewed                  int
,      Hot                  bit
,      RegisterDate      datetime
,      ModifyDate            datetime
,      AdminID            int

First, I was trying to use Numeric(8,0) on Stock and Numeric(10,0) on Viewed, but I got many error messages by using stored precedure.
These are my questions
1.Can you tell me any recommended datatype from the above?
2. When do I use int ? Give me an example.
3. When do I use Numeric? Give me an example
4. When do I use Decimal? Give me an example
5. I am familiar with datatype, int, datatime,char, and nvarchar, but is there anything that I need to know about datatype money,numeric,and decimal? like command error that people make?

Thank you.

Thank you.
0
erin027
Asked:
erin027
  • 5
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Numeric (8,0) / decimal(8,0) means it cant store any decimal point, it works similar to an integer.
so if you want to store the some decimal points, say 3 decimal points, change it to  numeric(8,3)  or decimal (8,3) here the 8 is the total no of digits out of 3 are used for precision.

Now, when you use a char(30), no matter what the length of string you are going to store, sql will allocate the space for 30 characters, so it is always better to change to varchar if the string length varies .

regarding the usage oof mone, you can of coure replace it with numeric(10,3) if you dont want to store more than 3 decimal points

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
regarding the differences b/w Numeric and decimal, there is nothing;
0
 
erin027Author Commented:
Thank you aneeshattingal.
I unstood fully, but I am still getting an error message when I do Exec Stored Procedure
Error message:
"Procedure or function CreateNewProduct has too many arguments specified."
The things that I leave blank should insert NULL values.
Can you tell me what did I do wrong?
Thank you.

Does any of Datatype doesn't accept value NULL?

Create Proc CreateNewProduct (
      @CategoryID            int
,      @SubCategoryID      int
,      @ProductName      nvarchar(50)
,      @RetailPrice      money
,      @SalePrice            money
,      @Stock                  Numeric(8,0)
,      @Size                  nvarchar(50)
,      @Weight                  decimal(6,2)
,      @Note                  nvarchar(100)
,      @Description      nvarchar(4000)
,      @Image1                  nvarchar(30)
,      @Image2                  nvarchar(30)
,      @Image3                  nvarchar(30)
,      @VendorID            int
,      @ReferenceURL      nvarchar(300)
,      @BottomPrice      money
,      @TopPrice            money
,      @AdminNote            nvarchar(4000)
,      @Viewed                  Numeric(10,0)
,      @Hot                  bit
,      @RegisterDate      datetime
,      @ModifyDate            datetime
,      @AdminID            int
)
AS
Set Nocount On
IF @CategoryID = ''
      Set @CategoryID=null
IF @SubCategoryID = ''
      Set @SubCategoryID=null
IF @RetailPrice = ''
      Set @RetailPrice=null
If @SalePrice = ''
      Set @SalePrice=null
If @Stock = ''
      Set @Stock=''
If @Size = ''
      Set @Size=null
If @Weight =''
      Set @Weight=null
If @Note = ''
      Set @Note=null
If @Description = ''
      Set @Description=null
If @Image1 = ''
      Set @Image1=null
If @Image2 = ''
      Set @Image2=null
If @Image3 = ''
      Set @Image3=null
If @VendorID = ''
      Set @VendorID=null
If @ReferenceURL = ''
      Set @ReferenceURL=null
If @BottomPrice = ''
      Set @BottomPrice=null
If @TopPrice = ''
      Set @TopPrice=null
If @AdminNote = ''
      Set @AdminNote=null
If @Viewed = ''
      Set @Viewed=null
If @Hot = ''
      Set @Hot=null
If @RegisterDate = ''
      Set @RegisterDate=null
If @ModifyDate = ''
      Set @ModifyDate=null

Declare @ProductID int

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

SET @ProductID = SCOPE_IDENTITY()
Insert ProductCategory (ProductID,SubCategoryID,CategoryID)
Values (@ProductID,@SubCategoryID,@CategoryID)

Exec CreateNewProduct       
      '38'
,      '14'
,      'test'
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
,      ''
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
erin027Author Commented:
All of my column is Nullable.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
)
AS
Set Nocount On

Declare @ProductID int

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

SET @ProductID = SCOPE_IDENTITY()
Insert ProductCategory (ProductID,SubCategoryID,CategoryID)
Values (@ProductID,@SubCategoryID,@CategoryID)


GO


-- call sp like this , if the any of these column is null either you pass a null explicitly or use named params
Exec CreateNewProduct      
      '38'
,      '14'
,      'test'
0
 
erin027Author Commented:
You are the best! aneeshattingal!

It seems like everything is working except on ASP coding page.
I've tested it several times and tried different things but I am getting this error message:
"Microsoft OLE DB Provider for SQL Server error '80004005'
The precision is invalid. "
I do not know what seems to be to problem. It seems like you already know my level and my problem, can you look at my asp coding and tell me what should I correct?
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.
Pleae help me. THhnk you so Much!

I am just uploading the Command coding:
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
'Else
      .Parameters("@RetailPrice").Value = RetailPrice
end if

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

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

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

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

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

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

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

      .Parameters("@AdminNote").Value = AdminNote
      
if abc.item("viewed") = "" then
      .Parameters("@Viewed").Value = 1
else
      .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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I am not sure about the 'ASP' coding, may be some error while passing the decimal values. try passing integer values and check whether you are getting the same error
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Also you can make use of the SQL server profiler ,use the defualt trace. Run the profiler just before running that page and check what is coming in the profiler window.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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