problem with saving

[bettybarnes]

i have a problem with saving data to access database this is my code

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If inc <> -1 Then

            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("Personnel").NewRow()

            dsNewRow.Item("PerID") = txtPersonnelID.Text
            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Lastname") = txtLastName.Text
            dsNewRow.Item("Middlename") = txtMiddleName.Text
            dsNewRow.Item("Gender") = txtGender.Text
            dsNewRow.Item("Address") = txtAddress.Text
            dsNewRow.Item("ContactNo") = txtContactNo.Text
            dsNewRow.Item("EmailAddress") = txtEmailAdd.Text
            dsNewRow.Item("BDay") = txtBday.Text
            dsNewRow.Item("Department") = txtDept.Text
            dsNewRow.Item("HiredDate") = txtHiredDate.Text
            dsNewRow.Item("RenewalDate") = txtRenewalDate.Text

            ds.Tables("Personnel").Rows.Add(dsNewRow)

            da.Update(ds, "Personnel")

            MsgBox("New Record added to the Database")

            btnSave.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True

        End If
    End Sub

and when i save there's always Systems.Windows on the personnel id textbox? what seems to be the problem with my coding?

Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        btnSave.Enabled = True
        btnAddNew.Enabled = False
        btnUpdate.Enabled = True
        btnDelete.Enabled = False


        txtPersonnelID.Clear()
        txtFirstName.Clear()
        txtLastName.Clear()
        txtMiddleName.Clear()
        txtGender.Clear()
        txtAddress.Clear()
        txtContactNo.Clear()
        txtEmailAdd.Clear()
        txtBday.Clear()
        txtDept.Clear()
        txtPosition.Clear()
        txtHiredDate.Clear()
        txtRenewalDate.Clear()
    End Sub


 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("Personnel").Rows(inc).Item(0) = txtPersonnelID
        ds.Tables("Personnel").Rows(inc).Item(1) = txtFirstName.Text
        ds.Tables("Personnel").Rows(inc).Item(2) = txtLastName.Text
        ds.Tables("Personnel").Rows(inc).Item(3) = txtMiddleName.Text
        ds.Tables("Personnel").Rows(inc).Item(4) = txtGender.Text
        ds.Tables("Personnel").Rows(inc).Item(5) = txtAddress.Text
        ds.Tables("Personnel").Rows(inc).Item(6) = txtContactNo.Text
        ds.Tables("Personnel").Rows(inc).Item(7) = txtEmailAdd.Text
        ds.Tables("Personnel").Rows(inc).Item(8) = txtBday.Text
        ds.Tables("Personnel").Rows(inc).Item(9) = txtDept.Text
        ds.Tables("Personnel").Rows(inc).Item(10) = txtPosition.Text
        ds.Tables("Personnel").Rows(inc).Item(11) = txtHiredDate.Text
        ds.Tables("Personnel").Rows(inc).Item(12) = txtRenewalDate.Text


        da.Update(ds, "Personnel")

        MsgBox("Data updated")

    End Sub

here's the screenshot of my program http://i43.photobucket.com/albums/e355/bettybarnes/screenshot.jpg

[the_carpenter]

Your missing something on your btnUpdate_Click

Look at the line that reads...

ds.Tables("Personnel").Rows(inc).Item(0) = txtPersonnelID

It's missing something, isn't it?

It should be the following...

ds.Tables("Personnel").Rows(inc).Item(0) = txtPersonnelID.Text

[bettybarnes]

whoa that really helps! thank you!

but another problem when i add another employee it does not add to the database and and save button does not work?

[bettybarnes]

the error message is the Input string was not in a correct format.Couldn't store <> in ContactNo Column. Expected type is Double.

[the_carpenter]

Your database is expecting a number not a string.

If you can look at the design of your table, you will notice that that ContactNo is a number data format where all the other fields should be a Text data format.

And what you are doing is trying to push text into a data field only set up to take numbers. Even if the only thing in the textbox is numbers without an explicit conversion to a numeric (double) format the program will complain every time.

