p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   stored procedure dynamic insert (http://p2p.wrox.com/showthread.php?t=24149)

harpua January 19th, 2005 04:59 PM

stored procedure dynamic insert
 
Hello everybody, can anybody help me out, for the life of my I cannot get this to work or figure out why this thinks @iPrintBuyerID is a varchar, I have hard coded an int value in my input parameters. I am trying to create a dynamic Insert statement because I am have 3 tables but only the table name changes the fields are all the same, so if you have a better way of doing this I would love to know, as I cannot find anything on it anywhere I have even tried using CASE to do this, nothing seems to work.

Microsoft OLE DB Provider for SQL Server error '80040e07'
Syntax error converting the varchar value ' + @iPrintBuyerID + ' to a column of data type int.


CREATE PROCEDURE sp_ModifyGetBids_AddVendor
(
@strTable varchar(50),
@iPieceID int,
@iVendorID int,
@iRepID int,
@DateNotified datetime,
@iPrintBuyerID int,
@NewBidID int OUTPUT
)
AS
EXEC('INSERT INTO ' + @strTable +
' (Piece_ID_Bids, Vendor_ID_Bids, Rep_ID_Bids, Vendor_Notified, PrintBuyer_Bids) VALUES' +
' ('' + @iPieceID + '', '' + @iVendorID + '', '' + @iRepID + '', '' + @DateNotified + '', '' + @iPrintBuyerID + '')')
SELECT @NewBidID = @@IDENTITY


AAAAGGGGHH!!!!
Mike

Santhi January 20th, 2005 09:05 AM

Check whether iPrintBuyerID is declared as VarChar in the table.


harpua January 20th, 2005 10:52 AM

No it's not thats what is driving me crazy, no where It is considered to be a varchar.

.CreateParameter("@iPrintBuyerID", adInteger, adParamInput, , intAdminID)

Thanks
Mike

Santhi January 21st, 2005 12:50 AM

Then problem should be with your insert statement creation.Try with the below statement

'INSERT INTO ' + @strTable +
' (Piece_ID_Bids, Vendor_ID_Bids, Rep_ID_Bids, Vendor_Notified, PrintBuyer_Bids) VALUES
 (' + @iPieceID + '
, ' + @iVendorID + ', ' + @iRepID + ', ' + @DateNotified + ', ' + @iPrintBuyerID + '))'



All times are GMT -4. The time now is 09:49 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.