MySQL - Error 1062 duplicate entry For Primary

Error 1062 duplicate entry For Primary

Sometimes if you work with MySQL/MariaDB database, you’ve got "suprises". One day ago I’ve got MySQL Error when I de-activated primay key on one table, and then re-activate. I’ve got an error like the image above.

When I check the record that show on error message, I found that record is already unique. It shouldn't be cause an error. 

Unique Record

Then, how I did solve those problem.

Well, maybe Solution Number one below solution is not quite practical or good enought but it's work for me.

Solution Number One

        Step 1. First  Backup your Database, if something goes wrong.

        Step 2. run SQL : SELECT DISTINCT * FROM sourcetable

        Step 3. Export or Copy Paste the query result to Excel File

        Step 4. Create SQL Script to inserting data

        Step 5. DELETE source table 

        Step 6. Run SQL Script that we have created on Excel to inserting data.

Re-Active Primary Key

        Step 7. Re-Activate Primary Key on source table

Solution Number Two:

This is quite similiar with solution number one, but this is more "advanced" and  using with pure SQL.

        Step 1. "Clone" table from source table with SQL to clone table

                    INSERT TNTO clone_table SELECT * FROM source_table

        Step 2. DELETE source table 

        Step 3. Insert table source from clone table using SELECT DISTINCT

                    INSERT TINO source_table SELECT DISTINCT * FROM clone_table

        Step 4.  Re-Activate Primary key on source table

Re-Active Primary Key

Maybe it's a MariaDB bug or MariaDB/MySQL still keeps primary key records when we deactivate the primary key on another table/system that we don't know yet if we are not cleaning the data ( delete all). Then when we re-activate the primary key, it causes the error message above.

Good Luck. Hope this is useful.

Software :

MySQL / MariaDB ver 10.4.25

SQLyog Community 

How to Remove Password from Protected Sheet on Ms. Excel

There are several ways to remove password from the protected sheet, but I am choosing one that is simplest and easier. That is Using Google Sheets.

1. Open Google sheet on your browser
2. Click File – Import, and upload your protected Excel files. Choose an option: Replace Spreadsheet.
   
   
   
3. Then Click File – Download as Microsoft ( .xlsx ) files.
   
   
   
4. Open downloaded Excel files and you have unprotected Excel files from your original protected Excel files. It’s Worked!

For your information, I already tried using VBA Editor.

1. Open the Excel file.
2. Press "Alt + F11" to open the VBA editor.
3. In the VBA editor, click on "Insert" and then choose "Module" to insert a new module. In the module window, paste the following code:

            Sub UnprotectSheet()

                Dim ws As Worksheet

                    For Each ws In ActiveWorkbook.Worksheets

                        ws.Unprotect

                    Next ws

            End Sub

Then Press "F5" to run the code. This will unprotect all sheets in the workbook and Save the file

But it still asks you for the password like the image above. So today with this method, we can’t use it anymore.

And the third way is by save as your protected Excel files to .zip files and then edit the .xml files. Yes maybe it works but it’s too complicated for some users.

Good Luck.

Hope this is useful.

Software :

Microsoft® Excel® for Microsoft 365 MSO (Version 2305 Build 16.0.16501.20074) 64-bit

Windows 10 Pro 64 bit.

Visual Basic with Ms Excel as Database

Belum lama ini, saya seorang rekan meminta bantuan dibuatkan sebuah program kecil, yaitu progam untuk membuat cetakan invoice dari file excel. Awalnya saya ingin membuatkan program kecil yang paten menggunakan Visual Basic dan MySQL sebagai database. Tetapi karena keterbatasan waktu saya memutuskan untuk memakai VBA, agar embedded langsung dengan file excel. Tiba - tiba punya ide untuk membuat program dari Visual Basic tetapi menggunakan file Excel ( .xls atau .xlsx ) sebagai database. Setelah browsing kesana kemari akhirnya menemukan caranya, berikut dasar - dasar pemrograman Visual Basic dengan Microsoft Excel.

Pertama yaitu membuat Connection String, yaitu menggunakan provider Jet OLEDB 4.0, pada Visual Basic - References ,saya menggunakan Microsoft ActiveX Data Object 2.6, berikut syntax connection string.

  
    Set Conn = New ADODB.Connection
    With Conn 
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & App.Path &; _
         "\file.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""
        .CursorLocation = adUseClient
        .Open
    End With 

