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!")