select...insert

  • 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.

  • 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.

  • 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).

  • 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.

  • 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

  • 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.

  • 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?

  • 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?

  • 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?

  • 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.

  • 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