none
you cannot add or change a record because a related record is required in table "code"..

    Question






  • Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\user\Desktop\Baxter\BaxterOCR_new.mdb;Jet OLEDB:Database Password=12345;") cmd = New OleDbCommand("Insert Into Batch(BatchNo,CodeNo,BarCodeNo,ExpiryDate) Values(@BatchNo,@CodeNo,@BarCodeNo,@ExpiryDate)", con) With cmd.Parameters .Add("@BatchNo", OleDbType.VarChar).Value += txtBatchNo.Text .Add("@CodeNo", OleDbType.VarChar).Value += txtCodeNo.Text .Add("@BarCodeNo", OleDbType.VarChar).Value += ComboBoxBarCodeNo.Text .Add("@ExpiryDate", OleDbType.VarChar).Value += txtExpiryDate.Text End With con.Open() cmd.ExecuteNonQuery() End Sub
    this is my code.

    hi, i am facing this error:" you cannot add or change a record because a related record is required in table "code""
    how do i solve it?

    thanks
    buz
    Thursday, November 12, 2009 8:03 AM

All replies

  • how do i know is the data being save?
    if it is not save, can VB know and promt a msgbox?

    thanks
    buz
    Thursday, November 12, 2009 8:21 AM
  • Hi buz,

     

    Does the initial problem you posted solved?

    If it does, please share the solution here.

    You can add try around the cmd.ExecuteNonQuery. In the catch scope, you can use msgbox to show that “There is an exception thrown. The data is not saved”.

    Meanwhile, you can judge on the return value of cmd.ExecuteNonQuery.

    Dim reusltCount as int = cmd.ExecuteNonQuery

    ResultCount is the number of rows affected.

     

    Best Regards

    Yichun Feng

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 16, 2009 10:10 AM
  • change the parameters to:

    With
    cmd.Parameters
                .Add(new OleDBParameter("@BatchNo", txtBatchNo.Text))

    if i use the cmd.ExcuteNonQuery
    there is an error..
    (you cannot add or change a record because a related record is required in table "code"..)

    if i remove the cmd.ExcuteNonQuery,
    is it running, but the MS access is not updating..
    Tuesday, November 17, 2009 1:23 AM
  • Hi buz,

     

    For your issue, I recommend that you insert parameters like this,

    (Use “[]” for column name and call AddWithValue method)

    cmd = New OleDbCommand("Insert Into [Batch]([BatchNo],[CodeNo],[BarCodeNo],[ExpiryDate]) Values(@BatchNo,@CodeNo,@BarCodeNo,@ExpiryDate)", con)

     

            With cmd.Parameters

                .AddWithValue("@BatchNo", txtBatchNo.Text))

                .AddWithValue("@CodeNo", txtCodeNo.Text))

                .AddWithValue("@BarCodeNo", ComboBoxBarCodeNo.Text))

                .AddWithValue("@ExpiryDate",txtExpiryDate.Text))

            End With

    Here is an important point for the parameters.

    You need to add the parameters according to the sequence in the command. If you add as follows,

                .AddWithValue("@CodeNo", txtCodeNo.Text))

                .AddWithValue("@BatchNo", txtBatchNo.Text))

    The result will not be correct or even throws exception. Because the OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement. See more..

     

    If ExecuteNonQuery throws exception, that means the inserting is not successful. And you can’t delete ExecuteNonQuery since you command hasn’t been executed without calling it.

     

    If you still get exception, please post the detail exception description here.

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 18, 2009 5:16 AM
  • there is still a problem in the
    cmd.ExcuteNonQuery
    and at access side, i have more column but that is not neccessary to me so i am not putting every colum inside..


    this is my most current code
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
            '    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\user\Desktop\Baxter\BaxterOCR_new.mdb;Jet OLEDB:Database Password=12345;")
            Dim cmd As OleDbCommand
            con.Open()
            cmd = New OleDbCommand("Insert Into [Batch]([BatchNo],[OperatorID],[CodeNo],[BarCodeNo],[ExpiryDate]) Values(@BatchNo,@OperatorID,@CodeNo,@BarCodeNo,@ExpiryDate)", con)
            With cmd.Parameters
                .AddWithValue("@BatchNo", txtBatchNo.Text)
                .AddWithValue("@OperatorID", txtOperatorID.Text)
                .AddWithValue("@CodeNo", txtCodeno.Text)
                .AddWithValue("@BarCodeNo", txtBarCodeNo.Text)
                .AddWithValue("@ExpiryDate", txtExpiryDate.Text)
            End With
            con.Close()
            cmd.ExecuteNonQuery()
        End Sub
    thanks
    buz
    Wednesday, November 18, 2009 7:30 AM
  • Hi buz,

    What is the expection you get?


    Best Regards
    Yichun Feng 
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, November 19, 2009 2:37 AM