VBScript/ADO - Loading file contents into table via stored procedure

Question

VBScript/ADO - Loading file contents into table via stored procedure

mdenner

SSC Eights!

Points: 833
More actions
January 19, 2005 at 12:01 pm

#86596

I have a table that looks something like:
CREATE TABLE Files (
FileName VarChar(128),
ErrorLog NText
)
I have a stored procedure that looks something like:
CREATE PROCEDURE proc_AddFile
@FileName VarChar(128),
@ErrorLog NText = NULL
AS
INSERT INTO Files(FileName, ErrorLog) VALUES(@FileName, @ErrorLog)
GO
I have a VBScript file that uses something like this procedure to load the text of files:
Dim objConn, objCommand, objStream, strFileName
Set objCmd = CreateObject("ADODB.Command")
Set objConn = CreateObject("ADODB.Connection")
Set objStream = CreateObject("ADODB.Stream")
'code to connect to SQL and set objCmd = proc_AddFile is not included
objStream.Open
objStream.LoadFromFile strFileName
objCmd.Parameters("@FileName") = strFileName
objCmd.Parameters("@ErrorLog") = objStream.Read
objCmd.Execute()
The use of the ADO Stream object and using objStream.Read as an input into the stored procedure is based on some example code I've culled from the Web. However, I get an error on the Execute line: ADODB.Stream: Operation is not allowed in this context. If I try to just hand the parameter objStream, without specifying a method or property, I get:ADODB.Command: Arguments are of the wrong type, are not of acceptable range, or are in conflict with one another.
I've looked in a number of sources as well as the ADO documentation and can't find any clear examples of how to pass the contents of a file to a stored procedure. Can anyone help? I don't think it's related to Unicode vs. ANSI (i.e., NText vs. Text).
Thanks,
Mark Denner

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

mike gercevich SSCrazy Points: 2961 More actions · Answer 1

I'm fairly certain that if you modify your ADO call to the following, the insert will complete as expected. -Mike Gercevich

With objCmd

.ActiveConnection = objComm

.CommandText = "proc_AddFile"

.CommandType = adCmdText OR adExecuteNoRecords

.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,0)

.Parameters.Append .CreateParameter("@Filename", adVarChar, adParamInput, 128, strFileName)

.Parameters.Append .CreateParameter("@ErrorLog", adVarWChar, adParamInput, 1073741823, objStream.Read)

.Execute()

End With

If objCmd("@RETURN_VALUE").Value = 0 Then

MsgBox "Insert Successful!"

Else

MsgBox "Insert Failed!"

End If

Set objCmd.ActiveConnection = Nothing

Set objCmd = Nothing

If NOT objComm.State = 0 Then

Set objComm.Close

End If

SET objComm = Nothing

mdenner SSC Eights! Points: 833 More actions · Answer 2

Mike-

Thanks very much for the feedback. Your code is a lot cleaner than mine but I think I discovered the source of the problem after doing a little more googling. The stream has to be handed to the stored procedure as:

objStream.ReadText

This satisfies the ADO type checking and whatever else was causing the errors I was getting yesterady.

I'm lazy in my programming and usually just use .Parameters.Refresh and then pass the procedure arguments as in my original code, rather than specifying the type and length in .CreateParameter method calls. I think I've read that calling .Parameters.Refresh is less efficient (probably more client/server communications) than using .CreateParameter, but in this case I'm not overly concerned about performance. That and I find the need to translate between VB types and SQL types with different type names (i.e., adVarWChar instead of NVarChar) and to specify lengths to be very tedious and error prone.

Thanks again for the excellent feedback.

Mark