468,457 Members | 1,786 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,457 developers. It's quick & easy.

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

I have been collecting data from ms.access database into a class object

Expand|Select|Wrap|Line Numbers
  1. '_Get and put in data from database Fingerprint(RAS) into CPresensiFingerprint
  2. Public Function GetdbFingerprint() As CPresensiFingerprint
  3. Dim presensiFinger As New CPresensiFingerprint
  4. Dim SQLSelectdbRAS As String
  5. Dim rsFinger As Recordset
  6. Dim dateNow_ As Date
  7.  
  8. dateNow_ = um_TgldanJamSkrg()
  9.  
  10.     SQLSelectdbRAS = "SELECT a.DN, b.DIN, c.PIN, c.UserName, b.Clock, d.ItemName " & _
  11.                      "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
  12.                      "LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
  13.                      "LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
  14.                      "WHERE b.DIN=c.DIN AND Format(b.Clock, 'mm/dd/yyyy')=#" & _
  15.                      Format(dateNow_, "mm/dd/yyyy") & "#"
  16.     Debug.Print SQLSelectdbRAS
  17.     Set rsFinger = da_GetRs(SQLSelectdbRAS)
  18.  
  19.     If Not rsFinger.EOF Then
  20.            presensiFinger.device = rsFinger.Fields("DN").Value
  21.            presensiFinger.idFinger = rsFinger.Fields("DIN").Value
  22.            presensiFinger.Nik = rsFinger.Fields("PIN").Value
  23.            presensiFinger.Name = rsFinger.Fields("UserName").Value
  24.            presensiFinger.DateNow = rsFinger.Fields("Clock").Value
  25.            presensiFinger.timeIn = rsFinger.Fields("Clock").Value
  26.            presensiFinger.AbsentType = rsFinger.Fields("ItemName").Value
  27.     End If
  28.  
  29.     rsFinger.Close
  30.     Set rsFinger = Nothing
  31.  
  32.     Set GetdbFingerprint = presensiFinger
  33.  
  34. End Function
  35.  
  36. With name from class module
  37. CPresensiFingerprint
  38.  
  39. Public PVID As Long
  40. Public device As Integer
  41. Public idFinger As Integer
  42. Public DateNow As Date
  43. Public Nik As String
  44. Public Name As String
  45. Public timeIn As Date
  46. Public timeOut As Date
  47. Public AbsentType As String
  48. Public OTHoursNotApproved As Integer
  49.  
  50. I want input the created data from ms.access into sql server 2005 database with class module object
  51.  
  52. '_Input data from CPresensiFingerprint into dbSIKawan (table finger_data_karyawan)
  53. Public Function SavePresensiIn(ByVal presensiSlot As CPresensiFingerprint) As Long
  54.     Dim rsSIKawan As Recordset
  55.     Dim SQLInsertdbSIKawan As String
  56.     Dim SQLSelectdbSIKawan As String
  57.     Dim PVID As Long
  58.  
  59.     Set presensiSlot = New CPresensiFingerprint
  60.  
  61.     SQLInsertdbSIKawan = "INSERT INTO finger_data_karyawan(Device, idFinger, NIKKary, Namakary," & _
  62.                " TglHadir, TimeIn, TipeAbsen)" & _
  63.                " VALUES('" & presensiSlot.device & "', '" & presensiSlot.idFinger & "', '" & _
  64.                presensiSlot.Nik & "', '" & presensiSlot.Name & "', '" & _
  65.                Format(DateValue(presensiSlot.DateNow), "mm/dd/yyyy") & "', '" & _
  66.                Format(presensiSlot.timeIn, "mm/dd/yyyy hh:nn:ss") & "', '" & _
  67.                presensiSlot.AbsentType & "')"
  68.     Call ExeQUERY(SQLInsertdbSIKawan)
  69.  
  70.     SQLSelectdbSIKawan = "SELECT TOP 1 a.idNo FROM finger_data_karyawan a WHERE a.NIKKary='" & _
  71.                          presensiSlot.Nik & "' AND a.TglHadir='" & _
  72.                          Format(DateValue(presensiSlot.DateNow), "mm/dd/yyyy") & "' ORDER BY a.idNo DESC"
  73.     Set rsSIKawan = da_GetRecord(SQLSelectdbSIKawan)
  74.     PVID = rsSIKawan.Fields("idNo").Value
  75.  
  76.     rsSIKawan.Close
  77.     Set rsSIKawan = Nothing
  78.  
  79.     SavePresensiIn = PVID
  80.  
  81. End Function
  82.  
  83. I want the data that has collected in sql server 2005 from class module with auto number, 
  84.  
  85. Private Sub Form_Load()
  86. Dim presensiSlot As CPresensiFingerprint
  87.  
  88. Call SavePresensiIn(presensiSlot)
  89.  
  90. 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
1 2322
jimatqsi
1,254 Expert 1GB
An auto-number can never be NULL. I don't see anything in your code that is setting idNo to anything, so if this is all the relevant code I am wondering if the column is defined correctly in the table.

You don't tell very much where this error is occuring, only "when running the application", so I think maybe the error is happening elsewhere, where some code is saying "idNo=" NULL something that is null, or else the column is not really defined to be an auto-number.

Jim
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
By using this site, you agree to our Privacy Policy and Terms of Use.