5 Replies - 4863 Views - Last Post: 12 April 2011 - 01:14 PM Rate Topic: -----

#1 OAC_DevTeam   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-April 11

Visual Basic not inserting a row into an Access Database

Posted 06 April 2011 - 02:57 PM

Hi,
I'm working on a program for my company and have hit a wall in my knowledge of Visual Basic since it has been a few years since I've worked in it.

I don't know how to insert a row into an access database. I've tried a bunch of different things I found on other forums and the like, but none of them worked. Right now i'm just using a Connection Object with ExecuteNonQuery() and it doesn't return any errors but it also does not add anything to the database.

I've posted my code below.

Public Class PunchForm

    Private Sub cin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cin.Click
        Try
            'Creates a new connection object
            Dim MyConnObj As New OleDb.OleDbConnection
            'create a new recordset
            Dim myRecSet As New DataSet
            'Creates a new data adapter to handle queries
            Dim dataAdapt As OleDb.OleDbDataAdapter
            'Creates a string to house the SQL query string
            Dim sSQL As String
            Dim Command As New OleDb.OleDbCommand
            'Holds the number of rows returned from a query
            Dim trows As Integer
            'Creates a new object to house the logged in user's information
            Dim persona As New User

            'Create a user from the last form's gathered information
            persona.setUser(LoginForm.usertext.Text)
            persona.setPass(LoginForm.usertext.Text)

            'Set up a connection using the connection object
            MyConnObj.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb;Jet OLEDB:Database Password=Password1"
            MyConnObj.Open()

            'build the select string here
            sSQL = "SELECT * FROM punches WHERE empID LIKE '" & persona.getUser() & "' AND pout IS NULL"
            'Create a new Data Adapter and uses it to fill the recordset
            dataAdapt = New OleDb.OleDbDataAdapter(sSQL, MyConnObj)
            dataAdapt.Fill(myRecSet, "Clock_In_Query")

            'Gets the number of rows returned from the query.
            trows = myRecSet.Tables("Clock_In_Query").Rows.Count
            If trows = 1 Then
                MsgBox("You're already clocked in! Contact your System Administrator if this is not the case.")
            ElseIf trows <= 1 Then
                'THIS IS WHERE THE PROBLEM IS
                'Insert a new row into punches to show the user is clocked in.
                Dim comm_ins As New OleDb.OleDbCommand("Insert into punches([empID], [date], [pin], pdleave) values (@empid, @date, @pin, @pdleave)", MyConnObj)
                comm_ins.Parameters.AddWithValue("@empid", persona.getUser())
                comm_ins.Parameters.AddWithValue("@date", DateValue(Now))
                comm_ins.Parameters.AddWithValue("@pin", TimeValue(Now))
                comm_ins.Parameters.AddWithValue("@pdleave", 0)
                comm_ins.ExecuteNonQuery()

            Else
                'Admin needs to go in and delete any extra punch-ins that
                'haven't been punched out.
                MsgBox("Duplicate Records Found. Contact Your Administrator.")
                MyConnObj.Close()
                Me.Close()
            End If

            'Close the connection to the DB
            MyConnObj.Close()

            'Let the user know they are clocked in.
            MsgBox("Clocked In!")

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub



Any insight would be much appreciated.

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Visual Basic not inserting a row into an Access Database

#2 RedRabbit   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 71
  • Joined: 09-May 10

Re: Visual Basic not inserting a row into an Access Database

Posted 07 April 2011 - 05:07 AM

try this:

Dim comm_ins As New OleDb.OleDbCommand("Insert into [punches](empID, date, pin, pdleave) values (@empid, @date, @pin, @pdleave)", MyConnObj)

 

Was This Post Helpful? 0
  • +
  • -

#3 OAC_DevTeam   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-April 11

Re: Visual Basic not inserting a row into an Access Database

Posted 07 April 2011 - 08:17 AM

View PostRedRabbit, on 07 April 2011 - 05:07 AM, said:

try this:

Dim comm_ins As New OleDb.OleDbCommand("Insert into [punches](empID, date, pin, pdleave) values (@empid, @date, @pin, @pdleave)", MyConnObj)

 


Nope that didn't work either. The reason I had the fields in square brackets originally was because I know that 'date' is a reserved word in VB, and I just wanted to be safe with the rest.

Is there possibly another way to Insert? I've seen multiple ways described online, but they all came with errors, and this way didn't.
Was This Post Helpful? 0
  • +
  • -

#4 OAC_DevTeam   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-April 11

Re: Visual Basic not inserting a row into an Access Database

Posted 12 April 2011 - 08:39 AM

Well, I figured it out, I'm entirely sure what's different about this, but it works. I have another column for punch out time that I wasn't inserting anything into, so I tried inserting a null value, and it appears to work!

Here's the code:
'ADODB Connection setup, and insert command executed
                Dim SQLline As String
                Dim conn As ADODB.Connection
                conn = New ADODB.Connection
                conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Shared Programs\Times\Timeclock\Times\db1.mdb;Jet OLEDB:Database Password=Password1"
                conn.Open()
                SQLline = "INSERT INTO [punches](empID, [date], pin, pout, pdleave) values ('" & persona.getUser() & "', '" & DateValue(Now) & "', '" & TimeValue(Now) & "', null, 0)"
                conn.Execute(SQLline)
                conn.Close()



Thanks for your help RedRabbit.
Was This Post Helpful? 0
  • +
  • -

#5 minalira143   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 12-April 11

Re: Visual Basic not inserting a row into an Access Database

Posted 12 April 2011 - 11:31 AM

Did you try not to include entity name in your table where you want to insert values?

Like: "Insert into [table] values ([attribute],[attribute],[attribute],etc)"

