Hi folks. I am trying to update an app to VB dot net from vb6 and have enouctered a really basic problem. I will add the code of course in a sec. I

Question

Trying to update an old VB6 app to VB.Net. I am having trouble with syntax, I think. In any case it is a simple matter of inserting a new record to the autolog table. (code below).

I would like to ask something else that is often not documented too. It seems that I have to use command builders and so on - is there no way I can simply use an SQL statement and execute it against the background table? The tables are in Access while I am developing but will be scaled up on the final release of the software.

I have altered my code to the following by making use of the error suggestions at the foot of mygui.

It now looks like this and the only thing is that it is throwing a logic error at me which is that every end function must have a preceding "function". Perhaps I am being a little bit dim

  Function MAutolog(ByVal Action As String) As Boolean
        Dim SQL = "Insert Into Autolog (Action) Values (@Action)"
        Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC User\Documents\Freightmaster\resources\freightmaster.accdb"),
              cmd As New OleDb.OleDbCommand(SQL, con)
            cmd.Parameters.Add("@Action", OleDb.OleDbType.VarChar).Value = Action
            con.Open()
            cmd.ExecuteNonQuery()
        End Using

        MAutolog = True
    End Function
Copy

I would like to thank you for your help in advance. I can not tell you how much I will appreciate it.

Code

---

    Module ModFunctions
        Function MAutolog(ByVal UserID As Long, ByVal Action As String) As Boolean
            Dim dbprovider As String
            Dim dbsource As String
            Dim mydocumentsfolder As String
            Dim fulldatabasepath As String
            Dim TheDatabase As String
            Dim SQL As String
            Dim DS As New DataSet
            Dim da As OleDb.OleDbDataAdapter
            Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC User\Documents\Freightmaster\resources\freightmaster.accdb")
   
            con.Open()
    
            '----------------------------
    
            SQL = "Select * from Autolog"
            da = New OleDb.OleDbDataAdapter(SQL, con)
            da.Fill(DS, "Log")
            con.Close()
    
            Dim CB As New OleDb.OleDbCommandBuilder(da)
            Dim DSNEWROW As DataRow
            DSNEWROW = DS.Tables("Log").NewRow()
            DSNEWROW.Item("UserID") = UserID
            DSNEWROW.Item("Action") = Action
            DS.Tables("log").Rows.Add(DSNEWROW)
            da.Update(DS, "log")
    
            MAutolog = True
    End function
Copy

Answer 1

Database objects like Connection and Command use unmanaged code and need their Dispose methods to release these resources. Either call this method on these objects or use Using...End Using blocks which will do this for you even if there is an error. In this code, both the Connection and Command are included in the Using block by separating them be a comma.

By Val is the default so is not necessary.

Always use parameters to avoid sql injection. Using values directly from user input can allow malicious code to be executed on your database. The value of a parameter is not considered as executable code by the database.

OleDb does not care about parameter names. You could just as easily use ? in the sql statement. I use names for readability. You do need some sort of name to add the parameter. OleDb considers the position of the parameter in the sql statement. The position must match the order that the parameters are added to the parameters collection.

This is the code for the Insert if UserID in an auto-number field. You do not provide a value for auto-number fields. The database will handle that.

Function MAutolog(Action As String) As Boolean
    Dim SQL = "Insert Into Autolog (Action) Values (@Action)"
    Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC User\Documents\Freightmaster\resources\freightmaster.accdb"),
            cmd As New OleDbCommand(SQL, con)
        cmd.Parameters.Add("@Action", OleDbType.VarChar).Value = Action
        con.Open()
        cmd.ExecuteNonQuery()
    End Using

    MAutolog = True
End Function
Copy

If UserID is not auto-number

Function MAutolog(UserID As Long, Action As String) As Boolean
    Dim SQL = "Insert Into Autolog (UserID, Action) Values (@UserID, @Action)"
    Using con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC User\Documents\Freightmaster\resources\freightmaster.accdb"),
            cmd As New OleDbCommand(SQL, con)
        cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = UserID
        cmd.Parameters.Add("@Action", OleDbType.VarChar).Value = Action
        con.Open()
        cmd.ExecuteNonQuery()
    End Using

    MAutolog = True
End Function
Copy