Click here to Skip to main content
15,167,925 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi.
I am using a class module to simplify accessing my database (Access at the moment but will be MYSQL)

The Class module uses a routine called ExecSQL(SQL as string). When I use this for selecting or filtering records it works perfectly. However when I use a insert query I am told that there is a syntax mismatch and I can not figure out why.

The insert query is coded thus: ExecSQL("Insert into Autolog (Action) Values ('Log in failure')") where autolog is the table and Action is the field to insert the data to.

I would greatly appreciate some assistance. Thank you in anticipation

Code starts________________________________
VB.NET
Imports Microsoft.VisualBasic
Imports System.Data.OleDb

Public Class DbControl
    'Create new database connection
    Private DBCon As New OleDbConnection("Provider = microsoft.ACE.oledb.12.0;" &
                                         "Data source=FreightMaster.accdb;")

    'Prepare DBCmd as OLEDB
    Private DBCmd As OleDb.OleDbCommand
    'Preparing data 
    Public dbda As oledbdataadapter
    Public DBDT As DataTable
    'Query Parameters
    Public Params As New List(Of OleDb.OleDbParameter)
    'qry statistics
    Public RecordCount As Integer
    Public Exception As String

    Public Sub execQuery(ByVal Query As String)

        'Reset query stats 
        RecordCount = 0
        Exception = ""
        Try
            'try to open the connection
            DBCOn.open()
            'create the command
            DBCmd = New OleDbCommand(Query, DBCon)
            ' Load Params into dbcommand
            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

            'clear the parameters
            params.clear()

            'execute command
            DBDT = New datatable
            DBDA = New oledbdataAdapter(dbcmd)
            RecordCount = dbda.Fill(DBDT)
        Catch ex As Exception
            Exception = ex.message
            MessageBox.Show(Exception)

        End Try
        'close the connection if it remains open
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

    'Include query and cmd params
    Public Sub AddParam(ByVal Name As String, ByVal value As Object)
        Dim NewParam As New OleDbParameter(Name, value)
        Params.Add(NewParam)
    End Sub

End Class


What I have tried:

I have tried all other types of sql that I can think of - but it is only the insert sql that fails
Posted
Updated 30-Oct-21 13:17pm
v2
Comments
Gerry Schmitz 30-Oct-21 17:49pm
   
You're not showing any actual "insert" code.

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/insert-into-statement-microsoft-access-sql

1 solution

Well, for one, you cannot use a DataAdapter to execute an INSERT query.

You're going to have to provide another method for your code to call to execute non-SELECT queries.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900