[RESOLVED] Problem with CONNECTION object

[szlamany]

I saw remarks here about how slow forms load and such on these PPC devices.

So I decided to make my life easy and have just one label, one textbox and one button to process activity on a single form.

First time you click the BUTTON it opens the connection to the SQL CE database. Then it changes the label caption to LAST NAME.

Next time you click the BUTTON it reads the label caption to determine what happens next - unfortunately the DCN connection object has become NOTHING. I'm confused.

I've not done a lot of VS2005 work yet - so I cannot figure out why my DCN connection object is not persisting. I've made it PUBLIC up top - but that did not change anything.

I've stepped through the code - when I get to line 18 the DCN object is nothing. If I put the DIM...NEW in the IF/block that initially creates/opens it - then it gets worse. I tried IF DCN IS NOTHING in front of the DIM - but that's not doing it either.

Imports System.Data.SqlServerCe
Imports System.Data.SqlClient
 
Public Class APC
 
    Private Dcn As SqlCeConnection
    Private Drc As SqlCeCommand
    Private Dse As SqlCeEngine
    Private LDrd As SqlDataReader
 
    Private Sub APC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox.PasswordChar = "*"
        TextBox.Focus()
        Button.BackColor = System.Drawing.Color.Green
    End Sub
 
    Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
        Dim Dcn As New SqlCeConnection
        Button.BackColor = System.Drawing.Color.Yellow
        Button.Refresh()
        Cursor.Current = Cursors.WaitCursor
        If TextBox.PasswordChar = "*" Then
            Dim booNotFound As Boolean
            Dcn.ConnectionString = "DataSource=\My Documents\Business\APC.sdf; Password=" & TextBox.Text
            Try
                Dcn.Open()
                TextBox.PasswordChar = ""
            Catch ex As SqlCeException
                If ex.NativeError = 25046 Then
                    booNotFound = True
                Else
                    MsgBox(ex.Message)
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            If booNotFound Then
                Dim dse As New SqlCeEngine("DataSource=\My Documents\Business\APC.sdf; Password=" _
                                                & TextBox.Text & ";encrypt database=TRUE")
                Try
                    lblStatus.Text = "Creating database"
                    lblStatus.Refresh()
                    dse.CreateDatabase()
                    Dcn.Open()
                    lblStatus.Text = ""
                    lblStatus.Refresh()
                    If LoadDatabase(Dcn) = False Then
                        Dcn.Close()
                        Dcn.Dispose()
                        System.IO.File.Delete("\My Documents\Business\APC.sdf")
                        Cursor.Current = Cursors.Default
                        MsgBox("Database did not sync!")
                        Me.Close()
                        Exit Sub
                    End If
                    TextBox.PasswordChar = ""
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
            If TextBox.PasswordChar = "*" Then
                TextBox.SelectAll()
                TextBox.Focus()
            Else
                TextBox.Text = ""
                Label.Text = "Last Name"
            End If
        Else
            lstList.Items.Clear()
            lstList.Columns.Clear()
            lstList.Columns.Add("Student",lstlist.Width,HorizontalAlignment.Left    )
            Select Case Label.Text
                Case "Last Name"
                    Dim Drc As New SqlCeCommand
                    Dim Drd As SqlCeDataReader
                    Drc.Connection = Dcn
                    Drc.CommandType = Data.CommandType.Text
                    Drc.CommandText = "Select * From Student Where LastName Like '" & TextBox.Text & "%'"
                    Drd = Drc.ExecuteReader()
                    While Drd.Read
                        Dim lwi As New ListViewItem
                        lwi.Text = Drd(1) & ", " & Drd(2) & " (" & Drd(5) & ")"
                        lstList.Items.Add(lwi)
                    End While
            End Select
        End If
        Cursor.Current = Cursors.Default
        Button.BackColor = System.Drawing.Color.Green
    End Sub

[petevick]

Hi,
but you are doing
Dim Dcn As New SqlCeConnection in your click which will override the private dcn, and create a new one every time.

My advice would be to open at the start, and only close at the end for performance reasons

Pete

[szlamany]

I was trying to only open once - but having a problem - I think - with the scope of the variable. I'm probably not following good VS2005 OO concepts - but I finally got it to work with these changes.

Imports System.Data.SqlServerCe
Imports System.Data.SqlClient
 
Public Class APC
 
    Public Dcn As New SqlCeConnection <-*********************
    Private Drc As SqlCeCommand
    Private Dse As SqlCeEngine
    Private LDrd As SqlDataReader
 
    Private Sub APC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox.PasswordChar = "*"
        TextBox.Focus()
        Button.BackColor = System.Drawing.Color.Green
        End Sub
 
    Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
        Button.BackColor = System.Drawing.Color.Yellow
        Button.Refresh()
        Cursor.Current = Cursors.WaitCursor
        If TextBox.PasswordChar = "*" Then
            Call OpenDatabase() <-******************
        Else
            lstList.Items.Clear()
            lstList.Columns.Clear()
            lstList.Columns.Add("Student", lstList.Width, HorizontalAlignment.Left)
            Select Case Label.Text
                Case "Last Name"
                    Dim Drc As New SqlCeCommand
                    Dim Drd As SqlCeDataReader
                    Drc.Connection = Dcn
                    Drc.CommandType = Data.CommandType.Text
                    Drc.CommandText = "Select * From Student Where LastName Like '" & TextBox.Text & "%'"
                    Drd = Drc.ExecuteReader()
                    While Drd.Read
                        Dim lwi As New ListViewItem
                        lwi.Text = Drd(1) & ", " & Drd(2) & " (" & Drd(5) & ")"
                        lstList.Items.Add(lwi)
                    End While
            End Select
        End If
        Cursor.Current = Cursors.Default
        Button.BackColor = System.Drawing.Color.Green
    End Sub
 
    Private Function OpenDatabase() As Boolean  <--*** made this a function
        OpenDatabase = False
        'Dim Dcn As New SqlCeConnection <--***************
        Dim booNotFound As Boolean
        Dcn.ConnectionString = "DataSource=\My Documents\Business\APC.sdf; Password=" & TextBox.Text
        Try
            Dcn.Open()
            TextBox.PasswordChar = ""
        Catch ex As SqlCeException
            If ex.NativeError = 25046 Then
                booNotFound = True
            Else
                MsgBox(ex.Message)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        If booNotFound Then
            Dim dse As New SqlCeEngine("DataSource=\My Documents\Business\APC.sdf; Password=" _
                                            & TextBox.Text & ";encrypt database=TRUE")
            Try
                lblStatus.Text = "Creating database"
                lblStatus.Refresh()
                dse.CreateDatabase()
                Dcn.Open()
                lblStatus.Text = ""
                lblStatus.Refresh()
                If LoadDatabase(Dcn) = False Then
                    Dcn.Close()
                    Dcn.Dispose()
                    System.IO.File.Delete("\My Documents\Business\APC.sdf")
                    Cursor.Current = Cursors.Default
                    MsgBox("Database did not sync!")
                    Me.Close()
                    Exit Function
                End If
                TextBox.PasswordChar = ""
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
        If TextBox.PasswordChar = "*" Then
            TextBox.SelectAll()
            TextBox.Focus()
        Else
            TextBox.Text = ""
            Label.Text = "Last Name"
        End If
 
    End Function

[petevick]

Hi,
as I said, the

Dim Dcn As New SqlCeConnection

was overriding your

Private DCN as SqlCeConnection

which was the root of the problems.

Pete