How to add rows in an Access Table with parameters?

This is my function:

Function SQL_InsertUpdate(mySQLConnection As OleDbConnection, mySQLCommand As String, mySQLTable As String, mySQLTableColumns() As String, myParameters() As String)
    Dim SQLCommand As OleDbCommand = New OleDbCommand(mySQLCommand, mySQLConnection)

    Dim myStringConstruct = mySQLCommand & " " & mySQLTable & " ("

    '==============
    For Each item In mySQLTableColumns
        myStringConstruct = myStringConstruct & item & ", "
    Next
    myStringConstruct = Strings.Left(myStringConstruct, Len(myStringConstruct) - 2)
    myStringConstruct = myStringConstruct & ") VALUES ("

    For i As Integer = 0 To mySQLTableColumns.Length - 1
        myStringConstruct = myStringConstruct & "@" & mySQLTableColumns(i) & ", "
        SQLCommand.Parameters.AddWithValue("@" & mySQLTableColumns(i), myParameters(i))
    Next

    myStringConstruct = Strings.Left(myStringConstruct, Len(myStringConstruct) - 2)
    myStringConstruct = myStringConstruct & ")"
    SQLCommand.ExecuteNonQuery()
End Function

This is how I call the function:

            Dim myParameters() As String = ({myNewID.ToString, myUser.ToString, myDepartment.ToString, mySubsidiary.ToString, myTitle.ToString, myRecurrence.ToString, myImpact.ToString, myTimeSaved.ToString, myPriority.ToString, myStatus.ToString, myTechnology.ToString, myDeveloper.ToString, myCostSave.ToString, myDescription.ToString, myCommentary.ToString, myDateSubmitted.ToString, myDateModified.ToString, myInReviewDate.ToString, myManagerReviewDate.ToString, myDigitalReviewDate.ToString, myRejectedDate.ToString, myInProgressDate.ToString, myDevelopedDate.ToString, myImplementedDate.ToString})
            Dim mySQLTableColumns() As String = ({"ID", "myUser", "myDepartment", "mySubsidiary", "myTitle", "myRecurrence", "myImpact", "myTimeSaved", "myPriority", "myStatus", "myTechnology", "myDeveloper", "myCostSave", "myDescription", "myCommentary", "myDateSubmitted", "myDateModified", "myInReviewDate", "myManagerReviewDate", "myDigitalReviewDate", "myRejectedDate", "myInProgressDate", "myDevelopedDate", "myImplementedDate"})

            SQL_InsertUpdate(SQLConnection, "INSERT INTO", "SIMSBase", mySQLTableColumns, myParameters)

This is the constructed command string output:

INSERT INTO SIMSBase (ID, myUser, myDepartment, mySubsidiary, myTitle, myRecurrence, myImpact, myTimeSaved, myPriority, myStatus, myTechnology, myDeveloper, myCostSave, myDescription, myCommentary, myDateSubmitted, myDateModified, myInReviewDate, myManagerReviewDate, myDigitalReviewDate, myRejectedDate, myInProgressDate, myDevelopedDate, myImplementedDate) VALUES (@ID, @myUser, @myDepartment, @mySubsidiary, @myTitle, @myRecurrence, @myImpact, @myTimeSaved, @myPriority, @myStatus, @myTechnology, @myDeveloper, @myCostSave, @myDescription, @myCommentary, @myDateSubmitted, @myDateModified, @myInReviewDate, @myManagerReviewDate, @myDigitalReviewDate, @myRejectedDate, @myInProgressDate, @myDevelopedDate, @myImplementedDate)

This is the error I receive:

  $exception  {"Syntax error in INSERT INTO statement."}  System.Data.OleDb.OleDbException

Now I have no clue what syntax error I could have, I looked here for another person who has no issue in this Stack question: Insert data into SQL database in VB.NET, my syntax is similar.

I don't know what is wrong, could it give out a syntax error if the Access database columns are not Data Type Short/Long Text?

The parameters are added properly (checked the debug).

Actually, I often find it too much work to create a complex insert routine. And even worse is I often don't care or want to supply all of the columns.

.net as a result has what is called a command builder for you.

And this quite much means you can write a lot of your code in a simular way to how VBA code in Access works.

So, say I want to add a new row - the table might have 50 columns, but I don't really care.

So, I can write the code this way:

    Dim rstHotels As DataTable

    rstHotels = MyRst("SELECT * FROM tblHotels WHERE ID = 0")

    ' now add 3 new hotels

    For i = 1 To 3

        Dim OneRow = rstHotels.NewRow

        OneRow("HotelName") = "Hotel #" & i
        OneRow("City") = "City #" & i
        OneRow("FirstName") = "Test First name #" & i
        OneRow("Active") = True

        rstHotels.Rows.Add(OneRow)

    Next

    ' ok, added rows to rstHotels - now write back to database

    MyRstUpDate(rstHotels, "tblHotels")

    ' or update 5 rows and do compplex processing to exising data.

    Dim rstFun As DataTable = MyRst("SELECT * from tblHotels where City = 'Banff'")

    For Each MyRow As DataRow In rstFun.Rows

        MyRow("Active") = True
        ' more complex cpde here

    Next

    ' now send data changes back to database

    MyRstUpdate(rstFun, "tblHotels")

So, note how we don't have to have some complex insert statement, and we don't hve to write some loop that gives one hoot about the number of columns. So the .net data operations have build in all this stuff for you - little or even next to no reason for you to try and re-invent the wheel here.

And the two handy dandy code routines I have? The are :

Public Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable
    Using conn As New OleDbConnection(My.Settings.AccessDB)
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            rstData.TableName = strSQL
        End Using
    End Using

    Return rstData

End Function


Public Sub MyRstUpdate(rstData As DataTable, strTableName As String)

    Using conn As New OleDbConnection(My.Settings.AccessDB)
        Using cmdSQL As New OleDbCommand("SELECT * from " & strTableName, conn)

            Dim da As New OleDbDataAdapter(cmdSQL)
            Dim daUP As New OleDbCommandBuilder(da)
            conn.Open()
            da.Update(rstData)
        End Using
    End Using

End Sub

Now, I am really rather free to just code out my general routines.

So, you need to say load up a grid, or even a combo box? You can now do this:

ListBox1.DataSource = MyRst("SELECT ID, Salutation from tblGender ORDER BY Salutation")

So, for a simple insert, or even edit of some rows? No need to create some monster huge insert statement with a boatload of parameters. Just create a data table, and then use a simple data row to either add new rows, or even update existing ones.

The beauty of above is not only do you eliminate a boatload of parameters, but you also get parameter safe, and even type conversions. So, you can for example do this:

 OneRow("InvoiceDate") = Date.Today

Thus a strong typed value of "money" or integer, or datetime can be used in code - and no messey format convertions are required in most cases.

This so called "data base" first can be really handy, and often for some operations this is a lot less setup time and learning curve then say using EF, or even the previous dataset designer (EF = "Entity framework", which works really much like the older data set designer system - but introduction of these object model systems can be a big system to chew on when you just starting out).

But, no, don't write your own looping code to write out and create all the columns for a update command. (or insert command - note how that ONE routine can handle both updates or inserts. And you can even use row.Delete and then call tht update routine - it will also work!!.

If you think about this, that really amounts to a lot of work, and built in systems exist for this propose - saves you having to re-invent the wheel.

How to add rows in an Access Table with parameters?

Related

Recent Posts