Databases
StackOverflow2013
Postsdbo
POI need to insert records if no record already exists. How do I fix this code to do this?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POI need to insert records if no record already exists. How do I fix this code to do this?
    text
    copied!<p>This code is really driving me nuts.</p> <p>Earlier today, here in this great forum, I got help with loading initial default values into the db with the following code.</p> <pre><code> s = "INSERT INTO tblTrainings (CourseId, tblLocations.LocationId, dateId,AvailableSeats) (SELECT @CosID, @LocID, @dat,Seating_Capacity FROM tblLocations, tblCourses WHERE tblCourses.locationId= tblLocations.LocationId and courseId = @cosId and tblLocations.locationID=@LocID)" s += "UPDATE tblTrainings SET AvailableSeats = AvailableSeats - 1 WHERE CourseID = @cosID AND LocationID = @locId AND dateId = @dat" </code></pre> <p>History:</p> <p>I have a dataList with several rows of records and a link that says, "Click here to register.</p> <p>When you mouse over this link, it shows the IDs of date, course, location.</p> <p>When a user clicks that link, if checks the db to see if dateId, CourseId, LocationId and AvailableSeats are null.</p> <p>The Problem:</p> <p>The issue I was having with the solution above is that each time a user hits the Register link an update to existing record is made. This is good but at same time, a new record is inserted into the db and this is not good.</p> <p>I decided, therefore, to perform a CHECK first.</p> <p>Check the db for an existing of record meeting certain criteria (specified in SELECT statement below) </p> <p>If availableSeats (RemainingSeates) is null and dateId is null and courseid is null and locationid is null, then perform an INSERT statement.</p> <p>If they are not null, perform an UPDATE statement.</p> <p>It doesn't matter what I do so far, only UPDATE statements are being performed.</p> <p>Any ideas how to resolve this?</p> <p>Below is the code I am using:</p> <pre><code>Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim username = Session.Item("Username").ToString Dim connStr As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString Dim conn As New SqlConnection(connStr) Try Dim s As String Dim counter As Integer 'If AvailableSeats already saved, then don't do an INSERT s = "SELECT Count(*) Counter FROM tblTrainings WHERE AvailableSeats Is Null and CourseId is null and LocationId is null and dateId is null" 'Response.Write(s) 'Response.End() Dim connSt As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString Dim connc As New SqlConnection(connSt) Dim cmdc As New SqlCommand(s, connc) connc.Open() cmdc.ExecuteNonQuery() counter = cmdc.ExecuteScalar() ' Now let's see if we found existing record If counter &gt; 0 Then s = "INSERT INTO tblTrainings (CourseId, tblLocations.LocationId, dateId,AvailableSeats) (SELECT @CosID, @LocID, @dat,Seating_Capacity FROM tblLocations, tblCourses WHERE tblCourses.locationId= tblLocations.LocationId and courseId = @cosId and tblLocations.locationID=@LocID)" s += "UPDATE tblTrainings SET AvailableSeats = AvailableSeats - 1 WHERE CourseID = @cosID AND LocationID = @locId AND dateId = @dat" Else s += "UPDATE tblTrainings SET AvailableSeats = AvailableSeats - 1 WHERE CourseID = @cosID AND LocationID = @locId AND dateId = @dat" End If Response.Write(s) Response.End() Dim cmd = New SqlCommand(s, conn) cmd.Parameters.AddWithValue("@cosID", Request.QueryString("cosId")) cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid")) cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate")) conn.Open() cmd.ExecuteNonQuery() conn.Close() 'Display some feedback to the user to let them know it was processed Label1.ForeColor = System.Drawing.Color.Green Label1.Text = "Record successfully saved!" Catch 'If the message failed at some point, let the user know Label1.ForeColor = System.Drawing.Color.Red Label1.Text = "Your record failed to save, please try again." End Try End Sub s = "IF EXISTS (SELECT TrainingId FROM tblTrainings WHERE AvailableSeats Is NOT NULL and CourseId is NOT NULL and LocationId is NOT NULL and dateId is NOT NULL) " s += " BEGIN " '***Record already exists in the tblTrainings table, update existing record instead instead*** s += "UPDATE tblTrainings SET AvailableSeats = AvailableSeats - 1 WHERE CourseID = @cosID AND LocationID = @locId AND dateId = @dat " s += " End " s += " ELSE " s += " BEGIN " '***No record exists in the tblTrainings table; create one and update it at same time.*** s += " INSERT INTO tblTrainings (CourseId, tblLocations.LocationId, dateId,AvailableSeats) (SELECT @CosID, @LocID, @dat,Seating_Capacity FROM tblLocations, tblCourses WHERE tblCourses.locationId= tblLocations.LocationId and courseId = @cosId and tblLocations.locationID=@LocID)" s += "UPDATE tblTrainings SET AvailableSeats = AvailableSeats - 1 WHERE CourseID = @cosID AND LocationID = @locId AND dateId = @dat" s += " End " </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload