Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Code works with MSDE 2000 but not SQL 2005

Status
Not open for further replies.

lidds

Programmer
Jun 9, 2005
72
GB
I have a small problem, I have written a small application to update a database table. Now on my computer I have a copy of the database but using MSDE 2000 and when I run my application it works perfectly, however I also have a copy of the database attached to MS SQL 2005 on another computer. Now when I got an run my application on this machine, it fails with a "Time out" error. I have changed the timeout settings on the SQL Server however it then just sits there for ages, whereas when I run it on my development machine MSDE 2000 it loops through the code quickly.

What I am trying to do is load a number of images into the database table. Here is the code:

Code:
        Dim myDB As New DBAccess

        Me.txtFrmStatus.Text = "Connecting to database"
        Me.txtFrmStatus.Refresh()

        If myDB.Connect("ReviewInsight") = False Then
            Exit Sub
        Else
            Dim myReader As OleDb.OleDbDataReader = Nothing
            Dim myCmd1 As New OleDb.OleDbCommand("spQryAllComments")
            myCmd1.CommandType = CommandType.StoredProcedure
            myCmd1.Parameters.Add(New OleDb.OleDbParameter("@project", OleDb.OleDbType.VarChar)).Value = "LPP"

            myReader = myDB.RunMyDataQuery(myCmd1)

            Dim userName As String = Nothing

            Do While myReader.Read
                Try
                    Me.picPreview.Image = Nothing
                    Dim imageByte As Byte() = Nothing
                    Dim strID As String = Nothing

                    strID = myReader.Item("ID")

                    Me.txtFrmStatus.Text = "Reading image"
                    Me.txtFrmStatus.Refresh()

                    Dim bmp As New Bitmap("C:\temp\thumb\" & strID & ".png")
                    Me.picPreview.Image = bmp
                    Me.picPreview.Refresh()

                    Me.txtStatus.Text = strID
                    Me.txtStatus.Refresh()

                    'Read image into byte 
                    ' save image to stream... 
                    Me.txtFrmStatus.Text = "Reading image into byte"
                    Me.txtFrmStatus.Refresh()
                    Dim clsStream1 As New System.IO.MemoryStream
                    Me.picPreview.Image.Save(clsStream1, System.Drawing.Imaging.ImageFormat.Png)

                    ' read bytes from stream... 
                    Me.txtFrmStatus.Text = "Reading bytes from stream"
                    Me.txtFrmStatus.Refresh()
                    Dim emptyByte As Byte = Nothing
                    Dim b As Byte() = DirectCast(Array.CreateInstance(GetType(Byte), clsStream1.Length), Byte())
                    clsStream1.Position = 0
                    clsStream1.Read(b, 0, b.Length)
                    clsStream1.Close()

                    Me.txtFrmStatus.Text = "Updating database"
                    Me.txtFrmStatus.Refresh()
                    Dim myCmd2 As New OleDb.OleDbCommand("spUpdateRptImage")
                    myCmd2.CommandTimeout = 1000
                    myCmd2.CommandType = CommandType.StoredProcedure
                    myCmd2.Parameters.Add(New OleDb.OleDbParameter("@ID", OleDb.OleDbType.VarChar)).Value = strID
                    myCmd2.Parameters.Add(New OleDb.OleDbParameter("@commRptImage", OleDb.OleDbType.LongVarBinary, b.Length, ParameterDirection.Input, False, emptyByte, emptyByte, "commImage", DataRowVersion.Current, b))
                    myDB.DoMyUpdate(myCmd2)
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            Loop

            myDB.DisConnect()
            myReader.Close()

            MsgBox("finished")
        End If

Note: myDB is a connection class that I have written.

This is the stored procedure:

Code:
CREATE PROCEDURE [dbo].[spUpdateRptImage] @ID as varchar(50),@commRptImage as image
AS
UPDATE commentsTbl SET commRptImage=@commRptImage WHERE [ID]=@ID
GO

Any guidance would be of help

Thanks

Simon
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top