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

__________________
Peace
Mike
http://www.eclecticpixel.com

Check whether iPrintBuyerID is declared as VarChar in the table.

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

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 + '))'