problem with saving

Question

bettybarnes 7 Posting Whiz in Training

14 Years Ago

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

vb.net

2 Contributors
15 Replies
167 Views
6 Days Discussion Span
Latest Post 14 Years Ago Latest Post by the_carpenter

the_carpenter 21 Junior Poster in Training

14 Years Ago

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

the_carpenter 21 Junior Poster in Training

14 Years Ago

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)

the_carpenter 21 Junior Poster in Training

14 Years Ago

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

or maybe I did... :-)

the_carpenter 21 Junior Poster in Training

14 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

bettybarnes 7 Posting Whiz in Training · Answer 1 · 2010-09-03T12:01:32+00:00

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 7 Posting Whiz in Training · Answer 2 · 2010-09-03T12:02:56+00:00

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

bettybarnes 7 Posting Whiz in Training · Answer 3 · 2010-09-03T21:09:25+00:00

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

bettybarnes 7 Posting Whiz in Training · Answer 4 · 2010-09-04T19:48:37+00:00

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

bettybarnes 7 Posting Whiz in Training · Answer 5 · 2010-09-06T20:51:09+00:00

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

the_carpenter 21 Junior Poster in Training · Answer 6 · 2010-09-07T01:27:44+00:00

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 7 Posting Whiz in Training · Answer 7 · 2010-09-07T14:57:04+00:00

ahm thanks but i have another problem

oledbexception was unhandled
Syntax error in INSERT INTO statement.

in da.update(ds, "Personnel")

the_carpenter 21 Junior Poster in Training · Answer 8 · 2010-09-07T15:47:32+00:00

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

bettybarnes 7 Posting Whiz in Training · Answer 9 · 2010-09-07T16:04:02+00:00

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

bettybarnes 7 Posting Whiz in Training · Answer 10 · 2010-09-07T16:05:52+00:00

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 21 Junior Poster in Training · Answer 11 · 2010-09-07T22:34:07+00:00

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"