Problem with Null parameters and Store Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with Null parameters and Store Procedure

Author

Topic

dnagle
Starting Member

1 Post

Posted - 2002-02-06 : 15:31:46

ASP Code:

Set updateCmd = Server.CreateObject("ADODB.Command")
CountryCode = cInt(trim(Request.Form("CountryCode")))
CountryYear = cInt(trim(Request.Form("Year")))

With updateCmd
.ActiveConnection = conn
.CommandText = "cbUPDATE_CountryYearData"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("CountryCode",adSmallInt,adParamInput,,CountryCode)
.Parameters.Append .CreateParameter("CountryYear",adInteger,adParamInput,,CountryYear)
if len(Request.Form("Area")) > 0 then
LandArea = cInt(trim(Request.Form("Area")))
.Parameters.Append .CreateParameter("CountryArea",adInteger,adParamInput,,LandArea)
end if
if len(Request.Form("Population")) > 0 then
Population = cInt(trim(Request.Form("Population")))
.Parameters.Append .CreateParameter("CountryPopulation",adDouble,adParamInput,,Population)
end if
.Parameters.Append .CreateParameter("ModifyBy",adChar,adParamInput,Len(strUser),strUser)
.Execute
End With

Stored Procedure code:

CREATE PROCEDURE dbo.cbUPDATE_CountryYearData
(
@CountryCode smallint,
@CountryYear int,
@CountryArea int = NULL,
@CountryPopulation float = NULL,
@ModifyBy varchar(30) )
AS

BEGIN
UPDATE Comp_Country_Data
SET Country_Area = @CountryArea,
Country_Population = @CountryPopulation,
Modify_By = @ModifyBy,
Modify_Date = getDate()
WHERE Country_Code = @CountryCode
AND Country_Year = @CountryYear
END
GO

Error Message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type char to float.

/tools/test_area/action_update_country.asp, line 208

Judging by the error message it doesn't like the Population value when a value isn't passed to the procedure. How can I get around this proble?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-06 : 15:38:55

2 things:

1. Population = cInt(trim(Request.Form("Population")))

You convert it to an int here, but it's passed to SQL server as a double. Try CDbl() instead.

2. Try this instead:

if len(Request.Form("Population")) > 0 then
 Population = cInt(trim(Request.Form("Population")))
Else 
 Population = vbNull
End If
.Parameters.Append .CreateParameter("CountryPopulation",adDouble,adParamInput,,Population)

This will always pass a value for population, even if it is an explicit Null. If the vbNull constant doesn't work, you may need to create another default value and catch it in the stored procedure (like -1.0, for example).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources