如果没有记录，我需要插入记录。我该如何修改这段代码呢?

This code is really driving me nuts.

这段代码真让我抓狂。

Earlier today, here in this great forum, I got help with loading initial default values into the db with the following code.

今天早些时候，在这个伟大的论坛中，我得到了使用以下代码将初始默认值加载到db的帮助。

 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"

History:

历史:

I have a dataList with several rows of records and a link that says, "Click here to register.

我有一个拥有几行记录和一个链接的dataList，上面写着“点击这里注册”。

When you mouse over this link, it shows the IDs of date, course, location.

当您在此链接上单击时，它将显示日期、当然、位置的id。

When a user clicks that link, if checks the db to see if dateId, CourseId, LocationId and AvailableSeats are null.

当用户单击该链接时，如果检查db以查看dateId、CourseId、LocationId和AvailableSeats是否为空。

The Problem:

存在的问题:

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.

我在上面的解决方案中遇到的问题是，每当用户点击注册链接时，就对现有记录进行更新。这很好，但是同时，新的记录插入到数据库中，这是不好的。

I decided, therefore, to perform a CHECK first.

因此，我决定先检查一下。

Check the db for an existing of record meeting certain criteria (specified in SELECT statement below)

检查db是否存在符合某些标准的记录(在下面的SELECT语句中指定)

If availableSeats (RemainingSeates) is null and dateId is null and courseid is null and locationid is null, then perform an INSERT statement.

如果availableSeats (regulingseates)是null, dateId是null，而courseid是null, locationid是null，那么执行一条INSERT语句。

If they are not null, perform an UPDATE statement.

如果它们不是null，那么执行一条UPDATE语句。

It doesn't matter what I do so far, only UPDATE statements are being performed.

到目前为止，我做什么都不重要，只执行UPDATE语句。

Any ideas how to resolve this?

有什么办法解决这个问题吗?

Below is the code I am using:

下面是我正在使用的代码:

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 > 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 "

2 个解决方案

#1

Do with filter condition ....

与过滤条件....

Dim cmd as SqlCommand
Dim s1 as String = "" '----> insert stat

s = "UPDATE tblTrainings SET AvailableSeats = AvailableSeats - 1 WHERE CourseID = @cosID AND LocationID = @locId AND dateId = @dat"

conn.Open()    
If counter = 0 Then
    'here is the code you can get value for availableseat 

    s1 = "INSERT INTO tblTrainings (CourseId, tblLocations.LocationId, dateId,AvailableSeats) VALUES (@CosID, @LocID, @dat, @Capacity)" 

    Response.Write(s1)
    Response.End()

    cmd = New SqlCommand(s1, conn)

    cmd.Parameters.AddWithValue("@cosID", Request.QueryString("cosId"))
    cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid"))
    cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate"))
    cmd.Parameters.AddWithValue("@capacity",  ... ) '----------------> fill the value 

    cmd.ExecuteNonQuery()     
End If

Response.Write(s)
Response.End()

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"))

cmd.ExecuteNonQuery()
conn.Close()

#2

-1

You need a semicolon at the end of the INSERT statement:

在INSERT语句的末尾需要一个分号:

If counter > 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

#1