data adapter insert statement not working

[olmie75]

I am having an issue where I keep getting a runtime error for "syntax error in INSERT INTO statement" on the Update method of a oledb data adapter. I can't seem to get a string that shows me the exact text of the insert statement. Also, I've written code to create & execute the insert statement myself, which works fine, but I would obviously prefer to use the built in data objects to save time.

Can anyone explain why the "da.Update(ds, "Observations")" command gives me this error, and what I can do to fix it?

Also, how can I see the exact text of the insert statement generated by this command? (cb.GetInsertCommand.CommandText just returns a generic template with ? for all values)

Thanks in advance


Here is the code that doesn't work:

Private Sub AddRecord()

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("Observations").NewRow()
        'dsNewRow.Item("Timestamp") = Now
        dsNewRow.Item("Project_FK") = cboProject.SelectedValue
        dsNewRow.Item("ProjPlant_FK") = cboPlant.SelectedValue
        dsNewRow.Item("MfgArea_FK") = cboMfgArea.SelectedValue
        dsNewRow.Item("ProjTeam_FK") = cboTeamMember.SelectedValue
        dsNewRow.Item("Observation") = txtObservation.Text
        dsNewRow.Item("ImpactFactor") = txtImpactFactor.Text
        dsNewRow.Item("Recommendation") = txtRecommendation.Text
        dsNewRow.Item("POC") = cboPOC.SelectedItem
        dsNewRow.Item("SW") = cboSWOT.SelectedItem
        dsNewRow.Item("SQDCM") = cboSQDCM.SelectedItem
        dsNewRow.Item("BestPractice") = chkBestPractice.Checked
        dsNewRow.Item("Use") = chkUseObservation.Checked
        ds.Tables("Observations").Rows.Add(dsNewRow)

        'cb.GetInsertCommand.CommandText

         da.Update(ds, "Observations")


        intMaxRows = intMaxRows + 1


    End Sub

This code (where I create the INSERT text myself, works)

Private Sub AddRecord()

        Dim strInsert As String
        Dim cmdInsert As OleDb.OleDbCommand

        strInsert = "INSERT INTO tblObservations (Project_FK, ProjPlant_FK, MfgArea_FK, " & _
         "ProjTeam_FK, Observation, ImpactFactor, Recommendation, POC, SW, SQDCM, BestPractice, Use)  " & _
         "VALUES (" & cboProject.SelectedValue & ", " & _
         cboPlant.SelectedValue & ", " & _
         cboMfgArea.SelectedValue & ", " & _
        cboTeamMember.SelectedValue & ", '" & _
        txtObservation.Text & "', '" & _
        txtImpactFactor.Text & "', '" & _
        txtRecommendation.Text & "', '" & _
        cboPOC.SelectedItem & "', '" & _
        cboSWOT.SelectedItem & "', '" & _
        cboSQDCM.SelectedItem & "', " & _
        chkBestPractice.Checked & ", " & _
        chkUseObservation.Checked & ")"

        cmdInsert = New OleDb.OleDbCommand(strInsert, con)
        con.Open()
         cmdInsert.ExecuteNonQuery()
         con.Close()
        intMaxRows = intMaxRows + 1

        MsgBox("New Record added to the Database")

    End Sub

Here is the form initialization & declarations:

Public Class frmObservationDetail

    Dim intCurrentRow As Integer
    Dim intMaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim strSql As String
    Dim bolIsNewRecord As Boolean


 Private Sub frmObservationDetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblMfgAreaList' table. You can move, or remove it, as needed.
        Me.TblMfgAreaListTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblMfgAreaList)
        'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblProjTeam' table. You can move, or remove it, as needed.
        Me.TblProjTeamTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblProjTeam)
        'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblProjPlant' table. You can move, or remove it, as needed.
        Me.TblProjPlantTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblProjPlant)
        'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblProjects' table. You can move, or remove it, as needed.
        Me.TblProjectsTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblProjects)

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\vb_assessment_tool.mdb"
        con.Open()

        strSql = "SELECT * FROM tblObservations"
        da = New OleDb.OleDbDataAdapter(strSql, con)
        da.Fill(ds, "Observations")

        con.Close()

        intMaxRows = ds.Tables("Observations").Rows.Count
        intCurrentRow = 0


        NavigateRecords()

    End Sub

End Class

 

[cjard]

Youre accessing a Jet/Access database, you wouldnt do this data access code yourself, and you definitely shouldnt write the statement like you are doing currently (prone to error, sql injection attack, cant be optimized by the server and not well encapsulated).. See the DW2 link in my sig, section on Creating a Simple Data Application

It will show you how to select from and insert to an access database

 

[olmie75]

Some had helped me to figure out that the reason I was getting an error on "da.Update(ds, "Observations")" was because I had a column named as a reserved word ("Timestamp"). Regarding your cjard's reply, this is an internal application for a small company, so I don't think SQL injection attack is an issue, and the access file resides locally & the database will only have a small number of records < 200, so the optimization isn't critical.

It seems like there are many, many ways to do this. I know SQL and data form functionality very well, but I'm having trouble comprehending all the data objects used in VB.net and the data-binding stuff in the visual studio form interface. I briefly reviewed the walkthroughs, but they don't really help me understand the fundementals, and every book or resource I find seems to use a different method. Can anyone recommend a good book that will help explain the fundementals of the vb.net data objects and visual studio functionality?

 

[cjard]

Regarding your cjard's reply, this is an internal application for a small company, so I don't think SQL injection attack is an issue, and the access file resides locally & the database will only have a small number of records < 200, so the optimization isn't critical.

Take a read of the PQ link in my signature; there are many bad reasons and no good reasons for forming SQL as is done here..

I briefly reviewed the walkthroughs, but they don't really help me understand the fundementals

The fundamentals:
Connect to your database using the Server Explorer
Make a new dataset
Navigate the connection to the db, find the table(s) you want to use and drag them into the dataset
Studio will look at the schema of the tables and write SQLs to select from and update/insert/delete into them

You end up with a local object representing the database table, and a device for pushing data into and out of it (tableadapter)

when you say:
tableadpater.Fill(relevant datatable)
data is pulled down from the database

edit some, add some, delete some then say:
tableadapter.Update(relevant datatable)
data is pushed back to the database

You can add your own custom push and pull commands too.


This process isnt very evident if you follow the walkthrough, no.. but you DO end up with working objects and code that does this. The brunt of the donkeywork is done by hidden code available when you click SHOW ALL FILES on the solution explorer, so please dont think its some weird voodoo

-

I dont recommend books, because I've never read one that makes as good a job as the Microsoft Walkthroughs do.. Microsoft are the horse's mouth, get the news straight from them.. But you will have to use your own curiousity too!

 

[olmie75]

I'm a little confused by your reply. Doesn't the code below work the "right" way that you describe (which I got working once I figured out the "Timestamp" issue).

One other question that I know I'll need an anser to eventually, what if I want to execute code in a transaction? (Can I do this using an Access DB)?

Private Sub AddRecord()

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("Observations").NewRow()
        'dsNewRow.Item("Timestamp") = Now
        dsNewRow.Item("Project_FK") = cboProject.SelectedValue
        dsNewRow.Item("ProjPlant_FK") = cboPlant.SelectedValue
        dsNewRow.Item("MfgArea_FK") = cboMfgArea.SelectedValue
        dsNewRow.Item("ProjTeam_FK") = cboTeamMember.SelectedValue
        dsNewRow.Item("Observation") = txtObservation.Text
        dsNewRow.Item("ImpactFactor") = txtImpactFactor.Text
        dsNewRow.Item("Recommendation") = txtRecommendation.Text
        dsNewRow.Item("POC") = cboPOC.SelectedItem
        dsNewRow.Item("SW") = cboSWOT.SelectedItem
        dsNewRow.Item("SQDCM") = cboSQDCM.SelectedItem
        dsNewRow.Item("BestPractice") = chkBestPractice.Checked
        dsNewRow.Item("Use") = chkUseObservation.Checked
        ds.Tables("Observations").Rows.Add(dsNewRow)

        'cb.GetInsertCommand.CommandText

         da.Update(ds, "Observations")


        intMaxRows = intMaxRows + 1


    End Sub

 

[cjard]

I'm a little confused by your reply. Doesn't the code below work the "right" way that you describe

In a word.. No

Follow the steps in DW2, "Simple Data Application" and you'll see what i mean

One other question that I know I'll need an anser to eventually, what if I want to execute code in a transaction? (Can I do this using an Access DB)?

Using Old Way (what you ahve here) i dont really know. If you use TransactionScope and Microsoft Transaction Services I guess it would work the same. Manually enlisting all the adapters in a transaction, I'm not sure. I've never done that in Old Way, but it probably works a little like New Way (adapter.Connection.??????Transaction() methods)