INSERT NULL ? Stored procedure Insert Problem.HELP

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)

INSERT NULL ? Stored procedure Insert Problem.HELP

Author

Topic

Ayeidea
Starting Member

17 Posts

Posted - 2001-10-25 : 03:25:39

Hi all. ok heres the deal. I have a form that i need to insert the fields into a stored procedure.Some of the form fields may be blank when submiting, but i want to use the same stored procedure. can i use the same procedure or how can i get around sometimes having a blank field that is trying to send to the stored procedure. here is the error i get:

quote:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to int.

/admin/edit/main/product.color.asp, line 149

now line 149 is the Append line for a field that i happen to be leaving blank.(the city field) you can see it below.
here is the stored procedures asp code:

quote:
set InsertProduct_Sp = Server.CreateObject("ADODB.Command")
InsertProduct_Sp.ActiveConnection = MM_onlineSQL_STRING
InsertProduct_Sp.CommandText = "akolade.SP_InsertProduct"
InsertProduct_Sp.CommandType = adCmdStoredProc
InsertProduct_Sp.CommandTimeout = 0
InsertProduct_Sp.Prepared = true
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("RETURN_VALUE",adInteger, adParamReturnValue, 4)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@LeagueID", adInteger, adParamInput,4,InsertProduct_Sp__LeagueID)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@CityStID", adInteger, adParamInput,4,InsertProduct_Sp__City)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@TeamID", adInteger, adParamInput,4,InsertProduct_Sp__team)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Product_Type", adVarChar, adParamInput,30,InsertProduct_Sp__Product_Type)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Player_no ", adVarChar, adParamInput,50,InsertProduct_Sp__player)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Brand", adVarChar, adParamInput,30,InsertProduct_Sp__Brand)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Product_Name", adVarChar,adParamInput,30,InsertProduct_Sp__Product_Name)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Product_Description", adVarChar, adParamInput,255,InsertProduct_Sp__Product_Description)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Picture_ID", adVarChar, adParamInput,30,InsertProduct_Sp__Picture_ID)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Picture_ID_T", adVarChar, adParamInput,30,InsertProduct_Sp__Picture_ID_T)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Price", adCurrency, adParamInput,4,InsertProduct_Sp__Product_Price)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Weight", adInteger, adParamInput,4,InsertProduct_Sp__Product_Weight)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@Quanity", adInteger, adParamInput,4,InsertProduct_Sp__Product_Price)
InsertProduct_Sp.Parameters.Append InsertProduct_Sp.CreateParameter("@theID",adInteger, adParamOutput, 4)
Set rsNewProdID = InsertProduct_Sp.Execute

and finaly heres the stored procedure code.

quote:

CREATE PROCEDURE [SP_InsertProduct]
( @LeagueID [int],
@CityStID [int],
@TeamID [int],
@Product_Type [varchar](255),
@Player_no [varchar](50),
@Brand [varchar](255),
@Product_Name [varchar](255),
@Product_Description [varchar](255),
@Picture_ID [varchar](255),
@Picture_ID_T [varchar](255),

@Price [money],
@Weight [int],
@Quanity [int],

@theID [int] OUTPUT

)

AS
Declare @newID int

SET NOCOUNT ON
INSERT INTO [akolade].[akolade].[products]
( [LeagueID],
[CityStID],
[TeamID],
[Product_Type],
[Player_no],
[Brand],
[Product_Name],
[Product_Description],
[Picture_ID],
[Picture_ID_T])

VALUES
( @LeagueID,
@CityStID,
@TeamID,
@Product_Type,
@Player_no,
@Brand,
@Product_Name,
@Product_Description,
@Picture_ID,
@Picture_ID_T)

SELECT @newid = @@IDENTITY

INSERT INTO [akolade].[akolade].[Prod_Supply]
( [ProdID],
[Quanity],
[Price],
[Weight]
)

VALUES
( @newid,
@Quanity,
@Price,
@Weight )

SELECT @newid as theid

SET NOCOUNT OFF

if anyone can help or point me in the right direction. i would greatly appreciate it! thanks
john

Edited by - AYEIDEA on 10/25/2001 03:38:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources