Click here to Skip to main content
15,844,602 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I create an app with one table using vb.net and entered some data.
Then I decide I wanted another table so I created another table without delete the original table both tables have the same column names.

I would like to use ImportRow to move data from one table to the other table
where the column txYear equals a value in a textbox ie 2021.

I can load this original table in a DataGridView so the table is valid.

QUESTION
Is ImportRow the best way to move the data to the other table?
How would I set a condition where txYear = 2021 so only data in the original table is Imported ?
Would also like to delete from the original table all rows that contain txYear = 2021 ?

What I have tried:

Here is the code that creates the two tables
VB
Public Sub makeTxData()

     'create table TxDataTable String for cmd
     Dim create_table As String = String.Empty
     create_table = "CREATE TABLE IF NOT EXISTS TxData(
                     TID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                     txSortDate TEXT,
                     txAmount TEXT,
                     txYear INTEGER,
                     txSearchMonth INTEGER)"

     Dim dbTable As String = "TxDataTable"

     If Not My.Computer.FileSystem.FileExists(dbTable) Then
         Try
             Using conn As New SQLiteConnection(connStr)
                 conn.Open()
                 Using cmd As New SQLiteCommand(create_table, conn)
                     cmd.ExecuteNonQuery()
                 End Using
             End Using
             tbMessage.Text = "DB Created Select BACK"
         Catch ex As Exception
             tbMessage.Text = "TxData Table FAILED"
         End Try
     End If

 End Sub

Same code structure was used to create the TxArchiveData
VB
Dim dbTable As String = "TxArchiveData"


Here is the code I am trying to use ImportRow in
VB
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
    Dim TxDataTable As New DataTable
    Dim TxArchiveData As New DataTable

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()
        Using cmd As New SQLiteCommand("", conn)

            cmd.CommandText = "SELECT * FROM TxData"
            For Each dr As DataRow In TxDataTable.Rows
                TxArchiveData.ImportRow(dr)
            Next

        End Using
    End Using

End Sub
Posted

It would be quicker to write a simple app that inserts the records into the new table, by SELECTing the relevant ones from the original. See SQLite INSERT[^].
 
Share this answer
 
Comments
Choroid 12-Nov-23 11:57am    
Some what agree Richard but that still leaves the data I move to the other database in the original database. The ideal would be to have the archive data in the original app and still be able to access it. It is a Check Book app and 2 year old data is filling up my DGV. YES I know I could filter it to only show a range of years. Archiving data and bulk removal was the intended goal. Thanks
Richard MacCutchan 12-Nov-23 12:06pm    
You could simplify the entire system by filtering on current year, past twelve months, eighteen months etc.
Choroid 12-Nov-23 13:15pm    
Thought about that but just really want to learn how Clone & Copy OR importrow work
Choroid 13-Nov-23 16:47pm    
Richard I posted and answer sort of. I feel a little ashamed it was AI generated code I found and did not notice till after I refactored it a little and went back to the site I copied it from
I still do not know how to use ImportRow and I am trying to workout how to fix this codes one
short coming
Andre Oosthuizen 14-Nov-23 13:16pm    
a Bit of confusion here - "data I move to the other database in the original database" but question is about one table to another, which is it - table to table - db to db?
The above is not really a full on solution, sorry...

To move data based on a specific condition, such as where your 'txYear' column equals a value from a TextBox as in '2021' your code should look like the following using 'ImportRow' with the same field names in both tables (see my comment above about the confusion from table to table or db to db) -

VB.NET
Imports System
Imports System.Data
Imports System.Data.SQLite

Module Program
    Sub Main()
        Dim connectionString As String = "Your SQLite connection string Hhere..."

        Using sourceConnection As New SQLiteConnection(connectionString)
            Using destinationConnection As New SQLiteConnection(connectionString)
                sourceConnection.Open()
                destinationConnection.Open()

                'Assume you have sourcetable and destinationtable already defined...
                Dim sourceTable As New DataTable("SourceTable")
                Dim destinationTable As New DataTable("DestinationTable")

                'Assume you have fetched data into your sourceTable...

                ' Assuming txtYear is a TextBox with the desired year value as per your question...
                Dim yearToFilter As String = txtYear.Text

                For Each sourceRow As DataRow In sourceTable.Rows
                    'Check if the 'txYear' column equals the specified year...
                    If sourceRow("txYear").ToString() = yearToFilter Then
                        'Use ImportRow to copy the structure and data of the row over...
                        Dim destinationRow As DataRow = destinationTable.NewRow()
                        destinationRow.ImportRow(sourceRow)

                        'Add the new row to your new destination table...
                        destinationTable.Rows.Add(destinationRow)
                    End If
                Next

                'Now you can work with the destinationTable, which contains the copied data based on the condition set in year, in this case 2021...
            End Using
        End Using
    End Sub
