Rookie needing some help

[OMRebel]

Hey everyone. I'm very new to the world of programming. I just finished up a course on VB.NET (Intro course), but we never worked with databases during that course. So, that leads up to this problem I'm having. I'm trying to write an application for my work using VB Express, and working with an Access database. I'm attaching the entire project to this post, along with the database.

I have been able to use the Wizard to add entries to Physicians, Insurance, etc.. However, when it comes to entering the actual transactions, that is where I am clueless. If someone can give me a nudge in the right direction, I'd really appreciate it.

The form that I'm working with in that project is the frmTrans form. In there, I want to set it up on the btnEnter Click event that the data is then written to the tblTrans table. A couple examples:

The combobox on the form ramed "NameComboBox" was setup using the wizard. It is getting the field "Name" from the tblInsurance table. So, if I have 2 records in there with:
ID Name
--- --------
1 Acme
2 Widget

And Widget is selected from the combox box, then its ID of 2 needs to be written to the field "Insurance" in the tblTrans table.

Another example is the txtAccount text box. I need whatever is in there to be written to the Account field in the tblTrans table.

If someone could help me out on this, I'd really appreciate it. Once I see how those two are done, I should be able to get the ball rolling.

Thanks.

 

[jmcilhinney]

Forget your project for the moment. Work through some tutorials using basic scenarios and then when you have the basics down you can apply them to your own project. There are a few tutorial links in my signature and fellow member TechGnome has links to his own ADO.NET tutorials in his signature.

Finally, please refrain from posting binary files to the forum. I've removed all binaries from your attachment but please do not post zipped project folders without deleting the binaries first in future. If you delete the bin and obj folders that will do the job, and they will just be recreated next time you compile. Also, posting entire projects is not normally a good idea. I for one will rarely download someone else's project. There are very few situations where you can't either describe the problem or post the code directly in the thread.

 

[OMRebel]

Thanks for the suggestion. Sorry about posting exe's. I didn't even think about deleting those out. I ended up going through several tutorials, and that helped me to get this project working. Again, my apologies for not posting correctly.

 

[cjard]

The tuts I would recommend for you, being a NET2.0 user, are the DATA WALKTHROUGHS from microsoft. If you google for data walkthroughs then youll turn up some hits from msdn - read these. The access methods they propose will sove your problems in the other thread regarding missing table names too.

Having acombo box bound to a list of values but writing its data into another table is very easy. When you get the basics working, approach us again and we'll tell you more in depth version of this:

Ensure your form has a dataset containing both the table to be updated and the table providing the value list. I'll refer to this as tblWorking and tblLookup respectively
Set the data source type to be represented by a combo in he datasources window
Drop a combo onto the form from the tblWorking data source
In the advanced data bindings for the combo, move the default binding to .Text up into SelectedValue instead
Set the datasource of the combo to be tblLookup
Set the displaymember and valuemember properties to the relevant columns (display is what the user sees, value is what the combo writes into the bound field of .SelectedValue binding)

and youre done!``

 

[OMRebel]

You've been busy today! )

I went through several tutorials, and found one that made sense. I was able to get my entire application written and working fairly well. Just problems figuring out how to write reports, but that's a different matter and I put a new thread on that in the printing forum.

 

[OMRebel]

In fact, here is my completed form:

Imports System.Data
Imports System.Data.OleDb

