Salam hangat buat smua...
Ceritanya saya bikin prosedur untuk mindahin data (upload) dari *.xls ke
databse SQL Server, ketika masih pake Ms Access sebagai database saya tidak
mendapatkan masalah apa pun lancar-lancar aja.
Nah ketika ku pindahin database ke SQL Server 2000 sesuai permintaan, setiap
kali menjalankan modul tersebut timbul error, tapi ketika ditutup seluruh
project trus login lagi, proses terebut berjalan normal, ini terjadi ketika
saya masih menjalankan aplikasi dengan perintah Run/F5 (melalui project),
tapi ketika sudah saya compile, error tersebut tidak muncul
Takutnya saya itu akan terjadi dikemudian hari, yang pastinya mempengaruhi
kepercayaan customer dong.
Ini error yang muncul "Selected collating sequence not supported by the
operating system"
Ini potongan kode prosedur yang dijalankan
Dim dbNilai As AdoClass, dbExl As ADODB.Connection ==(anggap bahwa saya
sudah bikin sebuah modul AdoClass untuk koneksi ke SQl Server)
Dim WithEvents rsN As ADODB.Recordset
Dim WithEvents rsNilai As ADODB.Recordset
Dim fl As String, fold As String, u As Long, nemu As String, nemulagi As
String, sukses As Boolean, nulRecord As Long, sql1 As String
fold = Left(txtfileEris, SlashEnd)
fl = Mid(txtfileEris, Len(fold) + 1)
Set dbNilai = New AdoClass
dbNilai.DbConnect
Set dbExl = New ADODB.Connection
Set rsN = New ADODB.Recordset
Set rsNilai = New ADODB.Recordset
dbExl.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtfileEris &
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
rsN.CursorLocation = adUseClient
sql = "select nim from [Sheet1$] where nilai is null"
rsN.Open sql, dbExl, adOpenStatic, adLockReadOnly ==> kalo di debug, proses
menunjukan letak kesalahan itu disini
If Not rsN.EOF Then
nulRecord = rsN.RecordCount - 1
Else
nulRecord = 0
End If
rsN.Close
If nulRecord > 0 Then
MsgBox "DITEMUI : " & nulRecord & " NILAINYA TIDAK ADA ATAU BUKAN A-E,
PROSES DILEWATKAN", vbInformation, "INFO"
End If
rsN.Open "select nim,kode_mk,nilai,smt,inisial_dosen from [Sheet1$] where
nilai IN ('A','B','C','D','E')", dbExl, adOpenStatic, adLockReadOnly
sukses = True
For u = 0 To rsN.RecordCount - 1
'nemu = Ketemu(rsN(0), rsN(1))
'If Left(nemu, 1) = "0" Then
If dbNilai.ExecQuery("insert into tabel_nilaix
(nim,kode_mk,nilai,smt,inisial_dosen) values ('" & rsN(0) & "','" & rsN(1) &
"','" & rsN(2) & "','" & rsN(3) & "','" & rsN(4) & "')") = False Then
sukses = False
Exit For
End If
rsN.MoveNext Next 'tambahan
If sukses = False Then
MsgBox "Gagal Upload Record # " & rsN.AbsolutePosition, vbCritical,
"ERROR"
Else
MsgBox "Upload Record Nilai Berhasil", vbInformation, "INFO"
End If
'Set rsNilai = Nothing
Set rsNilai = dbNilai.SelectRecord(sql1)
If Not rsNilai.EOF Then
nulRecord = rsNilai.RecordCount - 1
Else
nulRecord = 0
End If
Set rsNilai = Nothing
If nulRecord > 0 Then
MsgBox "DITEMUI : " & nulRecord & " NILAINYA TIDAK ADA ATAU BUKAN A-E,
PROSES DILEWATKAN", vbInformation, "INFO"
End If
Set rsNilai = dbNilai.SelectRecord("select
nim,kode_mk,nilai,smt,inisial_dosen from tabel_nilaix where nilai IN
('A','B','C','D','E')")
sukses = True
For u = 0 To rsNilai.RecordCount - 1
nemulagi = Ketemu(rsNilai(0), rsNilai(1))
If Left(nemulagi, 1) = "0" Then
If dbNilai.ExecQuery("insert into tabel_nilai
(nim,kode_mk,nilai,smt,inisial_dosen) values ('" & rsNilai(0) & "','" &
rsNilai(1) & "','" & rsNilai(2) & "','" & rsNilai(3) & "','" & rsNilai(4) &
"')") = False Then
sukses = False
Exit For
End If
Else
If Mid(nemulagi, 2, 1) = "A" Or Mid(nemulagi, 2, 1) = "B" Or
Mid(nemulagi, 2, 1) = "C" Or Mid(nemulagi, 2, 1) = "D" Or Mid(nemulagi, 2,
1) = "E" Then
If Asc(UCase(rsNilai("nilai"))) < Asc(Mid(nemulagi, 2, 1)) Then
If dbNilai.ExecQuery("update tabel_nilai set nilai='" &
rsNilai("nilai") & "',smt='" & rsNilai("smt") & "',inisial_dosen='" &
rsNilai("inisial_dosen") & "' where nim='" & rsNilai("nim") & "' and
kode_mk='" & rsNilai("kode_mk") & "'") = False Then
sukses = False
Exit For
End If
End If
End If
End If
rsNilai.MoveNext
Next
If sukses = False Then
MsgBox "Gagal Upload Record # " & rsN.AbsolutePosition, vbCritical,
"ERROR"
Else
MsgBox "Upload Record Nilai Berhasil", vbInformation, "INFO"
End If
Set rsNilai = Nothing
Set rsN = Nothing
dbExl.Close
Set dbExl = Nothing
Exit Sub
errHandle:
MsgBox Err.Description, vbCritical, "ERROR"
[Non-text portions of this message have been removed]
