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

VSLearner2013

How can I parameterized the following query?

Dim StrSql = "INSERT INTO SalesTable(A1,A2,A3,A4) VALUES('" _
+ Me.txt1.Text + "','" _
+ Me.dtp2.Text + "','" _
+ Me.cbo3.Text + "','" _
+ Me.txt4.Text + "')"

Please help.

Richard Deeming

You'll need to fix your DAL.ExecuteSql method to accept parameters, or ditch it and use raw ADO.NET or an ORM. The standard approach using raw ADO.NET would be:

Using connection As New SqlConnection("YOUR CONNNECTION STRING HERE")
Using command As New SqlCommand("INSERT INTO SalesTable (A1, A2, A3, A4) VALUES (@A1, @A2, @A3, @A4)", connection)
command.Parameters.AddWithValue("@A1", Me.txt1.Text)
command.Parameters.AddWithValue("@A2", Me.dtp2.Text)
command.Parameters.AddWithValue("@A3", Me.cbo3.Text)
command.Parameters.AddWithValue("@A4", Me.txt4.Text)

    connection.Open()
    command.ExecuteNonQuery()
End Using

End Using

Also, do yourself a favour and give your controls meaningful names, rather than accepting the default names generated by the designer. You might remember what value txt4 holds now, but when you come back to the code in a few months, you'll have no idea what it means.

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

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.