Code works with MSDE 2000 but not SQL 2005

[lidds]

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:

        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:

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

 

[jmcilhinney]

I'm not sure whether it will make a difference but is there a reason that you're using OleDb to access SQL Server instead of SqlClient? Also, you're creating your parameters in a far more complex way than is necessary, particularly the last one. How can you be specifying a source column name when the data isn't in a DataTable for there to be a source column? Just use AddWithValue:

myCmd1.Parameters.AddWithValue("@project", "LPP")
myCmd2.Parameters.AddWithValue("@ID", strID)
myCmd2.Parameters.AddWithValue("@commRptImage", b)

Don't make things more complex than they need to be.

I'd also point out that a DAL where you create the Commands and such in the forms is no DAL at all. It seems like your DBAccess class simply contains a Connection and calls methods on Commands. You may as well have the Connections in the form too if you're going to have the rest.