So... it's an easy fix.

ds.Tables("Personnel").Rows(inc).Item(6) = Convert.ToDouble(txtContactNo.Text)

You may have to add that conversion to the save as well as the update.

Other fields may complain too.

Possibly the date fields... not too sure.
But if they do use the Convert.ToDateTime(control.text)

[bettybarnes]

yes i tried this but d data i put didnt add to the database?

[the_carpenter]

maybe repost your code... I think you missed something...

or maybe I did... :-)

[bettybarnes]

here is the code

Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        btnSave.Enabled = True
        btnAddNew.Enabled = False
        btnUpdate.Enabled = True
        btnDelete.Enabled = False


        txtPersonnelID.Clear()
        txtFirstName.Clear()
        txtLastName.Clear()
        txtMiddleName.Clear()
        txtGender.Clear()
        txtAddress.Clear()
        txtContactNo.Clear()
        txtEmailAdd.Clear()
        txtBday.Clear()
        txtDept.Clear()
        txtPosition.Clear()
        txtHiredDate.Clear()
        txtRenewalDate.Clear()
    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If inc <> -1 Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("Personnel").NewRow()
            dsNewRow.Item("PerID") = txtPersonnelID.Text
            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Lastname") = txtLastName.Text
            dsNewRow.Item("Middlename") = txtMiddleName.Text
            dsNewRow.Item("Gender") = txtGender.Text
            dsNewRow.Item("Address") = txtAddress.Text
            dsNewRow.Item("ContactNo") = Convert.ToDouble(txtContactNo.Text)
            dsNewRow.Item("EmailAddress") = txtEmailAdd.Text
            dsNewRow.Item("BDay") = txtBday.Text
            dsNewRow.Item("Department") = txtDept.Text
            dsNewRow.Item("HiredDate") = txtHiredDate.Text
            dsNewRow.Item("RenewalDate") = txtRenewalDate.Text


            ds.Tables("Personnel").Rows.Add(dsNewRow)
            da.Update(ds, "Personnel")

            MsgBox("New Record added to the Database")

            btnSave.Enabled = True
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True

        End If
    End Sub

[the_carpenter]

So now it doesn't error but doesn't save the data?

What is the purpose of this line?:

If inc <> -1 Then

I don't see where it's declared or any value is being assigned to it.

[bettybarnes]

oh sorry i already declared inc as dim inc as integer on public class form

[the_carpenter]

Ok... well as long as it's value isn't -1 and you're are pressing the save button after adding the new record and not the update button.

Side note: You really shouldn't enable the update button in the btnAddNew_Click I wouldn't think anyway.

So have you run through this stepping through the code to see where it's going wrong?

[bettybarnes]

ahm thanks but i have another problem

oledbexception was unhandled
Syntax error in INSERT INTO statement.

in da.update(ds, "Personnel")

[the_carpenter]

Did this happen after pressing add then update? What exactly did you do to create this error?

[bettybarnes]

no. it happened when i clicked save button. i didn't add anything

[bettybarnes]

this is my whole code

