Reviewing my code. Displaying a BLOB image through a browser

I need to display a blob image from a database through my web browser
and I am struggling big. Below you will see the error message I'm
getting as well as my code for ASP and my HTML code. Any advice is
greatly appreciated. Thanks


This is the error I get when I try to open my asp page. When I try to
open the HTML page that uses the ASP page as a source for my image I
get a box for an image but it has the icon for a broken image.
Technical Information (for support personnel)
=B7 Error Type:
Server object, ASP 0177 (0x800401F3)
Invalid class string
/ben/bam3.asp, line 11
=B7 Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR
11.4322)
=B7 Page:
GET /ben/bam3.asp
=B7 Time:
Tuesday, December 19, 2006, 11:12:49 AM
=B7 More information:
Microsoft Support
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++
Below is my code. I modified some code I found at 4guysfromrolla.com
I am trying to use an ASP page to grab a binary image from a database.

My Table is called A_870211EE_7122_4AB3_B513_96B2AA58447A
The Column within the table where the images are stored is AF_Photo_1
The column where I define which employee gets their picture shown is
AF_Employee_Number
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++





<%
Dim iAF_Employee_Number

iAF_Employee_Number =3D Request.QueryString("AF_Employee_Number")

Dim objConn, objRS, strSQL
strSQL =3D "SELECT AF_Photo_1 FROM
A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE AF_Employee_Number =3D " &
iAF_Employee_Number

Set objConn =3D Server.CreateObject("ODBC.Connection")

objConn.Open "DSN=3DAsureIDc51Test", "xx", "xxxxx" ß username and
password removed for security

Set objRS =3D Server.CreateObject("ODBC.Recordset")

objRS.Open strSQL, objConn

Response.ContentType =3D "image/jpeg"

Response.BinaryWrite objRS("AF_Photo_1")
%>


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++
This is the code for my HTML page calling to get an employee's photo
based on their id.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++



charset=3Dwindows-1252">

Re: Reviewing my code. Displaying a BLOB image through a browser

bfiser@gmail.com wrote:
> · Error Type:
> Server object, ASP 0177 (0x800401F3)
> Invalid class string
>
> Set objConn = Server.CreateObject("ODBC.Connection")

I don't know of a class called "ODBC". You need to replace "ODBC" with
"adodb" in this and the other CreateObject lines that follow.
>
> objConn.Open "DSN=AsureIDc51Test", "xx", "xxxxx" ß username and
> password removed for security

Don't use ODBC if you have the choice:
http://www.aspfaq.com/show.asp?id=2126


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

Ok I made that change and now I get this error message in my browser.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '=3D'.
/ben/bam3.asp, line 14

Ben


Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
> > =B7 Error Type:
> > Server object, ASP 0177 (0x800401F3)
> > Invalid class string
> >
> > Set objConn =3D Server.CreateObject("ODBC.Connection")
>
> I don't know of a class called "ODBC". You need to replace "ODBC" with
> "adodb" in this and the other CreateObject lines that follow.
> >
> > objConn.Open "DSN=3DAsureIDc51Test", "xx", "xxxxx" ß username and
> > password removed for security
>
> Don't use ODBC if you have the choice:
> http://www.aspfaq.com/show.asp?id=3D2126
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

bfiser@gmail.com wrote:
> Ok I made that change and now I get this error message in my browser.
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near '='.
> /ben/bam3.asp, line 14
>
> Ben
>
>
The only way to debug syntax errors in sql statements is to look at
them. This cannot be done by looking at the vbscript code that generates
them. You need to write the sql statement to Response and look at it in
the browser window. Do this and if you cannot figure it out, show us the
response.written statement, as well as providing some details about your
database.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
>> Ok I made that change and now I get this error message in my browser.
>>
>> Error Type:
>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
>> syntax near '='.
>> /ben/bam3.asp, line 14
>>
>> Ben
>>
>>
> The only way to debug syntax errors in sql statements is to look at
> them. This cannot be done by looking at the vbscript code that
> generates them. You need to write the sql statement to Response and
> look at it in the browser window.

By this, I mean:

Response.Write strSQL
Response.End

Comment out these statements when you have a valid sql statement.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

OK I'm very new at this so I am not sure I understand what I need to do
for:
"You need to write the sql statement to Response and look at
it in
the browser window."

What do you need to know about the database? It is a SQL database
called AsureIDc51Test. It is tied to an ID card program where our HR
person takes a picture of the employee and then fills out a few generic
bits of employee info (Name, Department, Title) and then prints out an
ID Card.
The column for the image is AF_Photo_1, the table for this information
is A_870211EE_7122_4AB3_B513_96B2AA58447A and AF_Employee_Number is the
employee's ID number which is a unique identifier for each employee.

I hope the above and below info helps you and I'm sorry about my lack
of knowledge about this topic, I may have gotten in over my head
however I'm confident I can learn. Thanks for any help you or anyone
else can offer.

This is all the code I have:






<%
Dim iAF_Employee_Number
iAF_Employee_Number = Request.QueryString("AF_Employee_Number")
Dim objConn, objRS, strSQL
strSQL = "SELECT AF_Photo_1 FROM
A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE AF_Employee_Number = " &
iAF_Employee_Number
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "AsureIDc51Test", "sa", "metafile"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn
Response.ContentType = "image/JPEG"
Response.BinaryWrite objRS("AF_Photo_1")
%>






Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
> > Ok I made that change and now I get this error message in my browser.
> >
> > Error Type:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near '='.
> > /ben/bam3.asp, line 14
> >
> > Ben
> >
> >
> The only way to debug syntax errors in sql statements is to look at
> them. This cannot be done by looking at the vbscript code that generates
> them. You need to write the sql statement to Response and look at it in
> the browser window. Do this and if you cannot figure it out, show us the
> response.written statement, as well as providing some details about your
> database.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

bfiser@gmail.com wrote:
> OK I'm very new at this so I am not sure I understand what I need to
> do for:
> "You need to write the sql statement to Response and look at
> it in
> the browser window."
>
> What do you need to know about the database? It is a SQL database

That's a good start. What version of MS SQL Server (if that is indeed
what you mean by "SQL")?
Never ask a db-related question without providing this information: it
is almost always relevant.

> called AsureIDc51Test. It is tied to an ID card program where our HR
> person takes a picture of the employee and then fills out a few
> generic bits of employee info (Name, Department, Title) and then
> prints out an ID Card.
> The column for the image is AF_Photo_1, the table for this information
> is A_870211EE_7122_4AB3_B513_96B2AA58447A and AF_Employee_Number is
> the employee's ID number which is a unique identifier for each
> employee.

What is the datatype of AF_Employee_Number?



You haven't shown us the result of:

Response.Write strSQL

We cannot help you without seeing this.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

--That's a good start. What version of MS SQL Server (if that is indeed
what you mean by "SQL")? - MS SQL Server 2000 STD Edition

--What is the datatype of AF_Employee_Number? - int is the datatype

-- "You need to write the sql statement to Response and look at it in
the browser window." -
Ok I did this for response code

<%Dim objConn, objRS, strSQL
strSQL = "SELECT AF_Photo_1 FROM
A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE AF_Employee_Number = " &
iAF_Employee_Number
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "AsureIDc51Test", "sa", "metafile"
Response.Write strSQL
%>


My browser outputs this:

SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE
AF_Employee_Number =


I hope I did that right.





Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
> > OK I'm very new at this so I am not sure I understand what I need to
> > do for:
> > "You need to write the sql statement to Response and look at
> > it in
> > the browser window."
> >
> > What do you need to know about the database? It is a SQL database
>
> That's a good start. What version of MS SQL Server (if that is indeed
> what you mean by "SQL")?
> Never ask a db-related question without providing this information: it
> is almost always relevant.
>
> > called AsureIDc51Test. It is tied to an ID card program where our HR
> > person takes a picture of the employee and then fills out a few
> > generic bits of employee info (Name, Department, Title) and then
> > prints out an ID Card.
> > The column for the image is AF_Photo_1, the table for this information
> > is A_870211EE_7122_4AB3_B513_96B2AA58447A and AF_Employee_Number is
> > the employee's ID number which is a unique identifier for each
> > employee.
>
> What is the datatype of AF_Employee_Number?
>
>
>
> You haven't shown us the result of:
>
> Response.Write strSQL
>
> We cannot help you without seeing this.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

bfiser@gmail.com wrote:
>
> My browser outputs this:
>
> SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE
> AF_Employee_Number =
>
>
> I hope I did that right.

Yes you did, and you now have the answer, right? There's nothing after
the equals sign so Jet is returning a syntax error. You need to
investigate why iAF_Employee_Number has no value. Are you sure the
querystring has a key called "AF_Employee_Number"?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
>>
>> My browser outputs this:
>>
>> SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE
>> AF_Employee_Number =
>>
>>
>> I hope I did that right.
>
> Yes you did, and you now have the answer, right? There's nothing after
> the equals sign so Jet

Oops, not "Jet" - read "SQL Server" instead.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

Ok I am positive that AF_Employee_Number is a column in my table.
What could I do if I know the employee number I want to see is 301867.
What would be the proper way to code that in ASP (if that makes sense)
using this query string:

strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
WHERE AF_Employee_Number = "

I just want to get an image to come up first, then I'll worry about
getting others to come up!




Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
> >
> > My browser outputs this:
> >
> > SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE
> > AF_Employee_Number =
> >
> >
> > I hope I did that right.
>
> Yes you did, and you now have the answer, right? There's nothing after
> the equals sign so Jet is returning a syntax error. You need to
> investigate why iAF_Employee_Number has no value. Are you sure the
> querystring has a key called "AF_Employee_Number"?
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

