none
I cannot insert data into ms access database from vb.net 2015 RRS feed

  • Question

  • Am developing a School Management System using vb.net 2015 and ms access database as backend. I have written code to insert data into the database from the vb.net forms but when I ran the code, not data is inserted into the database. Please assist me. Here is part of the code:

    If er = 0 ThenTry                cmd.Connection = cn                cmd.CommandText = "INSERT INTO Student(AdmNo,AdmDate,StuName,DOB,Age,Gender,AdmittedTo,RollNo,GuName,PostalAddress,PhoneNo,AltPhoneNo,Relationship,Occupation,EmailAddress,PhysicalAddress,Religion,County) values('" & txtAdmNo.Text & "','" & dtpAdmDate.Text & "','" & txtStudName.Text & "','" & dtpDoB.Text & "','" & txtAge.Text & "','" & cbGender.Text & "','" & cbAdmTo.Text & "','" & txtRollNo.Text & "','" & txtGuNames.Text & "','" & txtPostalAddress.Text & "','" & txtPhoneNo.Text & "','" & txtAltPhoneNo.Text & "','" & txtRelationship.Text & "','" & txtOccupation.Text & "','" & txtEmailAddress.Text & "','" & txtPhysicalAddress.Text & "','" & cbReligion.Text & "','" & cbCounty.Text & "')"                cmd.ExecuteNonQuery()                If pho = 1 Then'Photo Saving                    cmd = NewOleDbCommand                    cmd.CommandText = " INSERT INTO Photo VALUES (@PID, @AdmNo, @SName, @StudImage) "                    cmd.Parameters.AddWithValue("@PID", ph)                    cmd.Parameters.AddWithValue("@SID", txtAdmNo.Text)                    cmd.Parameters.AddWithValue("@SName", txtStudName.Text)                    Photos1 = File.ReadAllBytes(OpenFileDialog1.FileName)                    cmd.Parameters.AddWithValue("@StudImage", Photos1)                    cmd.Connection = cn                    cmd.ExecuteNonQuery()                EndIf'MsgBox("New Class Added.")Dim result AsInteger = MessageBox.Show("New Student Added. Want To Add Another One.""Added"MessageBoxButtons.YesNo)                If result = DialogResult.No ThenMe.Close()                ElseIf result = DialogResult.Yes Then                    ds.Clear()                    txtAdmNo.Clear()                    txtStudName.Clear()                    txtAge.Clear()                    txtRollNo.Clear()                    txtGuNames.Clear()                    txtPostalAddress.Clear()                    txtPhoneNo.Clear()                    txtAltPhoneNo.Clear()                    txtRelationship.Clear()                    txtOccupation.Clear()                    txtEmailAddress.Clear()                    txtPhysicalAddress.Clear()                    cbAdmTo.ResetText()                    cbGender.ResetText()                    cbReligion.ResetText()                    cbCounty.ResetText()                    str = "select * from Student"                    cmd = NewOleDbCommand(str, cn)                    da.SelectCommand = cmd                    da.Fill(ds, "Student")                    LastNo = ds.Tables("Student").Rows.Count + 1                    If LastNo >= 0 Then                        txtAdmNo.Text = "SID" & LastNo                    Else                        txtAdmNo.Text = "SID" & 0                    EndIfEndIfCatch ex AsException                MsgBox("Student Not Added.")                ds.Clear()

    call clear

                                                                                                                          

    Tuesday, November 7, 2017 4:44 PM

All replies

  • See AddNewRow in this code sample

    https://code.msdn.microsoft.com/Adding-new-records-into-53ce3eb1?redir=0


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, November 7, 2017 4:55 PM
    Moderator
  • Some additional thoughts (I was quick with my first reply as I was heading off to a meeting)

    • If the database is in Solution Explorer, was placed there by you adding it. If this is the case, select the database in solution explorer, select properties, look at "Copy to output directory) make sure it's set to copy if newer.
    • ExecuteNonQuery returns an integer representing how many rows were inserted (in this case one), if ExecuteNonQuery returned 1 and you don't see the record this is either an issue as per the first bullet or you are looking at the wrong database. If ExecuteNonQuery returned 0 then the INSERT did fail.
    • If there is a try-catch with an empty catch, make sure to handle the exception e.g. MessageBox.Show(ex.Message).

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, November 7, 2017 6:05 PM
    Moderator
  • Are you getting an exception (error)? If not then you might want to check Karen's link below to see if it's an issue with the Copy to Output Directory property:

    https://code.msdn.microsoft.com/windowsdesktop/Working-with-Copy-to-11fbc251

    Otherwise, if you are getting an error please post it.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, November 7, 2017 6:43 PM
  • Hi Jemoh,

    In your first insert statement, I find there are AdmDate(DateTime), Age(Integer), please convert into the correct value type.

    Sometimes Data Source=|DataDirectory|\... is problematic when debugging. Please bear on mind that you'll have another database in \bin\debug at your project folder when you are debugging your code. Probably you're updating the records in this database instead the original one.

    Try to set an absolute path and check if the records are being updated. You can calculate the number of inserts

    Dim total = cmd.ExecuteNonQuery()

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Monday, November 20, 2017 12:57 AM
    Wednesday, November 8, 2017 2:58 AM
    Moderator