July 8, 2009 at 6:14 am
Hi there.
I have to populate a Temp Table with data, including a photo, and the problem being that I cannot get the photo populated, since the filename consists of data actually contained in the Temp Table.
Here's the Temp Table:
Create Table #PersonTable
(
FirstName VarChar(50)
LastName VarChar(50)
IDNumber Int
Photo Image
)
I populate it like this:
Declare @SQLStatement VarChar(1000)
Set @SQLStatement = 'Select Pers_FirstName, Pers_LastName, Pers_IDNum From Person Where Pers_Status = ' + RTrim(InpStatus)
Insert Into #PersonTable Exec (@SQLStatement)
Note that InpStatus is an input StoredProc Parameter that indicates I want the people marked as Important Client.
After the table is populated, I then try to do the photo with an Update statement:
Declare @PhotoString VarChar(1000)
Declare @PhotoName VarChar(255)
Set @PhotoString = 'Update #PersonTable
Set Photo = (Select BulkColumn From OpenRowset(Bulk ''' + RTrim(@PhotoName) + ''', SINGLE_BLOB) as Photo)'
Begin Try
Exec (@PhotoString)
End Try
Begin Catch
Update #PersonTable Set Photo = ''
End Catch
My problem is in this update statement...
How do I generate @PhotoName if the name of the photo is the IDNumber in the TempTable?
e.g. Set @PhotoName = RTrim(Cast(IDNumber As VarChar)) + '.jpg'
This needs to be done for all the records in the TempTable, and each photo is obviously different.
The reasoning behind the coding I have supplied here is that this query is actually much more complex, I have just simplified it here not to confuse the issue.
Any help would be greatly appreciated!
July 9, 2009 at 2:37 am
Okay, I've actually solved the problem by looping through the Temptable and building the name for each instance...
If there is a more efficient way, please let me know.
Thanx.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply