Participating Frequently

解決済み

Select Name from CFSelect, then drop the Name Profile/Info into the Textfield to Update or Delete

Forum|Forum|16 years ago
December 22, 2009
返信数 2.
1401 ビュー

Hello! I need help.

I have a db with "users" table (idusers, username, password, firstname, lastname)

On the Update/Delete page. I need a dropdown list combing (lastname, firstname).

Once that name is selected. That user's info will populate on the textfield, which allows me to update/change the info, then hit the Update button.

OR hit the Delete button to delete the user from the list.

このトピックへの返信は締め切られました。

解決に役立った回答 Daverms

Hi Daverms!

I appreciate your quick response and gave me more knowledge how this thing should work.

Unfortunately, it has not work out for me. Here's what I did:

1. I created a brand new table called "employee" in my database

2. I am using MySQL Workbench as my gui for my database. I created columns (empid, firstname, lastname, address, and activeflag (as BLOB? I typed in BOOL for Boolean and BLOB showed up).

3. I entered 1 row of data. Unfortunately in the "activeflag" column I am not allowed to type anything to it. I was trying to type in 1, but it's not working.

4. I copied/paste your code to my cfm page called "emloyee.cfm".

5. I placed the code from <CFQUERY> all the way to the <CFIF> right above the <HTML> tag.. then paste the code inside the body (from the <script language part all the way to the <form>)

6. the only thing I change from your code was the datasource (which is my database/connection?).

7. I preview the page, it shows up as Employee: (Drop Down List) with no data. The text input did not show up either.

I think we're getting there.. I just don't know what I did wrong.

--------------------------------

UPDATE:

I managed to changed the activeflag status to 0 instead of 1 using the commandline MySQL. I couldn't get the Workbench to work on changing BOOL stuff.. I run the page. Check the drop-down list. a Value of "0" shows up. I selected it, then the input box shows up with the details for me to update or delete. Update and Delete function works.. so the only thing that is not working right now is the value "0" instead of the full name.

Also I wanted a "New Employee" in the drop down, then able to input new data into the input textfield to create a brand new employee.

Hi,

I think the firstname and lastname concatenation might be the reason (I did that in SQL server, where as you are trying that in MySQL)...

So, try rewriting this query,

<cfquery name="EmployeeSelect" datasource="your_dsn">
    SELECT
        empid,
        lastname + ',' + firstname as employeename
    FROM
        employee
    WHERE
        activeflag=0
</cfquery>

as

<cfquery name="EmployeeSelect" datasource="your_dsn">
    SELECT
        empid,
        concat(lastname,firstname) as employeename
    FROM
        employee
    WHERE
        activeflag=0
</cfquery>

I hope this might resolve your issue.

HTH

D

Dan_Bracuk

Inspiring

What you are looking to achieve is similar to a concept called related selects. There is more than one way to accomplish it. Here is a page that explains one of those ways. http://www.pathcom.com/~bracuk/code/RelatedSelects.htm.

D

Daverms

Inspiring

I hope the following example will help you..

P.S : Modify the input fields and db / table coulmns according to your need.

<cfquery name="EmployeeSelect" datasource="your_dsn">
    SELECT
        empid,
        lastname + ',' + firstname as employeename
    FROM
        employee
    WHERE
        activeflag=0
</cfquery>
<cfif isdefined('form.selEmployeeName')>
    <cfquery name="EmployeeInfoSelect" datasource="your_dsn">
        SELECT
            *
        FROM
            employee
        WHERE
            empid=#form.selEmployeeName#
    </cfquery>
</cfif>
<cfif isdefined('btnUpdate')>
    <cfquery name="EmployeeInfoUpdate" datasource="your_dsn">
        UPDATE
            employee
        SET
            firstname = '#trim(txtFirstName)#',
            lastname = '#trim(txtlastname)#',
            address = '#trim(txtaddress)#'
        WHERE
            empid = #form.selEmployeeName#
    </cfquery>
    <script language="javascript">
        alert ("Updated successfully!");
        location.href="employee.cfm";
    </script>
</cfif>
<cfif isdefined('btnDelete')>
    <cfquery name="EmployeeInfoDelete" datasource="your_dsn">
        UPDATE
            employee
        SET
            activeFlag=1
        WHERE
            empid = #form.selEmployeeName#
    </cfquery>
    <script language="javascript">
        alert ("Deleted successfully!");
        location.href="employee.cfm";
    </script>
</cfif>

<script language="javascript">
    function fnFetchEmployee(empId)
    {
        if (empId != "")
        {
            document.frmEmployee.submit();
        }
        else
        {
            alert ("Please select an Employee");
            document.frmEmployee.txtFirstName.value = "";
            document.frmEmployee.txtLastName.value = "";
            document.frmEmployee.txtAddress.value = "";
            return false;
        }
    }
</script>
<form name="frmEmployee" action="employee.cfm" method="post">
    <table border="0" cellpadding="1" cellspacing="0">
        <tr>
            <td>Employee :</td>
            <td>
                <select name="selEmployeeName" onChange="fnFetchEmployee(this.value);">
                    <option value="" <cfif not isdefined('form.selEmployeeName')>selected</cfif>>-- SELECT --</option>
                    <cfoutput query="EmployeeSelect">
                        <option value="#empid#" <cfif isdefined('EmployeeInfoSelect') and #EmployeeInfoSelect.empid# eq #empid#>selected</cfif>>#employeename#</option>
                    </cfoutput>
                </select>
            </td>
        </tr>
        <cfif isdefined('EmployeeInfoSelect')>
            <cfoutput>
                <tr>
                    <td>First Name :</td>
                    <td><input type="text" name="txtFirstName" value="#EmployeeInfoSelect.firstname#"></td>
                </tr>
                <tr>
                    <td>First Name :</td>
                    <td><input type="text" name="txtLastName" value="#EmployeeInfoSelect.lastname#"></td>
                </tr>
                <tr>
                    <td>Address :</td>
                    <td><input type="text" name="txtAddress" value="#EmployeeInfoSelect.address#"></td>
                </tr>
                <tr>
                    <td><input type="submit" name="btnUpdate" value="Update"></td>
                    <td><input type="submit" name="btnDelete" value="Delete"></td>
                </tr>
            </cfoutput>
        </cfif>
    </table>
</form>

A

arnoldporche47作成者

Participating Frequently

Thanks for this code!

At first, it was looking for a column "activeflag".. I did not what it was and what it is for, but I added it to my table and there were no errors.

2 Things that didn't do it:

1.) The drop-down list items only shows up as 0.0 and not the full name (first and last name)

2.) There were no textfield in the page (Just the drop down list and the text Employee:

D

Daverms

Inspiring

Hi,

At first, it was looking for a column "activeflag".. I did not what it was and what it is for, but I added it to my table and there were no errors.

While deleting a record, the good practice would be like, keeping it in the table with as an "inactive" record, that is what this "activeflag" field performs in our case, (ie, employee records which are all having "1" as activestatus, we can consider them as deleted employees). Also in our delete query we are not actually performing any deletions rather we are just setting the activestatus to "1".

2 Things that didn't do it:
1.) The drop-down list items only shows up as 0.0 and not the full name (first and last name)
2.) There were no textfield in the page (Just the drop down list and the text Employee:

Prerequisites :

* Create a table as "employee" with "firstname", "lastname", "address" and "activestatus" fields.

* Populate the "employee" with some test data.

* Then execute your ColdFusion code, and you can see the dropdown populated with the employee data, and when you select a particular record there, the textboxes will be displayed with the appropriate info.

HTH

サインアップ

ソーシャルログイン

コミュニティへログイン

ソーシャルログイン

ファイルをウイルススキャンする。

このファイルはダウンロードできません