Public Class Form1
    Dim inc As Integer
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String
    Dim MaxRows As Integer
    Private Sub PersonnelBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.PersonnelBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.PersonnelQMDBDataSet)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'PersonnelQMDBDataSet.Personnel' table. You can move, or remove it, as needed.
        Me.PersonnelTableAdapter.Fill(Me.PersonnelQMDBDataSet.Personnel)
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Documents and Settings\JIM\My Documents\PersonnelQMDB.mdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()
        sql = "SELECT * FROM Personnel"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Perinfo")
        con.Close()


        MaxRows = ds.Tables("PerInfo").Rows.Count
        inc = -1
    End Sub
    Private Sub NavigateRecords()

        PerIDTextBox.Text = ds.Tables("PerInfo").Rows(inc).Item(0)
        FirstNameTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(1)
        LastNameTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(2)
        MiddleNameTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(3)
        GenderTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(4)
        AddressTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(5)
        ContactNoTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(6)
        EmailAddressTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(7)
        BDayTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(8)
        DepartmentTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(9)
        PositionTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(10)
        HiredDateTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(11)
        RenewalDateTextBox.Text = ds.Tables("Perinfo").Rows(inc).Item(12)
    End Sub



    Private Sub cmdFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub

    Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If
    End Sub

    Private Sub cmdPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        ElseIf inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc = 0 Then
            MsgBox("First Record")
        End If

    End Sub

    Private Sub cmdLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub

    Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        If MessageBox.Show("Do you really want to Delete this Record?", _
    "Delete", MessageBoxButtons.YesNo, _
    MessageBoxIcon.Warning) = DialogResult.No Then

            MsgBox("Operation Cancelled")
            Exit Sub
        End If


        ds.Tables("PerInfo").Rows(inc).Delete()
        MaxRows = MaxRows - 1
        inc = 0
        NavigateRecords()
        da.Update(ds, "PerInfo")

    End Sub

    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("PerInfo").Rows(inc).Item(0) = PerIDTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(1) = FirstNameTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(2) = LastNameTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(3) = MiddleNameTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(4) = GenderTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(5) = AddressTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(6) = ContactNoTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(7) = EmailAddressTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(8) = BDayTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(9) = DepartmentTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(10) = PositionTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(11) = HiredDateTextBox.Text
        ds.Tables("PerInfo").Rows(inc).Item(12) = RenewalDateTextBox.Text

        da.Update(ds, "PerInfo")
        MsgBox("Data updated")

    End Sub

    Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
        cmdSave.Enabled = True
        cmdadd.Enabled = False
        cmdUpdate.Enabled = False
        cmdDelete.Enabled = False

        PerIDTextBox.Clear()
        FirstNameTextBox.Clear()
        LastNameTextBox.Clear()
        MiddleNameTextBox.Clear()
        GenderTextBox.Clear()
        AddressTextBox.Clear()
        ContactNoTextBox.Clear()
        EmailAddressTextBox.Clear()
        BDayTextBox.Clear()
        DepartmentTextBox.Clear()
        PositionTextBox.Clear()
        HiredDateTextBox.Clear()
        RenewalDateTextBox.Clear()
    End Sub

    Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
        cmdSave.Enabled = False
        cmdadd.Enabled = True
        cmdUpdate.Enabled = True
        cmdDelete.Enabled = True
        inc = 0
        NavigateRecords()
    End Sub

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        If inc <> -1 Then
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow
            dsNewRow = ds.Tables("PerInfo").NewRow()
            dsNewRow.Item("PerID") = PerIDTextBox.Text
            dsNewRow.Item("FirstName") = FirstNameTextBox.Text
            dsNewRow.Item("Lastname") = LastNameTextBox.Text
            dsNewRow.Item("MiddleName") = MiddleNameTextBox.Text
            dsNewRow.Item("Gender") = GenderTextBox.Text
            dsNewRow.Item("Address") = AddressTextBox.Text
            dsNewRow.Item("ContactNo") = ContactNoTextBox.Text
            dsNewRow.Item("EmailAddress") = EmailAddressTextBox.Text
            dsNewRow.Item("BDay") = BDayTextBox.Text
            dsNewRow.Item("Department") = DepartmentTextBox.Text
            dsNewRow.Item("Position") = PositionTextBox.Text
            dsNewRow.Item("HiredDate") = HiredDateTextBox.Text
            dsNewRow.Item("RenewalDate") = RenewalDateTextBox.Text

            ds.Tables("PerInfo").Rows.Add(dsNewRow)
            da.Update(ds, "PerInfo")
            MsgBox("New Record added to the Database")

            cmdSave.Enabled = False
            cmdadd.Enabled = True
            cmdUpdate.Enabled = True
            cmdDelete.Enabled = True
        End If
    End Sub
End Class

[the_carpenter]

You changed something with the database.

What's the table called? PerInfo or Personnel ???

Be consistent. If the table is called Personnel... you need to change all those references from "PerInfo" to "Personnel"