I have been collecting data from ms.access database into a class object
'_Get and put in data from database Fingerprint(RAS) into CPresensiFingerprint
Public Function GetdbFingerprint() As CPresensiFingerprint
Dim presensiFinger As New CPresensiFingerprint
Dim SQLSelectdbRAS As String
Dim rsFinger As Recordset
Dim dateNow_ As Date
dateNow_ = um_TgldanJamSkrg()
SQLSelectdbRAS = "SELECT a.DN, b.DIN, c.PIN, c.UserName, b.Clock, d.ItemName " & _
"FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
"LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
"LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
"WHERE b.DIN=c.DIN AND Format(b.Clock, 'mm/dd/yyyy')=#" & _
Format(dateNow_, "mm/dd/yyyy") & "#"
Debug.Print SQLSelectdbRAS
Set rsFinger = da_GetRs(SQLSelectdbRAS)
If Not rsFinger.EOF Then
presensiFinger.device = rsFinger.Fields("DN").Value
presensiFinger.idFinger = rsFinger.Fields("DIN").Value
presensiFinger.Nik = rsFinger.Fields("PIN").Value
presensiFinger.Name = rsFinger.Fields("UserName").Value
presensiFinger.DateNow = rsFinger.Fields("Clock").Value
presensiFinger.timeIn = rsFinger.Fields("Clock").Value
presensiFinger.AbsentType = rsFinger.Fields("ItemName").Value
End If
rsFinger.Close
Set rsFinger = Nothing
Set GetdbFingerprint = presensiFinger
End Function
With name from class module
CPresensiFingerprint
Public PVID As Long
Public device As Integer
Public idFinger As Integer
Public DateNow As Date
Public Nik As String
Public Name As String
Public timeIn As Date
Public timeOut As Date
Public AbsentType As String
Public OTHoursNotApproved As Integer
I want input the created data from ms.access into sql server 2005 database with class module object
'_Input data from CPresensiFingerprint into dbSIKawan (table finger_data_karyawan)
Public Function SavePresensiIn(ByVal presensiSlot As CPresensiFingerprint) As Long
Dim rsSIKawan As Recordset
Dim SQLInsertdbSIKawan As String
Dim SQLSelectdbSIKawan As String
Dim PVID As Long
Set presensiSlot = New CPresensiFingerprint
SQLInsertdbSIKawan = "INSERT INTO finger_data_karyawan(Device, idFinger, NIKKary, Namakary," & _
" TglHadir, TimeIn, TipeAbsen)" & _
" VALUES('" & presensiSlot.device & "', '" & presensiSlot.idFinger & "', '" & _
presensiSlot.Nik & "', '" & presensiSlot.Name & "', '" & _
Format(DateValue(presensiSlot.DateNow), "mm/dd/yyyy") & "', '" & _
Format(presensiSlot.timeIn, "mm/dd/yyyy hh:nn:ss") & "', '" & _
presensiSlot.AbsentType & "')"
Call ExeQUERY(SQLInsertdbSIKawan)
SQLSelectdbSIKawan = "SELECT TOP 1 a.idNo FROM finger_data_karyawan a WHERE a.NIKKary='" & _
presensiSlot.Nik & "' AND a.TglHadir='" & _
Format(DateValue(presensiSlot.DateNow), "mm/dd/yyyy") & "' ORDER BY a.idNo DESC"
Set rsSIKawan = da_GetRecord(SQLSelectdbSIKawan)
PVID = rsSIKawan.Fields("idNo").Value
rsSIKawan.Close
Set rsSIKawan = Nothing
SavePresensiIn = PVID
End Function
I want the data that has collected in sql server 2005 from class module with auto number,
Private Sub Form_Load()
Dim presensiSlot As CPresensiFingerprint
Call SavePresensiIn(presensiSlot)
End Sub
but when i running the application. i get error message.
'Cannot insert the value NULL into column 'idNo', table 'dbSIKawan.dbo.finger_data_karyawan'; column does not allow nulls. INSERT fails'
I want the all data insert into sql server database with auto number, so if I want to add other data, the old data is not affected by new data.
Can anyone help me?
Regards,
Octo