I have an asp.net page called default.

The page has 3 fields: patientid, userid and resourceid.


I need to develop a web service that will use the patientid and resourceid fields to query a table called resource. The resource table contains the following fields:

Resourceid,resource,patientid,recorded and resourcefile.

The web service should use the patientid and resourceid parameter from the default.aspx page and display the resourcefile which is an image data type on an image.aspx file.

The userid parameter should be used to track who accessed the resource file and should update a log table which has the following fields: userid, patientid and resourceid and datetime.

I created my web service:

<WebMethod()> _
    Public Function RetreiveImage(uid AS string ,pid as string ,rid as string) As String

        Dim userid As String
        Dim patientid As String
        Dim resourceid As String

        Dim MyConnection As SqlConnection = New SqlConnection("server=(local)\NetSDK;database=hosp ital;Trusted_Connection=yes")
        Dim MyCommand1 As SqlDataAdapter = New SqlDataAdapter("select resourcefile from resource where patientid = '" & patientid & "' And userid = '" & userid & "' And resourceid = '" & resourceid & "'", MyConnection)
        Dim MyCommand2 As SqlDataAdapter = New SqlDataAdapter("insert into log (patientid,userid,resourceid) Values ('" & patientid & "','" & userid & "','" & resourceid & "')", MyConnection)



How do I run the 1st sql statement and pass the image file to a page called image.aspx?

How do I invoke this web service? I have two pages a default page an image page. The default page contains the 3 text boxes and a submit button. The image page does not have anthing yet. Just used to display the file image.

Thanks

Sanjeet

__________________
Sanjeet

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("C onnectionString"))
        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?


Sanjeet

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("C onnectionString"))
        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


Sanjeet