Problem passing decimal value to stored procedure 1

[YAmoah]

I'm getting the following error when I attempt to pass a parameter (via an ASP page) with a decimal data type to a SQL Server 2000 stored procedure:

Microsoft OLE DB Provider for SQL Server error '80004005'

The precision is invalid.


Here's my ASP code:

set cmd = server.CreateObject ("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "sp_Name"
cmd.CommandType = 4
cmd.Parameters.Append(cmd.CreateParameter ("LATITUDE",adDecimal,adParamInput,,Cdbl(vLatitude)))
cmd.Parameters.Append(cmd.CreateParameter ("LONGITUDE",adDecimal,adParamInput,,Cdbl(vLongitude)))


I've also tried:

cmd.Parameters.Append(cmd.CreateParameter ("LATITUDE",adDecimal,adParamInput,(8,4),Cdbl(vLatitude)))
cmd.Parameters.Append(cmd.CreateParameter ("LONGITUDE",adDecimal,adParamInput,(8,4),Cdbl(vLongitude)))


Here is my stored procedure code:

CREATE PROCEDURE [DBO].[sp_Name]
@LATITUDE DECIMAL(8,4),
@LONGITUDE DECIMAL(8,4)

AS

SELECT *
FROM TABLE
WHERE LONGITUDE = @LONGITUDE
AND LATITUDE = @LATITUDE


In the table:

LATITUDE DECIMAL(8,4) LENGTH = 5
LONGITUDE DECIMAL (8,4) LENGTH = 5


Has anyone encountered this problem?

 

[SQLBill]

What does the value of the input look like? Your Latitude and Longitude will only accept the format 1234.5678

-SQLBill

Posting advice: FAQ481-4875

 

[YAmoah]

The values being passed are:

Latitude = 38.8146
Longitude = 77.6276

 

[gmmastros]

The problem here, is that the CreateParameter function does not support decimals because decimals have a precision and a scale. The CreateParameter function doesn't support that.

The good news, is that I know how to 'get it to work'. This isn't really a SQL Server issue. It's an ADO issue. I'm not familiar with ASP, but the same issue applies with VB. That being said, here is some VB code (that you can convert to asp).

    Dim oCMD As ADODB.Command
    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim oParam1 As ADODB.Parameter
    Dim oParam2 As ADODB.Parameter
    
    Set DB = CreateObject("ADODB.Connection")
    DB.ConnectionString = g_strConn
    DB.CursorLocation = adUseClient
    DB.CommandTimeout = 0
    DB.Open
    
    Set oParam1 = CreateObject("ADODB.Parameter")
    oParam1.Name = "@Longitude"
    oParam1.Direction = adParamInput
    oParam1.Type = adDecimal
    oParam1.Precision = 8
    oParam1.NumericScale = 4
    oParam1.Value = [!]Longitude Value Here[/!]

    Set oParam2 = CreateObject("ADODB.Parameter")
    oParam2.Name = "@Latitude"
    oParam2.Direction = adParamInput
    oParam2.Type = adDecimal
    oParam2.Precision = 8
    oParam2.NumericScale = 4
    oParam2.Value = [!]Latitude Value Here[/!]
    
    Set oCMD = New ADODB.Command
    oCMD.CommandType = adCmdStoredProc
    oCMD.ActiveConnection = DB
    oCMD.Parameters.Append oParam1
    oCMD.Parameters.Append oParam2
    oCMD.CommandText = "[!]SP NAME HERE[/!]"
     
    Set RS = CreateObject("ADODB.Recordset")
    
    Set RS.ActiveConnection = DB
    RS.CursorLocation = adUseClient
    Call RS.Open(oCMD, , adOpenStatic, adLockBatchOptimistic)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[YAmoah]

Thank you for your help. The ASP code is working now. However, I've encountered another problem. It seems that SQL Server does not recognize @LATITUDE AND @LONGITUDE as decimals, even though I'm declaring them as decimals. I'm getting the following error:

Error converting data type varchar to numeric

Even when I try to only display the value:

PRINT 'TEST = ' + @LATITUDE

It's still giving me the error.

Am I missing something?

 

[vongrunt]

That 'even' causes an error - string + number in T-SQL does not produce another string.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLBill]

You must convert the decimal values to varchar or char values.

It's trying to ADD TEST= to the value of @Latitude. You don't want to ADD, you want to CONCATENATE.

-SQLBill

Posting advice: FAQ481-4875