On UPDATE, Vertical Scroll moves to first row instead of selected row for editing.

[VSLearner2013]

Thanks for your kind reply. Being a learner, I got this code from other source and using it in my sample project. The following code is the code in the module:

Imports System.Data.OleDb
Imports System.Data
Module DAL
Dim OledbCon As OleDbConnection
Public Msg As String = String.Empty
Public Function GetCon() As OleDbConnection
Dim DbFullPath As String = Application.StartupPath + "\"
DbFullPath += "MyDataBase.accdb"
If OledbCon Is Nothing Then
OledbCon = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbFullPath + ";Jet OLEDB:Database Password=123456;")
OledbCon.Open()
Return OledbCon
End If
If OledbCon.State = ConnectionState.Open Then Return OledbCon
OledbCon.Open()
Return OledbCon
End Function
Public Function ExecuteSql(ByRef dt As DataTable, ByVal StrSql As String, ByRef Msg As String) As Boolean
Try
Dim Cmd As OleDbCommand = New OleDbCommand(StrSql, GetCon())
Cmd.CommandType = CommandType.Text
Dim DA As New OleDbDataAdapter
DA.SelectCommand = Cmd
DA.Fill(dt)
Return True
Catch ex As Exception
Msg = ex.Message.ToString()
Return False
End Try
End Function
End Module

Will the above code DAL.ExecuteSql which is in module works for me? Or how can I modify the module code accordingly.

[Richard Deeming]

That code has a lot of problems. Try something like this instead:

Imports System.Data
Imports System.Data.OleDb
Imports System.Globalization

Module DAL
Private Function GetConnection() As OleDbConnection
Dim result As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\MyDataBase.accdb;Jet OLEDB:Database Password=123456;")
result.Open()
Return result
End Function

Private Function CreateCommand(ByVal connection As OleDbConnection, ByVal query As String, ByVal ParamArray parameters() As Object) As OleDbCommand
    Dim command As OleDbCommand = connection.CreateCommand()
    
    If parameters IsNot Nothing Then
        For index = 0 To parameters.Length - 1
            Dim i As String = index.ToString("D", CultureInfo.InvariantCulture)
            Dim p As String = "{" + i + "}"
            Dim name As String = "@p" + i
            query = query.Replace(p, name)
            command.Parameters.AddWithValue(name, parameters(index))
        Next
    End If
    
    command.CommandText = query
    Return command
End Function

Public Function LoadData(ByVal query As String, ByVal ParamArray parameters() As Object) As DataSet
    Using connection As OleDbConnection = GetConnection()
        Using command As OleDbCommand = CreateCommand(connection, query, parameters)
            Dim result As New DataSet()
            Dim da As New OleDbDataAdapter(command)
            da.Fill(result)
            Return result
        End Using
    End Using
End Function

Public Function ExecuteScalar(ByVal query As String, ByVal ParamArray parameters() As Object) As Object
    Using connection As OleDbConnection = GetConnection()
        Using command As OleDbCommand = CreateCommand(connection, query, parameters)
            Return command.ExecuteScalar()
        End Using
    End Using
End Function

Public Sub ExecuteNonQuery(ByVal query As String, ByVal ParamArray parameters() As Object)
    Using connection As OleDbConnection = GetConnection()
        Using command As OleDbCommand = CreateCommand(connection, query, parameters)
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

End Module

You have three public methods to call - LoadData when you want to load data into a Da

[VSLearner2013]

Thanks a million to your kind and detail reply. I will try it now in my project and will comeback.

[VSLearner2013]

I am getting the error at the this point of code in UpdateGrid function.

Private Sub UpdateGrid(ByVal RowIndex As Integer):

Dim dt As DataTable = DAL.LoadData("SELECT * FROM SalesTable")

Error 1 Value of type 'System.Data.DataSet' cannot be converted to 'System.Data.DataTable'.>

)

[Richard Deeming]

The LoadData method returns a DataSet, in case you want more than one resultset from your query. Try:

Dim dt As DataTable = DAL.LoadData("SELECT * FROM SalesTable").Tables(0)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[VSLearner2013]

I have 15 Fields in my MS Access Database Table (The first field is ID and it has AutoNumber property). Getting the following error on Updating a row:

Malformed GUID in query expression '{14}'.

btnSave.Text = "Update"
DAL.ExecuteNonQuery("UPDATE SalesTable SET ID={0},A1={1},A2={2},A3={3},A4={4},A5={5},A6={6},A7={7},A8={8},A9={9},A10={10},A11={11},A12={12},A13={13},A14={14} WHERE [ID] = {0}", _
Me.txt1.Text, _
Me.dtp2.Text, _
Me.cbo3.Text, _
Me.txt4.Text, _
Me.cbo5.Text, _
Me.txt6.Text, _
Me.cbo7.Text, _
Me.cbo8.Text, _
Me.cbo9.Text, _
Me.cbo10.Text, _
Me.cbo11.Text, _
Me.txt12.Text, _
Me.txt13.Text, _
Me.txt14.Text)
End If

[Richard Deeming]

You're not passing enough parameters in. You've got 15 placeholders, but you're only passing in 14 parameters.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[VSLearner2013]

But when I add the first field ...

Me.txtID.Text, _

it gives this error:

Cannot update 'ID'; field not updateable.

[Richard Deeming]

Because you're trying to update a field which you can't update. Don't include the ID column in the SET part of the query:

DAL.ExecuteNonQuery("UPDATE SalesTable SET A1={0}, A2={1}, A3={2}, A4={3}, A5={4}, A6={5}, A7={6}, A8={7}, A9={8}, A10={9}, A11={10}, A12={11}, A13={12}, A14={13} WHERE [ID] = {14}", _
Me.txt1.Text, _
Me.dtp2.Text, _
Me.cbo3.Text, _
Me.txt4.Text, _
Me.cbo5.Text, _
Me.txt6.Text, _
Me.cbo7.Text, _
Me.cbo8.Text, _
Me.cbo9.Text, _
Me.cbo10.Text, _
Me.cbo11.Text, _
Me.txt12.Text, _
Me.txt13.Text, _
Me.txt14.Text, _
Me.txtID.Text)

Try to keep the parameters in the same order that they appear in the query. OleDb tends to ignore the parameter names, and depends on the order of the parameters.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[VSLearner2013]

Thanks. But I have a doubt here... In my Database, The first field name is "ID" (Which should be 0) with a primary key and Autonumber. And here in the above code, it is after txt14.text. Is it okay?

[Richard Deeming]

Yes, because ID is the last parameter in the query:

WHERE [ID] = {14}

The number in the curly-brackets is the zero-based index of the parameter you've passed to the method. It has nothing to do with the column order in the table. As I said, OleDb tends to use positional parameters, so you need to pass the parameters to the method in the same order that they appear in the query. Unfortunately, this means that if you use the same value twice in a single query, you have to pass it to the method twice:

' This won't work for OleDb:
DAL.Method("SELECT {0}, {1}, {0}", FirstValue, SecondValue)
' You'd have to use this instead:
DAL.Method("SELECT {0}, {1}, {2}", FirstValue, SecondValue, FirstValue)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[VSLearner2013]

Thanks a lot. It works fine.