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>
--------------------------------------------------------------------------------------------- 1 8703
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" <p4*******@hotmail.com> wrote in message
news:2d*************************@posting.google.co m... 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> --------------------------------------------------------------------------------------------- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jason |
last post by:
I have a table that matches up Securities and Exchanges. Individual
securities can belong on multiple exchanges. One of the columns, named
PrimaryExchangeFlag, indicates if a particular exchange is...
|
by: Martin |
last post by:
Hello,
I'm new with triggers and I can not find any good example on how to
do the following:
I have two tables WO and PM with the following fields:
WO.WONUM, VARCHAR(10)
WO.PMNUM,...
|
by: stu_gots |
last post by:
I have been losing sleep over this puzzle, and I'm convinced my train
of thought is heading in the wrong direction. It is difficult to
explain my circumstances, so I will present an identical...
|
by: ZRexRider |
last post by:
Hi,
I have trigger that enforces the creation of a sortorder that is always
1 digit higher than the current highest on Inserts.
This trigger works great if I add one row at a time so I think...
|
by: MaRCeLO PeReiRA |
last post by:
Hi guys,
I am in troubles with a SERIAL field.
I have five tables. A parent table and four child
tables. When I do the INSERT in the parent table, I
have an ID (generated) by the sequence...
|
by: Rahul |
last post by:
Hi Everybody
I have some problem in my script. please help me. This is script file.
I have one *.inq file. I want run this script in XML files. But this
script errors shows . If u want i am...
|
by: Jay |
last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send
text messages to many, many employees via system.timer at a 5 second
interval. Basically, I look in a SQL table (queue) to...
|
by: LanaR |
last post by:
Hello,
one sql statement is causing severe performance issue. The problem occurs only in UDB environment, the same statemnt on the mainframe is running fine. I have an explain output from the sql....
|
by: deepaks85 |
last post by:
Dear All,
I want to send some data through a form with Multiple attachment in an HTML Format. I have tried it but it is not working for me. I am able to send data without attachment but with the...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| | |