Do I Substract 1 From 45 Each Time A Student Registers For A Seat?

I have 2 fieldnames on 2 different tables. One fieldname is called SeatCapacity. This fieldname name has a value of 45 and it is on a table called Locations Then there is another f

Solution 1:

If you have a place where you keep a SeatCapacity, then why you not copying it from there.

As I noticed you know LocationID before creating row in tblMain, and in Location table you have a SeatCapacity, so SQL query will be:

...Please use parameters for inserted data...

INSERTINTOtblMain(CourceId, LocationId, dateId, RemainingSeats) 
(SELECT @CourceID
, @LocationID
, @DateID
, SeatCapacity FROM Locations WHERE ID=@LocationID);

in VB.NET adding paramaters will be:

Dim param ASNew System.Sql.SqlClient.SqlParameter("@CourceID", Request.QueryString("cosId"))

... and so on

Then add parameters to your SqlCommand and execute query

Solution 2:

Supposing that the Locations table has a primary key called locationID then your query to set the initial value for the tblMain table could be written

s = "INSERT INTO tblMain (CourseId, LocationId, dateId,RemainingSeats) VALUES " +
    "(@cosID, @locID, @dat (SELECT TOP(1) SeatCapacity from Location WHERE LocationID = @locID))"

Dim cmd = new MySqlCommand(s, con)
cmd.Parameters.AddWithValue("@codID", Request.QueryString("cosId"))
cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid")) 
cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate")) 

Note that I don't know the exact datatype for CourseID and LocationId, if they are numbers then you need to comvert to number the values retrieved from the querystring

Now the update operation is very simple. Just

s = "UPDATE tblMain SET RemainingSeats = RemainingSeats - 1 WHERE ""CourseID = @cosID AND LocationID = @locId AND dateId = @dat"
....