By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,759 Members | 1,778 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,759 IT Pros & Developers. It's quick & easy.

Auto number. Cannot insert the value NULL into column 'idNo'

P: 19
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
Oct 18 '10 #1
Share this Question
Share on Google+
1 Reply


danp129
Expert 100+
P: 321
In SQL Server, an auto numbered column would be an number type field (tinyint/smallint/int/bigint/numeric with no scale) with "Identity" specified.

E.g.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[myTable] (
  2.     [id] [tinyint] IDENTITY(1,1) NOT NULL,
  3.     [name] [varchar](100) NOT NULL,
  4. )
  5. GO
tinyint will only go up to 255, so choose a data type that works for you.
Oct 18 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.