My goodness - what a mess of code.
Judging by the tablename, you are using SQL Server. If so, and the "ID"
field is an IDENTITY field you can use:
"SELECT Scope_Identity"
to return the identity value of the last created record.
strSQL = "SELECT Scope_Identity"
Set objRS = objConn.Execute strSQL
If not objRS.EOF then
intLastID = objRS.Fields(0).Value
End If
objRS.Close
Set objRS = Nothing
NOTE: You must use the same connection object that you used to do the
original INSERT. Looking at your code, it seems to be some of that
Macromedia auto-generated rubbish. That usually uses implicit connections -
you;ll need to explicitly open a connection and re-use it -or- use a sproc
to do this.
Cheers
Ken
"PT" <p4pradeep@hotmail.com> wrote in message
news:2d868f1e.0410062315.edbc3f6@posting.google.co m...[color=blue]
>I got a problem. And thats.....
>
> First of all, I got these three tables.
>
> ------------------- ------------------ ----------------------
> tblPerson tblPersonSoftware tblSoftware
> ------------------- ------------------ ----------------------
> PID PName PID* SID* SID SWName
> --- ----- --- --- --- ------
> 1 Thomas 1 1 1 MS Office
> 2 Mark 1 2 2 Visual Studio
> 3 Philip 2 3 3 Dreamweaver
> . .... 3 1 4 SQL Server
> . .... . . . ..........
> ------------------- ------------------ ----------------------
> * - foreign key
>
> Now I got a form in asp, with textbox (to insert persons name into
> table 'tblPerson') and checkboxes (created dynamically from table
> 'tblSoftware'). One can choose check any softwares needed. And these
> values should be inserted into table 'tblPersonSoftware'.
>
> First I have to insert persons name into 'tblPerson', get the ID from
> it (from the last inserted value), get the values of checked
> checkboxes, and insert multiple records in 'tblPersonSoftware' with
> PID from the last inserted value in 'tblPerson' and SID with the
> values of checkboxes.
>
> I created the form with Dreamweaver. I'll show the code that I have
> now.
>
> ---------------------------------------------------------------------------------------------
>
> <%
> ' *** Edit Operations: declare variables
>
> Dim MM_editAction
> Dim MM_abortEdit
> Dim MM_editQuery
> Dim MM_editCmd
>
> Dim MM_editConnection
> Dim MM_editTable
> Dim MM_editRedirectUrl
> Dim MM_editColumn
> Dim MM_recordId
>
> Dim MM_fieldsStr
> Dim MM_columnsStr
> Dim MM_fields
> Dim MM_columns
> Dim MM_typeArray
> Dim MM_formVal
> Dim MM_delim
> Dim MM_altVal
> Dim MM_emptyVal
> Dim MM_i
>
> MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
> If (Request.QueryString <> "") Then
> MM_editAction = MM_editAction & "?" & Request.QueryString
> End If
>
> ' boolean to abort record edit
> MM_abortEdit = false
>
> ' query string to execute
> MM_editQuery = ""
> %>
> <%
> ' *** Insert Record: set variables
>
> If (CStr(Request("MM_insert")) = "frmNewPerson") Then
>
> MM_editConnection = MM_userverwaltung_STRING
> MM_editTable = "dbo.tblPersonen"
> MM_editRedirectUrl = "/view-personen.asp"
> MM_fieldsStr = "txtName|value"
> MM_columnsStr = "PName|',none,''"
>
> ' create the MM_fields and MM_columns arrays
> MM_fields = Split(MM_fieldsStr, "|")
> MM_columns = Split(MM_columnsStr, "|")
>
> ' set the form values
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
> Next
>
> ' append the query string to the redirect URL
> If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
> If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
> Request.QueryString <> "") Then
> MM_editRedirectUrl = MM_editRedirectUrl & "?" &
> Request.QueryString
> Else
> MM_editRedirectUrl = MM_editRedirectUrl & "&" &
> Request.QueryString
> End If
> End If
>
> End If
> %>
> <%
> ' *** Insert Record: construct a sql insert statement and execute it
>
> Dim MM_tableValues
> Dim MM_dbValues
>
> If (CStr(Request("MM_insert")) <> "") Then
>
> ' create the sql insert statement
> MM_tableValues = ""
> MM_dbValues = ""
> For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
> MM_formVal = MM_fields(MM_i+1)
> MM_typeArray = Split(MM_columns(MM_i+1),",")
> MM_delim = MM_typeArray(0)
> If (MM_delim = "none") Then MM_delim = ""
> MM_altVal = MM_typeArray(1)
> If (MM_altVal = "none") Then MM_altVal = ""
> MM_emptyVal = MM_typeArray(2)
> If (MM_emptyVal = "none") Then MM_emptyVal = ""
> If (MM_formVal = "") Then
> MM_formVal = MM_emptyVal
> Else
> If (MM_altVal <> "") Then
> MM_formVal = MM_altVal
> ElseIf (MM_delim = "'") Then ' escape quotes
> MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
> Else
> MM_formVal = MM_delim + MM_formVal + MM_delim
> End If
> End If
> If (MM_i <> LBound(MM_fields)) Then
> MM_tableValues = MM_tableValues & ","
> MM_dbValues = MM_dbValues & ","
> End If
> MM_tableValues = MM_tableValues & MM_columns(MM_i)
> MM_dbValues = MM_dbValues & MM_formVal
> Next
> MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues
> & ") values (" & MM_dbValues & ")"
>
> If (Not MM_abortEdit) Then
> ' execute the insert
> Set MM_editCmd = Server.CreateObject("ADODB.Command")
> MM_editCmd.ActiveConnection = MM_editConnection
> MM_editCmd.CommandText = MM_editQuery
> MM_editCmd.Execute
>
> MM_editCmd.ActiveConnection.Close
>
> If (MM_editRedirectUrl <> "") Then
> Response.Redirect(MM_editRedirectUrl)
> End If
> End If
>
> End If
>
> <%
> Dim rsSoftware
> Dim rsSoftware_numRows
>
> Set rsSoftware = Server.CreateObject("ADODB.Recordset")
> rsSoftware.ActiveConnection = MM_userverwaltung_STRING
> rsSoftware.Source = "SELECT SID, SWName FROM dbo.tblSoftware ORDER BY
> SWName ASC"
> rsSoftware.CursorType = 0
> rsSoftware.CursorLocation = 2
> rsSoftware.LockType = 1
> rsSoftware.Open()
>
> rsSoftware_numRows = 0
> %>
> <%
> Dim rsPersonSoftware
> Dim rsPersonSoftware_numRows
>
> Set rsPersonSoftware = Server.CreateObject("ADODB.Recordset")
> rsPersonSoftware.ActiveConnection = MM_userverwaltung_STRING
> rsPersonSoftware.Source = "SELECT PID, SID FROM dbo.tblPersonSoftware"
> rsPersonSoftware.CursorType = 0
> rsPersonSoftware.CursorLocation = 2
> rsPersonSoftware.LockType = 1
> rsPersonSoftware.Open()
>
> rsPersonSoftware_numRows = 0
> %>
>
> <html>
> <body>
> <form action="<%=MM_editAction%>" method="POST" name="frmNewPerson">
> <table>
> <tr valign="baseline">
> <td nowrap align="right">Name:</td>
> <td> <input type="text" name="txtName" value="" size="32"> </td>
> </tr>
> <tr valign="baseline">
> <td nowrap align="right" valign="top">Software:</td>
> <td>
> <table border="0">
> <tr>
> <%
> colCount = 1
> While ((Repeat1__numRows <> 0) AND (NOT rsSoftware.EOF))
> If colCount mod 4=0 Then
> %>
> <td><input type="checkbox" name="chkSoftware"
> value="<%=(rsSoftware.Fields.Item("SID").Value)%>" >
> <%=(rsSoftware.Fields.Item("SWName").Value)%></td>
> </tr>
> <tr>
> <% colCount = colCount+1
> Else
> %>
> <td><input type="checkbox"
> name="chkSoftware<%=(rsSoftware.Fields.Item("SID") .Value)%>"
> value="<%=(rsSoftware.Fields.Item("SID").Value)%>" >
> <%=(rsSoftware.Fields.Item("SWName").Value)%></td>
> <% colCount = colCount+1
> End If
> Repeat1__index=Repeat1__index+1
> Repeat1__numRows=Repeat1__numRows-1
> rsSoftware.MoveNext()
> Wend
> %>
> </table>
> </td>
> </tr>
> </table>
> <input type="hidden" name="MM_insert" value="frmNewPerson">
> </form>
> </body>
> </html>
> ---------------------------------------------------------------------------------------------[/color]
