vennlou
06-16-2003, 09:07 AM
I have a page which allows the user to insert a new record to a database. I have got this working with np problems. However, what I need to do is for two of the values defect_frequency_id + defect_severity_id = defect_priorty_cal
I am not sure how I would do this. Below is the form for the user to enter the new details and then the asp add which does the insert.
<html>
<head>
<title>darwinadd</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!--METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%
Dim MyConn, SQL, objRS, id, firstname, lastname, username, password
Set MyConn=Server.CreateObject("ADODB.Connection")
'MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Databases\demo.mdb"
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\intranet\thomtest\design\elecd\darwin\darwin_v100_rc1.mdb"
SQL = "Select distinct tbl_model_name.model_name, tbl_model_name.model_name_id" & _
" from tbl_master_defect_details, tbl_model_name "
Set objRS=MyConn.Execute(SQL)
%>
<form method="post" name="form1" action="darwinaddit1.asp">
<strong>Title</strong>
<input type="text" name="Defect_Title"><br>
<strong>Description</strong>
<input type="text" name="Defect_description"><br>
<strong>Comments</strong>
<input type="text" name="Defect_comments"><br>
<strong>Software Version</strong>
<input type="text" name="Defect_SW_Ver_Found"><br>
<strong>Hardware ID</strong>
<input type="text" name="Defect_HW_Serial_Number"><br>
<strong>Preivous Reference</strong>
<input type="text" name="Defect_Previous_Ref"><br>
<strong>Date</strong>
<input type="text" name="Defect_Date_Discovered"><br>
<strong>Model Name</strong>
<select Name="model_name_id">
<%
DO WHILE NOT objrs.EOF
Dim strCONO
strCONO = objrs("model_name")
strCONO1 = objrs("model_name_id")
%>
<option value ="<% =strCONO1 %>"><%=strCONO%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_personnel.personnel, tbl_personnel.personnel_id" & _
" from tbl_master_defect_details, tbl_personnel"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Tester</strong>
<select Name="personnel_id">
<%
DO WHILE NOT objrs.EOF
dim strpers
dim strpers1
strPERS = objrs("personnel")
strPERS1 = objrs("personnel_id")
%>
<option value ="<% =strPERS1 %>"><%=strPERS%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_severity.severity, tbl_defect_severity.defect_severity_id" & _
" from tbl_master_defect_details, tbl_defect_severity"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Severity</strong>
<select Name="defect_severity_id">
<%
DO WHILE NOT objrs.EOF
dim strsev
dim strsev1
strsev = objrs("severity")
strsev1 = objrs("defect_severity_id")
%>
<option value ="<% =strsev1 %>"><%=strsev%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_frequency.frequency, tbl_defect_frequency.defect_frequency_id" & _
" from tbl_master_defect_details, tbl_defect_frequency"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Frequency</strong>
<select Name="defect_frequency_id">
<%
DO WHILE NOT objrs.EOF
dim strfre
dim strfre1
strfre = objrs("frequency")
strfre1 = objrs("defect_frequency_id")
%>
<option value ="<% =strfre1 %>"><%=strfre%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_Status.Status, tbl_defect_Status.defect_Status_id" & _
" from tbl_master_defect_details, tbl_defect_Status"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Status</strong>
<select Name="defect_Status_id">
<%
DO WHILE NOT objrs.EOF
dim strsta
dim strsta1
strsta = objrs("Status")
strsta1 = objrs("defect_Status_id")
%>
<option value ="<% =strsta1 %>"><%=strsta%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_Genre.Genre, tbl_defect_Genre.defect_Genre_id" & _
" from tbl_master_defect_details, tbl_defect_Genre"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Genre</strong>
<select Name="defect_Genre_id">
<%
DO WHILE NOT objrs.EOF
dim strgen
dim strgen1
strgen = objrs("Genre")
strgen1 = objrs("defect_Genre_id")
%>
<option value ="<% =strgen1 %>"><%=strgen%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_Project_name.Project_name, tbl_Project_name.Project_name_id" & _
" from tbl_master_defect_details, tbl_Project_name"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Project</strong>
<select Name="Project_name_id">
<%
DO WHILE NOT objrs.EOF
dim strpro
dim strpro1
strpro = objrs("Project_name")
strpro1 = objrs("Project_name_id")
%>
<option value ="<% =strpro1 %>"><%=strpro%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<input type="submit" value="submit">
<input type="reset" value="reset">
</form>
</body>
</html>
<html>
<head>
<title>addit</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!--METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%
Dim MyConn, SQL, objRS, id, firstname, lastname, username, password
strsql = ""
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\intranet\thomtest\design\elecd\darwin\darwin_v100_rc1.mdb; "
strsql = "INSERT INTO tbl_master_defect_details(defect_title, defect_description, defect_comments, defect_sw_ver_found, defect_date_discovered, defect_hw_serial_number, defect_previous_ref, defect_status_id, defect_genre_id, defect_frequency_id, defect_severity_id, project_name_id, model_name_id, personnel_id) " & _
" Values('" & request ("Defect_Title") & "', '" & request("Defect_description") & "', '" & request ("defect_comments") & "', '" & request("defect_sw_ver_found") & "', '" & request("defect_date_discovered") & "', '" & request("defect_hw_serial_number") & "', '" & request("defect_previous_ref") & "','" & request("defect_status_id") & "', '" & request("defect_genre_id") & "','" & request("defect_frequency_id") & "','" & request("defect_severity_id") & "','" & request("project_name_id") & "','" & request("model_name_id") & "', '" & request("personnel_id") & "')"
%>
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open strconn
conn.execute(strsql)
conn.close
set conn = nothing
%>
Thank you for successfully adding a new record
</body>
</html>
I need help in adding the two values together and then inserting the total into the database.
Many thanks
I am not sure how I would do this. Below is the form for the user to enter the new details and then the asp add which does the insert.
<html>
<head>
<title>darwinadd</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!--METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%
Dim MyConn, SQL, objRS, id, firstname, lastname, username, password
Set MyConn=Server.CreateObject("ADODB.Connection")
'MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Databases\demo.mdb"
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\intranet\thomtest\design\elecd\darwin\darwin_v100_rc1.mdb"
SQL = "Select distinct tbl_model_name.model_name, tbl_model_name.model_name_id" & _
" from tbl_master_defect_details, tbl_model_name "
Set objRS=MyConn.Execute(SQL)
%>
<form method="post" name="form1" action="darwinaddit1.asp">
<strong>Title</strong>
<input type="text" name="Defect_Title"><br>
<strong>Description</strong>
<input type="text" name="Defect_description"><br>
<strong>Comments</strong>
<input type="text" name="Defect_comments"><br>
<strong>Software Version</strong>
<input type="text" name="Defect_SW_Ver_Found"><br>
<strong>Hardware ID</strong>
<input type="text" name="Defect_HW_Serial_Number"><br>
<strong>Preivous Reference</strong>
<input type="text" name="Defect_Previous_Ref"><br>
<strong>Date</strong>
<input type="text" name="Defect_Date_Discovered"><br>
<strong>Model Name</strong>
<select Name="model_name_id">
<%
DO WHILE NOT objrs.EOF
Dim strCONO
strCONO = objrs("model_name")
strCONO1 = objrs("model_name_id")
%>
<option value ="<% =strCONO1 %>"><%=strCONO%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_personnel.personnel, tbl_personnel.personnel_id" & _
" from tbl_master_defect_details, tbl_personnel"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Tester</strong>
<select Name="personnel_id">
<%
DO WHILE NOT objrs.EOF
dim strpers
dim strpers1
strPERS = objrs("personnel")
strPERS1 = objrs("personnel_id")
%>
<option value ="<% =strPERS1 %>"><%=strPERS%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_severity.severity, tbl_defect_severity.defect_severity_id" & _
" from tbl_master_defect_details, tbl_defect_severity"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Severity</strong>
<select Name="defect_severity_id">
<%
DO WHILE NOT objrs.EOF
dim strsev
dim strsev1
strsev = objrs("severity")
strsev1 = objrs("defect_severity_id")
%>
<option value ="<% =strsev1 %>"><%=strsev%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_frequency.frequency, tbl_defect_frequency.defect_frequency_id" & _
" from tbl_master_defect_details, tbl_defect_frequency"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Frequency</strong>
<select Name="defect_frequency_id">
<%
DO WHILE NOT objrs.EOF
dim strfre
dim strfre1
strfre = objrs("frequency")
strfre1 = objrs("defect_frequency_id")
%>
<option value ="<% =strfre1 %>"><%=strfre%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_Status.Status, tbl_defect_Status.defect_Status_id" & _
" from tbl_master_defect_details, tbl_defect_Status"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Status</strong>
<select Name="defect_Status_id">
<%
DO WHILE NOT objrs.EOF
dim strsta
dim strsta1
strsta = objrs("Status")
strsta1 = objrs("defect_Status_id")
%>
<option value ="<% =strsta1 %>"><%=strsta%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_defect_Genre.Genre, tbl_defect_Genre.defect_Genre_id" & _
" from tbl_master_defect_details, tbl_defect_Genre"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Genre</strong>
<select Name="defect_Genre_id">
<%
DO WHILE NOT objrs.EOF
dim strgen
dim strgen1
strgen = objrs("Genre")
strgen1 = objrs("defect_Genre_id")
%>
<option value ="<% =strgen1 %>"><%=strgen%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<%
mySQL = "Select distinct tbl_Project_name.Project_name, tbl_Project_name.Project_name_id" & _
" from tbl_master_defect_details, tbl_Project_name"
Set objRS=MyConn.Execute(mySQL)
%>
<strong>Project</strong>
<select Name="Project_name_id">
<%
DO WHILE NOT objrs.EOF
dim strpro
dim strpro1
strpro = objrs("Project_name")
strpro1 = objrs("Project_name_id")
%>
<option value ="<% =strpro1 %>"><%=strpro%> </option>
<%
objrs.MoveNext
Loop
%>
</select>
<br>
<input type="submit" value="submit">
<input type="reset" value="reset">
</form>
</body>
</html>
<html>
<head>
<title>addit</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!--METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%
Dim MyConn, SQL, objRS, id, firstname, lastname, username, password
strsql = ""
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\intranet\thomtest\design\elecd\darwin\darwin_v100_rc1.mdb; "
strsql = "INSERT INTO tbl_master_defect_details(defect_title, defect_description, defect_comments, defect_sw_ver_found, defect_date_discovered, defect_hw_serial_number, defect_previous_ref, defect_status_id, defect_genre_id, defect_frequency_id, defect_severity_id, project_name_id, model_name_id, personnel_id) " & _
" Values('" & request ("Defect_Title") & "', '" & request("Defect_description") & "', '" & request ("defect_comments") & "', '" & request("defect_sw_ver_found") & "', '" & request("defect_date_discovered") & "', '" & request("defect_hw_serial_number") & "', '" & request("defect_previous_ref") & "','" & request("defect_status_id") & "', '" & request("defect_genre_id") & "','" & request("defect_frequency_id") & "','" & request("defect_severity_id") & "','" & request("project_name_id") & "','" & request("model_name_id") & "', '" & request("personnel_id") & "')"
%>
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open strconn
conn.execute(strsql)
conn.close
set conn = nothing
%>
Thank you for successfully adding a new record
</body>
</html>
I need help in adding the two values together and then inserting the total into the database.
Many thanks