Running Stored Procedure in a Loop

[Jerry]

I'm trying to execute a stored procedure in a loop while paging through
database table records but the stored procedure isn't running. I get
the folowing error: The component 'adodb.connection' cannot be created.
Apartment threaded components can only be created on pages with an <%@
Page aspcompat=true %> page directive.

Can anyone tell me what I'm doing wrong? Below is my code.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
'Create a connection string
Dim connString As String
Dim strID As String
Dim strSP As String
connString = "Provider=SQLOLEDB;Data Source=OET-JBEACH;Initial
Catalog=TEST;User Id=test;Password=test;"

'Open a connection
Dim objConnection As OleDbConnection
objConnection = New OleDbConnection(connString)
objConnection.Open()

Dim strSQL As String = "SELECT Blob_ID, Blob_Date FROM
dbo.tblBlob WHERE Blob_PhotoYesNo=33 ORDER BY Blob_Date desc"

'Create a command object
Dim objCommand As OleDbCommand
objCommand = New OleDbCommand(strSQL, objConnection)

'Get a datareader
Dim objDataReader As OleDbDataReader
objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)

'run stored procedure for each record in the table
While objDataReader.Read()
'set file name
strID = objDataReader("Blob_ID") & "_" &
Right(objDataReader("Blob_Date"), 4) & ".jpg"
'set stored procedure execute line
strSP = "ntext2file, 'c:\blob\" & strID & "', 'tblBlob',
'Blob_Photo', 'where Blob_ID=" & objDataReader("Blob_ID") & "'"

' ***** the response.write part works. the rest doesn't

Response.Write(strSP & "<br>")

Dim c
c = Server.CreateObject("adodb.connection")
c.Open(Application("connString"))
c.Execute(strSP)
c.Close()
c = Nothing
End While

'Close the datareader/db connection
objDataReader.Close()
End Sub
</script>

 

[jason]

Why are you trying to call the SP through an ADODB (COM) object? Why
not use an OleDbCommand? In fact, if you're using Sql Server (which I'm
guessing you are), why not ditch the OleDbConnection/Command for a
SqlConnection/Command, which are optimized for Sql Server...

'***** snip
Dim connection As SqlConnection
Dim command As SqlCommand

connection.Open( Application( "connString" ) )
command = new SqlCommand()
command.CommandType = CommandType.StoreProcedure
command.CommandText = "ntext2file"

' add the parameters...
etc...

'**** end snip...

Sorry, gotta run or I'd finish that code, but it should get you on the
right track...

 

[Jerry]

Thanks Jason,

I'm very new at .Net. thanks for the start

 

[Jeff Dillon]

What exactly DOES the response.write show?

It looks like "ntext2file, 'c..."

Which would put a comma right before the first parameter, which is invalid
syntax.

And you should always try to avoid putting a sproc call in a loop like that.
You can certainly do what you want to do in a single call even if you have
to use SQL cursors, but even then, there are ways around that, too

Jef

Jeff

 

[Jerry]

I caught that extra comma after I posted. I removed it and tried again
with no luck. The resuts of the response.write are:
ntext2file 'c:\blob\999_2006', 'tblBlob', 'Blob_Photo', 'where Blob_ID
= 999'

I'm exporting images from an ntext field to jpg files. There are over
200 of them so I'm trying to automate it in the loop.

 

[Jeff Dillon]

You are calling a sproc 200 times in a loop??? Wrong! Like I said, do it in
ONE call. If you need help to understand this, you're in the right group.

Does the query work ONCE in Query Analzyer?

And you are passing in a "where" clause as a parameter? You logic is
incorrect.

Jeff

 

[Jerry]

The query does work in the analyzer. The sp takes the following
parameters:
"EXEC saveNtext2file FileName, tableName, columnName, WhereCondition".
The sp uses the parameters to build the SQL statement: "set @sql =
N'SELECT @c =(select DATALENGTH('+@column+')/2 from '+@table+'
'+@where+')'"

What I ended up doing is exporting all of the Blob_IDs and putting them
into an array. Then using a for loop I step through the array and call
the sp each time.

I'm more used to classic asp but I'll be doing more and more .net work
in the near future. Do you have any suggstions on where I can go to get
some good resources for learning .net (online, books, etc.)?

Thanks,

 

[Jeff Dillon]

Why export the blob id's into an array. This is what I mean by doing it in a
single call. Do the join to the table that contains the blob_id's

But at a more basic level, don't you think you should test your connections,
etc, with a SINGLE call to a simple sproc first, instead of jumping right
into a 200 call loop?? In programming, we get the basics working first, then
move onto more difficult logic, based on exisitng, working logic.

Sounds like you don't even have your connection working yet. There are tons
of examples in the help files. Look in the help for the Connection object
for example.

Jeff