End Module
 
Share this answer
 
Is ImportRow the best way to move the data to the other table?
I still do not know how to use "ImportRow" and per @Richard MacCutchan began to think about a simple app that selected the data from one table and inserted that data in another table. So where do I store the data for the Insert function can add the data to the other table.
Did a little Google Search and found some code that looked like my idea Copy Paste and Try
Holly Cow it worked with this realization I went back to the site I found the code on
BIG Surprise it was a AI Code generator site WHAT do I want to admit this even if I refactored the code so I understood the process of connecting to the DB
Some Observations I would never thought to use "@Column1" to refer to the actual Column in my Table
This code has one short coming once you insert the data that is it on the second try you get a
{"constraint failed" & vbCrLf & "UNIQUE constraint failed: TxArchiveData.TID"}
I am not sure how to FIX this a work in I hope progress

Please Do Not Flame Me here is the link to said Revelation
Code Generator - CodePal[^]

Here is my refactored code and some of CodePal AI code
Added Error Trap so data can NOT be Archived Twice
Private Sub DataPull()

    ' Represents a utility function for writing data from one table to another in the same database.
    '
    ' PARAMETERS:
    I()
    If count > 0 Then
        MessageBox.Show("That Year " + tbYear.Text + " Has Been Archived", "Warning", MessageBoxButtons.OK)
    End If
    Dim sourceTable As String = "TxData"
    Dim destinationTable As String = "TxArchiveData"

    ' EXCEPTION:
    ' Throws SqlException if there is an error executing the SQL query.

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()
        ' Create a SQL command to select all data from the source table.
        Using cmd As New SQLiteCommand("", conn)
            cmd.CommandText = $"SELECT * FROM {sourceTable} WHERE txYear =" & tbYear.Text
            ' Execute the select command and retrieve the data.
            Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
                ' Create a SQL command to insert the data into the destination table.
                Using cmd2 As New SQLiteCommand("", conn)
                    cmd2.CommandText = $"INSERT INTO {destinationTable} VALUES (@Column1, @Column2, @Column3,@Column4,@Column5)"
                    ' Prepare the insert command parameters.
                    cmd2.Parameters.Add("@Column1", DbType.Int64)
                    cmd2.Parameters.Add("@Column2", DbType.String)
                    cmd2.Parameters.Add("@Column3", DbType.String)
                    cmd2.Parameters.Add("@Column4", DbType.Int64)
                    cmd2.Parameters.Add("@Column5", DbType.Int64)

                    ' Loop through the data and insert it into the destination table.
                    While rdr.Read()
                        ' Set the parameter values.
                        cmd2.Parameters("@Column1").Value = rdr.GetInt64(0)
                        cmd2.Parameters("@Column2").Value = rdr.GetString(1)
                        cmd2.Parameters("@Column3").Value = rdr.GetString(2)
                        cmd2.Parameters("@Column4").Value = rdr.GetInt64(3)
                        cmd2.Parameters("@Column5").Value = rdr.GetInt64(4)

                        ' Execute the insert command.
                        cmd2.ExecuteNonQuery()
                        count = count + 1
                        tbInfo.Text = count.ToString
                    End While
                End Using
            End Using
            End Using
    End Using
End Sub
Private Sub I()
    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()

        Using cmd As New SQLiteCommand("", conn)
            cmd.CommandText = "SELECT COUNT(*) FROM TxArchiveData WHERE txYear = " & tbYear.Text
            'Dim count As Integer Needs to be declard as toplevel variable
            'OR in a DataModule as gv_count it is used in DataPull() Sub
            cmd.Parameters.AddWithValue("@value", tbYear)
            count = CInt(cmd.ExecuteScalar())
        End Using
    End Using

End Sub
 
Share this answer
 
v2
Comments
Richard MacCutchan 14-Nov-23 3:43am    
Well that is pretty much what I suggested in my Solution above. Of course, if you wroite the code by hand you would use the actual column names rather than the AI generated ones.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900