I don't know if this will help. I've encounter the same problem like yours before.

try this site: http://www.sqlcourse.com/index.
Was This Post Helpful? 0
  • +
  • -

#6 OAC_DevTeam   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-April 11

Re: Visual Basic not inserting a row into an Access Database

Posted 12 April 2011 - 01:14 PM

Alright, now the insert is working, and I wrote a virtual carbon copy of it for the clock out function, with an update instead of insert. It works, but only if it is the first one clicked. Basically, when the form loads it shows two buttons "Clock In" and "Clock Out". The new problem is that if I click "Clock Out" I then can only clock out, unless I reload the program, and if I click "Clock In" I can only clock in until I reload the program.

Here's the code, so you can see what I've done.
Public Class PunchForm

    Private Sub cin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cin.Click
        Try
            'Creates a new connection object
            Dim MyConnObj As New OleDb.OleDbConnection
            'create a new recordset
            Dim myRecSet As New DataSet
            'Creates a new data adapter to handle queries
            Dim dataAdapt As OleDb.OleDbDataAdapter
            'Creates a string to house the SQL query string
            Dim sSQL As String
            Dim Command As New OleDb.OleDbCommand
            'Holds the number of rows returned from a query
            Dim trows As Integer
            'Creates a new object to house the logged in user's information
            Dim persona As New User

            'Create a user from the last form's gathered information
            persona.setUser(LoginForm.usertext.Text)
            persona.setPass(LoginForm.usertext.Text)

            'Set up a connection using the connection object
            MyConnObj.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb;Jet OLEDB:Database Password=Password1"
            MyConnObj.Open()

            'build the select string here
            sSQL = "SELECT * FROM punches WHERE empID LIKE '" & persona.getUser() & "' AND pout IS NULL"
            'Create a new Data Adapter and uses it to fill the recordset
            dataAdapt = New OleDb.OleDbDataAdapter(sSQL, MyConnObj)
            dataAdapt.Fill(myRecSet, "Clock_In_Query")

            'Gets the number of rows returned from the query.
            trows = myRecSet.Tables("Clock_In_Query").Rows.Count
            If trows = 1 Then
                MsgBox("You're already clocked in! Contact your System Administrator if this is not the case.")
            ElseIf trows <= 1 Then
                'ADODB Connection setup, and insert command executed
                Dim SQLline As String
                Dim conn As ADODB.Connection
                conn = New ADODB.Connection
                conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Shared Programs\Times\Timeclock\Times\db1.mdb;Jet OLEDB:Database Password=Password1"
                conn.Open()
                SQLline = "INSERT INTO [punches](empID, [date], pin, pout, pdleave) values ('" & persona.getUser() & "', '" & DateValue(Now) & "', '" & TimeValue(Now) & "', Null, 0)"
                conn.Execute(SQLline)
                conn.Close()

                'Let the user know they are clocked in.
                MsgBox("Clocked In!")
            Else
                'Admin needs to go in and delete any extra punch-ins that
                'haven't been punched out.
                MsgBox("Duplicate Records Found. Contact Your Administrator.")
                MyConnObj.Close()
                Me.Close()
            End If

            'Close the connection to the DB
            MyConnObj.Close()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub

    Private Sub cout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cout.Click
        Try
            'Creates a new connection object
            Dim MyConnObjo As New OleDb.OleDbConnection
            'create a new recordset
            Dim myRecSeto As New DataSet
            'Creates a new data adapter to handle queries
            Dim dataAdapto As OleDb.OleDbDataAdapter
            'Creates a string to house the SQL query string
            Dim sSQLo As String
            Dim Commando As New OleDb.OleDbCommand
            'Holds the number of rows returned from a query
            Dim trowso As Integer
            'Creates a new object to house the logged in user's information
            Dim persona As New User

            'Create a user from the last form's gathered information
            persona.setUser(LoginForm.usertext.Text)
            persona.setPass(LoginForm.usertext.Text)

            'Set up a connection using the connection object
            MyConnObjo.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb;Jet OLEDB:Database Password=Password1"
            MyConnObjo.Open()

            'build the select string here
            sSQLo = "SELECT * FROM punches WHERE empID LIKE '" & persona.getUser() & "' AND pout IS NULL"
            'Create a new Data Adapter and uses it to fill the recordset
            dataAdapto = New OleDb.OleDbDataAdapter(sSQLo, MyConnObjo)
            dataAdapto.Fill(myRecSeto, "Clock_Out_Query")

            'Gets the number of rows returned from the query.
            trowso = 0
            trowso = myRecSeto.Tables("Clock_Out_Query").Rows.Count
            If trowso = 1 Then
                'ADODB Connection setup, and insert command executed
                Dim SQLline As String
                Dim conn As ADODB.Connection
                conn = New ADODB.Connection
                conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Shared Programs\Times\Timeclock\Times\db1.mdb;Jet OLEDB:Database Password=Password1"
                conn.Open()
                SQLline = "UPDATE punches SET pout = '" & TimeValue(Now) & "' WHERE empID LIKE '" & persona.getUser() & "' AND pout IS NULL"
                conn.Execute(SQLline)
                conn.Close()

                'Let the user know they are clocked in.
                MsgBox("Clocked Out!")
            ElseIf trowso < 1 Then
                MsgBox("You aren't clocked in yet! Contact your System Administrator if this is not the case.")
            Else
                'Admin needs to go in and delete any extra punch-ins that
                'haven't been punched out.
                MsgBox("Duplicate Records Found. Contact Your Administrator.")
                MyConnObjo.Close()
                Me.Close()
            End If

            'Close the connection to the DB
            MyConnObjo.Close()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try
    End Sub



I'm thinking that maybe it has something to do with variables lasting across the form instance of something like that.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1