[RESOLVED] Problem with CONNECTION object
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.
VB Code:
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
Re: Problem with CONNECTION object
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
Re: Problem with CONNECTION object
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.
VB Code:
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
Re: Problem with CONNECTION object
Hi,
as I said, the
vb Code:
Dim Dcn As New SqlCeConnection
was overriding your
vb Code:
Private DCN as SqlCeConnection
which was the root of the problems.
Pete
