bettybarnes 7 Posting Whiz in Training

hi everyone i got this error Cannot insert explicit value for identity column in table 'Patients' when IDENTITY_INSERT is set to OFF.

i set identity to Yes in sql server 2005..whenever i run my code i get this error message

here's part of my code

Private Function GenerateCaseNumber() As String

        Dim dr As SqlDataReader = Nothing
        Dim com As SqlCommand = Nothing
        Dim value As String = "10000"

        Try
            ' Fetch the latest ID from the database
            cs.Open()
            com = New SqlCommand("SELECT TOP 1 Case_number FROM Patients ORDER BY Case_number DESC", cs)
            dr = com.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                dr.Read()
                value = dr.Item("Case_number")
            End If
            dr.Close()

            ' Increase the ID by 1
            value += 1

            ' Because incrementing a string with an integer removes 0's
            ' we need to replace them. If necessary.
            If value <= 9 Then                'Value is between 0 and 10
                value = "000" & value
            ElseIf value <= 99 Then        'Value is between 9 and 100
                value = "00" & value
            ElseIf value <= 999 Then        'Value is between 999 and 1000
                value = "0" & value
            End If
        Catch ex As Exception
            ' If an error occurs, check the connection state and close it if necessary.
            If cs.State = ConnectionState.Open Then
                cs.Close()
            End If
            value = "10000"
        End Try

        Return value
    End Function

form event i call
txtcase_number.text = generatecasenumber()

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        da.InsertCommand = New SqlCommand("INSERT INTO Case(Case_number) VALUES(@Case_number)", cs)
da.InsertCommand.Parameters.Add("@Case_Number", SqlDbType.VarChar).Value = IIf(String.IsNullOrEmpty(txtCaseNumber.Text), DBNull.Value, txtCaseNumber.Text)
        cs.Open()
        da.InsertCommand.ExecuteNonQuery()
        cs.Close()
        MsgBox("New Patient Successfully admitted!")
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.