I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.
Stored Procedure
ALTER Procedure [dbo].[WDR_CityAdd1]
(
@.CountryID int,
@.CityName nvarchar(50),
@.InternetAvail bit,
@.FlowersAvail bit,
@.CityID int OUTPUT
)
AS
IF EXISTS(SELECT 'True' FROM city WHERE CityName = @.CityName AND CountryID = @.CountryID)
BEGIN
SELECT
@.CityID = 0
END
ELSE
BEGIN
INSERT INTO City
(
CountryID,
CityName,
InternetAvail,
FlowersAvail
)
VALUES
(
@.CountryID,
@.CityName,
@.InternetAvail,
@.FlowersAvail
)
SELECT
@.CityID = 'CityID' ( I have also tried = @.@.Identity but that never returned anything it is an identity column 1,1)
END
Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.
Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterCityName As New SqlParameter("@.CityName", SqlDbType.NVarChar, 50)
parameterCityName.Value = CityName
myCommand.Parameters.Add(parameterCityName)
Dim parameterCityID As New SqlParameter("@.CityID", SqlDbType.Int, 4)
parameterCityID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCityID)
Try
dbConn.Open()
myCommand.ExecuteNonQuery()
dbConn.Close()
Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)
'AlertMessage = "City Added"
Catch ex As Exception
AlertMessage = ex.ToString
End Try
Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError
Thanks
Jerry
There are a couple of issues:
grbourque wrote:
...
SELECT @.CityID = 'CityID'
...
Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)
...
--> Conversion failed when converting the varchar value 'CityID' to data type int.
In this location,
SELECT @.CityID = 'CityID'
You are attempting to assign the @.CityID (an integer) the string value 'CityID'. That is what caused the error you reported.
I suggest using SCOPE_IDENTITY to capture the IDENTITY value of the last row entered by the current user/session.
SET @.CityID = SCOPE_IDENTITY()
Also, the RETURN value 'should' be an integer, and you are attempting to return a string value.
('Normally', one would use the RETURN value for success/failure reporting.)
Somewhere prior to the RETURN statement, you 'should' assign the parameterCityID value to a previously declared variable, then use that variable in your application.
|||Arnie
I had tried playing around with the 'cityID' fieldname and was getting different error messages. I have re-written the code with your help and it works. Sometimes this just amazes me. I do appreciate the help. Maybe with time I will be able to help others as I get a handle on this.
Jerry
No comments:
Post a Comment