I'm inserting a new record into an Access db. The primary key, an autonumber, is called "empID" I don't include that in my insert statement. I'm coming up with this error:
System.Data.OleDb.OleDbException (0x80004005):
Index or primary key cannot contain a Null value. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior,
String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at Tools_Employee
Here's the aspx.vb code:
Protected Sub btnSubmit_Click(sender As Object, e As System.EventArgs) Handles btnSubmit.Click
Dim strSQL, fName, lName, thisHireDate As String
Dim hDate As Date
thisHireDate = txtAnniversary.Text
hDate = CDate(thisHireDate)
fName = txtFirstName.Text
lName = txtLastName.Text
strSQL = "INSERT INTO EMPLOYEES (EmployeeNumber, Fname, Lname, Extension, [Title], [Functions], DeptHead, Dept, DeptContact, "
strSQL = strSQL + "Area, Chief, DeptDirector, DirectorOfOps, AdminAsst, EmployeeEmail, HireDate) "
strSQL = strSQL + "Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,#" & thisHireDate & "# )"
Dim con = EmployeePhone.addData
Using con
Using cmd As New OleDbCommand(strSQL, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@EmployeeNumber", txtEmpNo.Text)
cmd.Parameters.AddWithValue("@Fname", txtFirstName.Text)
cmd.Parameters.AddWithValue("@Lname", txtLastName.Text)
cmd.Parameters.AddWithValue("@Extension", txtExtension.Text)
cmd.Parameters.AddWithValue("@Title", txtTitle.Text)
cmd.Parameters.AddWithValue("@functions", txtJobFunction.Text)
If (chkDeptHead.Checked = True) Then
cmd.Parameters.AddWithValue("@deptHead", True)
Else
cmd.Parameters.AddWithValue("@deptHead", False)
End If
cmd.Parameters.AddWithValue("@dept", ddDept.SelectedValue)
If (chkContact.Checked = True) Then
cmd.Parameters.AddWithValue("@deptContact", True)
Else
cmd.Parameters.AddWithValue("@deptContact", False)
End If
cmd.Parameters.AddWithValue("@Area", ddArea.SelectedValue)
If (chkChief.Checked = True) Then
cmd.Parameters.AddWithValue("@Chief", True)
Else
cmd.Parameters.AddWithValue("@Chief", False)
End If
If (chkChief.Checked = True) Then
cmd.Parameters.AddWithValue("@DeptDirector", True)
Else
cmd.Parameters.AddWithValue("@DeptDirector", False)
End If
If (chkDirector.Checked = True) Then
cmd.Parameters.AddWithValue("@DirectorOfOps", True)
Else
cmd.Parameters.AddWithValue("@DirectorOfOps", False)
End If
If (chkAdmin.Checked = True) Then
cmd.Parameters.AddWithValue("@AdminAsst", True)
Else
cmd.Parameters.AddWithValue("@AdminAsst", False)
End If
cmd.Parameters.AddWithValue("@EmployeeEmail", txtEmail.Text)
Try
con.Open()
cmd.ExecuteNonQuery()
lbl1.Text = "<font color='red'>" & fName & " " & lName & " has been added to the database.</font>"
clearFields()
Catch ex As OleDbException
lbl2.Text = ex.ToString & " / " & strSQL
lbl1.Text = "<font color='red'>" & fName & " " & lName & " has not been added to the database.</font>"
Finally
con.Close()
End Try
End Using
End Using
End Sub
Here's the .vb class function it uses
Public Shared Function addData() As OleDbConnection
Dim con As New OleDbConnection(GetConnectionString.ToString())
Return con
End Function
Private Shared Function GetConnectionString() As String
Dim returnValue As String = Nothing
Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("EmployeeCS")
returnValue = settings.ConnectionString
Return returnValue
End Function
I think the problem is in how your DB is configured, not the code.
What database are you using? I know in MSSQL you need to set the primary key to be "Identity" as well, and ensure that it has an initial seed and increment setting. Or, if you are not using an int and are using a string or uniqueIdentifier, you will need
to establish how it gets its value ("NEWID()" in the case of uniqueidentifier).
"Dream as if you'll live forever, live as if you'll die today." --James Dean
You tried to add a new record but did not enter a value in the field that contains the primary key.
You tried to add a Null value to a primary key field.
You executed a query that tried to put a Null value in a primary key field.
What Is a Primary Key?
A primary key is a field or set of fields in your table that provide Microsoft Office Access 2007 with a unique identifier for every row. In a relational database, such as an Office Access 2007 database, you divide your information into separate, subject-based
tables. You then use table relationships and primary keys to tell Access how to bring the information back together again. Access uses primary key fields to quickly associate data from multiple tables and combine that data in a meaningful way.
Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary
key.
An example of a poor choice for a primary key would be a name or address. Both contain information that might change over time.
Access ensures that every record has a value in the primary key field, and that the value is always unique.
What Is a Null?
A Null is a value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Microsoft Visual Basic, the Null keyword indicates a Null value. Some fields,
such as primary key fields, cannot contain Null.
Solution
To solve this problem, you must enter a value in the primary key field before moving to another record.
jslist
Member
42 Points
33 Posts
Insert Error: Index or primary key cannot contain a Null Value
Nov 02, 2012 05:45 PM|LINK
I'm inserting a new record into an Access db. The primary key, an autonumber, is called "empID" I don't include that in my insert statement. I'm coming up with this error:
System.Data.OleDb.OleDbException (0x80004005): Index or primary key cannot contain a Null value. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at Tools_Employee
Here's the aspx.vb code:
Protected Sub btnSubmit_Click(sender As Object, e As System.EventArgs) Handles btnSubmit.Click Dim strSQL, fName, lName, thisHireDate As String Dim hDate As Date thisHireDate = txtAnniversary.Text hDate = CDate(thisHireDate) fName = txtFirstName.Text lName = txtLastName.Text strSQL = "INSERT INTO EMPLOYEES (EmployeeNumber, Fname, Lname, Extension, [Title], [Functions], DeptHead, Dept, DeptContact, " strSQL = strSQL + "Area, Chief, DeptDirector, DirectorOfOps, AdminAsst, EmployeeEmail, HireDate) " strSQL = strSQL + "Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,#" & thisHireDate & "# )" Dim con = EmployeePhone.addData Using con Using cmd As New OleDbCommand(strSQL, con) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@EmployeeNumber", txtEmpNo.Text) cmd.Parameters.AddWithValue("@Fname", txtFirstName.Text) cmd.Parameters.AddWithValue("@Lname", txtLastName.Text) cmd.Parameters.AddWithValue("@Extension", txtExtension.Text) cmd.Parameters.AddWithValue("@Title", txtTitle.Text) cmd.Parameters.AddWithValue("@functions", txtJobFunction.Text) If (chkDeptHead.Checked = True) Then cmd.Parameters.AddWithValue("@deptHead", True) Else cmd.Parameters.AddWithValue("@deptHead", False) End If cmd.Parameters.AddWithValue("@dept", ddDept.SelectedValue) If (chkContact.Checked = True) Then cmd.Parameters.AddWithValue("@deptContact", True) Else cmd.Parameters.AddWithValue("@deptContact", False) End If cmd.Parameters.AddWithValue("@Area", ddArea.SelectedValue) If (chkChief.Checked = True) Then cmd.Parameters.AddWithValue("@Chief", True) Else cmd.Parameters.AddWithValue("@Chief", False) End If If (chkChief.Checked = True) Then cmd.Parameters.AddWithValue("@DeptDirector", True) Else cmd.Parameters.AddWithValue("@DeptDirector", False) End If If (chkDirector.Checked = True) Then cmd.Parameters.AddWithValue("@DirectorOfOps", True) Else cmd.Parameters.AddWithValue("@DirectorOfOps", False) End If If (chkAdmin.Checked = True) Then cmd.Parameters.AddWithValue("@AdminAsst", True) Else cmd.Parameters.AddWithValue("@AdminAsst", False) End If cmd.Parameters.AddWithValue("@EmployeeEmail", txtEmail.Text)Try con.Open() cmd.ExecuteNonQuery() lbl1.Text = "<font color='red'>" & fName & " " & lName & " has been added to the database.</font>" clearFields() Catch ex As OleDbException lbl2.Text = ex.ToString & " / " & strSQL lbl1.Text = "<font color='red'>" & fName & " " & lName & " has not been added to the database.</font>" Finally con.Close() End Try End Using End Using End SubHere's the .vb class function it uses
Public Shared Function addData() As OleDbConnection Dim con As New OleDbConnection(GetConnectionString.ToString()) Return con End Function Private Shared Function GetConnectionString() As String Dim returnValue As String = Nothing Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("EmployeeCS") returnValue = settings.ConnectionString Return returnValue End FunctionAny Ideas?
AceCorban
Star
12318 Points
2269 Posts
Re: Insert Error: Index or primary key cannot contain a Null Value
Nov 02, 2012 06:46 PM|LINK
I think the problem is in how your DB is configured, not the code.
What database are you using? I know in MSSQL you need to set the primary key to be "Identity" as well, and ensure that it has an initial seed and increment setting. Or, if you are not using an int and are using a string or uniqueIdentifier, you will need to establish how it gets its value ("NEWID()" in the case of uniqueidentifier).
RameshRajend...
Star
7983 Points
2099 Posts
Re: Insert Error: Index or primary key cannot contain a Null Value
Nov 02, 2012 07:00 PM|LINK
Index or primary key cannot contain a Null value. (Error 3058)
</div> <div class="cdArticle cntGSGrid18 cntGSFirst"> <div class="cdArticleBody"> <div class="cdArticleText cntArticleBody"> <div class="devdocvsbody" xmlns:mshelp="urn:schemas-microsoft-com:mshelp" xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">Possible causes:
What Is a Primary Key?
A primary key is a field or set of fields in your table that provide Microsoft Office Access 2007 with a unique identifier for every row. In a relational database, such as an Office Access 2007 database, you divide your information into separate, subject-based tables. You then use table relationships and primary keys to tell Access how to bring the information back together again. Access uses primary key fields to quickly associate data from multiple tables and combine that data in a meaningful way.
Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key.
An example of a poor choice for a primary key would be a name or address. Both contain information that might change over time.
Access ensures that every record has a value in the primary key field, and that the value is always unique.
What Is a Null?
A Null is a value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Microsoft Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, cannot contain Null.
Solution
To solve this problem, you must enter a value in the primary key field before moving to another record.
</div> </div> </div> </div>Thank u
oned_gk
All-Star
30919 Points
6328 Posts
Re: Insert Error: Index or primary key cannot contain a Null Value
Nov 03, 2012 01:33 AM|LINK
Make your primery key field of EMPLOYEES in access table to autonumber
nikunjnandan...
Participant
872 Points
223 Posts
Re: Insert Error: Index or primary key cannot contain a Null Value
Nov 03, 2012 05:59 AM|LINK
As error says you are going to insert NULL value in primary key.
Primary key never allows you to null value.
In Sql server there is facility of identity column.please search same for access.
or do manually entry in primary key column. manually entry is not good idea.
Nikunj Nandaniya
My Blog
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Insert Error: Index or primary key cannot contain a Null Value
Nov 03, 2012 06:24 AM|LINK
Hi,
Please debug your app by pressing F5 to see what statement your error or exception is thrown out on?
jslist
Member
42 Points
33 Posts
Re: Insert Error: Index or primary key cannot contain a Null Value
Nov 15, 2012 01:06 PM|LINK
I ended up creating a new page with the code and it worked.