May 30, 2003 at 7:28 am
hi David!
Ok, I must be doing something wrong, (surprise!).
Now looking at the two forms below, the first one pulls data dynamically from the db; the other hardcodes the same info that the first one is pulling.
So essentially, the two codes perform the same task.
Please take a look:
<TR>
<TD><FONT CLASS='Arial10' COLOR='darkred'><B>
Department</B>
</TD>
<td><select name="Driverdept">
<%
sql = "SELECT tblDept.DeptID, tblDept.DeptName FROM tblDept "
set deptset = safetyDB.Execute(sql)
While not DeptSet.EOF
%>
<option value="<%=DeptSet(0)%>"><%=DeptSet(1)%></option>
<%
DeptSet.MoveNext
wend
%>
</select>
</td>
</TR>
<TR>
<TD><FONT CLASS='Arial10' COLOR='darkred'><B>
Department</B>
</TD>
<td><select name="Driverdept">
<OPTION value="1">Transportation</OPTION>
<OPTION value="2">Water</OPTION>
<OPTION value="3">HR</OPTION>
<OPTION value="4">Administration</OPTION>
<OPTION value="5">Purchasing</OPTION>
</select>
</td>
</TR>
The same holds true for tblemp.
given the above, I still did it the second option which is what you suggested.
Then on the proc, I have this:
Create Procedure InsertAccidentRep(
@DeptID int,
@EmpID int,
@AccidentDesc varchar(3000),
@AccidentDay datetime,
@LocationDesc nvarchar(100),
@FactorsDesc nvarchar(100),
@CollisionDesc nvarchar(100),
@WeatherDesc nvarchar(100),
@AtFault nvarchar(100),
@DrugTest nvarchar(100),
@Result nvarchar(100),
@TestType nvarchar(100),
@ConditionDesc nvarchar(100),
@InjuryDesc nvarchar(4000)
)
AS
Begin
select @EmpID = e.EmpID,@DeptID = d.DeptID
from tblEmp e
inner join tblDept d on d.deptid = e.deptid
where e.fullname = @fullName
IF (@EmpID IS NULL) or (@DeptID IS NULL)
BEGIN
RAISERROR('No record exists for this employee',1,1)
ROLLBACK TRANSACTION
END
ELSE
/* Create and populate new AccidentMain Record */
INSERT INTO AccidentMain(
DeptID,
EmpID,
AccidentDesc,
AccidentDate,
TimeOfAccident,
TimeOfAccidentAMPM,
Loc_Desc,
FactorsDesc,
CollisionDesc,
WeatherDesc,
AtFault,
DrugTest,
Result,
TestType,
ConditionDesc,
InjuryDesc
)
VALUES (
@DeptID,
@EmpID,
@AccidentDesc ,
@AccidentDay ,
@TimeOfAccident,
@TimeOfAccidentAMPM,
@LocationDesc ,
@FactorsDesc ,
@CollisionDesc ,
@WeatherDesc,
@AtFault,
@DrugTest,
@Result,
@TestType,
@ConditionDesc,
@InjuryDesc
)
End
Now to use stored proc with asp, there are 3 components:
1, the stored proc,
2, the asp page that invokes the stored proc
and 3, another asp code (part of the form I posted above) that calls the asp page that invokes the stored proc.
I have done, I think what you suggested but I keep getting an error that no value has been supplied for deptid and empid.
I am not sure where the problem lies but if I can eliminate these two possibilities (the form and the proc), then that will leave only one area to focus on. That area will be the asp page that calls the stored proc.
If it is not too much of a problem, can you please verify that I am doing the stored proc well?
thanks for all the help.
May 30, 2003 at 7:58 am
Ah my error methinks. Remove this code from the beginning of your proc,
select @EmpID = e.EmpID,@DeptID = d.DeptID
from tblEmp e
inner join tblDept d on d.deptid = e.deptid
where e.fullname = @fullName
IF (@EmpID IS NULL) or (@DeptID IS NULL)
BEGIN
RAISERROR('No record exists for this employee',1,1)
ROLLBACK TRANSACTION
END
ELSE
and
End
at the end of the proc, you do not need it as the data will be passed to it.
Then you call make the call to proc by
SQL = "exec InsertAccidentRep " & Request.Form("Driverdept") & "," & Request.Form("ename") & "," & Request.Form("...") etc
safetyDB.Execute(sql)
or if you use Command object then
Set Comm = CreateObject("ADODB.Command")
Comm.ActiveConnection = safetyDB
Comm.CommandType = 4 'adCmdStoredProc
Comm.CommandText = "InsertAccidentRep"
Comm.Parameters("@DeptID").Value = Request.Form("Driverdept")
Comm.Parameters("@EmpID").Value = Request.Form("ename")
Comm.Parameters("...").Value = Request.Form("...")
Comm.Execute
Far away is close at hand in the images of elsewhere.
Anon.
May 30, 2003 at 8:13 am
I guess it is reaching that annoying stage because what you suggested I just did.
As far as the command object is concerned, I have been using it now for over a year.
Infact this code snip is what I have just used for a project and it has always worked.
This time, it is being stubborn.
<%@ Language=VBScript %>
<!-- #include file="adovbs.inc" -->
Dim objConn,objCmd
Set objConn = server.CreateObject("ADODB.Connection")
Set objCmd= server.CreateObject("ADODB.Command")
objConn.CursorLocation = 3
objConn.Open "DSN=Safety"
With objCmd
.CommandType = adCmdStoredProc
Set .ActiveConnection = objConn
.CommandText = "InsertAccidentRep"
.Parameters.Append .CreateParameter("@DeptID", AdInteger, adparamInput,4)
.Parameters.Append .CreateParameter("@EmpID", AdInteger, adparamInput,4)
'Set the parameter values
.Parameters("@deptid") = Request.Form("DriverDept")
.Parameters("@empid") = Request.Form("Ename")
'Now Let's Run The Stored Proc
.Execute IngRecs,, AdExecuteNoRecords
End With
(of course there are more parameters. This is just an example of how I use the command object).
May 30, 2003 at 8:42 am
What part is being stubborn. ASP or proc. Are you getting any errors?
Is the data being passed correctly?
Debug by displaying the values of the parameters after they have been filled to see if there is a data problem.
Nearly there, the last mile is always the longest.
Edited by - davidburrows on 05/30/2003 08:44:22 AM
Far away is close at hand in the images of elsewhere.
Anon.
May 30, 2003 at 9:05 am
David,not only are you good, but you are very patient--thank you very much.
I figured out the problem but unfortunately,I couldn't get here fast enough to stop you from thinking.
It was a dumb mistake on my part.
On the form page, I should be posting the form objects, not get them.
<form name=safety action=nextpage.asp method=get>
form method should have been post.
As soon as I made that change, it worked like a charm.
Thanks again for all your help.
I still have one other little problem.
I want to be able to automatically populate all text fields related to employee once a user clicks on a name on the dropdown list.
If you have any ideas, please pass that on to me.
thanks again
May 30, 2003 at 9:34 am
Two ways
1. Declare the employee columns as input params to proc, in asp before calling InsertAccidentRep call another proc to retrieve them from employee table and then pass them to InsertAccidentRep proc.
2. Do not declare the employee columns as input params to proc but instead declare them as variables. Select the variables from the employee table using @empid and use these variables in your insert statement.
Either will do and choice depends on how much flexibilty you want.
Far away is close at hand in the images of elsewhere.
Anon.
May 30, 2003 at 10:53 am
hi David!
One last question, I promise
Do not declare the employee columns as input params to proc but instead declare them as variables
Select the variables from the employee table using @empid
Select the variables from the employee table using @empid
how?
May 30, 2003 at 11:00 am
sorry, I messed up the last post.
It should look like this:
Do not declare the employee columns as input params to proc but instead declare them as variables
--Declare @address varchar(50)
--Declare @City varchar(50)
Declare @State varchar(50)
Declare @ZipCode varchar(50)
Declare @Age int
Declare @HomePhone varchar(50)
Declare @WorkPhone varchar(50)
Select the variables from the employee table using @empid
SELECT @Address = Address,
@City = City
@State = State
@Zip = ZipCode
@Age = Age
@PhoneHome = HomePhone
@PhoneWork = WorkPhone
FROM tblEmp
WHERE EmpID = @EmpID
Select the variables from the employee table using @empid
how?
May 30, 2003 at 11:02 am
my sincere apology, I must be losing my mind.
The last part of last post should be:
and use these variables in your insert statement.
how?
May 30, 2003 at 11:09 am
Just the same way as you would if they parameter variables, e.g.
INSERT INTO AccidentMain(
EmpID,
DeptID,
...
FName,
LName,
Address,
City,
State,
ZipCode,
...
)
VALUES (
@EmpID,
@DeptID,
...
@FName,
@LName,
@Address,
@City,
@State,
@ZipCode,
...
)
Far away is close at hand in the images of elsewhere.
Anon.
May 30, 2003 at 6:49 pm
thank you David,you are awesome!!!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply