Inserting records to database from multiple checkboxes in vb.net

Question

I have a table like these:

Club Table contains: ClubID(IDENTITY), Name, Address

Genre table contains: GenreID and Genre

ClubGenre table contains Club ID, GenreID

Club has a one to many relationship with Genre

I created an application that functions like a database editor. I have a group of checkbox contained in a group box labeled Genres. It contains the Genre in the Genre table. This is not databound though, I typed it manually. I'm having troubles with my Add/Save button. What I'm basically trying to do is this

• Click New record button
• textboxes and checkboxes will be enabled
• input data
• click save
• Insert Name and address into club table (I know how to do it up to this part)

Since my ClubID is IDENTITY, it will be auto generated right. I need this ClubID to insert data into my ClubGenre table.

Let's say checkbox 1 labeled Pop is checked . In my genre table, it contains a record namely 1 as genreID and Pop as genre

This means my insert statement will be like:

INSERT INTO Genre VALUES(*thenewlyaddedClubID*,1)

Now if checkbox 1,3,5 are checked it will be like:

INSERT INTO Genre VALUES(*thenewlyaddedClubID*,1)
INSERT INTO Genre VALUES(*thenewlyaddedClubID*,3)
INSERT INTO Genre VALUES(*thenewlyaddedClubID*,5)

I've come up with a solution in retrieving the newly added CLUB ID but can't put it all together yet. I'm thinking of putting the new clubID in a hidden textbox using the statement

SELECT MAX(clubID) AS barid FROM club

Answer 1

using the highest ID is error prone!

You can retrieve the last inserted ID with the @@IDENTITY, maybe even better though would be SCOPE_IDENTITY, which returns the last ID within the scope.

here is an example from the MSDN:

USE AdventureWorks2008R2;
GO
--Display the value of LocationID in the last row in the table.
SELECT MAX(LocationID) FROM Production.Location;
GO
INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';
GO
--Display the value of LocationID of the newly inserted row.
SELECT MAX(LocationID) FROM Production.Location;
GO

edit: do you really want to use checkboxes for the genre? Why not a combobox with genres and an add button, that puts clubIDs and GenreIDs into a separate helper table, so you can later add genres without changing the interface ... just sayin

Answer 2

Using the MAX(clubID) is not safe (we can't be sure how the database generates the ID's). It is better to get the new ID based on the other values (Name and Address).

So you can do something like this:

Sub Add()
  '' // Do the insert to club table here

  '' // Get the new clubID:
  Dim sql As String = "SELECT clubID FROM Club WHERE Name='{0}' AND Address='{1}'"
  Dim clubId As Integer = ''RunTheSQl...(String.Format(sql, Name.Text , Address.Text)

  If Genre1.Checked Then
    sql = "INSERT INTO ClubGenre VALUES({0}, 1)"
    ''...RunTheSql...(String.Format(sql, clubId)
  End If  
End Sub