Hello guys,
I'm working on my code vb.net to save data into access database.
I'm kinda lost right now. Couldn't find the solution to the error. it highlight on dbcommand.ExecuteNonQuery() as state as the title problem.
Here's my code.
Appreaciate the help.

`

Imports System.Data.OleDb
Imports System.String
Imports System.Windows.Forms
Imports System.Security
Imports System.IO
Imports System.Text.RegularExpressions

Public Class Form1

    Dim connection As New OleDbConnection
    Dim command As New OleDbCommand
    Dim dbcommand As New OleDbCommand
    Dim Schomasys As New OleDbDataAdapter
    Dim sms As New DataTable

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

        'Database access connection'
        connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Environment.CurrentDirectory & "\Schomasys.accdb;Persist Security Info=False;"

    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Savebtn.Click

        Dim name As String = ""
        Dim icno As String = ""
        Dim gender As String = ""
        Dim uni As String = ""
        Dim address As String = ""
        Dim state As String = ""
        Dim contactno As String = ""
        Dim email As String = ""
        Dim costcenter As String = ""
        Dim hm As String = ""
        Dim g1name As String = ""
        Dim g1icno As String = ""
        Dim g1add As String = ""
        Dim g2name As String = ""
        Dim g2icno As String = ""
        Dim g2add As String = ""
        Dim rating As String = ""
        Dim appform As String = ""
        Dim cert As String = ""
        Dim ic As String = ""
        Dim g1ic As String = ""
        Dim g2ic As String = ""

        If txtname.Text <> "" Then
            If txticno.Text <> "" Then
                If (radMale.Checked = True Or radFemale.Checked = True) Then
                    If txtuni.Text <> "" Then
                        If txtaddress.Text <> "" Then
                            If combostate.Text <> "Select" Then
                                If txtcontactno.Text <> " " Then
                                    If txtemail.Text <> "" And validemail.Text <> "Invalid Email" Then
                                        If txtcost.Text <> "" Then
                                            If txthm.Text <> "" Then
                                                If txtg1name.Text <> "" Then
                                                    If txtg1icno.Text <> "" Then
                                                        If txtg1add.Text <> "" Then
                                                            If txtg2name.Text <> "" Then
                                                                If txtg2icno.Text <> "" Then
                                                                    If txtg2add.Text <> "" Then

                                                                        'Checked List'
                                                                        If chcrating.Checked = True Then
                                                                            rating = "Yes"
                                                                        Else
                                                                            rating = "No"
                                                                        End If
                                                                        If chcappf.Checked = True Then
                                                                            appform = "Yes"
                                                                        Else
                                                                            appform = "No"
                                                                        End If
                                                                        If chccert.Checked = True Then
                                                                            cert = "Yes"
                                                                        Else
                                                                            cert = "No"
                                                                        End If
                                                                        If chcic.Checked = True Then
                                                                            ic = "Yes"
                                                                        Else
                                                                            ic = "No"
                                                                        End If
                                                                        If chcg1ic.Checked = True Then
                                                                            g1ic = "Yes"
                                                                        Else
                                                                            g1ic = "No"
                                                                        End If
                                                                        If chcg2ic.Checked = True Then
                                                                            g2ic = "Yes"
                                                                        Else
                                                                            g2ic = "No"

                                                                        End If


                                                                        'put the data into the variable'
                                                                        name = txtname.Text
                                                                        icno = txticno.Text
                                                                        If radMale.Checked = True Then
                                                                            gender = "Male"
                                                                        Else
                                                                            gender = "Female"
                                                                        End If

                                                                        uni = txtuni.Text
                                                                        address = txtcontactno.Text
                                                                        state = combostate.SelectedItem
                                                                        contactno = txtcontactno.Text
                                                                        email = txtemail.Text
                                                                        costcenter = txtcost.Text
                                                                        hm = txthm.Text
                                                                        g1name = txtg1name.Text
                                                                        g1icno = txtg1icno.Text
                                                                        g1add = txtg1add.Text
                                                                        g2name = txtg2name.Text
                                                                        g2icno = txtg2icno.Text
                                                                        g2add = txtg2add.Text

                                                                        'End put data into variable'

                                                                        connection.Open()
                                                                        dbcommand.CommandText = "INSERT INTO Personal_Details(Student_Name,University,IC_Number, Gender,Address, State, Contact_Number, Email_Address,Cost_Center,Hiring_Manager, G1_Name, G1_Address, G1_IC_Number, G2_Name, G2_Address, G2_IC_Number, Rating_Sheet, App_Form, Certs, IC, G1_IC, G2_IC) VALUES ('" & name & "','" & uni & "','" & icno & "','" & gender & "','" & address & "','" & state & "','" & contactno & "','" & email & "','" & costcenter & "','" & hm & "','" & g1name & "','" & g1add & "','" & g1icno & "','" & g2name & "','" & g2add & "','" & g2icno & "','" & rating & "','" & appform & "','" & cert & "','" & ic & "','" & g1ic & "','" & g2ic & "')"
                                                                        dbcommand.Connection = connection
                                                                        dbcommand.ExecuteNonQuery()

                                                                        'insert data into the database

                                                                        MsgBox("Details successfully added!", MsgBoxStyle.Information)

                                                                        'Clear all the data in the textbox
                                                                        txtname.Text = ""
                                                                        txtuni.Text = ""
                                                                        txticno.Text = ""
                                                                        radMale.Checked = False
                                                                        radFemale.Checked = False
                                                                        txtaddress.Text = ""
                                                                        combostate.Text = "Select"
                                                                        txtcontactno.Text = ""
                                                                        txtemail.Text = ""
                                                                        txtcost.Text = ""
                                                                        txthm.Text = ""
                                                                        txtg1name.Text = ""
                                                                        txtg1add.Text = ""
                                                                        txtg1icno.Text = ""
                                                                        txtg2name.Text = ""
                                                                        txtg2add.Text = ""
                                                                        txtg2icno.Text = ""
                                                                        chcrating.Checked = False
                                                                        chcappf.Checked = False
                                                                        chccert.Checked = False
                                                                        chcic.Checked = False
                                                                        chcg1ic.Checked = False
                                                                        chcg2ic.Checked = False

                                                                        connection.Close()
                                                                        Form1_Load(sender, e)

                                                                    Else
                                                                        MsgBox("Plase fill in Guarantor 2 Address")
                                                                        txtg2icno.Focus()
                                                                    End If
                                                                Else
                                                                    MsgBox("Guarantor 2 IC Number is not yet filled")
                                                                    txtg2add.Focus()
                                                                End If

                                                            Else
                                                                MsgBox("Plase enter Guarantor 2 Name")
                                                                txtg2name.Focus()
                                                            End If
                                                        Else
                                                            MsgBox("Plase fill in Guarantor 1 Address")
                                                            txtg1icno.Focus()
                                                        End If
                                                    Else
                                                        MsgBox("Guarantor 2 IC Number is not yet filled")
                                                        txtg1add.Focus()
                                                    End If
                                                Else
                                                    MsgBox("Plase enter Guarantor 1 Name")
                                                    txtg1name.Focus()
                                                End If
                                                            Else
                                                                MsgBox("Please fill in the hiring manager name")
                                                                txthm.Focus()
                                                            End If
                                                        Else
                                                            MsgBox("Kindly fill in the cost center number. Just put no if you doesn't have it")
                                                            txtcost.Focus()
                                                        End If
                                                    Else
                                                        MsgBox("Incorrect Email")
                                                        txtemail.Focus()
                                                    End If
                                                Else
                                                    MsgBox("Please enter correct phone number")
                                                    txtcontactno.Focus()
                                                End If
                                            Else
                                                MsgBox("Please select the state")
                                                combostate.Focus()
                                            End If
                                        Else
                                            MsgBox("Please insert the address")
                                            txtaddress.Focus()
                                        End If
                                    Else
                                        MsgBox("No university details! Please fill in")
                                        txtuni.Focus()
                                    End If
                                Else
                                    MsgBox("Gender is not yet checked")
                                End If
                            Else
                                MsgBox("Please enter the IC Number")
                                txticno.Focus()
                            End If
                        Else
                            MsgBox("No name details detected, please enter")
                            txtname.Focus()
                        End If


    End SubInline Code Example Here

`

