sqleception was unhandled

[bettybarnes]

this is the error message...

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Admission_Record_PT_Chart". The conflict occurred in database "HospitalManagementSystem", table "dbo.PT_Chart", column 'Chart_ID'.
The statement has been terminated.

my code is

Imports System.Data.SqlClient
Public Class Admission

    Private cs As New SqlConnection("Data Source=SAbio;Initial Catalog=HospitalManagementSystem;User ID=sa;Password=TRACERT123")
    Private da As New SqlDataAdapter("SELECT * FROM Admission_Record", cs)
    Private ds As New DataSet
    Private cmd As New SqlCommandBuilder(da)

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

        da.InsertCommand = New SqlCommand("INSERT INTO Admission_Record VALUES(@IPD_ID, @LName, @FName,@MName, @Address, @Birthplace, @Birthdate, @Age, @Sex, @Room_ID, @CivilStatus, @Nationality, @Religion, @Date_Admitted, @Date_Discharged, @Time_Admitted, @Time_Discharged, @Chart_ID, @Occupation)", cs)
        da.InsertCommand.Parameters.Add("@IPD_ID", SqlDbType.VarChar).Value = TxtIPD_ID.Text
        da.InsertCommand.Parameters.Add("@LName", SqlDbType.VarChar).Value = TxtLName.Text
        da.InsertCommand.Parameters.Add("@FName", SqlDbType.VarChar).Value = TxtFName.Text
        da.InsertCommand.Parameters.Add("@MName", SqlDbType.VarChar).Value = TxtMName.Text
        da.InsertCommand.Parameters.Add("@Address", SqlDbType.VarChar).Value = TxtAddress.Text
        da.InsertCommand.Parameters.Add("@Birthplace", SqlDbType.VarChar).Value = TxtBirthplace.Text
        da.InsertCommand.Parameters.Add("@Birthdate", SqlDbType.VarChar).Value = TxtBirthdate.Text
        da.InsertCommand.Parameters.Add("@Age", SqlDbType.VarChar).Value = TxtAge.Text
        da.InsertCommand.Parameters.Add("@Sex", SqlDbType.VarChar).Value = cbSex.Text
        da.InsertCommand.Parameters.Add("@Room_ID", SqlDbType.VarChar).Value = TxtRmID.Text
        da.InsertCommand.Parameters.Add("@CivilStatus", SqlDbType.VarChar).Value = cbCStatus.Text
        da.InsertCommand.Parameters.Add("@Nationality", SqlDbType.VarChar).Value = TxtNationality.Text
        da.InsertCommand.Parameters.Add("@Religion", SqlDbType.VarChar).Value = TxtReligion.Text
        da.InsertCommand.Parameters.Add("@Date_Admitted", SqlDbType.VarChar).Value = TxtDate_Admitted.Text
        da.InsertCommand.Parameters.Add("@Date_Discharged", SqlDbType.VarChar).Value = TxtDate_Discharged.Text
        da.InsertCommand.Parameters.Add("@Time_Admitted", SqlDbType.VarChar).Value = TxtTime_Admitted.Text
        da.InsertCommand.Parameters.Add("@Time_Discharged", SqlDbType.VarChar).Value = TxtTime_Discharged.Text
        da.InsertCommand.Parameters.Add("@Chart_ID", SqlDbType.VarChar).Value = TxtChart_ID.Text
        da.InsertCommand.Parameters.Add("@Occupation", SqlDbType.VarChar).Value = txtOccupation.Text

        cs.Open()
        da.InsertCommand.ExecuteNonQuery()
        cs.Close()
    End Sub

what do i do if i'm gonna put a blank on a field? if i leave it as null?

[CodeWord]

Please correct me if I misunderstood this.

What I gather is that you are inserting an Admission Record, but don't want to insert a chartID?

If the chartID allows nulls, then you can do this:

da.InsertCommand.Parameters.Add("@Chart_ID", SqlDbType.VarChar).Value = iif(String.IsNullOrEmpty(TxtChart_ID.Text),DBNull.Value,TxtChart_ID.Text)

[bettybarnes]

I already used your code but it sends the same error message..

[CodeWord]

If you have access to the Admission_Record table, can you verify that the CHARTID field allows NULLS?

If not, then you will either need to change it so that it does, or you will have to require a valid ChartID prior to inserting the record.

To help you understand, the primary key / foreign key relationship requires that the value inserted into ChartID in the Admission_Record table HAS to exist in the PT_Chart table. If it does not then you will get an exception. If you do not want to specify a chartID, then that column in the Admission_Record table has to allow nulls.

There may be other FK relationships on that table so even after fixing this one issue, you could get the same error, but for a different field. The same rules apply though.

[bettybarnes]

hi tnx.. it was running well when i put chart_id CH000010 specified in pt_chart table..
but what if i want to admit a patient and create a new chart_id in the admission_record.. is it possible?

[bettybarnes]

hi can u help me also how to refresh this means when i add a new patient msgbox will appear and new all fields are cleared again for new transaction.. i already have

[bettybarnes]

i already had set allow nulls of chart_id in admission_record...

[CodeWord]

what if i want to admit a patient and create a new chart_id in the admission_record.. is it possible?

Yes, it is possible. Simply insert the new chartID and information first, then insert the Admission_Record record

I would advise you to learn about transactions and make sure that these two inserts are transactional.

hi can u help me also how to refresh this means when i add a new patient msgbox will appear and new all fields are cleared again for new transaction.. i already have

I'm not sure I completely understand.

If you have databound your controls, and you are using a CurrencyManager, then you can simply add a new record to your internal datatable and the controls will automatically clear because you are on a new record.

Databinding is a very handy way of managing multiple controls and keeping them all on the current record.

There are lots of articles on databinding in VB.net and using a CurrencyManager.

[bettybarnes]

hmm tnx what i meant is if i added a patient the form's fields are cleared out and ready for new transaction.. because in my project i could add patient but the problem is, it wont refresh..