sql删除以及插入语句求助

[dxdx1982]

课程ID是由课程代码+两位顺序数字组成
比如课程代码是300495
那么课程ID可能有30049501,30049502,30049503,30049506,30049509
但是如果我把30049502删除了,那我再往里新增的话
希望新增的课程代码能写成30049502...
就是补顺序的空挡....
这个不会写...
用什么语句来控制?

这是现在的代码
期待帮助，万分感谢

Private Sub ComAdd_Click()
Dim strSQL As String
Dim i As Integer
On Error GoTo Errorprocess
If CobCouType.Text = "实践教学" Then
If Trim(TtCouNoA.Text) = "" Or _
Trim(TtCouNameA.Text) = "" Or _
Trim(CobCouTypeA.Text) = "" Or _
Trim(CobExaTypeA.Text) = "" Or _
Trim(CobTermA.Text) = "" Or _
Trim(TtCreHourA.Text) = "" Then
Call MsgBox("您输入的数据不完整，不能保存!", 0 + 16, "课程管理系统")
Exit Sub
End If
Else
If Trim(TtCouNoA.Text) = "" Or _
Trim(TtCouNameA.Text) = "" Or _
Trim(CobCouTypeA.Text) = "" Or _
Trim(CobExaTypeA.Text) = "" Or _
Trim(CobTermA.Text) = "" Or _
Trim(TtCreHourA.Text) = "" Or _
Trim(TtPerA.Text) = "" Or _
Trim(TtPerCouA.Text) = "" Or _
Trim(TtPerExpA.Text) = "" Or _
Trim(TtPerComA.Text) = "" Then
Call MsgBox("您输入的数据不完整，不能保存!", 0 + 16, "课程管理系统")
Exit Sub
End If
End If

Set rec = g_Conn.Execute("select * from 课程 where 课程编号 = '" & TtCouNoA.Text & "'")
If rec.RecordCount > 0 Then

If CobCouType.Text = "实践教学" Then
For i = 1 To rec.RecordCount
If TtCouNo.Text = rec("课程编号").Value & TtCouName.Text = rec("课程名称").Value & CobCouType.Text = rec("课程类型").Value & CobExaType.Text = rec("考试类型").Value & CobTerm.Text = rec("授课学期").Value & TtCreHour.Text = rec("学分").Value Then
Call MsgBox("您输入的课程信息已经存在，不能保存!", 0 + 16, "课程管理系统")
Exit Sub
Else
If rec.EOF = True Then
rec.MoveLast
Else
rec.MoveNext
End If
End If
Next

Else
For i = 1 To rec.RecordCount
If TtCouNo.Text = rec("课程编号").Value & TtCouName.Text = rec("课程名称").Value & CobCouType.Text = rec("课程类型").Value & CobExaType.Text = rec("考试类型").Value & CobTerm.Text = rec("授课学期").Value & TtCreHour.Text = rec("学分").Value & TtPer.Text = rec("学时").Value & TtPerCou.Text = rec("讲课学时").Value & TtPerExp.Text = rec("实验学时").Value & TtPerCom.Text = rec("上机学时").Value Then
Call MsgBox("您输入的课程信息已经存在，不能保存!", 0 + 16, "课程管理系统")
Exit Sub
Else
If rec.EOF = True Then
rec.MoveLast
Else
rec.MoveNext
End If
End If
Next

End If



If rec.RecordCount < 9 Then
g_Conn.BeginTrans
strSQL = " INSERT INTO 课程([课程ID],[课程编号],[课程名称],[课程类型],[考试类型],[授课学期],[学分],[学时],[讲课学时],[实验学时],[上机学时]) "
strSQL = strSQL & " VALUES("
strSQL = strSQL & "'" & TtCouNoA.Text + "0" + Str(rec.RecordCount + 1) & "',"
strSQL = strSQL & "'" & TtCouNoA.Text & "',"
strSQL = strSQL & "'" & TtCouNameA.Text & "',"
strSQL = strSQL & "'" & CobCouTypeA.Text & "',"
strSQL = strSQL & "'" & CobExaTypeA.Text & "',"
strSQL = strSQL & "'" & CobTermA.Text & "',"
strSQL = strSQL & "'" & CDbl(TtCreHourA.Text) & "',"
strSQL = strSQL & "'" & CLng(TtPerA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerCouA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerExpA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerComA.Text) & "')"
g_Conn.Execute (strSQL)
g_Conn.CommitTrans
MsgBox "课程信息新增成功！"
Else
g_Conn.BeginTrans
strSQL = " INSERT INTO 课程([课程ID],[课程编号],[课程名称],[课程类型],[考试类型],[授课学期],[学分],[学时],[讲课学时],[实验学时],[上机学时]) "
strSQL = strSQL & " VALUES("
strSQL = strSQL & "'" & TtCouNoA.Text + Str(rec.RecordCount + 1) & "',"
strSQL = strSQL & "'" & TtCouNoA.Text & "',"
strSQL = strSQL & "'" & TtCouNameA.Text & "',"
strSQL = strSQL & "'" & CobCouTypeA.Text & "',"
strSQL = strSQL & "'" & CobExaTypeA.Text & "',"
strSQL = strSQL & "'" & CobTermA.Text & "',"
strSQL = strSQL & "'" & CDbl(TtCreHourA.Text) & "',"
strSQL = strSQL & "'" & CLng(TtPerA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerCouA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerExpA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerComA.Text) & "')"
g_Conn.Execute (strSQL)
g_Conn.CommitTrans
MsgBox "课程信息新增成功！"
End If

Else
g_Conn.BeginTrans
strSQL = " INSERT INTO 课程([课程ID],[课程编号],[课程名称],[课程类型],[考试类型],[授课学期],[学分],[学时],[讲课学时],[实验学时],[上机学时]) "
strSQL = strSQL & " VALUES("
strSQL = strSQL & "'" & TtCouNoA.Text + "01" & "',"
strSQL = strSQL & "'" & TtCouNoA.Text & "',"
strSQL = strSQL & "'" & TtCouNameA.Text & "',"
strSQL = strSQL & "'" & CobCouTypeA.Text & "',"
strSQL = strSQL & "'" & CobExaTypeA.Text & "',"
strSQL = strSQL & "'" & CobTermA.Text & "',"
strSQL = strSQL & "'" & CDbl(TtCreHourA.Text) & "',"
strSQL = strSQL & "'" & CLng(TtPerA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerCouA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerExpA.Text) & "',"
strSQL = strSQL & "'" & CDec(TtPerComA.Text) & "')"
g_Conn.Execute (strSQL)
g_Conn.CommitTrans
MsgBox "课程信息新增成功！"
End If
Adodc1.Refresh
End Sub

[lajiaolong]

谈思路，不谈代码。
1.Select DISTINCTROW ID From TabName保存记录数
Select ID From TabName再保存记录数，如果不等于上面的，说明有重复，先解决这个问题。
2.Select ID From TabName Order By ID
然后看第一条的ID加上记录数是不是等于最后一条的ID-1，如果不是，说明中间有不连续，再用折半还是别的什么查找法找到不连续的第一个点即可。