locked
Saving data with ADO.NET and OleDB (for Access database) RRS feed

  • Question

  • User-92840964 posted

    Hello all,

    I am trying to have my ASP.NET program update an Access database. I use a OleDbDataAdapter, attach a OleDbCommandBuilder to it, fill a DataSet, and make my changes. Then I call the Update() method of the DataSet. However, it gives me an error that makes me suspect that CommandBuilder isn't building properly:

    The error is a bad SQL syntax error in the filter (WHERE), and appears to be related to the fact that it is missing )'s at the end. But I didn't write any of this, it's all from the OleDbCommandBuilder. Here's the WHERE part:

    ((PersonID = ?) AND ((? = 1 AND ID IS NULL) OR (ID = ?)) AND ((? = 1 AND FirstName IS NULL) OR (FirstName = ?)) AND ((? = 1 AND Last Name2 IS NULL) OR (Last Name2 = ?)) AND ((? = 1 AND BirthDate IS NULL) OR (BirthDate = ?)) AND ((? = 1 AND School IS NULL

    As you can see, it also isn't replacing my ?'s with the actual data; it left them in there when issuing the UPDATE command. Also I have no idea why it is filtering everything like this in the first place, unless that's just standard behavior before it builds the UPDATE command based on the DataSet changes?

    Here is my code:


    Dim con As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename)
                con.Open()
    
                'Open RS
                Dim ds As New DataSet
                Dim da As New OleDb.OleDbDataAdapter(sql, con)
                da.Fill(ds)
                Dim cb As New OleDb.OleDbCommandBuilder(da)
                Dim t As DataTable = ds.Tables(0)
    
                    Dim r As DataRow
                    If Me.PleaseAddANewRecord Then
                        r = t.NewRow
                    Else
                        r = t.Rows(0)
                    End If
    
                    For Each n In Me.DataToSave
                        r(n.Key) = n.Value
                    Next
    
                    da.Update(ds)
                    Return True
    
    
                ds.Dispose()
                da.Dispose()
                con.Close()
                con.Dispose()


    Tuesday, November 23, 2010 9:31 PM

All replies

  • User-821857111 posted

    First thing I would advise is that you get rid of the dataset approach to simply updating a record, and run an SQL UPDATE statement using ExecuteNonQuery: http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access. You might also like to try using named paramters, whihc must be in the same order as they appear in the SQL when you add them to the the Parameters collection.


    Wednesday, November 24, 2010 12:23 AM
  • User-1675817941 posted

     hi,

     use this code

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
    System.EventArgs) Handles Button2.Click
    Try
    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;")
    cn.Open()
    str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" &_
    TextBox3.Text & "')"
    'string stores the command and CInt is used to convert number to string
    cmd = New OleDbCommand(str, cn)
    icount = cmd.ExecuteNonQuery
    MessageBox.Show(icount)
    'displays number of records inserted
    Catch
    End Try
    cn.Close()
    End Sub
    End Class

    this links may helps you

    http://msdn.microsoft.com/en-us/library/ms971485.aspx

    http://www.startvbdotnet.com/ado/msaccess.aspx

    Thanks , 

     

    Wednesday, November 24, 2010 9:38 AM
  • User-821857111 posted

     use this code

    ...


    str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" &_
    TextBox3.Text & "')"

    No, don't.

    Use parameters.


    Wednesday, November 24, 2010 1:42 PM
  • User303496338 posted

    If you use the "textbox1.text" method instead of using parameters you are subject to sql injections.

    -the code would work- but open up some huge security holes for you. 

    Thursday, November 25, 2010 4:18 PM
  • User-92840964 posted

    Thanks all,

    Unfortunately, I am going to be supplied with a list of fields (by name) and their values at run-time, and therefore, it is easiest and cleanest for me to loop through the array of fields and assign values to each field from my array, as I did in the code posted earlier.

    This also naturally prevents SQL injection, as some of you mentioned, which is important for me (although I could acheive that with parameters as well).

    From what I understand, changing field values in a DataTable is a fully recommended and completely documented approach, and has its advantages in some cases (like mine).

    Can anyone understand why the code above didn't work? What did I do wrong?

    Saturday, November 27, 2010 6:10 PM