PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2010 ADD,DELETE,UPDATE the records in the access database-VBForums
Page 1 of 2 12 LastLast
Results 1 to 40 of 43

Thread: ADD,DELETE,UPDATE the records in the access database

  1. #1

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    ADD,DELETE,UPDATE the records in the access database

    Hi All,

    I have 7 tables in my access database. i wrote a code to select the data table using the combobox and to display the selected table in the datagridview.

    Now i need to add,edit or update, delete the data into the selected tables. In which the text boxes are common to the all the tables, for ex: if i select the table "Capacitor" in the combobox, whatever the data i entered into the textboxes will go to the "capacitor" tables, likewise other tables. Please anyone help me to resolve this code, i am struck in it for long time, as i am new to vb. Below is my code.

    Public Class Form1

    'To get the tables in the combobox
    Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()

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



    cnn.ConnectionString = ("ConnectionString")
    cnn.Open()
    Me.ComboBox1.DisplayMember = "TABLE_NAME"
    Dim restrictions() As String = New String(3) {}
    restrictions(3) = "Table"
    Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
    BindGrid()
    End Sub

    'To display the selected table in the datagridview
    Private Sub BindGrid()
    Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
    cnn.ConnectionString = ("ConnectionString")
    Dim con As New OleDb.OleDbConnection
    Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
    Dim cmd As New OleDb.OleDbCommand(sSql, cnn)
    cmd.CommandType = CommandType.Text
    Dim sda As New OleDb.OleDbDataAdapter(cmd)
    Dim dt As New DataTable()
    sda.Fill(dt)
    DataGridView1.DataSource = dt
    End Sub


    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    BindGrid()
    End Sub


    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

    If ComboBox1.Text = "Capacitor" Then
    CapacitorBindingsource.AddNew()
    End If

    End Sub

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click

    If ComboBox1.Text = "Capacitor" Then
    CapacitorBindingsource.Removecurrent()

    End If

    End Sub

    End Class
    Last edited by Ashwin975; May 8th, 2019 at 12:21 AM.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Please format your code snippets for readability.
    vb.net Code:
    1. Imports System.Data.OleDb
    2.  
    3. Public Class Form1
    4.  
    5.     'To get the tables in the combobox
    6.  
    7.  
    8.     Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
    9.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    10.         'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Capacitor' table. You can move, or remove it, as needed.
    11.         Me.CapacitorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Capacitor)
    12.  
    13.         cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
    14.         cnn.Open()
    15.         Me.ComboBox1.DisplayMember = "TABLE_NAME"
    16.         Dim restrictions() As String = New String(3) {}
    17.         restrictions(3) = "Table"
    18.         Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
    19.         BindGrid()
    20.  
    21.  
    22.     End Sub
    23.  
    24.     'To display the selected table in the datagridview
    25.  
    26.     Private Sub BindGrid()
    27.  
    28.         Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
    29.         cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
    30.         Dim con As New OleDb.OleDbConnection
    31.         Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
    32.         Dim cmd As New OleDb.OleDbCommand(sSql, cnn)
    33.         cmd.CommandType = CommandType.Text
    34.         Dim sda As New OleDb.OleDbDataAdapter(cmd)
    35.         Dim dt As New DataTable()
    36.         sda.Fill(dt)
    37.         DataGridView1.DataSource = dt
    38.     End Sub
    39.  
    40.     ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    41.     'BindGrid()
    42.     'End Sub
    43.  
    44.     Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    45.         BindGrid()
    46.         ' If ComboBox1.Text = "Capacitor" Then
    47.         ' End If
    48.     End Sub
    49.  
    50.  
    51.  
    52.     Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
    53.         TabControl1.Visible = False
    54.  
    55.         If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
    56.             GroupBox1.Visible = False
    57.             TabControl1.Visible = True
    58.         Else
    59.             MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
    60.             ' TabControl1.Visible = False
    61.             txtPassword.Clear()
    62.             txtUsername.Clear()
    63.             txtUsername.Focus()
    64.  
    65.         End If
    66.     End Sub
    67.  
    68.     Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
    69.         txtPassword.Clear()
    70.         txtUsername.Clear()
    71.  
    72.     End Sub
    73.  
    74.     Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
    75.  
    76.         Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
    77.         result = MsgBox("Are you sure you would like to exit?", vbYesNo)
    78.         If result = DialogResult.Yes Then
    79.             Application.Exit()
    80.         End If
    81.  
    82.     End Sub
    83.  
    84.     Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
    85.         On Error GoTo SaveErr
    86.         If ComboBox1.Text = "Capacitor" Then
    87.             CapacitorBindingsource.EndEdit()
    88.             CapacitorTableAdapter.Update(Mentor_DX_Library_DatabaseDataSet.Capacitor)
    89.         End If
    90.  
    91.         MessageBox.Show("Saved")
    92. SaveErr:
    93.         Exit Sub
    94.     End Sub
    95.  
    96.  
    97.  
    98.     Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    99.  
    100.         Me.txtdes.Text = ""
    101.         Me.txtrohs.Text = ""
    102.         Me.txtmanf.Text = ""
    103.         Me.txtmanfpn.Text = ""
    104.         Me.txtagile.Text = ""
    105.         Me.txttype.Text = ""
    106.         Me.txtpack.Text = ""
    107.         Me.txtvalue.Text = ""
    108.         Me.txtvoltage.Text = ""
    109.         Me.txttolerance.Text = ""
    110.         Me.txttemp.Text = ""
    111.         Me.txtpartnum.Text = ""
    112.         Me.txtcell.Text = ""
    113.         Me.txtsym.Text = ""
    114.         Me.txtagile.Tag = ""
    115.  
    116.     End Sub
    117.  
    118.     Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
    119.  
    120.         If ComboBox1.Text = "Capacitor" Then
    121.             CapacitorBindingsource.AddNew()
    122.         End If
    123.  
    124.     End Sub
    125.  
    126.     Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
    127.  
    128.     End Sub
    129.  
    130.     Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
    131.  
    132.     End Sub
    133.  
    134.  
    135. End Class
    Also, please only post relevant code. You've got two empty event handlers in there so they're obviously not relevant. How much of the rest has nothing to do with the question? The more difficult you make it for us, the less likely you will get the help you want.

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    Here is an example,

    Code:
    Imports System.Data.OleDb
    
    Public Class Form4
        Private con As New OleDbConnection(My.Settings.waterConnectionString)
        Private da As New OleDbDataAdapter()
        Private dt As New DataTable
    
    
        Private Sub Form4_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                con.Open()
    
                Dim restrictions() As String = New String(3) {}
                restrictions(3) = "Table"
                Me.ComboBox1.DisplayMember = "TABLE_NAME"
                Me.ComboBox1.DataSource = con.GetSchema("Tables", restrictions)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Me.DataGridView1.DataSource = Nothing
            dt = New DataTable
            da.SelectCommand = New OleDbCommand("Select * from " & Me.ComboBox1.Text, con)
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
    
        End Sub
    
        Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
            Dim cmdbldr As New OleDbCommandBuilder(da)
            Dim row As DataRow = dt.NewRow
    
            row("Field1") = Me.Field1TextBox.Text
            row("Field2") = Me.Field2TextBox.Text
            dt.Rows.Add(row)
            da.Update(dt)
        End Sub
    
    End Class

  4. #4

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    HI wes4dbt,

    The code doesn't work. Because i have already get the tables and listed through the bindgrid function. I am ending with the error, can you please modify in the same code if possible. Please help me to resolve this one.

  5. #5

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    imcilhinney, i have removed the unwanted code from the post and made code readable. Thank you for sorting out the mistake.

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    First, for me the code does work. It's your job to adapt it to work with your data

    Second, just saying "I am ending with the error" isn't helpful at all. What is the error? What line of code causes the error? Post your current code.

  7. #7

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Error in this statement, I dont know what would be the issue is.

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

    Dim cmdbldr As New OleDbCommandBuilder(da)
    Dim row As DataRow = dt.NewRow

    row("Agile number") = Me.txtagile.Text
    row("Description") = Me.txtdes.Text
    dt.Rows.Add(row)
    da.Update(dt) --> Syntax error in INSERT INTO statement

    End Sub

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Quote Originally Posted by Ashwin975 View Post
    Error in this statement, I dont know what would be the issue is.

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

    Dim cmdbldr As New OleDbCommandBuilder(da)
    Dim row As DataRow = dt.NewRow

    row("Agile number") = Me.txtagile.Text
    row("Description") = Me.txtdes.Text
    dt.Rows.Add(row)
    da.Update(dt) --> Syntax error in INSERT INTO statement

    End Sub
    You have a space in your column name, which is bad. You should always avoid doing so if at all possible. Just as you can't have an identifier (type, member or variable name) that contains spaces in VB, so you can't in SQL either. If it's within your power to do so, fix your database and remove all spaces in column names, i.e. use 'AgileNumber' or 'Agile_number' as you would in VB rather than 'Agile number'. If that's not your call to make then you must wrap your identifiers in brackets. You can make a command builder do that by setting its QuotePrefix and QuoteSuffix properties.

  9. #9

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    I have changed them without the spaces, still i am getting the same error.

    Also if i add the data and clicked the save button, the data didn't saved.



    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

    Dim cmdbldr As New OleDbCommandBuilder(da)
    Dim row As DataRow = dt.NewRow
    If ComboBox1.Text = "Capacitor" Then

    row("Agile_number") = Me.txtagile.Text
    row("Description") = Me.txtdes.Text
    row("Rohs") = Me.txtrohs.Text
    row("Manufacturer") = Me.txtmanf.Text
    row("Manufacturer_Part_number") = Me.txtmanfpn.Text
    'row("Description") = Me.txtagile.Text
    row("Type") = Me.txttype.Text
    row("Pack_type") = Me.txtpack.Text
    row("Value") = Me.txtvalue.Text
    row("Voltage") = Me.txtvoltage.Text
    row("Tolerance") = Me.txttolerance.Text
    row("Temp_Coefficient") = Me.txttemp.Text
    row("Part_number") = Me.txtpartnum.Text
    row("Cell_Name") = Me.txtcell.Text
    row("Symbol") = Me.txtsym.Text
    End If
    dt.Rows.Add(row)
    da.Update(dt) -->Syntax error in INSERT INTO statement.(Also if i removed this line and run, i can able to add the data but it is not saved in the database)
    End Sub

    For the save Process

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

    On Error GoTo SaveErr

    MentorDXLibraryDatabaseDataSetBindingSource.EndEdit()
    MessageBox.Show("Saved")

    SaveErr:

    Exit Sub

    End Sub

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Quote Originally Posted by Ashwin975 View Post
    Also if i removed this line and run, i can able to add the data but it is not saved in the database
    Of course it's not saved, because that's the line that does the saving.

    If you're still getting a syntax error in your without any spaces in the column names then at least one of the columns must be a reserved word. Again, just as you can't use a VB keyword as an identifier without wrapping it in brackets, so you can't do it in SQL either. You can check the documentation for your database to see what reserved words there are but the likely candidates seem to be "Type" and "Value". Again, avoid reserved words in table or column names if you can but there are times where they are the natural choice. In such cases, you just need to escape them in your SQL. As I said before, you can do that by setting the QuotePrefix and QuoteSuffix properties of your command builder.

  11. #11

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Thank you very much, it works now. i have added the commandbuilder prefix and suffix and it got worked now.

    Dim cb As New OleDb.OleDbCommandBuilder(da)
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"
    da.Update(dt)


    For the deletion below code deleting the current row, if i open again it shows it deleted row ?


    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click

    Dim inde As Integer

    inde = DataGridView1.CurrentCell.RowIndex

    Dim result As String '= MessageBox.Show("Are you sure you would like to exit?"

    result = MsgBox("Are you sure you would like to delete current row?", vbYesNo)

    If result = DialogResult.Yes Then

    DataGridView1.Rows.RemoveAt(inde)

    End If
    End Sub

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    If you're not already, bind your DataTable to your DataGridView via a BindingSource. To delete the current row, you call RemoveCurrent on that BindingSource. That will flag the underlying DataRow as Deleted, which what you're doing now does not. That way, the corresponding record will be deleted from the database when you call Update.

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    I updated my example to include Deletes and Edits, there are lots of changes so review it carefully.

    Code:
    Imports System.ComponentModel
    Imports System.Data.OleDb
    
    Public Class Form4
        Private con As New OleDbConnection(My.Settings.waterConnectionString)
        Private da As New OleDbDataAdapter()
        Private dt As New DataTable
        Private cmdbldr As OleDbCommandBuilder
        Private bs As New BindingSource
    
    
        Private Sub Form4_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                con.Open()
    
                Dim restrictions() As String = New String(3) {}
                restrictions(3) = "Table"
                Me.ComboBox1.DisplayMember = "TABLE_NAME"
                Me.ComboBox1.DataSource = con.GetSchema("Tables", restrictions)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Me.DataGridView1.DataSource = Nothing
            bs.DataSource = Nothing
            dt = New DataTable
            da.SelectCommand = New OleDbCommand("Select * from " & Me.ComboBox1.Text, con)
            da.Fill(dt)
    
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs
    
            cmdbldr = New OleDbCommandBuilder(da)
        End Sub
    
        Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
    
            Dim row As DataRow = dt.NewRow
    
            row("Field1") = Me.Field1TextBox.Text
            row("Field2") = Me.Field2TextBox.Text
            dt.Rows.Add(row)
            SaveData()
        End Sub
    
        Private Sub DeleteButton_Click(sender As Object, e As EventArgs) Handles DeleteButton.Click
            bs.RemoveCurrent()
            SaveData()
        End Sub
    
        Private Sub SaveData()
            bs.EndEdit()
            da.Update(dt)
        End Sub
    
        Private Sub Form4_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
            If bs.DataSource IsNot Nothing Then
                bs.EndEdit()
                da.Update(dt)
            End If
        End Sub
    End Class
    As jmc said in post #2, format your code so we can read it easily. You do that by clicking either "VB" or "#" icon from the toolbat and then insert your code between the code tags.

  14. #14

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    yeah right now i can able to the add data. for the deleting i facing this error,


    Private Sub savedata()

    bs.EndEdit()
    da.Update(dt) --> Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.

    End Sub

    Also in the database the table which is having more than 1000 rows, 1001 is in the as first row, the actual row no.1 is below that. After adding row in the database, i couldn't see the rows updated. once i close that and open again, i can see the added row in the database, for these type of tables.

  15. #15
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Please stop posting unformatted code snippets. Have you not noticed how everyone else's code is much more readable, primarily due to indenting. If you can make something bold then you can format it as text.

  16. #16
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Quote Originally Posted by Ashwin975 View Post
    yeah right now i can able to the add data. for the deleting i facing this error,


    Private Sub savedata()

    bs.EndEdit()
    da.Update(dt) --> Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.

    End Sub
    The clue is in the error message. In order to be able to use a command builder, your query must involve a single table and retrieve the primary key for that table. You may also have to set the MissingSchemaAction property of the data adapter to AddWithKey, although I'm not 100% sure that that is necessary.
    Quote Originally Posted by Ashwin975 View Post
    Also in the database the table which is having more than 1000 rows, 1001 is in the as first row, the actual row no.1 is below that. After adding row in the database, i couldn't see the rows updated. once i close that and open again, i can see the added row in the database, for these type of tables.
    Calling Update on a data adapter will simply save the changes in the specified DataTable to the database. If that DataTable is bound to a grid or something then you are already looking at the changes. If you have some other data displayed elsewhere in your app, even if it is from the same source, you won't see that magically change. Data you display only exists locally, even if you retrieved it from a remote location. Changes to that remote location won't be automatically reflected locally. If you want to update the local data then you have to do so explicitly, which is what happens when you close and open anew.

  17. #17

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    jim, even if i changed the primary key in the tables. i'm end up with the error. I don't know what add schema you mean it. can Please edit in the code, if possible.

  18. #18
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Quote Originally Posted by Ashwin975 View Post
    jim, even if i changed the primary key in the tables. i'm end up with the error.
    Then you did it wrong. If you're not going to tell us what you actually did then we can't tell you what'
    Quote Originally Posted by Ashwin975 View Post
    can Please edit in the code, if possible.
    I've told you what to do. Just do it.
    your query must involve a single table and retrieve the primary key for that table. You may also have to set the MissingSchemaAction property of the data adapter to AddWithKey
    There's nothing complicated in that. The first part isn't a code change and the second part is setting one property.

  19. #19
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    What field is your Primary Key?

    It's not uncommon for data not to be displayed in the same order as they are in the database. If you want to retrieve them in a certain order then you use the Order By clause in the Select statement.

    Code:
    Select fld1, fld2 from someTable ORDER BY fld2

  20. #20

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
        Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
    
            bs.RemoveCurrent()
            savedata()
        End Sub
    
      Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    
    
            If bs.DataSource IsNot Nothing Then
    
                bs.EndEdit()
                da.Update(dt)
            End If
    
    
       Private Sub savedata()
    
            bs.EndEdit()
            da.Update(dt)
    
        End Sub
    
        Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
            Dim cmdbldr As New OleDbCommandBuilder(da)
            Dim row As DataRow = dt.NewRow
    
            If ComboBox1.Text = "Capacitor" Then
    
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            End If
    
            dt.Rows.Add(row)
            da.Update(dt)
            savedata()
    
        End Sub
    Above is my code. I set the primary key as the ID in the table, Applies for all the tables. If i try to change that, i cannot able to save the database in access.

  21. #21
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    Do you retrieve the ID field with your Select statement?

    This shouldn't be in the Add routine,
    Code:
    Dim cmdbldr As New OleDbCommandBuilder(da)
    Not if your trying to follow my example.

    You have to have more code than what you posted.

  22. #22

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
    Imports System.Data.OleDb
    
    Public Class Form1
    
        'To get the tables in the combobox
    
    
        Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
        Private da As New OleDbDataAdapter
        Private dt As New DataTable
        Private bs As New BindingSource
        Private cmdbldr As OleDbCommandBuilder
    
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Inductor' table. You can move, or remove it, as needed.
            Me.InductorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Inductor)
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Connector' table. You can move, or remove it, as needed.
            Me.ConnectorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Connector)
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Resistor' table. You can move, or remove it, as needed.
            Me.ResistorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Resistor)
    
            cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
            Try
                cnn.Open()
                Dim restrictions() As String = New String(3) {}
                restrictions(3) = "Table"
                Me.ComboBox1.DisplayMember = "TABLE_NAME"
                Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
            cnn.Close()
        End Sub
    
        'To display the selected table in the datagridview
    
        ' Private Sub BindGrid()
    
    
        ' End Sub
    
        ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'BindGrid()
        'End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    
            Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
            Me.DataGridView1.DataSource = Nothing
            dt = New DataTable
            da.SelectCommand = New OleDbCommand(sSql, cnn)
            da.Fill(dt)
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs
            cmdbldr = New OleDbCommandBuilder(da)
    
        End Sub
    
    
        Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
            Dim cmdbldr As New OleDbCommandBuilder(da)
            Dim row As DataRow = dt.NewRow
    
            If ComboBox1.Text = "Capacitor" Then
    
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Resistor" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                'row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                'row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Connector" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                'row("Type") = Me.txttype.Text
                'row("Pack_type") = Me.txtpack.Text
                'row("Value") = Me.txtvalue.Text
                'row("Voltage") = Me.txtvoltage.Text
                'row("Tolerance") = Me.txttolerance.Text
                'row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Inductor" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                'row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                'row("Voltage") = Me.txtvoltage.Text
                'row("Tolerance") = Me.txttolerance.Text
                'row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            End If
    
    
            dt.Rows.Add(row)
            'da.FillSchema(dt)
            da.Update(dt)
            savedata()
    
        End Sub
    
        Private Sub savedata()
    
            bs.EndEdit()
            da.Update(dt)
    
        End Sub
    
    
    
        Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
    
    
        End Sub
    
        Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
    
            bs.RemoveCurrent()
            savedata()
        End Sub
    
        Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    
    
            If bs.DataSource IsNot Nothing Then
    
                bs.EndEdit()
                da.Update(dt)
            End If
        End Sub
    
    
    
    
    
    
    
        Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
            TabControl1.Visible = False
    
            If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
                GroupBox1.Visible = False
                TabControl1.Visible = True
            Else
                MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
                ' TabControl1.Visible = False
                txtPassword.Clear()
                txtUsername.Clear()
                txtUsername.Focus()
    
            End If
        End Sub
    
        Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
            txtPassword.Clear()
            txtUsername.Clear()
    
        End Sub
    
        Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
    
            Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
            result = MsgBox("Are you sure you would like to exit?", vbYesNo)
            If result = DialogResult.Yes Then
                Application.Exit()
            End If
    
        End Sub
    
        Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
            On Error GoTo SaveErr
            CapacitorBindingsource.EndEdit()
            ResistorBindingSource.EndEdit()
            ConnectorBindingSource.EndEdit()
    
            MessageBox.Show("Saved")
    SaveErr:
            Exit Sub
        End Sub
    
    
    
        Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    
            Me.txtdes.Text = ""
            Me.txtrohs.Text = ""
            Me.txtmanf.Text = ""
            Me.txtmanfpn.Text = ""
            Me.txtagile.Text = ""
            Me.txttype.Text = ""
            Me.txtpack.Text = ""
            Me.txtvalue.Text = ""
            Me.txtvoltage.Text = ""
            Me.txttolerance.Text = ""
            Me.txttemp.Text = ""
            Me.txtpartnum.Text = ""
            Me.txtcell.Text = ""
            Me.txtsym.Text = ""
            Me.txtagile.Tag = ""
    
        End Sub
    
    End Class
    this is my full code. i have used the commandbuilder too.

  23. #23
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    Modify your savedata method,

    Code:
        Private Sub SaveData()
            bs.EndEdit()
            da.Update(dt)
            da.Fill(dt)
            bs.MoveLast()
        End Sub
    Remove this from the btnadd,
    Code:
    Dim cmdbldr As New OleDbCommandBuilder(da)
    I'm not sure why you have this,
    Code:
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Inductor' table. You can move, or remove it, as needed.
            Me.InductorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Inductor)
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Connector' table. You can move, or remove it, as needed.
            Me.ConnectorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Connector)
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Resistor' table. You can move, or remove it, as needed.
            Me.ResistorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Resistor)
    This code does nothing,
    Code:
        Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
            On Error GoTo SaveErr
            CapacitorBindingsource.EndEdit()
            ResistorBindingSource.EndEdit()
            ConnectorBindingSource.EndEdit()
    
            MessageBox.Show("Saved")
    SaveErr:
            Exit Sub
        End Sub
    Last edited by wes4dbt; May 15th, 2019 at 08:23 PM.

  24. #24

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
    Imports System.Data.OleDb
    
    Public Class Form1
    
        'To get the tables in the combobox
    
    
        Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
        Private da As New OleDbDataAdapter
        Private dt As New DataTable
        Private bs As New BindingSource
        Private cmdbldr As OleDbCommandBuilder
    
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Inductor' table. You can move, or remove it, as needed.
            Me.InductorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Inductor)
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Connector' table. You can move, or remove it, as needed.
            Me.ConnectorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Connector)
            'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Resistor' table. You can move, or remove it, as needed.
            Me.ResistorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Resistor)
    
            cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
            Try
                cnn.Open()
                Dim restrictions() As String = New String(3) {}
                restrictions(3) = "Table"
                Me.ComboBox1.DisplayMember = "TABLE_NAME"
                Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
            cnn.Close()
        End Sub
    
        'To display the selected table in the datagridview
    
        ' Private Sub BindGrid()
    
    
        ' End Sub
    
        ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'BindGrid()
        'End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    
            Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
            Me.DataGridView1.DataSource = Nothing
            dt = New DataTable
            da.SelectCommand = New OleDbCommand(sSql, cnn)
            da.Fill(dt)
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs
            cmdbldr = New OleDbCommandBuilder(da)
    
        End Sub
    
    
        Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
    
            Dim row As DataRow = dt.NewRow
    
            If ComboBox1.Text = "Capacitor" Then
    
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Resistor" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                'row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                'row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Connector" Then
                row("Agile_number") = Me.txtagile.Text --> Column 'Agile_number' does not belong to table "This error after running the application but the actually the table has the coloumn" 
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text           
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Inductor" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            End If
    
    
            dt.Rows.Add(row)
            da.Update(dt) --> Syntax INSERT statement is missing
    
            savedata()
        End Sub
    
        Private Sub savedata()
            bs.EndEdit()
            da.Update(dt)
            da.Fill(dt)
            bs.MoveLast()
    
        End Sub
    
    
    
        Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
    
    
        End Sub
    
        Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
    
            bs.RemoveCurrent()
            savedata()
        End Sub
    
        Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    
    
            If bs.DataSource IsNot Nothing Then
    
                bs.EndEdit()
                da.Update(dt)
            End If
        End Sub
    
    
    
    
    
    
    
        Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
            TabControl1.Visible = False
    
            If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
                GroupBox1.Visible = False
                TabControl1.Visible = True
            Else
                MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
                ' TabControl1.Visible = False
                txtPassword.Clear()
                txtUsername.Clear()
                txtUsername.Focus()
    
            End If
        End Sub
    
        Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
            txtPassword.Clear()
            txtUsername.Clear()
    
        End Sub
    
        Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
    
            Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
            result = MsgBox("Are you sure you would like to exit?", vbYesNo)
            If result = DialogResult.Yes Then
                Application.Exit()
            End If
    
        End Sub
    
        Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
            On Error GoTo SaveErr
            CapacitorBindingsource.EndEdit()
            ResistorBindingSource.EndEdit()
            ConnectorBindingSource.EndEdit()
    
            MessageBox.Show("Saved")
    SaveErr:
            Exit Sub
        End Sub
    
    
    
        Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    
            Me.txtdes.Text = ""
            Me.txtrohs.Text = ""
            Me.txtmanf.Text = ""
            Me.txtmanfpn.Text = ""
            Me.txtagile.Text = ""
            Me.txttype.Text = ""
            Me.txtpack.Text = ""
            Me.txtvalue.Text = ""
            Me.txtvoltage.Text = ""
            Me.txttolerance.Text = ""
            Me.txttemp.Text = ""
            Me.txtpartnum.Text = ""
            Me.txtcell.Text = ""
            Me.txtsym.Text = ""
            Me.txtagile.Tag = ""
    
        End Sub
    
    End Class
    I have facing the error, in the specified the code. i cannot able to any data in to my database. Please help.

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    One problem I see is you have a field name "Value", this is a reserve word and shouldn't be used as a field name. Access even tells you that when you create the field but you have chosen to ignore the warning. You can still use it if you enclose the field names in brackets, like this

    Code:
            cmdbldr = New OleDbCommandBuilder(da)
            cmdbldr.QuotePrefix = "["
            cmdbldr.QuoteSuffix = "]"

  26. #26

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
    Imports System.Data.OleDb
    
    Public Class Form1
    
        'To get the tables in the combobox
    
    
        Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
        Private da As New OleDbDataAdapter
        Private dt As New DataTable
        Private bs As New BindingSource
        Private cmdbldr As OleDbCommandBuilder
    
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
            Try
                cnn.Open()
                Dim restrictions() As String = New String(3) {}
                restrictions(3) = "Table"
                Me.ComboBox1.DisplayMember = "TABLE_NAME"
                Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
            cnn.Close()
        End Sub
    
        'To display the selected table in the datagridview
    
        ' Private Sub BindGrid()
    
    
        ' End Sub
    
        ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'BindGrid()
        'End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    
            Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
            Me.DataGridView1.DataSource = Nothing
            dt = New DataTable
            da.SelectCommand = New OleDbCommand(sSql, cnn)
            da.Fill(dt)
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs
            cmdbldr = New OleDbCommandBuilder(da)
            cmdbldr.QuotePrefix = "["
            cmdbldr.QuoteSuffix = "]"
    
        End Sub
    
    
        Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
    
            Dim row As DataRow = dt.NewRow
            cmdbldr = New OleDbCommandBuilder(da)
            cmdbldr.QuotePrefix = "["
            cmdbldr.QuoteSuffix = "]"
    
            If ComboBox1.Text = "Capacitor" Then
    
                row("Agile number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer Part number") = Me.txtmanfpn.Text
                row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                row("Temp-Coefficient") = Me.txttemp.Text
                row("Part number") = Me.txtpartnum.Text
                row("Cell Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Resistor" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                'row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                'row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Connector" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                'row("Description") = Me.txtagile.Text
                'row("Type") = Me.txttype.Text
                'row("Pack_type") = Me.txtpack.Text
                'row("Value") = Me.txtvalue.Text
                'row("Voltage") = Me.txtvoltage.Text
                'row("Tolerance") = Me.txttolerance.Text
                'row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            ElseIf ComboBox1.Text = "Inductor" Then
                row("Agile_number") = Me.txtagile.Text
                row("Description") = Me.txtdes.Text
                row("Rohs") = Me.txtrohs.Text
                row("Manufacturer") = Me.txtmanf.Text
                row("Manufacturer_Part_number") = Me.txtmanfpn.Text
                row("Description") = Me.txtagile.Text
                row("Type") = Me.txttype.Text
                row("Pack_type") = Me.txtpack.Text
                row("Value") = Me.txtvalue.Text
                row("Voltage") = Me.txtvoltage.Text
                row("Tolerance") = Me.txttolerance.Text
                row("Temp_Coefficient") = Me.txttemp.Text
                row("Part_number") = Me.txtpartnum.Text
                row("Cell_Name") = Me.txtcell.Text
                row("Symbol") = Me.txtsym.Text
    
            End If
    
    
            dt.Rows.Add(row)
            da.Update(dt)
            savedata()
    
        End Sub
    
        Private Sub savedata()
            bs.EndEdit()
            da.Update(dt) -->Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.
            da.Fill(dt)
            bs.MoveLast()
    
        End Sub
    
    
    
        Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
    
    
        End Sub
    
        Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
    
            bs.RemoveCurrent()
            da.Fill(dt)
            savedata()
    
        End Sub
    
        Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    
    
            If bs.DataSource IsNot Nothing Then
                bs.EndEdit()
                da.Update(dt)
                cmdbldr = New OleDbCommandBuilder(da)
                cmdbldr.QuotePrefix = "["
                cmdbldr.QuoteSuffix = "]"
            End If
        End Sub
    
    
    
    
    
    
    
        Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
            TabControl1.Visible = False
    
            If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
                GroupBox1.Visible = False
                TabControl1.Visible = True
            Else
                MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
                ' TabControl1.Visible = False
                txtPassword.Clear()
                txtUsername.Clear()
                txtUsername.Focus()
    
            End If
        End Sub
    
        Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
            txtPassword.Clear()
            txtUsername.Clear()
    
        End Sub
    
        Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
    
            Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
            result = MsgBox("Are you sure you would like to exit?", vbYesNo)
            If result = DialogResult.Yes Then
                Application.Exit()
            End If
    
        End Sub
    
        Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
            '  On Error GoTo SaveErr
            ' CapacitorBindingsource.EndEdit()
            ' ResistorBindingSource.EndEdit()
            ' ConnectorBindingSource.EndEdit()
            savedata()
            MessageBox.Show("Saved")
            'SaveErr:
            ' Exit Sub
        End Sub
    
    
    
        Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    
            Me.txtdes.Text = ""
            Me.txtrohs.Text = ""
            Me.txtmanf.Text = ""
            Me.txtmanfpn.Text = ""
            Me.txtagile.Text = ""
            Me.txttype.Text = ""
            Me.txtpack.Text = ""
            Me.txtvalue.Text = ""
            Me.txtvoltage.Text = ""
            Me.txttolerance.Text = ""
            Me.txttemp.Text = ""
            Me.txtpartnum.Text = ""
            Me.txtcell.Text = ""
            Me.txtsym.Text = ""
            Me.txtagile.Tag = ""
    
        End Sub
    
    End Class
    Marked error i'm getting while trying to delete and save the data.

  27. #27
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database


  28. #28

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    The primary is automatically created in the Database and also it is unique, which is my ID (unique). This problem persists even after that.

  29. #29

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
        Private Sub savedata()
    
            bs.EndEdit()
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey        
            da.Update(dt) --> dynamic sql generation for the delete command is not supported against a select command
            da.Fill(dt)
            bs.MoveLast()
    
        End Sub
    Even if i add the missingschema action with Addwithkey i still get the same error. My database has the PK of ID which is unique. Please help me to resolve this error.

  30. #30
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    Your code doesn't make sense. You need to set MissingSchemaAction when you create the data adapter, before you call Fill. When it comes time to save the changes, you only need to call Update, not Fill or set MissingSchemaAction. It's pretty simple stuff: create the data adapter, configure it (i.e. set all its properties and create a command builder if you want one), call Fill to retrieve data into a DataTable, edit the data, call Update to save those changes. If your query involves a single table with a PK and you retrieve that PK then you will be able to generate the InsertCommand, UpdateCommand and DeleteCommand automatically via the command builder.
    Last edited by jmcilhinney; Jul 23rd, 2019 at 03:02 AM.

  31. #31

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
       
    
     Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
            'open connection if not already opened
            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If
            Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("ID").Value
            Dim myPrimaryKey As Integer = Me.DataGridView1.SelectedRows(0).Cells("ID").Value
            If ComboBox1.Text = "Capacitor" Then
                Dim str As String = "UPDATE Capacitor SET Agile Number=?, [Description]=?, Rohs=?, Manufacturer=?, Manufacturer Part number=?, [Type]=?, Pack type=?, Value=?, Voltage=?, Tolerance=?, Temp-Coefficient=?, Part number=?, Cell Name=?, Symbol=? WHERE ID = @ID"
                Dim cd As New OleDbCommand
                cd = New OleDbCommand(str, cnn)
                cd.Parameters.AddWithValue("Agile Number", txtagile.Text)
                cd.Parameters.AddWithValue("Description", txtdes.Text)
                cd.Parameters.AddWithValue("Rohs", txtrohs.Text)
                cd.Parameters.AddWithValue("Manufacturer", txtagile.Text)
                cd.Parameters.AddWithValue("Manufacturer Part number", txtagile.Text)
                cd.Parameters.AddWithValue("Type", txtagile.Text)
                cd.Parameters.AddWithValue("Pack type", txtagile.Text)
                cd.Parameters.AddWithValue("Value", txtagile.Text)
                cd.Parameters.AddWithValue("Voltage", txtagile.Text)
                cd.Parameters.AddWithValue("Tolerance", txtagile.Text)
                cd.Parameters.AddWithValue("Temp-Coefficient", txtagile.Text)
                cd.Parameters.AddWithValue("Part number", txtagile.Text)
                cd.Parameters.AddWithValue("Cell Name", txtagile.Text)
                cd.Parameters.AddWithValue("Symbol", txtagile.Text)
    
                cd.ExecuteNonQuery() --> Syntax error in UPDATE statement.
                MsgBox("Updated")
                cnn.Close()
            ElseIf ComboBox1.Text = "Connector" Then
                ' Dim str As String = "UPDATE Connector SET  AgileNumber= ""txtagile.Text", Description = "txtdes.Text", Rohs = "txtrohs.Text", Manufacturer = "txtmanf.Text", ManufacturerPartnumber = "txtmanfpn.Text", Partnumber = "txtpartnum.Text", CellName = "txtcell.Text", Symbol = "txtsym.Text"
                Dim cd As New OleDbCommand
                Dim str As String = "UPDATE Capacitor SET Agile Number=?, [Description]=?, Rohs=?, Manufacturer=?, Manufacturer Part number=?, [Type]=?, Pack type=?, Value=?, Voltage=?, Tolerance=?, Temp-Coefficient=?, Part number=?, Cell Name=?, Symbol=? WHERE ID = @ID"
                cd = New OleDbCommand(str, cnn)
                cd.Parameters.AddWithValue("Agile Number", txtagile.Text)
                cd.Parameters.AddWithValue("Description", txtdes.Text)
                cd.Parameters.AddWithValue("Rohs", txtrohs.Text)
                cd.Parameters.AddWithValue("Manufacturer", txtmanf.Text)
                cd.Parameters.AddWithValue("Manufacturer Part number", txtmanfpn.Text) 
                cd.Parameters.AddWithValue("Part number", txtpartnum.Text)
                cd.Parameters.AddWithValue("CellName", txtcell.Text)
                cd.Parameters.AddWithValue("Symbol", txtsym.Text)
                MsgBox("LA")
                cd.ExecuteNonQuery() --> Syntax error in UPDATE statement.
                MsgBox("Updated")
                cnn.Close()
    
            End If
    
        End Sub
    Above is my code for updating the current record and save it. While doing so i am ending with the Syntax error in UPDATE statement.. I don't what's the issue. While i am not using the ID textbox here, since it is autogenerated in the access.



    Also for the Delete and update command it works fine. Thanks jmcilhinney and wes4dbt.

  32. #32
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    You should avoid using spaces or other special characters in table or column names. Just as you would use AgileNumber as an identifier in your VB app, so you should in your database too. If you absolutely can't do that for some reason, you need to force those names to be treated as a single identifier in the SQL code. You already know how to do that because you're already doing it for column names that are keywords.

  33. #33

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    I have removed all the spaces in column names. Since i haven't given the textbox for the ID, whether that causing any issue. ID is autogenarated in the access Database.



    OleDbException was unhandled
    No value given for one or more required parameters.


  34. #34
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,753

    Re: ADD,DELETE,UPDATE the records in the access database

    That means that you haven't added a parameter for every place-holder in your SQL. Looking at your ElseIf block, I see 15 parameter place-holders in the SQL code and not nearly that many parameters added to the command.

  35. #35

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    Code:
        Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
    
            DataGridView1.BeginEdit(True)
            If Me.DataGridView1.Rows.Count > 0 Then
                If Me.DataGridView1.SelectedRows.Count < 2 Then
                    If Not cnn.State = ConnectionState.Open Then
                        cnn.Open()
                    End If
                    If ComboBox1.Text = "Connector" Then
                        Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Connector SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.ExecuteNonQuery()
                        cnn.Close()
                        MessageBox.Show("User updated!")
                    End If
                Else
                    MessageBox.Show("Select 1 row before you hit Update")
                End If
            End If
        End Sub
    For updating the existing record, i wrote this code. It runs without error, but the database and datagridview is not updating on the instance.

  36. #36
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,096

    Re: ADD,DELETE,UPDATE the records in the access database

    As you are using an Access based database, it is important that you add the parameters in the same order that they appear in the SQL statement.

    At the moment you add @ID first, but it is the last one in the SQL statement, so it needs to be added last.

  37. #37

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE Search the records in the access database

    SI, that works fine. Thank you.

    The below code is for the search button, but it couldn't worked out. Running without the error.


    Code:
        Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
            ' bs.Filter = "Agilenumber LIKE'" & txtsearch.Text & "%'"
            Dim strsearch As String
            Dim Task As String
          
            strsearch = Me.txtsearch.Text
            Task = "SELECT * FROM [" & ComboBox1.Text & " ] WHERE ((Agilenumber Like ""*" & strsearch & "*"") OR (Description Like ""*" & strsearch & "*"") OR (CellName Like ""*" & strsearch & "*""))"
            da.SelectCommand = New OleDbCommand(Task, cnn)
        End Sub

  38. #38
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,170

    Re: ADD,DELETE,UPDATE the records in the access database

    This is a new topic, you should start a new thread. btw - that code doesn't retrieve anything, it just sets up a select command.

  39. #39

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: ADD,DELETE,UPDATE the records in the access database

    I am getting the error for the update command.

    For the Capacitor the error is Syntax error in UPDATE statement. and for the Other table the error is No value given for one or more required parameters., Connector and IC is working fine, dont know what is the issue


    Code:
      Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
            DataGridView1.BeginEdit(True)
            If Me.DataGridView1.Rows.Count > 0 Then
                If Me.DataGridView1.SelectedRows.Count < 2 Then
                    Dim cmd As New OleDb.OleDbCommand
                    If Not cnn.State = ConnectionState.Open Then
                        cnn.Open()
                    End If
    
                    If ComboBox1.Text = "Capacitor" Then
                        ' Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Capacitor SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, [Type] = @Type, Packtype = @Packtype, [Value] = @Value, Voltage = @Voltage, [Tolerance] = @Tolerance, Temp-Coefficient = @Temp-Coefficient, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@Type", txttype.Text)
                        cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
                        cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
                        cmd.Parameters.AddWithValue("@Voltage", txtvoltage.Text)
                        cmd.Parameters.AddWithValue("@Tolerance", txttolerance.Text)
                        cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
    
                    ElseIf ComboBox1.Text = "Connector" Then
                        '  Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Connector SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
    
                    ElseIf ComboBox1.Text = "Crystal" Then
                        ' Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Crystal SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, Frequency = @Frequency, [Voltage] = @Voltage, [Value] = @Value, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@Frequency", txttolerance.Text)
                        cmd.Parameters.AddWithValue("@Voltage", txtvoltage.Text)
                        cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
                        'cmd.Parameters.AddWithValue("@Type", txttype.Text)
                        'cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
                        'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
    
                    ElseIf ComboBox1.Text = "Diode_Led" Then
                        '  Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Diode_Led SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, Frequency = @Frequency, [Voltage] = @Voltage, [Value] = @Value, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
                        cmd.Parameters.AddWithValue("@Voltage", txtvoltage.Text)
                        cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
                        'cmd.Parameters.AddWithValue("@Frequency", txttolerance.Text)
                        'cmd.Parameters.AddWithValue("@Type", txttype.Text)
                        'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
                    ElseIf ComboBox1.Text = "IC" Then
                        ' Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update IC SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
    
                    ElseIf ComboBox1.Text = "Inductor" Then
                        ' Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Inductor SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, [Packtype] = @Packtype, [Value] = @Value, Amps = @Amps, Resistance = @Resistance, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        ' cmd.Parameters.AddWithValue("@Type", txttype.Text)
                        cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
                        cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
                        cmd.Parameters.AddWithValue("@Amps", txtvoltage.Text)
                        cmd.Parameters.AddWithValue("@Resistance", txttolerance.Text)
                        'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
                    ElseIf ComboBox1.Text = "Resistor" Then
                        ' Dim cmd As New OleDb.OleDbCommand
                        cmd.Connection = cnn
                        cmd.CommandText = "Update Resistor SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, [Type] = @Type, Packtype = @Packtype, [Value] = @Value, Wattage = @Wattage, Tolerance = @Tolerance, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
                        cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
                        cmd.Parameters.AddWithValue("@Description", txtdes.Text)
                        cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
                        cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
                        cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
                        cmd.Parameters.AddWithValue("@Type", txttype.Text)
                        cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
                        cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
                        cmd.Parameters.AddWithValue("@Wattage", txtvoltage.Text)
                        cmd.Parameters.AddWithValue("@Tolerance", txttolerance.Text)
                        'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
                        cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
                        cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
                        cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
                        cmd.Parameters.AddWithValue("@ID", txtID.Text)
    
                    Else
                    End If
                    Dim Answer As Integer
                    Answer = MsgBox("Are you sure you wish to Update this record?", vbYesNo + vbExclamation + vbDefaultButton2, "Update Confirmation")
                    If Answer = vbYes Then
                        cmd.ExecuteNonQuery() --> No value given for one or more required parameters. and Syntax error in UPDATE statement.
                        refreshdata()
                        cnn.Close()
                        MessageBox.Show("User updated!")
                    End If
                Else
                    MessageBox.Show("Select 1 row before you hit Update")
                End If
            End If
        End Sub

  40. #40
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,096

    Re: ADD,DELETE,UPDATE the records in the access database

    For the Capacitor the error is Syntax error in UPDATE statement.
    The names of fields/tables/etc should only contain letters and numbers, and possibly an underscore... Temp-Coefficient is not valid due to the - character. The parameter placeholder @Temp-Coefficient is also not valid.

    No value given for one or more required parameters.
    That one is more awkward to solve, because there are lots of possible causes... and a quick look at "Crystal" didn't show an obvious issue.

    Check that you have the names of the fields/tables exactly correct, and try putting [] around the field/table names that don't already have them.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width