[RESOLVED] How to update whole table records with one single button click?
I want to use the following function "Spaces" in update query and to update the whole table records just in a single button click (to remove any unwanted spaces from the reocrds which wered already added earlier by mistake) instead of updating each record one by one. Because now, I have more than 6300 records in my MS Access database.
Note: I am not going to change any text / values in the textboxes. Simply I want to click on Edit button then click on Update button to remove unwanted spaces from table records.
These (around 6300) records were added earlier without using Spaces function.
Code:
Private Function Spaces(strText As String) As String
Return String.Join(" ", strText.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
End Function
Re: How to update whole table records with one single button click?
If I wanted to update all the records in a Database Table, I would do it like this,
Code:
Dim da As New OleDbDataAdapter("Select fields you want to Edit From yourTable", yourConnectionString)
Dim cb As New OleDbCommandBuilder(da)
Dim dt As New DataTable
da.Fill(dt)
For Each row As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
row(col) = "new value"
Next
Next
da.Update(dt)
I didn't really test this, just wrote it as an example.
Re: How to update whole table records with one single button click?
I am not able to figure it out. I am doing something wrong. Please guide me.
The following is the code:
Module Name is : DAL.vb
Code:
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 += "RTDataBase.accdb"
If OledbCon Is Nothing Then
OledbCon = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbFullPath + ";Jet OLEDB:Database Password=TestingPWD;")
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
Code:
Private Function Spaces(strText As String) As String
Return String.Join(" ", strText.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
End Function
Code:
Private Sub btnRempveSpaces_Click(sender As Object, e As EventArgs) Handles btnRempveSpaces.Click
Dim StrSql As String = "Select CustomerName, Address from CustomersTable ORDER BY CustomerName ASC"
Dim dt As New DataTable
Dim DA As New OleDbDataAdapter
For Each row As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
row(col) = "Spaces"
Next
Next
DA.Update(dt)
If Not DAL.ExecuteSql(dt, StrSql, Msg) Then MsgBox(Msg, MsgBoxStyle.Exclamation, "Error") : Exit Sub
Me.DataGridView1.DataSource = dt
If dt Is Nothing Or dt.Rows.Count <= 0 Then
Exit Sub
End If
End Sub
End Class
I think I am doing something wrong here in the above code : row(col) = "Spaces".
There is no error but it populates two columns CustomerName, Address (Fields) without removing any spaces from those columns.
Re: How to update whole table records with one single button click?
Actually, I need to update all the 39 fields in NewSalesDBTable as mentioned in the Post # 1. I was trying to check first with 2 Fields in CustomersTable in the above post. How can I proceed further? Got confused. Do I need to add the same line of code for each textbox (for all 39 Fields) like...
row(col) = Spaces(Me.txt1.Text) & "','"
row(col) = Spaces(Me.txt2.Text) & "','"
row(col) = Spaces(Me.txt3.Text) & "','" ................................................
row(col) = Spaces(Me.txt37.Text) & "','"
row(col) = Spaces(Me.txtUDO.Text) & "','"
row(col) = Spaces(Me.txtUDB.Text) & "','"
Re: How to update whole table records with one single button click?
For testing, I put one Button and one DataGridView on form thinking that the textboxes are not required to update the whole table in one click. Please support how can I proceed further?
Note: Now, my problem is that I cannot go row by row and udpate it, as it has more than 6,300 records already entered.
Re: How to update whole table records with one single button click?
Originally Posted by VS2013
For testing, I put one Button and one DataGridView on form thinking that the textboxes are not required to update the whole table in one click. Please support how can I proceed further?
Note: Now, my problem is that I cannot go row by row and udpate it, as it has more than 6,300 records already entered.
At this point I am thinking you want us to write your application
Re: How to update whole table records with one single button click?
Actually it was the wrong answer to the wrong question in the first place... this should have been a SQL question with a SQL answer... especially if he's looking for a one-off to clean up the entire table.
Re: How to update whole table records with one single button click?
Private Sub btnRempveSpaces_Click(sender As Object, e As EventArgs) Handles btnRempveSpaces.Click
Dim StrSql As String = "Select CustomerName, Address from CustomersTable ORDER BY CustomerName ASC"
Dim dt As New DataTable
Dim DA As New OleDbDataAdapter
For Each row As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
row(col) = "Spaces"
Next
Next
DA.Update(dt)
If Not DAL.ExecuteSql(dt, StrSql, Msg) Then MsgBox(Msg, MsgBoxStyle.Exclamation, "Error") : Exit Sub
Me.DataGridView1.DataSource = dt
If dt Is Nothing Or dt.Rows.Count <= 0 Then
Exit Sub
End If
End Sub
End Class
This code has several problems. The DataAdapter has not been assigned a CommandText or a Connection String. Once you do that, you need to "Fill" the DataTable. This code does nothing because the datatable has nothing in it.
Once you fill the datatable then you can use your "Spaces" or replace row(col) = "Spaces"
Code:
Dim var = row(col).ToString
row(col) = String.Join(" ", var.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
My example is not meant to work with TextBoxes or bindingsources or any type of operator entry. It's meant to be in a Button Click event and it will go though the entire table in one shot.
Re: How to update whole table records with one single button click?
I have the following code in Module:
Code:
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 += "RTDataBase.accdb"
If OledbCon Is Nothing Then
OledbCon = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbFullPath + ";Jet OLEDB:Database Password=TestingPWD;")
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
Re: How to update whole table records with one single button click?
Code:
Dim da As New OleDbDataAdapter("Select fields you want to Edit From yourTable", yourConnectionString)
Dim cb As New OleDbCommandBuilder(da)
Dim dt As New DataTable
da.Fill(dt)
For Each row As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
Dim var = row(col).ToString
row(col) = String.Join(" ", var.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
Next
Next
da.Update(dt)
Re: How to update whole table records with one single button click?
Thank you so much for your kind reply. It is working fine. Just last question in this regard. Some of the fields contain * in brackets with space which are actually not required.
Like : CUSHION GNTS (4 * 5)
Actually it should be: CUSHION GNTS (4*5) .................. Without any space inside the brackets' text/values.
Re: How to update whole table records with one single button click?
Originally Posted by VS2013
Thank you so much for your kind reply. It is working fine. Just last question in this regard. Some of the fields contain * in brackets with space which are actually not required.
Like : CUSHION GNTS (4 * 5)
Actually it should be: CUSHION GNTS (4*5) .................. Without any space inside the brackets' text/values.
Sorry, I don't know. You should ask that question in a new thread, someone here probably knows how.
Re: How to update whole table records with one single button click?
Are you just trying to remove spaces?
Code:
Dim StringWithSpaces = "This String Has A Bunch Of Spaces I DOnt Want ( I Want Them Removed )"
Dim NewString As String = StringWithSpaces.Replace(" ", "")
MsgBox(NewString)
Re: How to update whole table records with one single button click?
Thanks. But I want to amend the below code to replace spaces before and after the astricks "*" and add one space after the period "." wherever int the string in addition to removing spaces as in the below code.
Code:
Dim var = row(col).ToString
row(col) = String.Join(" ", var.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
Note: Remove spaces before and after the astricks "*" wherever in the string. And add one spaceafter the period "."
Re: How to update whole table records with one single button click?
are you really saying that you know how to remove spaces before/after brackets
but not before/after astericks ?
do you not understand your own code ?
also,take a good look at post #12
do not put off till tomorrow what you can put off forever
Re: How to update whole table records with one single button click?
I don't see any SQL forum here to post it. What is the correct forum name, please? The below code does everything that I wanted to modify my table except to remove space before and after the astrick (*)and to add a single space after period (.).
Code:
Dim var = row(col).ToString
row(col) = String.Join(" ", var.Split({" "}, StringSplitOptions.RemoveEmptyEntries)).Replace("( ", "(").Replace(" )", ")")
Note: This code was previously provided by 4x2y in this forum.
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'remove all character within the brackets = ., !'?;:
'that means [\ ] <- put the characters in there
TextBox1.Text = System.Text.RegularExpressions.Regex.Replace(TextBox1.Text, "[\., !'?;:]", "")
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
TextBox1.Text = " CUSHION GNTS (4 * 5) ? !;"
End Sub
End Class
but handle all this with care, dont destroy your Data in the Table !!!
regards
Chris
Last edited by ChrisE; Feb 3rd, 2018 at 06:53 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Re: How to update whole table records with one single button click?
Originally Posted by VS2013
Okay. In that case, just removing space before and after astrick will be enough.
you realise that removing space before and after astrick
is exackt the same as removing space before and after brackets
and in a text field (as opposed to a numeric field) the period is not a decimal or thousands separator
suggestion:
instead of using that one-liner spaces function
write your own spaces function
that does every needed tranformation in a separate line
and as post#12 suggested:
that should really be an update query in your database
do not put off till tomorrow what you can put off forever
Re: How to update whole table records with one single button click?
Originally Posted by IkkeEnGij
no need to worry about that, his database has kind of self-destruction build in
yes, sounds dangerous what he wants to do with the data
let everybody find out on there own
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.