Click here to Skip to main content
15,940,550 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm wrestling with the correct parameter definition to use when attempting to store a file to a SQL server db using a stored procedure. The sproc needs to save the record and return the key/id to the caller. Sproc parameters look like this:

SQL
procedure sp_insert_attachment @attachment_id char(25) OUTPUT, @digital_image varbinary(max), @comments varchar(max),
           @file_name varchar(max), @file_size numeric(18,2), @parent_table varchar(50), @parent_id char(25), @import_date  datetime,
           @add_user  char(25), @add_datetime datetime

as

SET @attachment_id = CONVERT(CHAR(25), Substring(Replace(Newid(), '-', ''), 1, 25))

INSERT INTO [i_attachment]
           ([attachment_id]
           ,[digital_image]
           ,[comments]
           ,[file_name]
           ,[file_size]
           ,[parent_table]
           ,[parent_id]
           ,[import_date]
           ,[add_user]
           ,[add_datetime]
           ,[status])
     VALUES
           (@attachment_id,
           @digital_image,
		   @comments,
           @file_name,
		   @file_size,
		   @parent_table,
		   @parent_id,
		   @import_date,
           @add_user,
		   @add_datetime,
		   'A')


I've gone through several permutations of parameter types. Currently, this is what I have:

VB
set g_rst = Server.CreateObject("ADODB.Command")
g_rst.CommandText = "sp_insert_attachment"
g_rst.ActiveConnection = Mconn
g_rst.NamedParameters = true
g_rst.CommandType = adCmdStoredProc
g_rst.Parameters.Append(g_rst.CreateParameter("@attachment_id", SqlDbType.Char, adParamOutput))
g_rst.Parameters.Append(g_rst.CreateParameter("@digital_image", SqlDbType.VarBinary, adParamInput, objUpload.Form("FILE1").SaveAsBlob ))
g_rst.Parameters.Append(g_rst.CreateParameter("@comments", SqlDbType.VarChar, adParamInput, -1,  g_strNarrative))
g_rst.Parameters.Append(g_rst.CreateParameter("@file_name", SqlDbType.VarChar, adParamInput, -1,  g_strFileName))
g_rst.Parameters.Append(g_rst.CreateParameter("@file_size", SqlDbType.Decimal, adParamInput, g_dblFileSize))
g_rst.Parameters.Append(g_rst.CreateParameter("@parent_table", SqlDbType.VarChar, adParamInput, 50, "event"))
g_rst.Parameters.Append(g_rst.CreateParameter("@parent_id", SqlDbType.Char, adParamInput, 25, g_strEventID))
g_rst.Parameters.Append(g_rst.CreateParameter("@import_date", SqlDbType.DateTime2, adParamInput, DateTime.Parse(now())))
g_rst.Parameters.Append(g_rst.CreateParameter("@add_user", SqlDbType.Char, adParamInput, 25, session("sec_user_id")))
g_rst.Parameters.Append(g_rst.CreateParameter("@add_datetime", SqlDbType.DateTime2, adParamInput, DateTime.Parse(now())))

g_rst.Execute

'g_strAttachmentID = g_rst.Parameters("@attachment_id").Value

g_rst.Close


This is throwing a -2147217900 error. Any help is greatly appreciated.
Posted
Updated 23-Jul-14 5:16am
v4
Comments
CHill60 23-Jul-14 9:59am    
I've just focussed on the error rather than your question and have discovered that you either have an error in your Stored Procedure or do not have permission to update the database. What does the rest of your stored procedure contain?
littleGreenDude 23-Jul-14 11:15am    
I've added the body of the sproc above.
CHill60 23-Jul-14 11:21am    
Try adding declare @attachment_id char(25) after the as in your SP
littleGreenDude 23-Jul-14 11:23am    
it is already declared in the parameter list... after the 'as' produces

The variable name '@attachment_id' has already been declared. Variable names must be unique within a query batch or stored procedure.
littleGreenDude 23-Jul-14 11:24am    
Sproc runs when executed from t-sql

1 solution

I use this reference when mapping database datatypes to .net http://msdn.microsoft.com/en-us/library/4e5xt97a(v=vs.110).aspx[^]
 
Share this answer
 
Comments
littleGreenDude 23-Jul-14 9:57am    
Thank you. I've reviewed and updated the parameters based on your link, but I am still getting the error. Please do a quick review and let me know if you agree with the parameter choices. Any other ideas?
CHill60 23-Jul-14 10:30am    
I think the error might be in the SP - what is the code in there?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900