Edit Form PHP PDO

Question

I have a table in SQL Server database. I created an "edit.php" where we can update the database. The database connection is established using PDO.

I have a lot of Null values in the table. When I click "Edit" option, the form pops out. If I completely fill the records, the database gets updated. Otherwise I get this error message.

SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Error converting data type nvarchar to numeric.

The codes for my edit.php are included below:

require_once('database.php');
if (isset($_POST['btn_submit'])) {
        $id = $_POST['txt_id'];
        $site = $_POST['txt_site_code'];
        $location = $_POST['txt_location_name'];

 try {
       $stmt = $conn->prepare("UPDATE MATRIX SET Site_Code=:site, Location_name=:location
WHERE OBJECTID =:id");
$stmt->execute(array(':site' => $site, ':location' => $location,':id' => $id));
if ($stmt) {


                   header('Location:index.php');

               }
             } catch (PDOException $e) {
               echo $e->getMessage();
            }
}

    $object_id = '';
    $site = '';
    $location = '';

 if (isset($_GET['id'])) {
        $id = $_GET['id'];
        $stmt = $conn->prepare("SELECT * FROM MATRIX WHERE OBJECTID=:id");
        $stmt->execute(array(':id' => $id));
        $row = $stmt->fetch();
        $object_id = $row['OBJECTID'];
        $site = $row['Site_Code'];
        $location = $row['Location_name'];
}

?>
    <h2>Edit the records</h2>

    <form action="" method="post">
        <table border="3px" cellpadding="5px">

            <tr>
                <td>Site Code</td>
                <td><input type="text" name="txt_site_code"  value="<?= $site; ?>"></td>
            </tr>

            <tr>
                <td>Location Name</td>
                <td><input type="text" name="txt_location_name" value="<?= $location; ?>"></td>
            </tr>

             <tr>
                <td><input type="hidden" name="txt_id" value="<?= $object_id; ?>"></td>
                <td><input type="submit" name="btn_submit" value="Submit"></td>
            </tr>

</table>
</form>

I would appreciate your efforts to help.

if any of the answer helped you mark it as correct. – Zenel Rrushi Sep 01 '16 at 16:01 — Zenel Rrushi, Sep 01 '16 at 16:01

score 1 · Accepted Answer · answered Sep 01 '16 at 15:38

1

You have to prevent null datas, received by php.

Example :

$site = '';
if(isset($_POST['site'])) {
    $site = $_POST['site'];
}

Do it for each value you want to protect before update your database.

answered Sep 01 '16 at 15:38

Kevin Grosgojat

1,386
8
13

Thank you so much Kevin. I am not clueless anymore. – Saroj Thapa Sep 01 '16 at 15:55
It works for the columns that have datatypes text (NVARCHAR), but for the columns that have numeric datatypes, I still get the same error. – Saroj Thapa Sep 01 '16 at 16:49
Do the same thing with 0 instead of '' for numerics values. A numeric field can't be initialised by string. – Kevin Grosgojat Sep 01 '16 at 20:11
Hey Kevin, I tried assigning 0 for the numeric types. Still didn't work. Below are the two fileds, one with the text type and the other with the numeric type. if (isset($_POST['txt_arc_capacity'])){ $arc = $_POST['txt_arc_capacity']; }else{ $arc = 0; } – Saroj Thapa Sep 01 '16 at 20:47

score 1 · Answer 2 · answered Sep 02 '16 at 01:13

1

Insert string value (example '0') if there is no data submitted instead of numeric value. The error because of insert numeric value to a string type column.

answered Sep 02 '16 at 01:13

FullStack

198
7
16

Edit Form PHP PDO

2 Answers2

Linked