472,133 Members | 1,069 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

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

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

Set rsFinger = Nothing

Set GetdbFingerprint = presensiFinger

End Function

With name from class module

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

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?

Oct 18 '10 #1
1 2037
321 Expert 100+
In SQL Server, an auto numbered column would be an number type field (tinyint/smallint/int/bigint/numeric with no scale) with "Identity" specified.

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.

Similar topics

4 posts views Thread by Shahar | last post: by
3 posts views Thread by Fabio Negri Cicotti [MCP] | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.