add new record to access database using dataset? RRS feed

  • Question

  • User2012038908 posted


    How to add a new record to Access database using dataset and table? I have in mind something like this code below (that raises error) but I don't want to use any SQL. Simple like this:

    1. set connection to database and open it

    2. Select right dataset table in the database and add a new row to it using NewRow command.

    Can someone show me an example how to do it without using SQL and fill the dataset first with select command? Is it possible? I'm using VWD 2008.

    Dim conn As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;UserID=administrator;Passwo rd=palani;Trusted_connection=yes;Initial Catalog=palani;Data Source=PALANI")
    Dim da As New OleDb.OleDbDataAdapter()
    Dim selcust As New OleDb.OleDbCommand("select * from customerdetails", conn)
    Dim ds As New DataSet()
    Dim dt As DataTable
    Dim dr As DataRow
    Dim flag As Integer

    Private Sub cmdSAVE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSAVE.Click
    If flag = 1 Then
    dt = ds.Tables.Add("CustomerDetails")
    dr = dt.NewRow()
    dr(0) = CType(txtCustID.Text, Integer)----------------Here ERROR is Raised.
    dr(1) = txtName.Text
    dr(2) = txtAddress1.Text
    dr(3) = txtAddress2.Text
    dr(4) = txtCity.Text
    dr(5) = CType(txtPhoneNO.Text, Integer)
    dr(6) = CType(txtCellNo.Text, Integer)
    dr(7) = txtMailID.Text
    da.Update(ds, "customerdetails")
    End If

    Friday, May 2, 2014 11:17 AM


All replies

  • User-821857111 posted

    This approach is not recommended at all. You should use simple INSERT commands. It's much easier and uses less resources on the server. The dataset approach lifts the entire contents of the customerdetails table into memory for no good reason whatsoever.

    anyway, you say you want ot add data to an QAccess database, but oyur connection stirng is a SQL Server connection string. Can you clarify what type of database you are using? Also, what is the error that you get?

    Friday, May 2, 2014 2:45 PM
  • User2012038908 posted

    No error. I just copied this code from internet. I want to do the same in asp.net, because this way of coding is used in Delphi and I like it a lot so I don't have to use and select or insert SQL and trouble myself with "'''"''  ''"' '"" ' '

    Sunday, May 4, 2014 4:03 AM
  • User-821857111 posted


    so I don't have to ... trouble myself with "'''"''  ''"' '"" ' '

    If you used parameters, you wouldn't have to trouble yourself with escaping quotes (in ASP.NET or Delphi, for that matter).

    Use this approach instead: http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 4, 2014 4:41 PM