OleDb Connection

Tejal Pathak · March 17th, 2012, 07:37 AM

Need help to save (Insert ) record in access Database, Delete, edit records in database using OleDb command.
I tried On line Example of Insert Command of Vb 2010 book which is not working.
Apart from this i want very attractive login form for Vb 2010.
This is my first software in VB 2010, so If u have solution then pl. help me out.
Below is code to save record ,throw exception "error saving record"

Code:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim i As Integer
        With GridProdDet
            .Update()
            If AddFlg = True Then
                Dim str As OleDbConnection
                Dim cmdSaveProdTube As OleDbCommand
                Str = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\TubeProd\production.mdb;")
                str.Close()
                str.Open()
                cmdSaveProdTube = New OleDbCommand
                For i = 1 To .Rows.Count - 1

                    With cmdSaveProdTube
                        .Connection = str
                        .CommandText = "Insert into prod_trn (mach_no,main_hd, part_no,Partdesc" & _
                            " Values(@Mach_no, @main_hd, @part_no, @partdesc),str"
                        .Parameters.AddWithValue("@Mach_no", Me.GridProdDet.Rows(i).Cells(0).Value)
                        .Parameters.AddWithValue("@main_hd", Me.GridProdDet.Rows(i).Cells(1).Value)
                        .Parameters.AddWithValue("@part_no", Me.GridProdDet.Rows(i).Cells(2).Value)
                        .Parameters.AddWithValue("@partdesc", Me.GridProdDet.Rows(i).Cells(3).Value)

                    End With
                    Try
                        cmdSaveProdTube.ExecuteNonQuery()
                        MsgBox("Successfully Save!", MsgBoxStyle.Information, "")
                    Catch ex As Exception
                        MsgBox("Error Saving Records", vbExclamation, "")
                    End Try
                    str.Close()
                Next i
                ' Call InsertRecords(CInt(.Item(0, i).Value), .Item(1, i).ToString, .Item(2, i).ToString, .Item(3, i).ToString, .Item(4, i).Value, CInt(.Item(5, i).Value), CInt(.Item(6, i).Value), .Item(7, i).ToString, CInt(.Item(8, i).Value), CInt(.Item(9, i).Value), CInt(.Item(13, i).Value), CInt(.Item(14, i).Value))
            Else
                ' Call UpdateRecords(.Item(0, i).Value, .Item(1, i).Value, .Item(2, i).Value, .Item(3, i).Value, .Item(4, i).Value, .Item(5, i).Value, .Item(6, i).Value, .Item(7, i).Value, .Item(13, i).Value, .Item(9, i).Value)
            End If
        End With
    End Sub

RickBritt · April 3rd, 2012, 04:16 PM

I am new as well but I may be able to help you work through the issue

I would try
1. Replace the passed references in the .parameter.addwithvalue( with a fixed string like "abcd", number, date or what ever datatype is appropriate
2.Change msgbox by removing "Error Saving Records" and instead report ex so we can see the exact error produced

Code:

MsgBox(ex.Message, MsgBoxStyle.Critical)

3. set a debug breakpoint at the "Dim i as Integer" and use f11 to set through code. This can be very revealing

Tejal Pathak · April 4th, 2012, 07:32 AM

thanks for replying.

I have one more problem if u can help me..

Wants to add new row and delete row to data bounded grid.
data is bounded through tools from tool box, binding source ,data set.
Whatever changes made is upto data set only, not able to save it to database.

I searched and found that new row can be added to data source directly. but How?...

RickBritt · April 4th, 2012, 09:21 PM

Below are a few Subs that I created to acheave similar results
You will notice I used a menu bar and linked the subs to the menu options.
Button or menu just double click on button or menu option to go to source code view and a sub will be created for you. Copy and paste the code inside the subs below.
You can search help for more about functions run in subs
Let me know if this helped.

Code:

    Private Sub SaveChangesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveChangesToolStripMenuItem.Click
        ' sub to save changes
        Try
            Me.Validate()  
            Me.StatusCodesBindingSource.EndEdit()
            Me.StatusCodesTableAdapter.Update(Me.SatusCodeDataSet.StatusCodes)
            MsgBox("Update successful")
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

  
    Private Sub ExitWithoutSavingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitWithoutSavingToolStripMenuItem.Click
        'sub to close no save exicuted
        Me.Close()
    End Sub

   
    Private Sub HelpToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HelpToolStripMenuItem.Click
       'This sub opens a form named EditStatusCodeHelp to show a help screen
        EditStatusCodeHelp.Show()
    End Sub

    Private Sub UndoChangesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UndoChangesToolStripMenuItem.Click
        'TODO: This line of code loads data back into the 'StatusCodeDataSet.StatusCodes' table. Undoing last edit since last save changes
        Me.StatusCodesTableAdapter.Fill(Me.SatusCodeDataSet.StatusCodes)
    End Sub