Insert Error: Index or primary key cannot contain a Null Value : The Official Microsoft ASP.NET Forums

Reply

jslist

Member

42 Points

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 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

Any Ideas?

Reply

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).

"Dream as if you'll live forever, live as if you'll die today." --James Dean

Edited by AceCorban on Nov 02, 2012 06:46 PM
Edited by AceCorban on Nov 02, 2012 06:47 PM
Marked as answer by decker dong - msft on Nov 08, 2012 12:37 AM

Reply

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

<div class="cntGSZero cdArticleHead"> <div>Hai</div> <div>Plase Refere this...</div>

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:

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.