Every form own connection? -- Take 2 1

[stillwillyboy]

After spending way too much time on wondering about the issue of if individual forms needed their own connection to a server and doing a bunch of reading, I have come to the conclusion that yes they do. Each time a connection is made, something is done with the data retrieved (something is either added, changed, deleted or just looked at), then the connection is dropped and a new connections is made when a different form is opened. If this is right or wrong please let me know.

Anyway, I finally figured out that my StartUpForm doesn’t need to connect to the DB. The reason is because it is used like a splash screen. The StartUpForm does have menu items to the other forms that do need a connection. Below is my code for the ClientInfo form. This is where the clients (workers) info is added, changed, deleted or looked at.

I am having a problem with the syntax on the line “Dim m_adapter As SqlDataAdapter = (command, m_cnADONetConnection)”. The problem is the parenthesis (or lack thereof) after the word “command”. When I leave it out, I get the message “ ‘)’ expected “. When I put in the parenthesis, I then get an error message “End of statement expected” after m_cnADONetConnection) <<<End of statement is expected here.

Here is my code for the procedure that I am writing to review client information. I am wanting to be able to enter a client number in the client number field and have that client’s info appear. Thanks for any help with the syntax and looking up information.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Console

Public Class frmClientInfo 'open ClientInfo form and make connection to DB
    Private m_cnADONetConnection As New System.Data.SqlClient.SqlConnection()
    Private m_adapter As SqlClient.SqlDataAdapter
    Private m_cbCommandBuilder As SqlClient.SqlCommandBuilder
    Private m_rowPosition As Integer = 0
    
    Private Sub frmClientInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        m_cnADONetConnection.ConnectionString = _
    "Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator"
        m_cnADONetConnection.Open()
    End Sub
    
    'this code in Exit?
    Private Sub frmClientInfo_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        m_cnADONetConnection.Close()
        m_cnADONetConnection.Dispose()

    End Sub

    'use this form for READ only.
    Private Sub mnuReviewClientInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuReviewClientInformation.Click
 Dim m_cnADONetConnection As New System.Data.SqlClient.SqlConnection
 Dim command As New SqlCommand("Select " & _
  "tbl_ClntBasicInfo.ClientNumber, tbl_ClntBasicInfo.LastName, " & _
  "tbl_ClntBasicInfo.FirstName, tbl_ClntBasicInfo.MiddleInitial, " & _
  "tbl_ClntPayRates.Average, tbl_ClntPayRates.Fringe, " & _
  "tbl_ClntClientBranch.BranchNumber " & _
  "FROM (tbl_ClntBasicInfo " & _
  "Inner Join tbl_ClntPayRates " & _
  "ON tbl_ClntBasicInfo.ClientNumber = tbl_ClntPayRates.ClientNumber)" & _
  "INNER JOIN tbl_ClntClientBranch " & _
   "ON tbl_ClntBasicInfo.ClientNumber = tbl_ClntClientBranch.ClientNumber", m_cnADONetConnection)

‘Not sure what to do from this point on.
        Dim DS As New DataSet()
        Dim m_adapter As SqlDataAdapter = (command, m_cnADONetConnection)
   Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(m_adapter)

        m_adapter.Fill(DS)

   'Dim I As Integer

        If DS.Tables(0).Rows.Count = 0 Then
            txtClientNumberBasicData.Text = ""
            txtLastNameBasicData.Text = ""
            txtFirstNameBasicData.Text = ""
            txtMiddleInitialBasicData.Text = ""

            'these items are on the “Additional Data” tab page/form
            '   txtAverage.Text = ""
            '  txtFringe.Text = ""
            ' txtLocation.Text = ""
            Exit Sub
        End If
        txtClientNumberBasicData.Text = _
        DS.Tables(0).Rows(m_rowPosition)("ClientNumber").ToString

        txtLastNameBasicData.Text = _
        DS.Tables(0).Rows(m_rowPosition)("LastName").ToString

        txtFirstNameBasicData.Text = _
        DS.Tables(0).Rows(m_rowPosition)("FirstName").ToString()

        txtMiddleInitialBasicData.Text = _
        DS.Tables(0).Rows(m_rowPosition)("MiddleInitial").ToString()

        'the following are on the Additioanl Data tab 
        'txtAverage.Text = _
        ' DS.Tables(0).Rows(m_rowPosition)("Average").ToString()

        ' txtFringebasicdata.Text = _
        ' DS.Tables(0).Rows(m_rowPosition)("Fringe").ToString()

        ' txtLocation.Text = _
        ' DS.Tables(0).Rows(m_rowPosition)("BranchNumber").ToString()
    End Sub

‘these items are on each individual form to open and/or close the different forms.
    Private Sub CloseClientInformationForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CloseFormToolStripMenuItem.Click
        Me.Close()
    End Sub

    Private Sub mnuCustomerJobInformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuCustomerJobInformation.Click
        Me.Close()
        frmCustomerAndJobInformation.Show()
    End Sub

    Private Sub mnuTimeSheetEntry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuTimeSheetEntry.Click
        Me.Close()
        frmTimeSheetEntryForm.Show()
    End Sub


    Private Sub OpenProcessPayroll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProcessPayrollToolStripMenuItem.Click
        Me.Close()
        frmProcessPayrollForm.Show()

    End Sub

    Private Sub OpenReportsForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportsToolStripMenuItem.Click
        Me.Close()
        frmReportsForm.Show()
    End Sub

    Private Sub AdministrationToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AdministrationToolStripMenuItem.Click
        Me.Close()
        frmAdminMaintainenceForm.Show()
    End Sub

    Private Sub ExitProgram_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()
        frmStartUpForm.Show()
    End Sub

End Class

 

[jebenson]

When you type in your code, do you get the Intellisense popup telling you the sytntax of whatever object's constructor you are typing? If so, you can click on the little up and down arrows on the Intellisense box, where there will be a number like "2 of 4". When you click the arrow, the intellisense changes to show you a different syntax for a different constructor for the object. If you look at the 4 different constructors for the SqlDataAdapter, you will see that the only one that accepts a SqlCommand object will accept only a SqlCommand object. It's ok though, look at your code and you'll see that you have already referenced the SqlConnection object when you created your SqlCommand object:

Dim command As New SqlCommand("Whole buncha SQL", [red]m_cnADONetConnection[/red])

'this works because it's using the SqlConnection from 'command'
Dim m_adapter As SqlDataAdapter = (command)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira