Adding New Records and Tabbed Pages Q

[stillwillyboy]

The following code is for the “Add Client Information” form. (There will also be a separate “Change...” and a separate “Delete…” form. If I can just get the Add, I think I can get the others.) Each of these forms will have three tabbed pages: “Basic Data”, “Additional Info” and “Work History”. “Additional Data” will have wage and other info. The tabbed pages will be showing the info.

Since each form has tabbed pages, how do I get the info from the DB to the “Additioanl Info” and "Work History" tabbed page? Only the info for the “Basic Data” tabbed page is appearing (i.e. Client Number, LastName, etc.) Does each tabbed page need it’s own separate connection to the DB? This somehow seems like overkill of some sort.

Also, how do I change this code to add new client (worker) info, not read current info from the DB? I want to start with the Client Number, then the Last Name, etc. when entering data on the form. Your basic data entry procedure.

Can the following code be shortened and/or improved?

Imports System.Data
Imports System.Data.SqlClient

Public Class frmAddClientInfo 'open ClientInfo form and make connection to DB
Private m_cnADONetConnection As New System.Data.SqlClient.SqlConnection()
Private m_daDataAdapter As SqlClient.SqlDataAdapter
Private m_cbCommandBuilder As SqlClient.SqlCommandBuilder
Private m_dtClients As New DataTable
Private m_rowPosition As Integer = 0 '0 is the first record in DB

Private Sub frmAddClientInfoConnectToDB_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()

 ' Dim m_dadataadapter As New SqlDataAdapter
 'need SQL to add a new row in table for new record
m_dadataadapter = New SqlClient.SqlDataAdapter("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)

 'following line passes dataadapter to cmdbuilder
      m_cbCommandBuilder = New SqlClient.SqlCommandBuilder(m_dadataadapter)
  m_dadataadapter.Fill(m_dtClients) '<<info in DataTable
  Me.AddNewRecord()

End Sub
    'go to first empty row to add new record. 
Private Sub frmAddClientInfo_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        m_cnADONetConnection.Close()
        m_cnADONetConnection.Dispose()
End Sub

Private Sub AddNewRecord()
Dim m_dadataadapter As New SqlDataAdapter
Dim drNewRecord As System.Data.DataRow = m_dtClients.NewRow()

‘***conversion error msg at the following line. I know _ what 'it’s telling me, just not why it’s telling me. _
Using CInt to convert the client #.
drNewRecord("ClientNumber") = CInt(txtNewClientNumberBasicData.Text)
       
drNewRecord("LastName") = txtNewLastNameBasicData.Text
drNewRecord("FirstName") = txtNewFirstNameBasicData.Text
drNewRecord("MiddleInitial") = txtNewMiddleInitialBasicData


        m_dtClients.Rows.Add(drNewRecord)
        m_daDataAdapter.Update(m_dtClients)
        m_rowPosition = m_dtClients.Rows.Count = -1
        'Me.FindDesiredRecord()
        Me.ShowCurrentRecord()

End Sub

Private Sub ShowCurrentRecord()
        If m_dtClients.Rows.Count = 0 Then
            txtnewClientNumberBasicData.Text = ""
            txtNewLastNameBasicData.Text = ""
            txtNewFirstNameBasicData.Text = ""
            txtNewMiddleInitialBasicData.Text = ""

'the following are on the "Additional Data" tab form. 
            'txtAverageadditionaldata.Text = ""
            'txtFringe.Text = ""
            'txtLocation.Text = ""
            Exit Sub
        End If

        txtNewClientNumberBasicData.Text = _
       m_dtClients.Rows(m_rowPosition)("ClientNumber").ToString

        txtNewLastNameBasicData.Text = _
        m_dtClients.Rows(m_rowPosition)("LastName").ToString

        txtNewFirstNameBasicData.Text = _
        m_dtClients.Rows(m_rowPosition)("FirstName").ToString()

        txtNewMiddleInitialBasicData.Text = _
        m_dtClients.Rows(m_rowPosition)("MiddleInitial").ToString()

        'the following are on the "Additional Data" tab form. 
        '
        'txtAverage.Text = _
        'm_dtClients.Rows(m_rowPosition)("Average").ToString()

        'txtFringe.Text = _
        'm_dtClients.Rows(m_rowPosition)("Fringe").ToString()

 'txtLocation.Text = _
 'm_dtClients.Rows(m_rowPosition)("BranchNumber").ToString()
End Sub

Private Sub frmConnectToSQLTest_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        m_cnADONetConnection.Close()
        m_cnADONetConnection.Dispose()

End Sub

End Class

 

[jebenson]

To get a blank datatable, with the table's structure but no records, change you e select to this:

m_dadataadapter = New SqlClient.SqlDataAdapter("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 [red]where 1=0[/red]", m_cnADONetConnection)

One problem is that in your AddNewRecord sub you overwrite m_dadataadapter:

Private Sub AddNewRecord()
Dim m_dadataadapter As New SqlDataAdapter <-remove this line

The line above replaces the object you created in the Form's Load event. After this line m_dadataadapter has no SQL, commands, database connection, etc.

As to why this line

drNewRecord("ClientNumber") = CInt(txtNewClientNumberBasicData.Text)

is causing a conversion error, I can only guess because I don't know the specific error message. However, the Integer data type has a range of -2,147,483,648 through 2,147,483,647, so if your Client Number is larger than this, that could be causing the error.

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

 

[stillwillyboy]

The client number is four digits (1000, 1234, etc.). I set the Compile Option Strict to “ON”. It was Off. I still get the error message: “InvalidCastException was Unhandled.” With the added line of “Conversion from string "" to type 'Integer' is not valid.” I tried changing to CShort from CInt and get the message “Conversion from string "" to type 'Short' is not valid.”.

I’ve checked my DB (ClientPayroll) and the ClientNumber field is type Int. I’ve checked my text field in my VB form and it is “txtNewClientNumberBasicData.Text” like I have it coded.

I rem’d out the offending conversion line and ran the procedure. It went over the line as expected, but then hung up at the line “m_daDataAdapter.Update(m_dtClients)” with the error message “Dynamic SQL generation is not supported against multiple base tables.” Does that tell you anything?

Also, the “Add” form is being opened from a drop down menu named “Clients” on the programs start up form. On the “Clients” drop down is also a form called “Change”. It opens with the first client record in the DB (which is not the way it will work once I can get going. )

I also removed this: Dim m_dadataadapter As New SqlDataAdapter

Thanks for all the help.

Bill