bfiser@gmail.com wrote:
> Ok I am positive that AF_Employee_Number is a column in my table.

I don't believe I ever questioned that. Why is this relevant? Oh! Are
you talking about this question:

Are you sure the querystring has a key called "AF_Employee_Number"?

If so, this question has nothing to do with your database table. See the
next couple paragraphs.

> What could I do if I know the employee number I want to see is 301867.
> What would be the proper way to code that in ASP (if that makes sense)
> using this query string:

"query string" is a little confusing since the Request object has a
Querystring collection. When we talk about "querystring" let's be clear
that we are talking about the Request.Querystring collection, i.e., the
set of name/value pairs retrieved from the querystring of the url (the
stuff after the question mark):

http://yoursite/yourpage.asp?AF_Employee_Number=1234

I would term the following a "line of vbscript code that dynamically
creates a sql statement". I know this sounds pedantic, but I believe
your issue could have been dealt with several hours ago if we were clear
about the terminology.
>
> strSQL = "SELECT AF_Photo_1 FROM
> A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE AF_Employee_Number = "
>
> I just want to get an image to come up first, then I'll worry about
> getting others to come up!
>
>

Well, this:

SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE
AF_Employee_Number = 301867

is the statement you want to end up with, right? I.E. this is the
statement that needs to be sent to the database for execution, isn't it?
So that is the statement you need to see in the browser window when you
execute Response.Write strSQL. So for testing purposes, hard-code the
value in and run the page to make sure it works:

strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
WHERE AF_Employee_Number = 301867"
Response.Write strSQL

Once you have verified that works, work on making it happen dynamically:

Dim iAF_Employee_Number
AF_Employee_Number = 301867
strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
WHERE AF_Employee_Number = " & AF_Employee_Number
Response.Write strSQL


When that is working, work on getting the value from the querystring:

Dim iAF_Employee_Number
AF_Employee_Number = Request.QueryString("AF_Employee_Number")
strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
WHERE AF_Employee_Number = " & AF_Employee_Number
Response.Write strSQL


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Reviewing my code. Displaying a BLOB image through a browser

Bob,
Thanks for all of your guidance, I feel like I've learned quite a
bit in a short conversation with you. I was able to get this to work,
but I changed up my coding a bit and got some help from here:

http://support.microsoft.com/kb/173308

Also thanks for your patience.
Ben




Bob Barrows [MVP] wrote:
> bfiser@gmail.com wrote:
> > Ok I am positive that AF_Employee_Number is a column in my table.
>
> I don't believe I ever questioned that. Why is this relevant? Oh! Are
> you talking about this question:
>
> Are you sure the querystring has a key called "AF_Employee_Number"?
>
> If so, this question has nothing to do with your database table. See the
> next couple paragraphs.
>
> > What could I do if I know the employee number I want to see is 301867.
> > What would be the proper way to code that in ASP (if that makes sense)
> > using this query string:
>
> "query string" is a little confusing since the Request object has a
> Querystring collection. When we talk about "querystring" let's be clear
> that we are talking about the Request.Querystring collection, i.e., the
> set of name/value pairs retrieved from the querystring of the url (the
> stuff after the question mark):
>
> http://yoursite/yourpage.asp?AF_Employee_Number=1234
>
> I would term the following a "line of vbscript code that dynamically
> creates a sql statement". I know this sounds pedantic, but I believe
> your issue could have been dealt with several hours ago if we were clear
> about the terminology.
> >
> > strSQL = "SELECT AF_Photo_1 FROM
> > A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE AF_Employee_Number = "
> >
> > I just want to get an image to come up first, then I'll worry about
> > getting others to come up!
> >
> >
>
> Well, this:
>
> SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A WHERE
> AF_Employee_Number = 301867
>
> is the statement you want to end up with, right? I.E. this is the
> statement that needs to be sent to the database for execution, isn't it?
> So that is the statement you need to see in the browser window when you
> execute Response.Write strSQL. So for testing purposes, hard-code the
> value in and run the page to make sure it works:
>
> strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
> WHERE AF_Employee_Number = 301867"
> Response.Write strSQL
>
> Once you have verified that works, work on making it happen dynamically:
>
> Dim iAF_Employee_Number
> AF_Employee_Number = 301867
> strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
> WHERE AF_Employee_Number = " & AF_Employee_Number
> Response.Write strSQL
>
>
> When that is working, work on getting the value from the querystring:
>
> Dim iAF_Employee_Number
> AF_Employee_Number = Request.QueryString("AF_Employee_Number")
> strSQL = "SELECT AF_Photo_1 FROM A_870211EE_7122_4AB3_B513_96B2AA58447A
> WHERE AF_Employee_Number = " & AF_Employee_Number
> Response.Write strSQL
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.