On UPDATE, Vertical Scroll moves to first row instead of selected row for editing.
-
That code has a lot of problems. Try something like this instead:
Imports System.Data
Imports System.Data.OleDb
Imports System.GlobalizationModule 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 FunctionPrivate 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 SubEnd Module
You have three public methods to call -
LoadDatawhen you want to load data into aDaThanks a million to your kind and detail reply. I will try it now in my project and will comeback.
-
That code has a lot of problems. Try something like this instead:
Imports System.Data
Imports System.Data.OleDb
Imports System.GlobalizationModule 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 FunctionPrivate 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 SubEnd Module
You have three public methods to call -
LoadDatawhen you want to load data into aDaI 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'.>
)
-
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'.>
)
The
LoadDatamethod returns aDataSet, 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
-
The
LoadDatamethod returns aDataSet, 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
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 -
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 IfYou'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
-
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
But when I add the first field ...
Me.txtID.Text, _
it gives this error:
Cannot update 'ID'; field not updateable.
-
But when I add the first field ...
Me.txtID.Text, _
it gives this error:
Cannot update 'ID'; field not updateable.
Because you're trying to update a field which you can't update. Don't include the
IDcolumn in theSETpart 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
-
Because you're trying to update a field which you can't update. Don't include the
IDcolumn in theSETpart 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
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?
-
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?
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
-
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
Thanks a lot. It works fine.