Option HDR = Yes, artinya pada kolom pertama pada worsheet yang dipilih akan menjadi nama field / header.Kemudian Option IMEX=1 yaitu menerima data dari kolom excel dengan berbagai tipe data. Perlu diingat bahwa column di Excel berbeda dengan field pada table di file database dimana 1 field hanya mempunyai 1 tipe data, sedangkan di file excel bisa banyak tipe.

Kemudian untuk penggunakan Query / SQL standart, hanya perlu diingat pengganti table pada Excel adalah worksheet, contoh syntax penggunaan SQL ada di bawah ini :

 
    strquery = "SELECT * FROM [Sheet1$B1:B1124] WHERE COMPONENTS LIKE '%PANEL%'"
    oRS.Open strquery, oConn, adOpenDynamic, adLockReadOnly
    If Not oRS.BOF Then
        Do While Not oRS.EOF
        
        If Trim(oRS.Fields(0).Value) <> "" Then
            List2.AddItem vbTab & oRS.Fields(0).Value
        End If
        
        oRS.MoveNext
        Loop
    End If
    oRS.Close

Pada syntax SQL di atas, table digantikan dengan range baris data yaitu dari baris B1 s/d B1124, kemudian field COMPONENTS adalah salah satu kolom pada file Excel yang akan dicari datanya. Jika anda mendapatkan Error "No value given for one or more required parameters", maka kemungkinan adalah kolom / field yang anda cari setelah syntax WHERE, itu tidak termasuk dalam baris data setelah syntax FROM, jadi disini anda perlu lebih teliti.
Kemudian untuk membuat query dengan banyak nilai kosong / null, bisa menggunakan sintak seperti dibawah ini:

 
    sSQL = " SELECT [CODE], " _
           " IIF([COMPONENTS] is null, ' ', [COMPONENTS]) AS [COMPONENTS], " _
           " FROM [Sheet1$A1:L1124] " _
           " WHERE CODE = '" & Code_Components & "'"
    oRs.Open sSQL, oConn, adOpenStatic
    If Not oRs.BOF Then
        'statement
    End If
    oRs.close

Awalnya saya ingin membuat file Excel tersebut untuk  menyimpan data Invoice, tapi hal itu membuat file Excel menjadi bengkak ukuran-nya. Akhirnya saya memutuskan program ini hanya sebagai mesin pencetak invoice saja, sesuai dengan permintaan teman saya, jadi tidak menyimpan hasil transaksi invoice. Semoga Bermanfaat bagi rekan - rekan.

tools :

Microsoft Visual Basic 6.0

Microsoft Excel 2007

Microsoft Excel Lambat

Ada seorang rekan kerja mengeluhkan tentang Ms Excel yang dia gunakan sangat lambat ketika mengedit dokumen tertentu. Ketika membuka, berpindah worksheet sangat lama. Analisa awal adalah masalah pada dokumen yang dia edit yaitu ada link ke dokumen lain, akan tetapi setting di Ms Excel yang dia gunakan adalah Ask to update automatic links. Artinya dokumen tersebut tidak langsung mengupdate link ke dokumen lain, tetapi akan menanyakan terlebih dahulu. Akhirnya analisa awal gugur. Hari ini dia memberitahu bahwa ketika dia edit di rumah dan tidak terhubung dengan LAN ( Wifi ), Ms Excel berjalan normal. "Apakah saya harus mengedit tanpa terkoneksi dengan jaringan agar Ms Excel saya normal? " ujar dia. Jawab saya " Oke nanti akan saya cari tahu solusinya". Berbekal data terakhir bahwa jika terhubung jaringan Ms Excelnya berjalan normal, saya mencari via google dengan keyword " ms excel lambat ketika terkoneksi dengan jaringan ". Akhirnya saya mendapat solusi yang tepat dari link berikut ini, di dalam blog tersebut disampaikan bahwa ada 2 hal yang menyebabkan Ms Excel menjadi lambat :

1. Default printer adalah printer yang disharing di komputer lain dalam jaringan LAN.

2. Tampilan view dalam worksheet dalam mode pagebreak.

Kedua hal tersebut menyebabkan si Excel ini terus meminta informasi ke printer di komputer lain untuk disesuaikan dengan tampilan dalam worksheet tersebut. Karena masalah ini yang menyebabkan sangat lambat dalam mengedit dokumen tertentu yang posisi view-nya pagebreak. 

