Post reply

Primary key violation

  • March 28, 2008 at 5:28 pm

    #95843

    Hi all,

    I am trying to insert a new record in true db grid with bound data from a vb6 application. The bound control is connecting to a select query in a stored procedure. After I enter the data in the row and then try to click on the row above I get the error message "primary key violation". The primary key consists of one field. How do I change my stored procedure to allow this update to happen?

    Thank you.

  • March 28, 2008 at 6:56 pm

    #795542

    Maybe by first learning relational database basics?

    The error means you're inserting a primary key value that already exists in the table.

  • March 29, 2008 at 12:09 am

    #795573

    [font="Verdana"]Better you check whether the record exist into the table before inserting. You can do in inside the SProc.

    i.e.

    ...

    If Not Exist (Select {PK column(s) From {Table} Where (where criteria)})

    Begin

    {Insert Statement}

    End

    Else

    Begin

    {Record already exist!!!}

    End

    ....

    Thanks,

    Mahesh

    [/font]

    MH-09-AM-8694

  • March 29, 2008 at 7:55 am

    #795595

    All,

    I don't think you understand how bound true db grid works. It displays data from the select query and tries to update the database using the same query. But since I have the primary key, it does not know what to do.

    Thanks.

  • March 30, 2008 at 11:50 pm

    #795774

    ramadesai108 (3/29/2008)

    All,

    I don't think you understand how bound true db grid works. It displays data from the select query and tries to update the database using the same query. But since I have the primary key, it does not know what to do.

    Thanks.

    Ram, as you stated earlier, you are using SProc to Select and Insert / Update as well. Thats why I suggested you, before inserting any record just check whether it is exists into the table. If so, then update it else insert. So just add the condition I have posted earlier.

    Let us know,

    Thanks,

    Mahesh

    MH-09-AM-8694

  • March 31, 2008 at 5:54 am

    #795864

    ramadesai108 (3/29/2008)

    All,

    I don't think you understand how bound true db grid works. It displays data from the select query and tries to update the database using the same query. But since I have the primary key, it does not know what to do.

    Thanks.

    Maybe you should explain it better.

    Resultset from stored proc is readonly. A dbgrid control can update data only if you tell it how to do it: which table, which columns are keys and which are updatable.

    If resultset is from a select statement, it may be smart and figure out from provider and select statement which table, which columns are keys and which are updatable.

    Anyway, you're getting this error, because you're trying to insert key value that already exists in the table.

    Since you didn't figure out this obvious reason, your basic knowledge might be weak.

    So, explain better the problem or learn relational database fundamentals.

  • April 1, 2008 at 1:18 pm

    #796820

    Mahesh,

    I have a stored procedure that does only select. Then In the application I have a bound true db grid that displays the data. When I add new record, the bound adodc control tries to add the data to ado, but it fails giving "primary key violation". I need to change my stored proc. In the events the grid fires, I cannot update the recordset, or else it gives me the error "row cannot be located for updating". I understand your previous suggestion, but that would not work. The procedure takes no parameters.

    Thanks.

  • April 1, 2008 at 10:31 pm

    #797004

    ramadesai108 (4/1/2008)

    Mahesh,

    I have a stored procedure that does only select. Then In the application I have a bound true db grid that displays the data. When I add new record, the bound adodc control tries to add the data to ado, but it fails giving "primary key violation". I need to change my stored proc. In the events the grid fires, I cannot update the recordset, or else it gives me the error "row cannot be located for updating". I understand your previous suggestion, but that would not work. The procedure takes no parameters.

    Thanks.

    [font="Verdana"]Ram, writting SProc only for Select and for Insert, Update and even for Delete using some another way, it does not make any sense. Better you write a single SProc which can do Select, Insert, Update and Delete as well. You need to just pass Mode of the action as a parameter to this SProc. i.e. whether you are going to Insert/Update/Delete.

    If you are well aware with XML, try to implement XML so that you can bind all the Grid data into a single string and pass it to the SProc.

    Let us know, so that we can go into detail. And try to give some real life example with desired O/P as well.

    Thanks,

    Mahesh[/font]

    MH-09-AM-8694

  • April 2, 2008 at 3:01 am

    #797053

    ramadesai108 (3/28/2008)

    Hi all,

    I am trying to insert a new record in true db grid with bound data from a vb6 application. The bound control is connecting to a select query in a stored procedure. After I enter the data in the row and then try to click on the row above I get the error message "primary key violation". The primary key consists of one field. How do I change my stored procedure to allow this update to happen?

    Thank you.

    hi ramadesai108

    the error is not because of your stored procedure, it is from the primary key in your table

    you have to handle this error in vb application itself.

    cheers

    shamsudheen

  • April 2, 2008 at 3:17 am

    #797059

    [font="Verdana"]

    shamshudheen (4/2/2008)

    ramadesai108 (3/28/2008)

    Hi all,

    I am trying to insert a new record in true db grid with bound data from a vb6 application. The bound control is connecting to a select query in a stored procedure. After I enter the data in the row and then try to click on the row above I get the error message "primary key violation". The primary key consists of one field. How do I change my stored procedure to allow this update to happen?

    Thank you.

    hi ramadesai108

    the error is not because of your stored procedure, it is from the primary key in your table

    you have to handle this error in vb application itself.

    cheers

    shamsudheen

    shamshudheen, doing such validation at front end does not make any sence, its always better to do it at back end. Anyhow you have to check whether the records does exists or not. If you will perform this validation at back end it will save, rather i would say it will not increase the network traffic. In a single call to Database, you can do it at back end.

    Thanks,

    Mahesh

    [/font]

    MH-09-AM-8694

  • April 2, 2008 at 4:47 pm

    #797565

    All,

    I don't think that I have explained the situation correctly. I have a true db grid that is bound to an ADODC control. The ADODC is using a stored proc that contains a select statement. There are no parameters passes into the stored proc. When I display the form, it displays the record in the bound grid. When I try to add a row into the grid, the adodc tries to add the row in the database using the same select stored procedure automatically. Before I click on the row above or below, if I fill in the primarykey field value in the grid, then the ADODC gives me an error "Row cannot be located for updating". If I leave the primarykey field value as null, then I get error "violation of primary key".

    I have written many stored procedures with conditions that would either insert or update depending on the criteria. But this is a tough one. I don't know the inner working of ADODC, but it takes the same select query and tries to insert the record into the database.

    Your help is truly appreciated.

    Thank you.

  • April 3, 2008 at 12:14 am

    #797619

    tell me whether i am right in understanding your problem

    u have stored procedure with select query and you connecting this stored procedure to you db grid control. when you add new record you get database error. is i am right

    if so, can you do one thing instead of connecting your stored procedure can you connect directly table itself and check it is giving the same error

    thanks

    shamsudheen

  • April 3, 2008 at 7:45 am

    #797811

    Shamsudheen,

    Thanks for your reply. You are correct in your understanding. I cannot directly connect to a table because it is a join query.

    Thanks,

    Ram.

  • April 3, 2008 at 8:06 am

    #797827

    Ram,

    Can you show me the stored procedure which you connecting to the db grid.

    I think your stored procedure is using multiple tables.if so how and which table the db grid try to insert or update when you trying to insert a new row

    I think you have to learn more about db grid for this situation

    cheers

    shamsudheen

  • April 3, 2008 at 8:35 am

    #797857

    I'm not completely clear what you're trying to do. You mention leaving the primary key null, but you can't insert nulls into a primary key either . . .

    This is what I'd try. Get rid of the primary key on the table, run your app, and then select out the rows from the table. If you have a null in that column, you would have received an error. If you have a duplicate in that column, you would have received an error. Once you understand exactly what the app is doing that is generating an error, you'll have a better idea of what to look for in your code.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply