Is there a better way to insert blob images from SQL to Access DB?

I'm currently copying blob images from sql to access.  It works fine the way I'm doing it, but I've noticed that the query uses a lot of resources on the server.  Is there a better way I can do this?  I've pasted the code.  Any help would be greatly appreciated.  Thanks!
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM "&lbxblobtable&" WHERE d_busdate >= CAST('"&BeginDate&"' AS smalldatetime) AND d_busdate <= CAST('"&EndDate&"' AS smalldatetime)", Connection

Set objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = ""&lbxblobtable&""

for each column in rs.fields 
ct = column.type 
	if ct = 200 then 
		ct = 202
	elseif ct = 131 then 
		ct = 3		
	else
	end if 
	objFirstTable.Columns.Append column.name, ct
next
	
objADOXDatabase.Tables.Append objFirstTable 
		
Set Connection2 = Server.CreateObject("ADODB.Connection")

Set sqlcmd = CreateObject("ADODB.Command")

Connection2.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("WebLITS\App\"&randomNumber&"_"&lockboxname&".mdb") 

Connection2.Open

If rs.EOF Then 
'	Response.Write("No BLOB Records to Update!") 
'	Response.write "<br>"  
'	Response.write "<br>" 
Else

'	for each column in rs.fields
'	st = st & column.name & ", "
'	next
'	st = left(st,len(st)-2)

Do While NOT rs.Eof
	SQL = " "
	for each column in rs.fields
	SQL = SQL & " ?,"
	next
	SQL = left(SQL,len(SQL)-1)

	sqlcmd.ActiveConnection = Connection2
	sqlcmd.CommandType = 1
	SqlStmt = "INSERT INTO "&lbxblobtable&" VALUES (" & SQL & ")"
	sqlcmd.CommandText = SqlStmt

   	sqlcmd.Parameters.Append sqlcmd.CreateParameter("ID", 3, 1, , rs("ID"))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("BUSDATE", 202, 1, Len(Trim(rs("D_BUSDATE"))), Trim(rs("D_BUSDATE")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("REFNO", 202, 1, Len(Trim(rs("D_REFNO"))), Trim(rs("D_REFNO")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("IMAGE", 205, 1, 2147483647, rs("imgData"))
	Set sqlrst = sqlcmd.Execute()

	sqlcmd.Parameters.Delete ("ID")
	sqlcmd.Parameters.Delete ("BUSDATE")
	sqlcmd.Parameters.Delete ("REFNO")	
	sqlcmd.Parameters.Delete ("IMAGE")		
	rs.MoveNext     	
Loop
End If

rs.Close

Open in new window

CashmgmtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tommy11bCommented:
I know your not gonna like this but you should not at any time be inserting images into the database. As you see, it greatly ruins the performance of the database let alone the size it keeps increasing by. When you update a single column record (any record) it doesnt just update that value but copies a completly new matching record in memory. So if you update any column in that table, you are creating a new record. Look at the image size and picture it accumaliting by that size for every update you ever do on that record.

It is much better to store your images in a specified directory and instead simply store the file name & path to your database.
0
Om PrakashCommented:
Please check the modify code which might improve the performance.
<%
Set rs = Server.CreateObject("ADODB.Recordset") 
rs.Open "SELECT * FROM "&lbxblobtable&" WHERE d_busdate >= CAST('"&BeginDate&"' AS smalldatetime) AND d_busdate <= CAST('"&EndDate&"' AS smalldatetime)", Connection 

Set Connection2 = Server.CreateObject("ADODB.Connection") 
Set sqlcmd = CreateObject("ADODB.Command") 
Connection2.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("WebLITS\App\"&randomNumber&"_"&lockboxname&".mdb")  
Connection2.Open 
Dim Ctr 
Ctr = 0
If not rs.EOF Then  
	Do While NOT rs.Eof 
		    If Ctr = 0 then 'Execute only for the first time
				SQL = " " 
				for each column in rs.fields 
				SQL = SQL & " ?," 
				next 
				SQL = left(SQL,len(SQL)-1) 
				SqlStmt = "INSERT INTO "&lbxblobtable&" VALUES (" & SQL & ")" 
			end if	
	 
	        sqlcmd.ActiveConnection = Connection2 
	        sqlcmd.CommandType = 1 
	        sqlcmd.CommandText = SqlStmt 
	 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("ID", 3, 1, , rs("ID")) 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("BUSDATE", 202, 1, Len(Trim(rs("D_BUSDATE"))), Trim(rs("D_BUSDATE"))) 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("REFNO", 202, 1, Len(Trim(rs("D_REFNO"))), Trim(rs("D_REFNO"))) 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("IMAGE", 205, 1, 2147483647, rs("imgData")) 
	        Set sqlrst = sqlcmd.Execute() 
	 
	        sqlcmd.Parameters.Delete ("ID") 
	        sqlcmd.Parameters.Delete ("BUSDATE") 
	        sqlcmd.Parameters.Delete ("REFNO")       
	        sqlcmd.Parameters.Delete ("IMAGE")               
	        rs.MoveNext              
	        Ctr = Ctr + 1
	Loop 
End If 
rs.Close
%>

Open in new window

0
CashmgmtAuthor Commented:
I've decided to limit the blob images I want to retrieve from the lbxblobtable table.  I want to only copy over the BWF and BWR images.  What I did is create a recordset from the data table that has the 2 fields I need.  I want to then loop through the lbxblobtable table and copy only the records where the ID in the blob table match the BWF or BWR id's in the data table.  The problem I'm having is that when I loop it's copying only the first BWF id that it finds 20 times which is the correct number of images I need copied.  Can you see what I'm doing wrong?  If you look at the screen prints, the image from the data table, it shows all the id's I need from the blob table, but if you look at the blob table, all the images are BWF 212, it's not getting the other id's.
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open "SELECT * FROM "&lbxdatatable&" WHERE d_busdate >= CAST('"&BeginDate&"' AS smalldatetime) AND d_busdate <= CAST('"&EndDate&"' AS smalldatetime)", Connection

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM "&lbxblobtable&" WHERE ID = '" & rs1("BWF") & "' OR ID = '" & rs1("BWR") & "'", Connection

Set objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = ""&lbxblobtable&""

for each column in rs.fields 
ct = column.type 
	if ct = 200 then 
		ct = 202
	elseif ct = 131 then 
		ct = 3		
	else
	end if 
	objFirstTable.Columns.Append column.name, ct
next
	
objADOXDatabase.Tables.Append objFirstTable 
		
Set Connection2 = Server.CreateObject("ADODB.Connection")

Set sqlcmd = CreateObject("ADODB.Command")

Connection2.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("WebLITS\App\"&randomNumber&"_"&lockboxname&".mdb") 

Connection2.Open

Dim Ctr  
Ctr = 0 

Do While NOT rs1.Eof

	If Ctr = 0 then 'Execute only for the first time 
	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.Open "SELECT * FROM "&lbxblobtable&" WHERE (ID = '" & rs1("BWF") & "' OR ID = '" & rs1("BWR") & "')", Connection	
		SQL = " "
		for each column in rs.fields
		SQL = SQL & " ?,"
		next
		SQL = left(SQL,len(SQL)-1)
		SqlStmt = "INSERT INTO "&lbxblobtable&" VALUES (" & SQL & ")"
	End If
	sqlcmd.ActiveConnection = Connection2
	sqlcmd.CommandType = 1
	
	sqlcmd.CommandText = SqlStmt

   	sqlcmd.Parameters.Append sqlcmd.CreateParameter("ID", 3, 1, , rs("ID"))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("BUSDATE", 202, 1, Len(Trim(rs("D_BUSDATE"))), Trim(rs("D_BUSDATE")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("REFNO", 202, 1, Len(Trim(rs("D_REFNO"))), Trim(rs("D_REFNO")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("IMAGE", 205, 1, 2147483647, rs("imgData"))
	Set sqlrst = sqlcmd.Execute()

	sqlcmd.Parameters.Delete ("ID")
	sqlcmd.Parameters.Delete ("BUSDATE")
	sqlcmd.Parameters.Delete ("REFNO")	
	sqlcmd.Parameters.Delete ("IMAGE")		
	rs1.MoveNext
	Ctr = Ctr + 1    	
Loop

rs1.Close

Open in new window

ScreenHunter-03-Mar.-26-13.51.gif
ScreenHunter-02-Mar.-26-13.50.gif
0
CashmgmtAuthor Commented:
I figured out my problem.  I've attached the fixed code below.
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM "&lbxblobtable&"", Connection
rs.close

Set objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = ""&lbxblobtable&""

for each column in rs.fields 
ct = column.type 
	if ct = 200 then 
		ct = 202
	elseif ct = 131 then 
		ct = 3		
	else
	end if 
	objFirstTable.Columns.Append column.name, ct
next
	
objADOXDatabase.Tables.Append objFirstTable 

Set rs = Server.CreateObject("ADODB.Recordset")
SQL = "select blob.id as id, blob.d_busdate as d_busdate, blob.d_refno as d_refno, blob.imgData as imgData from "&lbxblobtable&" blob, "&lbxdatatable&" data WHERE blob.d_busdate >= CAST('"&BeginDate&"' AS smalldatetime) AND blob.d_busdate <= CAST('"&EndDate&"' AS smalldatetime) AND (blob.id = data.bwf or blob.id = data.bwr)"
rs.Open SQL, Connection

Set Connection2 = Server.CreateObject("ADODB.Connection")

Set sqlcmd = CreateObject("ADODB.Command")

Connection2.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("WebLITS\App\"&randomNumber&"_"&lockboxname&".mdb") 

Connection2.Open

Dim Ctr  
Ctr = 0 

If not rs.EOF Then 

Do While NOT rs.Eof

	If Ctr = 0 then 'Execute only for the first time 
		SQL = " "
		for each column in rs.fields
		SQL = SQL & " ?,"
		next
		SQL = left(SQL,len(SQL)-1)
		SqlStmt = "INSERT INTO "&lbxblobtable&" VALUES (" & SQL & ")"
	End If
	sqlcmd.ActiveConnection = Connection2
	sqlcmd.CommandType = 1
	
	sqlcmd.CommandText = SqlStmt

   	sqlcmd.Parameters.Append sqlcmd.CreateParameter("ID", 3, 1, , rs("ID"))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("BUSDATE", 202, 1, Len(Trim(rs("D_BUSDATE"))), Trim(rs("D_BUSDATE")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("REFNO", 202, 1, Len(Trim(rs("D_REFNO"))), Trim(rs("D_REFNO")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("IMAGE", 205, 1, 2147483647, rs("imgData"))
	Set sqlrst = sqlcmd.Execute()

	sqlcmd.Parameters.Delete ("ID")
	sqlcmd.Parameters.Delete ("BUSDATE")
	sqlcmd.Parameters.Delete ("REFNO")	
	sqlcmd.Parameters.Delete ("IMAGE")		
	rs.MoveNext
	Ctr = Ctr + 1    	
Loop
End If
rs.Close 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.