Vb 2010 and access 2010 - inserting a record updates current record

[brianmac]

Hi,
first, i hope this is being posted in the correct forum, if not, my apologies!!!!

Running vb 2010 and using access accdb. I have a program that when I insert a new record it updates the current record. Been searching the web all day. Please forgive my code, this is the first VB program I have written(if you want to call this slop a program) in 8 years. This is a whole new ballgame for me.

Thanks for any help!
Brian

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cn As New OleDb.OleDbConnection
        Dim cmd As OleDb.OleDbCommand
        Dim dr As OleDb.OleDbDataReader
        Dim str As String




        Dim stDate As String = DateTimePicker1.Text
        Dim dat As Date

        Dim recchk As Integer

        Dim errcode As Integer
        Dim linecheck1 As String = ComboBox1.Text + ComboBox15.Text + Convert.ToString(NumericUpDown1.Value)
        Dim linecheck2 As String = ComboBox2.Text + ComboBox16.Text + Convert.ToString(NumericUpDown2.Value)
        Dim linecheck3 As String = ComboBox3.Text + ComboBox17.Text + Convert.ToString(NumericUpDown3.Value)
        Dim linecheck4 As String = ComboBox4.Text + ComboBox18.Text + Convert.ToString(NumericUpDown4.Value)
        Dim linecheck5 As String = ComboBox5.Text + ComboBox19.Text + Convert.ToString(NumericUpDown5.Value)
        Dim linecheck6 As String = ComboBox6.Text + ComboBox20.Text + Convert.ToString(NumericUpDown6.Value)
        Dim linecheck7 As String = ComboBox7.Text + ComboBox21.Text + Convert.ToString(NumericUpDown7.Value)
        Dim linecheck8 As String = ComboBox8.Text + ComboBox22.Text + Convert.ToString(NumericUpDown8.Value)
        Dim linecheck9 As String = ComboBox9.Text + ComboBox23.Text + Convert.ToString(NumericUpDown9.Value)
        Dim linecheck10 As String = ComboBox10.Text + ComboBox24.Text + Convert.ToString(NumericUpDown10.Value)
        Dim linecheck11 As String = ComboBox11.Text + ComboBox25.Text + Convert.ToString(NumericUpDown11.Value)
        Dim linecheck12 As String = ComboBox12.Text + ComboBox26.Text + Convert.ToString(NumericUpDown12.Value)
        Dim linecheck13 As String = ComboBox13.Text + ComboBox27.Text + Convert.ToString(NumericUpDown13.Value)
        Dim linecheck14 As String = ComboBox14.Text + ComboBox28.Text + Convert.ToString(NumericUpDown14.Value)
        Dim blankpage As String = linecheck1 + linecheck2 + linecheck3 + linecheck4 + linecheck5 + linecheck6 +
        linecheck7 + linecheck8 + linecheck9 + linecheck10 + linecheck11 + linecheck12 + linecheck13 + linecheck14

        TextBox15.Text = ""

        'sunday starting date?
        dat = DateTime.Parse(stDate, Globalization.CultureInfo.CreateSpecificCulture("en-CA"))
        'Must be a sunday
        If dat.DayOfWeek <> DayOfWeek.Sunday Then
            TextBox15.ForeColor = Color.Red
            TextBox15.Text = "ERROR - Date selected must be a sunday"
        Else
            TextBox15.Text = ""
        End If

        'error check before processing records
        If ComboBox29.Text = "" Then
            errcode = 1
            TextBox15.Text = "ERROR - Name cannot be blank"
        ElseIf ComboBox30.Text = "" Then
            errcode = 2
            TextBox15.Text = "ERROR - Division cannot be blank"
        ElseIf blankpage = "00000000000000" Then
            TextBox15.Text = "No Time Entered"
        End If

        If linecheck1 <> "0" And (ComboBox1.Text = "" Or ComboBox15.Text = "" Or (NumericUpDown1.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 1"
        End If
        If linecheck2 <> "0" And (ComboBox2.Text = "" Or ComboBox16.Text = "" Or (NumericUpDown2.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 2"
        End If
        If linecheck3 <> "0" And (ComboBox3.Text = "" Or ComboBox17.Text = "" Or (NumericUpDown3.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 3"
        End If
        If linecheck4 <> "0" And (ComboBox4.Text = "" Or ComboBox18.Text = "" Or (NumericUpDown4.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 4"
        End If
        If linecheck5 <> "0" And (ComboBox5.Text = "" Or ComboBox19.Text = "" Or (NumericUpDown5.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 5"
        End If
        If linecheck6 <> "0" And (ComboBox6.Text = "" Or ComboBox20.Text = "" Or (NumericUpDown6.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 6"
        End If
        If linecheck7 <> "0" And (ComboBox7.Text = "" Or ComboBox21.Text = "" Or (NumericUpDown7.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 7"
        End If
        If linecheck8 <> "0" And (ComboBox8.Text = "" Or ComboBox22.Text = "" Or (NumericUpDown8.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 8"
        End If
        If linecheck9 <> "0" And (ComboBox9.Text = "" Or ComboBox23.Text = "" Or (NumericUpDown9.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 9"
        End If
        If linecheck10 <> "0" And (ComboBox10.Text = "" Or ComboBox24.Text = "" Or (NumericUpDown10.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 10"
        End If
        If linecheck11 <> "0" And (ComboBox11.Text = "" Or ComboBox25.Text = "" Or (NumericUpDown11.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 11"
        End If
        If linecheck12 <> "0" And (ComboBox12.Text = "" Or ComboBox26.Text = "" Or (NumericUpDown12.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 12"
        End If
        If linecheck13 <> "0" And (ComboBox13.Text = "" Or ComboBox27.Text = "" Or (NumericUpDown13.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 13"
        End If
        If linecheck14 <> "0" And (ComboBox14.Text = "" Or ComboBox28.Text = "" Or (NumericUpDown14.Value) = 0) Then
            TextBox15.Text = "Incomplete Line 14"
        End If

        'Good form, write records

        If TextBox15.Text = "" Then


            cn = New OleDb.OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=ServTech_timeSheet.accdb;")
            cn.Open()


            cmd = New OleDb.OleDbCommand("select * from STTranTab", cn)
            dr = cmd.ExecuteReader
            recchk = 0
            If dr.HasRows Then
                While dr.Read()

                    If dr(1) = ComboBox29.Text And dr(2) = Label17.Text Then

                        recchk = 1 ' cannot add record
                        Exit While
                    End If
                End While

            End If
            cn.Close()
            If recchk <> 1 Then
                cn.Open()

                'write records

                ' which records to write

                str = "Insert Into [STTranTab] ([tname], [tstrdate], [tdiv], [tdate], [tcust],[tjobcode], [ttimecode], [thours])" _
                                     + "Values([@tname#], [@tstrdate], [@tdiv], [@tdate], [@tcust],[@tjobcode], [@ttimecode], [@thours])"

                cmd = New OleDb.OleDbCommand
                cmd.CommandText = str
                cmd.Parameters.AddWithValue("@tname#", ComboBox29.Text)
                cmd.Parameters.AddWithValue("@strdate", Label17.Text)
                cmd.Parameters.AddWithValue("@tdiv", ComboBox30.Text)
                cmd.Parameters.AddWithValue("@tdate", "7/23/2011")
                cmd.Parameters.AddWithValue("@tcust", "general mills")
                cmd.Parameters.AddWithValue("@tjobcode", "Office")
                cmd.Parameters.AddWithValue("@ttimecode", "Holiday")
                cmd.Parameters.AddWithValue("@thours", "8.50")


                Try
                    cmd.Connection = cn
                    cmd.ExecuteNonQuery()
                Catch ex As OleDbException
                    MessageBox.Show(ex.Message)

                End Try


                'leave message that timesheet was submitted
                TextBox15.Text = "Timesheet for " + Label17.Text + " has been successfully submitted."
                'clear screen
                Button2.PerformClick()


            Else
                TextBox15.Text = "Timesheet for " + Label17.Text + " has already been submitted."
            End If

            dr.Close()
            cn.Close()


        End If




    End Sub
End Class

 

[Gopher2011]

To start with we need to debug some more.

Your using insert, but the problem may reside with the table name being unclear.
Do a simple sql insert where your string is to test if its the sql connection or your string.

swap this 
str = "Insert Into [STTranTab] ([tname], [tstrdate], [tdiv], [tdate], [tcust],[tjobcode], [ttimecode], [thours])" _
                                     + "Values([@tname#], [@tstrdate], [@tdiv], [@tdate], [@tcust],[@tjobcode], [@ttimecode], [@thours])"

For your version of below in simple SQL. 
str = "INSERT INTO table_name VALUES value1, value2, value3"

If that works we know the problems with the sql, if it does not we know its elsewhere.

 

[Gopher2011]

This is my working example - it contains everything sql wise for the connection.

Public DC As SqlConnection = New SqlConnection("Password=EXAMLPE;Persist Security Info=True;User ID=EXAMLPE;Initial Catalog=EXAMLPE;Data Source=EXAMLPE")
Public DS As New DataSet
Public SC As New SqlCommand

Button 1 code

s_Fr = Format(FR_Date.Value, "yyyy/MM/dd") & " 00:00:00"
s_To = Format(To_Date.Value, "yyyy/MM/dd") & " 23:59:59"

sP_1 As String = "SELECT DISTINCT [Test-Batch], Test.[TEST-AR] , Min(Data.DT) as StartDate, Test.Status, TEST.SV As Version, TEST.Lang As Language FROM Data "
sP_2 = "WHERE (Data.DT BETWEEN @pFr AND @pTo) "
sP_3 As String = "AND (LEN(Test.[TEST-AR]) = 12) GROUP BY [Test-Batch], Test.[TEST-AR], Test.Status, TEST.SW_V, TEST.Lang ORDER BY [Test-Batch]"

sP_Final = sP_1 & sP_2 & sP_3

PopulateTable_FrTo(sP_Final)

And the sub

Public Sub PopulateTable_FrTo(ByVal QueryStr As String)
        DC.Open()
        SC = New SqlCommand(QueryStr, DC)

        SC.Parameters.Add(New SqlParameter("@pFr", SqlDbType.VarChar, 10)).Value = frm_Main.s_Fr
        SC.Parameters.Add(New SqlParameter("@pTo", SqlDbType.VarChar, 10)).Value = frm_Main.s_To

        Using SR As SqlDataReader = SC.ExecuteReader()
            Application.DoEvents()
            DS.Tables.Clear()
            DS.Tables.Add("Main")
            DS.Tables("Main").Load(SR)
            frm_Main.dgv_Table.DataSource = DS.Tables("Main")
        End Using
        SC.Dispose()
        DC.Close()
    End Sub

 

[Gopher2011]

You will not be able to get this working due to my form 1 objects, but hopefully it can show you how to construct paramertierized sql, and compare it with your code.

 

[brianmac]

digesting it now. can't thank you enough for your help!!!!!!
brian

Ps - I will update on how I made out.
Pss - any good beginner vb books that you can recommend?

 

[Gopher2011]

Sadly I cant recommend any VB books (I use google and this forumn), however some admin folks in here are very very helpful, and far more knowledgeable than I in these matters.
They might be able to recommend a book.

jmcilhinney has some good links in his signature that I have used before with sql - Data Walkthrough on the microsoft website.

http://www.vbdotnetforums.com/members/jmcilhinney.html

 

[brianmac]

Syntax error in query expression. I had a couple of typo's. Thank you very much for your time!
Brian