victor31 commented: hi you just need to refresh your database and your server and your done.. +0

Recommended Answers

All 5 Replies

First are you sure there is definately a table named Personal_Details in your database?

If so try using [Personal_Details] instead of just Personal_Details access may not like the table name not being bracketed off.

Also, (because I know others will point this out,) it is a bit of a security risk not to use parameterised queries when building your SQL string - you run the risk of a SQL injection attack.

Can this not be put into a loop?!

What if you created an array of all the fields and then looped through them validating/sanitizing the data?

Yes, I have checked many times and the table is there..
i have try using [Personal_Details] but the error still appear.
So, i should change in access?

In order to simplify your code you might put the "please enter" text into the Tag property of each textbox, then you could write a generic routine that checks for blank fields. Something like

Private Function IsBlank (txt as TextBox)
    If txt.Text = "") Then
        MsgBox(txt.Tag)
        txt.Focus
        Return True
    End If

    Return False
End Sub

Then your posted code would look like

If IsBlank(txtname) Then Exit Sub
If IsBlank(txticno) Then Exit Sub
etc.

Or if you have all of the text fields collected in a groupbox you could do

For Each txt As TextBox In grpInput.Controls.OfType(Of TextBox)()
    If txt.Text = "" Then
        MsgBox(txt.Tag)
        txt.Focus()
        Exit Sub
    End If
Next

i have check again my access.. thanks all for the input.

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.