Post reply

Insert into db (using ASP/VB.NET and SQL Server 2005)

  • November 23, 2009 at 8:13 am

    #213028

    Hi,

    I have a web site using ASP/VB.NET and SQL Server 2005. The db has 4 tables. Only 1 table, Proj, has an index (PK is auto-number - Project_ID). The other tables do not need a PK but, they each have the corresponding Project_ID number from the Proj table. When the user wants to add a new record I want to ensure that record is not already in the db. If it is there then I want to delete the information prior to the Insertion. I want the deletion to always be completed first.

    After the deletion event I want the INSERT to occur when the "Submit" button is clicked. I believe I am OK with that event.

    I welcome and appreciate any coding/syntax suggestions.

    Thank you,

    John

  • November 23, 2009 at 8:43 am

    #1082983

    First, all tables need a PK. Otherwise you'll get into issues at some point trying to work with the individual rows. Set one up for all tables.

    Second, how do you know what the "same information" is? I assume you have some rules. If so, then what you might want to do is put this in a stored procedure. Send in your data, let the stored procedure do a "SELECT" to see if there are matching rows. If so, then you delete the existing rows. Hint, use the IF EXISTS() statement.

    Then do the insert.

  • November 23, 2009 at 10:59 am

    #1083063

    Thanks for the information. Currently I am not using a PK because it is not necessary. I may change that later. I will try the IF Exists() approach for the delete action.

  • November 23, 2009 at 11:20 am

    #1083075

    Adding the PK later will be overhead and more difficult. Adding it now is fairly trivial now, and it won't take much for you to code around it. I'd recommend you do it, even if you don't think you need it.

  • November 23, 2009 at 11:46 am

    #1083092

    Again, thank you.

  • November 23, 2009 at 1:51 pm

    #1083148

    If you want to make sure the data isn't there - then why check at all? Just issue the delete statement, followed by an insert statement.

    If no data matches, then nothing gets deleted. In most cases there's no reason to make 3 data calls when two will do the same thing.

    Steve's suggestion of using a stored proc AND using a PK are both very valid.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • November 24, 2009 at 6:04 am

    #1083391

    Thank you. Will do.

Viewing 7 posts - 1 through 6 (of 6 total)

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