Data not inserting into MS Access database

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 1468 Views - Last Post: 21 February 2016 - 07:28 AM Rate Topic: -----

#1 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Data not inserting into MS Access database

Posted 19 February 2016 - 11:47 AM

Hi there

I am trying to save form data to 4 different tables in a MS Access Database, but out of the 4 tables, only 2 tables the data is inserted into. I would appreciate any assistance in this issue, please.

My code below:

Private Sub AddStudent()

        ' Adds parameters 
        ' Student Details Table - the data does NOT get inserted into this table
        Access.AddParam("@track", TrackNum.Text)
        Access.AddParam("@idnum", IDNum.Text)
        Access.AddParam("@rdate", RegDate.Text)
        If MaleBtn.Checked = True Then
            MaleBtn.Text = "Male"
            Access.AddParam("@gen", MaleBtn.Text)
        Else
            If FemaleBtn.Checked = True Then
                FemaleBtn.Text = "Female"
                Access.AddParam("@gen", FemaleBtn.Checked)
            End If
        End If
        Access.AddParam("@init", StuInit.Text)
        Access.AddParam("@name", StuName.Text)
        Access.AddParam("@secname", StuSecName.Text)
        Access.AddParam("@surname", StuSurname.Text)
        Access.AddParam("@ethnic", EthnicityBox.GetItemText(EthnicityBox.SelectedItem))
        Access.AddParam("@bdate", BirthDate.Value.ToShortDateString)
        Access.AddParam("@age", AgeCalc.Text)
        Access.AddParam("@citiz", CitizenBox.Text)
        Access.AddParam("@primlang", PrimLangCombo.GetItemText(PrimLangCombo.SelectedItem))
        Access.AddParam("@seclang", SecLangCombo.GetItemText(SecLangCombo.SelectedItem))
        Access.AddParam("@primdis", PrimDisCombo.GetItemText(PrimDisCombo.SelectedItem))
        Access.AddParam("@secdis", SecDisCombo.GetItemText(SecDisCombo.SelectedItem))
        Access.AddParam("@weight", WeightCalc.Text)

        ' Executes Insert Command
        Access.ExecQuery("INSERT INTO StuDetails (TrackingNum, IDNum, RegDate, Gender, StuInitials, StuName, StuSecName, StuSurname, StuEthnicity, StuBirthdate, StuAge, StuCitizenship, StuPrimLang, StuSecLang, PrimDisabiility, SecDisability, Weighting) " &
        "VALUES (@track, @idnum, @rdate, @gen, @init, @name, @secname, @surname, @ethnic, @bdate, @age, @citiz, @primlang, @seclang, @primdis, @secdis, @weight); ")


        'Student Address Table - This one seems to work, the data is stored in here just fine
        Access.AddParam("@track", TrackNum.Text)
        Access.AddParam("@name", StuName.Text)
        Access.AddParam("@surname", StuSurname.Text)
        Access.AddParam("@add", StuAddress.Text)
        Access.AddParam("@subb", StuSuburb.Text)
        Access.AddParam("@city", StuCity.Text)
        Access.AddParam("@prov", ProvCombo.GetItemText(ProvCombo.SelectedItem))
        Access.AddParam("@pcode", StuPCode.Text)
        Access.AddParam("@country", StuCountry.Text)

        Access.ExecQuery("INSERT INTO StuAddress (TrackingNum, StuName, StuSurname, StuAddress, StuSuburb, StuCity, StuProvince, StuPCode, StuCountry) " &
                          "VALUES (@track, @name, @surname, @add, @subb, @city, @prov, @pcode, @country); ")

        'Student Contact Table - This one does NOT work for some reason
        Access.AddParam("@track", TrackNum.Text)
        Access.AddParam("@name", StuName.Text)
        Access.AddParam("@surname", StuSurname.Text)
        Access.AddParam("@home", StuHomeTel.Text)
        Access.AddParam("@cell", StuCellTel.Text)
        Access.AddParam("@email", StuEmail.Text)
        Access.AddParam("@telemerg", StuEmergNum.Text)
        Access.AddParam("@emergcont", StuEmergCont.Text)

        Access.ExecQuery("INSERT INTO StuContact (TrackingNum, StuName, StuSurname, StuHomeTel, StuCellTel, StuEmail, StuEmergCont, StuEmergContName) " &
                          "VALUES (@track, @name, @surname, @home, @cell, @email, @telemerg, @emergcont); ")

 ' Reports and aborts any errors
        If Not String.IsNullOrEmpty(Access.Exception) Then
            MsgBox(Access.Exception) : Exit Sub
        End If
        ' Success Message 
        MsgBox("Student added successfully!")
    End Sub



The data doesn't get stored in the Student Details Table and the Student Contacts Table. But the Student Address Table code works just fine, the data is stored in it fine. The code is almost the same, so I don't know why it is not working. Can anyone here please guide me on where I am going wrong, and could show me what the problem is?

[The database connection details are stored in a separate class file, and seems to be working fine, as 2 tables out of the 4 are storing the data.]

Is This A Good Question/Topic? 0
  • +

Replies To: Data not inserting into MS Access database

#2 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6602
  • View blog
  • Posts: 26,897
  • Joined: 12-December 12

Re: Data not inserting into MS Access database

Posted 19 February 2016 - 12:29 PM

Does Access.Exception return anything? Is it possible that your Access class is hiding Exception details?

What primary keys do you have for each table? What data types are they and how do you obtain them? Do you have any unique indexes as well?

(Why are you repeating the student's name and surname throughout? The reasoning behind a relational database is that you should not be repeating these details, using a unique value to identify the student.)
Was This Post Helpful? 0
  • +
  • -

#3 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 19 February 2016 - 01:32 PM

View Postandrewsw, on 19 February 2016 - 07:29 PM, said:

Does Access.Exception return anything? Is it possible that your Access class is hiding Exception details?


Yes, Access.Exception returns a message.

Quote

What primary keys do you have for each table? What data types are they and how do you obtain them? Do you have any unique indexes as well?


TrackingNum is the primary key for each table. IDNum is the only unique index. Could it be the fact that each of the tables uses the same primary key that could be causing this problem?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6602
  • View blog
  • Posts: 26,897
  • Joined: 12-December 12

Re: Data not inserting into MS Access database

Posted 19 February 2016 - 01:38 PM

Quote

Yes, Access.Exception returns a message.

What message?
Was This Post Helpful? 0
  • +
  • -

#5 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 19 February 2016 - 02:45 PM

It is coded in a separate class

Public Exception As String

Public Sub ExecQuery(Query As String)

    Exception = ""
    Try

        DBCon.Open()

        DBCmd = New OleDbCommand(Query, DBCon)

        Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    Catch ex As Exception
        Exception = ex.Message
    End Try


Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6602
  • View blog
  • Posts: 26,897
  • Joined: 12-December 12

Re: Data not inserting into MS Access database

Posted 19 February 2016 - 03:20 PM

Again, what is the error message?
Was This Post Helpful? 0
  • +
  • -

#7 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 02:48 AM

The Exception string stores the error message when the code runs [if there are any errors]. I tested this out by putting in the incorrect table name, and a message came up saying, "The Microsoft Access database engine cannot find the input table or query 'StudentDets'. Make sure it exists and that its name is spelled correctly."
Was This Post Helpful? 0
  • +
  • -

#8 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 04:30 AM

When typing out the query, does the column names of the database table have to be entered in order? Cause I entered the columns names in the order of the textboxes on the form and not in the order od the DB table names. Could this be the problem?
Was This Post Helpful? 0
  • +
  • -

#9 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 04:55 AM

So I thought maybe the one table is too big, so I broke it up into two tables. And that's not helping. I tried entering in the column names in order of the DB column names, still no luck with that either. I checked my spelling is correct including the uppercase & lowercase characters, and still no luck...
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6602
  • View blog
  • Posts: 26,897
  • Joined: 12-December 12

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 04:56 AM

The column order doesn't matter.

You indicated there was an error message:

Quote

Yes, Access.Exception returns a message.

Or did you mean that Access.Exception "would" return an error message, but didn't? Please clarify whether there was an error message or not.

I'll leave someone else to pursue this with you though.
Was This Post Helpful? 0
  • +
  • -

#11 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 06:32 AM

Sorry, I meant, Access.Exception WOULD return an error message if there was an error. But it DIDN'T return an error message when the application runs. So I'm assuming there was no error found?

This post has been edited by andrewsw: 20 February 2016 - 06:33 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#12 PeterH   User is offline

  • D.I.C Regular

Reputation: 60
  • View blog
  • Posts: 289
  • Joined: 03-September 09

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 07:19 AM

Noticed you are setting Access.AddParam("@gen", ??) on lines 10 and 14, differently?
Was This Post Helpful? 0
  • +
  • -

#13 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 09:35 AM

Yeah, I saw that earlier and corrected it. It's still not saving all the data in 2 of the tables.
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw   User is offline

  • Entwickler
  • member icon

Reputation: 6602
  • View blog
  • Posts: 26,897
  • Joined: 12-December 12

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 10:03 AM

I will add something further: Your ExecQuery method isn't executing the query. It also shouldn't use the word Exception as an identifier for the string, there is already an Exception class.

I would side-step your Access library for the moment and just manually build and execute the statements.

There is also a debugging tutorial linked in my signature below. Take this and you will be able to step through your code and get a better idea of where it goes wrong (and whether certain code is reached).
Was This Post Helpful? 0
  • +
  • -

#15 Slinky   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 70
  • Joined: 14-April 14

Re: Data not inserting into MS Access database

Posted 20 February 2016 - 01:14 PM

Okay, I tried debugging it. I ran each section of code separately, and got these 2 error messages for the 2 tables:
*For the Student Details table, it says, Data type mismatch in criteria expression - I checked all my data types, and they seem to be fine. There are 4 number columns, 6 short text columns and 2 date/time fields, which I have made sure my dates are converted to short date string:
        Access.AddParam("@rdate", RegDate.Value.ToShortDateString)
        Access.AddParam("@rdate", BirthDate.Value.ToShortDateString)



*The second table, Student Contacts Table, I get a message that says overflow
I googled the message and this came up:
To correct this error
Make sure that results of assignments, calculations, and data type conversions are not too large to be represented within the range of variables allowed for that type of value, and assign the value to a variable of a type that can hold a larger range of values, if necessary.
Make sure assignments to properties fit the range of the property to which they are made.
Make sure that numbers used in calculations that are coerced into integers do not have results larger than integers.
I checked all this out, and it still sends the same message.

So I really have no idea why it's sending me these error messages. In MS Access DB, I used short text. Should I be using Long Text for my text fields instead ?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2