Public Class frmTrans

    ' Setup Connection Object; con will hold the connection object
    Dim con As New OleDb.OleDbConnection        ' Transaction table
    Dim con2 As New OleDb.OleDbConnection       ' 2 is for the Charges
    Dim con3 As New OleDb.OleDbConnection       ' 3 is for the Patient
    ' *************************************************************************************************************
    ' NOTE: Doing the con2 and con3 are the only way I know how to do this.  It's a really bad hack, but it works.
    ' Eventually I'll need to clean this up to make it run smoother and faster.
    ' *************************************************************************************************************
    Dim ds As New DataSet               ' DataSet
    Dim ds2 As New DataSet
    Dim ds3 As New DataSet
    Dim da As OleDb.OleDbDataAdapter    ' Data adapter
    Dim da2 As OleDb.OleDbDataAdapter
    Dim da3 As OleDb.OleDbDataAdapter
    Dim sql As String                   ' Holds the query
    Dim sql2 As String
    Dim sql3 As String
    Dim MaxRows As Integer              ' Will hold Maximum # of Rows
    Dim inc As Integer                  ' Used for navigating through dataset


    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub


    Private Sub frmTrans_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ' **********************************************************************************************
        con2.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = c:\ProFees\profees.mdb"
        con2.Open()
        sql2 = "Select * From tblCharges"
        da2 = New OleDb.OleDbDataAdapter(sql2, con2)
        da2.Fill(ds2, "Charges")

        If ds2.Tables("Charges").Rows.Count > 0 Then
            With dgvCharges
                .DataSource = ds2.Tables("Charges")
            End With
        End If
        ' **********************************************************************************************

        'TODO: This line of code loads data into the 'ProfeesDataSet.tblTrans' table. You can move, or remove it, as needed.
        Me.TblTransTableAdapter.Fill(Me.ProfeesDataSet.tblTrans)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblPatient' table. You can move, or remove it, as needed.
        Me.TblPatientTableAdapter.Fill(Me.ProfeesDataSet.tblPatient)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblInsurance' table. You can move, or remove it, as needed.
        Me.TblInsuranceTableAdapter.Fill(Me.ProfeesDataSet.tblInsurance)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblPhys' table. You can move, or remove it, as needed.
        Me.TblPhysTableAdapter.Fill(Me.ProfeesDataSet.tblPhys)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblCharges' table. You can move, or remove it, as needed.
        Me.TblChargesTableAdapter.Fill(Me.ProfeesDataSet.tblCharges)

    End Sub

    Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

        ' Validating that Account number has been entered.
        If txtAccount.Text = "" Then
            MsgBox("Please Enter Account Number")
            Exit Sub
            txtAccount.Focus()
        End If

        ' Validating that the Charge number has been entered
        If txtCharge.Text = "" Then
            MsgBox("Please Enter Charge Number")
            Exit Sub
            txtCharge.Focus()
        End If

        ' Setup the Connection String; con is the connection string
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = c:\ProFees\profees.mdb"

        ' Open the connection to the database
        con.Open()
        sql = "Select * From tblTrans"

        ' Create the data adapter
        da = New OleDb.OleDbDataAdapter(sql, con)

        ' Fill the DataSet from the data adapter
        da.Fill(ds, "ProFees")      ' ProFees is the identifier.  Does not have to be name of table.

        ' Close the connection to the database
        con.Close()

        MaxRows = ds.Tables("ProFees").Rows.Count   ' Stores how many rows are in the DataSet
        inc = MaxRows

        If inc <> -1 Then       ' Make sure there is a valid record to add
            Dim cb As New OleDb.OleDbCommandBuilder(da) ' Command Builder
            Dim dsNewRow As DataRow ' DataRow Object - needed for adding new rows to DataSet
            dsNewRow = ds.Tables("ProFees").NewRow()  ' Creates the new DataRow object, stores it in dsNewRow variable

            '**********************************************************************************
            ' Update the Dataset
            dsNewRow.Item("Account") = txtAccount.Text
            dsNewRow.Item("AllowedAmnt") = txtAllowed.Text
            dsNewRow.Item("PaymentAmnt") = txtPayment.Text
            dsNewRow.Item("PatPortion") = txtPatPortion.Text
            dsNewRow.Item("PatPaid") = txtPatPaid.Text
            dsNewRow.Item("Charge") = txtCharge.Text
            dsNewRow.Item("DOS") = dtpDOS.Value.ToShortDateString
            dsNewRow.Item("Post") = dtpPost.Value.ToShortDateString
            dsNewRow.Item("FaceSheet") = chkFaceSheet.CheckState
            dsNewRow.Item("Insurance") = cboInsurance.Text
            dsNewRow.Item("Physician") = cboPhysician.Text
            '*********************************************************************************

            ds.Tables("ProFees").Rows.Add(dsNewRow)  ' This method adds the Row to the DataSet
            da.Update(ds, "ProFees")  ' DataAdapter update

        End If

        '*************************************************************************************
        ' Udate the DataBase from the DataSet
        ds.Tables("ProFees").Rows(inc).Item("Account") = txtAccount.Text
        ds.Tables("ProFees").Rows(inc).Item("AllowedAmnt") = txtAllowed.Text
        ds.Tables("ProFees").Rows(inc).Item("PaymentAmnt") = txtPayment.Text
        ds.Tables("ProFees").Rows(inc).Item("PatPortion") = txtPatPortion.Text
        ds.Tables("ProFees").Rows(inc).Item("PatPaid") = txtPatPaid.Text
        ds.Tables("ProFees").Rows(inc).Item("Charge") = txtCharge.Text
        ds.Tables("ProFees").Rows(inc).Item("DOS") = dtpDOS.Value.ToShortDateString
        ds.Tables("ProFees").Rows(inc).Item("Post") = dtpPost.Value.ToShortDateString
        ds.Tables("ProFees").Rows(inc).Item("FaceSheet") = chkFaceSheet.CheckState
        ds.Tables("ProFees").Rows(inc).Item("Insurance") = cboInsurance.Text
        ds.Tables("ProFees").Rows(inc).Item("Physician") = cboPhysician.Text
        '*************************************************************************************
        da.Update(ds, "ProFees")    ' This line here is what actually updates the database!!!!
        MsgBox("Data updated")

        ' Clear labels
        txtAccount.Text = ""
        txtCharge.Text = ""
        dtpDOS.Value = Today
        dtpPost.Value = Today
        txtAllowed.Text = "0"
        txtPayment.Text = "0"
        txtPatPortion.Text = "0"
        txtPatPaid.Text = "0"
        lblFirstName.Visible = False
        lblLastName.Visible = False

        ' Set focus back to txtAccount
        txtAccount.Focus()

    End Sub

    Private Sub txtCharge_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCharge.TextChanged
        ' Filter the datagrid as the user types in the textbox
        Dim aFilter As String
        Dim aRows As Integer

        If txtCharge.Text.Trim = "" Then
            aFilter = ""
        Else
            aFilter = "Code = " & txtCharge.Text
        End If

        ds2.Tables("Charges").DefaultView.RowFilter = aFilter
        aRows = ds2.Tables("Charges").DefaultView.Count

    End Sub

    Private Sub txtAccount_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAccount.Leave
        ' This will run a SQL to determine if the patient is in the system based on the Count.
        ' If the patient is in the system, then display in the labels.
        ' If the patient isn't in the system, then bring up the frmNewPatient form.

        ' TODO: There is a bug where the label isn't showing the correct Patient.  It is showing the previous
        '       patient name that was used.  Not sure why it is doing that.

        If txtAccount.Text <> "" Then   ' Only run through this is something is entered into the Account field

            con3.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = c:\ProFees\profees.mdb"
            con3.Open()
            sql3 = "Select * FROM tblPatient WHERE Account = " & txtAccount.Text
            da3 = New OleDb.OleDbDataAdapter(sql3, con3)
            da3.Fill(ds3, "Patient")

            If ds3.Tables("Patient").Rows.Count < 1 Then    ' Patient isn't in the system
                frmNewPatient.Show()
                con3.Close()         ' Close connection
            Else
                lblFirstName.Visible = True
                lblLastName.Visible = True
                lblFirstName.Text = ds3.Tables("Patient").Rows(0).Item(1)
                lblLastName.Text = ds3.Tables("Patient").Rows(0).Item(2)
            End If
            con3.Close() ' Close connection
        End If

    End Sub

    Private Sub txtAccount_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAccount.TextChanged

    End Sub
End Class

 

[vis781]

If txtAccount.Text = "" Then
MsgBox("Please Enter Account Number")
Exit Sub
txtAccount.Focus()

Please do not use the = operator for string comparisons, it's really, really, really slow. There is a method for determining whether a string holds a value built in..

If String.IsNullOrEmpty(txtAccount.Text) Then
...
...

 

[OMRebel]

Thanks for that advice. I made the changes to the three places that I was doing that at. Let me know if there's anything else you can suggest to make the code cleaner/better.

 

[cjard]

Well, that's a subjective thing. vis is correct in that = is comparatively incredibly slow but for the odd single comparison you wont really notice (we are talking microseconds) so you might find it makes your code clearer to use =

Inside of loops and stuff, for sure use String.Equals(s1,s2) as it's the fastest implementation if less readable..

 

[cjard]

If ds3.Tables("Patient").Rows.Count < 1 Then ' Patient isn't in the system
frmNewPatient.Show()
con3.Close() ' Close connection
Else

statement in bold is moot; in the case of a successful IF, the very next statement to be called after th bold one is..
yup. CloseConnection()