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