CODEWITHSUNDEEP
mysqlvb.netvisual-studio
Sambid Shrestha
Sambid Shrestha

Reputation: 41

SQL insert has no results

Insert into query from vb program adds no record in access database.

database connection class-->
Imports System.Data.OleDb
Public Class Data
    Shared dbconnection As OleDbConnection
    Shared dbcommand As OleDbCommand
    Shared dbadapter As OleDbDataAdapter
    Shared connectionString As String =     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\DELL\Documents\Visual Studio 2013\Projects\Chakana saving and credit\Chakana saving and credit\Chakana.mdb"
Shared dbtable As DataTable
Public Shared Function loadData(query As String, Optional ReturnValue As Boolean = False)
    dbconnection = New OleDbConnection(connectionString)
    dbadapter = New OleDbDataAdapter
    dbtable = New DataTable()
    dbcommand = New OleDbCommand(query)
    dbcommand.CommandType = CommandType.TableDirect
    dbadapter.SelectCommand = dbcommand
    dbadapter.SelectCommand.Connection = dbconnection
    dbconnection.Open()
    'MsgBox("query lexecute")
    If ReturnValue = True Then
        dbadapter.Fill(dbtable)
        Return dbtable
    Else
         Return 0
    End If


End Function

End Class

function call passing parameter query

 query = "INSERT INTO CustomerInfo (ClientId,FirstName) VALUES ('23','abce')"
    Data.loadData(query)

other call to function passing a SELECT query works fine but when i try insert query and afterwards open the database from access it has no results

Upvotes: 0

Views: 104

Answers (2)

Chris
Chris

Reputation: 825

Check out OleDbTransaction.Commit Method () and DbDataAdapter.Update Method which show how to open and commit a transaction within a connection.

Upvotes: 1

Mojtaba Rezaeian
Mojtaba Rezaeian

Reputation: 8736

You are not executing your DataAdapter to fill DataTable (because returnValue is set to false by default) plus you don't neither need to define returnValue nor DataAdapter for just filling a table. I recommend to change your function to something like this:

Imports System.Data.OleDb
Public Class Data

    Shared connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\DELL\Documents\Visual Studio 2013\Projects\Chakana saving and credit\Chakana saving and credit\Chakana.mdb"

    Public Shared Function loadData(query As String) As DataTable
        Dim dbtable As New DataTable
        Dim dbcommand As New OleDbCommand(query)
        dbcommand.Connection = New OleDbConnection(connectionString)
        dbcommand.CommandType = CommandType.TableDirect
        dbcommand.Connection.Open()
        dbtable.Load(dbcommand.ExecuteReader())
        dbcommand.Connection.Close()
        Return dbtable
    End Function

End Class

Upvotes: 0

Related Questions