Introduction
This tip discusses how to copy a specific record in a MYSql table with auto increment key.
Background
I have worked with MySql for several years now and there have been cases where I needed to copy (or duplicate) in a table where an auto increment key exists.
The following function for VB.NET is a solution that has been helping me a lot recently.
So, I decided to share it with you all!
Using the Code
This is public shared Boolean function and you can call it from your VB.NET code:
Public Shared Function copy_recod(ByVal tbl As String, _
ByVal tbl_id_name As String, ByVal tbl_id_value As Long) As Boolean
Dim dup_query As String = Nothing
Dim tbl_dat As New DataTable
Dim next_id As Long = 0
Dim copy_query As String = Nothing
Dim col_value As String = Nothing
Dim did_it As Boolean = False
dup_query = "select * from " & tbl & " _
where " & tbl_id_name & "=" & tbl_id_value
tbl_dat = retriveDataToDataGrid(dup_query)
If Not tbl_dat Is Nothing Then
ActionRecord("LOCK TABLES " & tbl & " WRITE")
ActionRecord("insert into " & tbl & _
"(" & tbl_id_name & ")values( NULL)")
next_id = Val(get_single_field("select MAX_
(" & tbl_id_name & ") from " & tbl, 0))
ActionRecord("UNLOCK TABLES")
copy_query = "update " & tbl & " set "
For Each r As DataRow In tbl_dat.Rows
For Each col As DataColumn In tbl_dat.Columns
If col.ColumnName <> tbl_id_name Then
col_value = r(col).ToString
If InStr(col.DataType.ToString, "String") > _
0 Or InStr(col.DataType.ToString, "Date") > 0 Then
copy_query = copy_query & col.ColumnName & _
"='" & col_value & "',"
Else
If IsDBNull(col_value) Or col_value.Length <= 0 Then col_value = 0
copy_query = copy_query & col.ColumnName & _
"=" & col_value & ","
End If
End If
Next
Next
copy_query = copy_query.Remove(copy_query.LastIndexOf(","), 1)
If Right(copy_query, 1) = "=" Then
copy_query = copy_query & "'" & 0 & "'"
End If
copy_query = copy_query & " where " & _
tbl_id_name & "=" & next_id
If ActionRecord(copy_query) > 0 Then
did_it = True
End If
End If
Return did_it
Points of Interest
In order for me to create this open function that could work for any table, regardless of number of fields, I had to use enough variables and have checkpoints to assure a clean and workable code.
The important part of this code is where it recognizes the field type and creates proper SQL command for copy.
ActionRecord()
function is my function to run a query. So you can change that with your command or function to launch the update
query.
History
- 27th July, 2012: Initial version