473,396 Members | 2,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 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

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
1 2127
danp129
323 Expert 256MB
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

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

Similar topics

3
by: Chris Gilbert | last post by:
I'm quite stuck with this: I have an import table called ReferenceMatchingImport which contains data that has been sucked from a data submission. The contents of this table have to be imported...
1
by: Ken | last post by:
Need help on the Auto Number or Identity Seed on the Oracle Database I got an Access database that need to be converted to Oracle 9i. Somehow the Trigger we created to simulate the "AUTO NUMBER"...
4
by: Shahar | last post by:
Hi I need to get a field name 'ID'(that is an auto-number field) right after I add a new row to table, it's work like that: myCommand.ExecuteNonQuery(); myCommand.CommandText = "SELECT...
3
by: Fabio Negri Cicotti [MCP] | last post by:
Hi All. I'm trying to insert data into 2 tables (parent-child) using the ADO.NET's SetParentRow method. The parent table has an identity column as primary key. When I execute the code below I...
5
by: Phil Latio | last post by:
I have 2 virtually identical tables and wish to move data between them. Basically one table is called "live_table" and the other is named "suspended_table" and the only difference is that the...
0
by: diane | last post by:
Just trying to upsize using VFP 9 with SQL 2005 using VFP remote views. One in particular keeps coming up and saying Cannot insert the value NULL into column, when I really don't think I am...
2
by: karenkksh | last post by:
Hi, The user_id is set as primary key. "Cannot insert the value NULL into column 'user_id', table 'C:\DOCUMENTS AND SETTINGS\KAREN\MY DOCUMENTS\VISUAL STUDIO...
1
by: billa856 | last post by:
Hi, I am trying to insert Null value in column(ShipDate) in my table.That column(ShipDate)'s type id date/time and format is short date. I am using "" to insert Null in that column(ShipDate)...
6
by: pbd22 | last post by:
Hi. I am trying to insert a zero into the below table and I am being told that: Cannot insert the value NULL into column 'UsageToday', table DB.dbo.Pub_Count'; column does not allow nulls....
1
by: ghjk | last post by:
I'm a new to postgres. i have a table which is having a colomn with auto increment value. When I insert value to that table, it gives an error. First column is the auto increment one....
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

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.