DIsplaying an image called web services

[Sanjeet1]

Here is my my web service: I am trying to retreive an image data type.

<WebMethod()> _
Public Function RetreiveImageDB(ByVal uid As String, ByVal pid As String, ByVal rid As String) As String

Dim userid As String
Dim patientid As String
Dim recordid As String

userid = uid
patientid = pid
recordid = rid


Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim SQLFile As String = "select resourcefileimage from resource where patientid = '" & patientid & "' And userid = '" & userid & "' And recordid = '" & recordid & "'"
Dim oCMD As New SqlCommand(SQLFile, MyConnection)


MyConnection.Open()


Dim b As Byte
b = oCMD.ExecuteScalar

'Update log table
Dim oCMD1 As New SqlCommand("exec sp_adduserlog @userid = '" & userid & "',@patientid = '" & patientid & "',@recordid= '" & recordid & "'", MyConnection)

MyConnection.Close()

Return b

---------------------------------------------
The prgram fails at :
b = oCMD.ExecuteScalar



Any suggestions?

 

[jbenson001]

What is the error you are getting?

 

[Sanjeet1]

I used a try catch and ths is what i got:

Cast from string "select resourcefileimage from re" to type 'Integer' is not valid.

It fails at: b = oCMD.ExecuteScalar(SQLFile)

Here is my code:

<WebMethod()> _
Public Function RetreiveImageDB(ByVal uid As String, ByVal pid As String, ByVal rid As String) As Byte

Dim userid As String
Dim patientid As String
Dim recordid As String

userid = uid
patientid = pid
recordid = rid


Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim SQLFile As String = "select resourcefileimage from resource where patientid = '" & patientid & "' And userid = '" & userid & "' And recordid = '" & recordid & "'"
Dim oCMD As New SqlCommand(SQLFile, MyConnection)


MyConnection.Open()
Dim b As Byte()
Try


b = oCMD.ExecuteScalar(SQLFile)

Catch Ex As Exception
Ex.Message.ToString()
'Update log table
Dim oCMD1 As New SqlCommand("exec sp_adduserlog @userid = '" & userid & "',@patientid = '" & patientid & "',@recordid= '" & recordid & "'", MyConnection)

MyConnection.Close()

Return b(1)
End Try



End Function

 

[jbenson001]

The problem is the construction of your select statement string. Why do you have single quotes embedded in the string? You would only need single quotes around values that are strings.

JIm

 

[Sanjeet1]

When I debug this is my sql statement:


select resourcefileimage from resource where patientid = '003' And userid = '003' And recordid = '003'

This is what got returned:

0xFFD8FFE000104A46494600010201006000600000FFED104A50686F746F73686F7020332E30003842494D03ED0A5265736F6C7574696F6E0000000010006000000001000100600000000100013842494D040D18465820476C6F62616C204C69676874696E6720416E676C650000000004000000783842494D04191246582047

 

[jbenson001]

Try this:

b = CType(oCMD.ExecuteScalar(SQLFile),Byte())