Solusinya pindah view pagebreak menjadi normal view, kemudian ganti default printer ke printer lokal, sehingga anda perlu memilih printer hanya ketika akan mencetak. Problem Solved! thx to Kemplang Pedes atas pencerahannya. Semoga bermanfaat juga bagi rekan lain yang mengalami masalah yang sama.

related links :

Ms Word lambat
Ms Word mencetak gambar tidak lengkap

perangkat lunak yang dipakai dalam kasus ini:

Microsoft Windows XP Professional
Microsoft Office 2007

Convert text or string to date - Excel

This time I'll share Ms Excel Trick, that is how to convert text to date in Microsoft Excel. As an example we have such a text format, YYYYMMDD (20120531), we will convert the text into a date format, the formula is  = DATE (LEFT (column_name; 4), MID (column_name, 5, 2); RIGHT (column_name; 2)), as shown above. If we use formula = DATE(column_name) will display an error message "You've entered too few arguments for this function." If we use the cell format it doesn't work.Good Luck.

Send Keys to Microsoft Excel

When  I was editing Ms Excel file, which requires me to edit some  column by requiring pressing the F2  +  Enter . Because the column consists of thousands of rows .Finally I decided to make a simple program from Visual Basic to help my work, the program is the Send Keys ver 1.0, I also include the source code that can be downloaded here, you can modify to be more perfect.  This program help us to automatically press keyboard many times as needed,so we do not have to do a boring job for the same thing many times. How to use this program? Firt select first key  for example is F2 key, then select  delay between the second key  (the unit is milliseconds, it mean  if you want to create a delay  1 second, you must type 1000, if you want to create a delay  0.5 seconds, you must type  500). Then select the second key, and type  number of times to repeat, then press the Start button, the program will automatically minimize within 2 seconds, then will start working in 5 seconds, meaning that within 5 seconds you should be ready to put the cursor in your excel file. Very simple and easy isn't? Good Luck!

How to Fix Corrupt Excel Files?

Not long ago, I was asked to help a friend to repair or recover the damaged excel file for viruses.

After searching with the help of google I finally found a pretty good shareware that is ExcelFix from Cimaware.One of the advantages of this program is simple and easy to use, there is also a feature that I think is most helpful is lost string bindings, bindings lost string will associate with other excel files that are similar to a corrupt excel file.

Here I explain how its Excelfix work.

First, find corrupt or damaged excel files and follow the instructions.





Excelfix will save files with different names for examples : file_name(ExcelFix).xls

And following this the coolest feature of ExcelFix is lost string bindings. And check this following image that show how it works.


You can choose the option how ExcelFix find lost string from inside the excel files or from another excel files.



And then click apply to binding lost string to excel file.

If you have a lot of excel files are corrupted, you can also use the feature multifiles of excelfix. This feature will speed up your work.


I guess I need not write much how to use ExcelFix when compared to other programs, ExcelFix the easiest and most complete features. Have fun, and perhaps this articles will help you.

related links:

The limit of excel

The Limit of Excel

Today, i want to share my experience when i was convert data from .dbf files to excel files. I've used free tools DBF Viewer, this freeware can convert data from .dbf ( clipper ) to .xls or .html files. So with that capabilities i can convert medical record data from old program that used clipper to .xls file. With excel files i can easily modify data, before exported to Ms SQL Server database.
The process is very easy and i didn't get any problems with the data. The data succesfully exported to SQL Server 2008 database. But after that, when i tested the program a few data was missing, not a few but a lot! When i checked the old program (clipper), i founded that a lot of medical record data was not imported to new database( Ms SQL Server 2008). After i check carefully, i found that .xls files have limitiation with the rows. The limit rows of .xls files if 65536 rows, so if you have data row more than 655536 rows, your data will be lost after you exported from .dbf of other data files.

If you want to convert .dbf data to SQL Server or bigger database server, the tips is use freet tools that can convert data to .sql script files, the sql script files has no limitation. So you can run the .sql files on Query Analyzer ( Ms SQL Server ), and you get the complete data from old database. Tools that i've used to convert data from .dbf ( clipper ) to .sql script is DBF Viewer 2000 from Hi Base Group, or you can download here.



the picture take from here