Edit Record

johnw75

Hi,
the problem is:
"SQLSTATE[22018]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'gruppe_id' to data type int."

Gruppe_ID is an int field on MS SQL Server 2008 table. When i try to update i get the error as follows.

The Edit is filled correctly as int but will be not correct send to DB.

What is wrong?

Thanks

allan

Are you using Editor here or something else? If so, is this with the PHP or .NET libraries?

Allan

johnw75

Hi,
i'm using latest Editor with PHP.

allan

Can you show me your PHP configuration of Editor and also your MS SQL schema please?

Thanks,
Allan

johnw75

Schema mssql;

CREATE TABLE [dbo].[ATS_WEB_Gruppe_Tbl](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Gruppe_Id] [int] NOT NULL,
    [Gruppe_Text] [nchar](100) NULL
) ON [PRIMARY]

liste.php

    <script type="text/javascript" language="javascript">
            $(document).ready(function() {
                var editor = new $.fn.dataTable.Editor( {
                    ajax: '../security/gruppe/ATS_WEB_Gruppe_Tbl.php',
                    table: '#ATS_WEB_Gruppe_Tbl',
                    display: 'envelope',
                    fields: [
                        {
                            "label": "gruppe_id",
                            "name": "gruppe_id"
                        },
                        {
                            "label": "gruppe_text",
                            "name": "gruppe_text"
                        }
                    ]
                } );

                var add = $('a.editor_create').on( 'click', function (e) {
                    e.preventDefault();
                    
                    editor
                        .title( 'Create new record' )
                        .buttons( { "label": "Add", "fn": function () { editor.submit() } } )
                        .create();
                } );
                
                var edit = $('#ATS_WEB_Gruppe_Tbl').on( 'click', 'a.editor_edit', function (e) {
                    e.preventDefault();
             
                    editor
                        .title( 'Edit record' )
                        .buttons( { "label": "Update", "fn": function () { editor.submit() } } )
                        .edit( $(this).closest('tr') );
                } );
            
                var dataTable = $('#ATS_WEB_Gruppe_Tbl').DataTable( {
                    "dom": "Tfrtip",
                    "responsive": "true",
                    "columns": [
                                   { data: "gruppe_id"  , "width": "10%" , "orderable" : true },
                                   { data: "gruppe_text", "width": "50%" , "orderable" : true },
                                   {
                                       data: null,
                                       className: "center",
                                       defaultContent: '<a href="" class="editor_edit">Edit</a>'
                                   }
                                 ],
                    "columnDefs": [
                                   { "className" : "dt-center", "targets" : [0] }
                                 ],
                    "select": {
                         style:    'os',
                         selector: 'td:first-child'
                     },
                     "paging" :         false,
                     "processing":      true,
                     "searching":       false,
                     "serverSide":      true,
                     "scrollY":         "500px",
                     "scrollCollapse" : true,
                     "info" :           true,
                     "ajax": {
                        url:  "../security/gruppe/ATS_WEB_Gruppe_Tbl.php" ,
                        error: function() {
                            $(".employee-grid-error").html("");
                            $("#ATS_WEB_Gruppe_Tbl").append('<tbody class="employee-grid-error"><tr><th colspan="3">Error ind processing</th></tr></tbody>');
                            $("#ATS_WEB_Gruppe_Tbl_processing").css("display","none");
                        }               
                                
                    }
                } );
            } );
    </script>

..
<body>
    <table id="ATS_WEB_Gruppe_Tbl" align="left" cellpadding="0" cellspacing="0" border="0" class="stripe">
        <thead>
            <tr>
                <th><?php echo $_SESSION['messages']->getText(3033); ?></th>
                <th><?php echo $_SESSION['messages']->getText(3034); ?></th>
                <th>Edit</th>
            </tr>
        </thead>
    </table>
</body>

ATS_WEB_Gruppe_Tbl.php

<?php

// DataTables PHP library and database connection
include ("../../../classes/Editor/DataTables.php");

// Alias Editor classes so they are easy to use
use DataTables\Editor, 
    DataTables\Editor\Field, 
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst ( $db, 'ATS_WEB_Gruppe_Tbl', 'ID' )
    ->fields(
        Field::inst( 'gruppe_id'   )
            ->validator('Validate::notEmpty')
            ->getFormatter ( function ( $val, $data, $field ) {
                             return intval( $val ) ;
                            } )
            ->setFormatter ( function ( $val, $data, $field ) {
                             return $val ;
                            } ),
        Field::inst( 'gruppe_text' )->validator( 'Validate::notEmpty' )
    )
    ->process( $_POST )
    ->json();
?>

i tried to change with set and getFormatter, Id is shown correctly with getFormatter as int in JSON. Edit Formular is correct field, but cannot save. Known error will be shown.

Thanks for help

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

johnw75

config.php

<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Database user / pass
 */

$server = "xxxx";
$username ="xxx";
$password = "xxx";
$database = "xxx";

$sql_details = array(
    "type" => "Sqlserver",  // Database type: "Mysql", "Postgres", "Sqlite" or "Sqlserver"
    "user" => $username,       // Database user name
    "pass" => $password,       // Database password
    "host" => $server,       // Database host
    "port" => "",       // Database connection port (can be left empty for default)
    "db"   => $database,       // Database name
    "dsn"  => ""        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
);

Database connection work. I changed nothing.

Thanks

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Thanks for the details. I'll take a look through them and get back to you tomorrow.

Regards,
Allan

johnw75

Hi Allan,

fine thanks.

J

johnw75

HI Allan,
any Progress?

J

allan

Hi,

Apologies for the delay! I've just been looking into it, and haven't been able to reproduce the issue myself unfortunately. Can you check what version of PHP you are using? There are a few threads about this error in the early 5.3 series.

Are you using Windows or *nux? I'm going to guess Linux since it mentioned the ODBC driver - is that correct? What version of the ODBC driver are you using and how is it configured? (I must confess I always found ODBC on linux to be a bit of a nightmare :-) ).

Regards,
Allan

johnw75

Hi,
thanks for your response. We are you using IIS 7.5 with web Platform manager installed php 5.5.34 on a windows Server 2012 with SQL Server 2008. We installed the Driver with the IIS Web Platform manager for PHP 5.5. Thats mean we need sqlserv_connect for connecting to the database etc. Could it be that the PDO Driver, with is part of Editor I think, works not fine?
Strange is that i changed all fields to varchar, but that interface ignored that.
Is it helpful for?

Regards J

allan

If you are using a Windows server, could you try the Microsoft SQLServer PDO driver rather than the ODBC driver?

Editor doesn't provide PDO drivers, but rather it utilises those those for the various database types available.

Regards,
Allan

johnw75

We are using Windows Server and these are excat the driver that we are using installed by Web Platform installer for IIS 7.5

johnw75

We are not using ODBC Driver. Strange is that Edit Dialog will be filled with correct information, but cannot stored in DB with same values.

johnw75

is the editor a trial version and not free, how can i see that, can that be a problem?

allan

The trial version shouldn't make any difference here - the only difference between the trial and the licensed version is that the Javascript in the trial is obfuscated and time limited. That will have no effect on the server-side aspect here.

The reason I mention ODBC is the error message you mentioned back at the top of the thread:

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]

Having said that, looking at the PHP documentation, it now looks like the MS SQLServer drivers (3.1 and 3.2) do actually require ODBC - which I don't think they did before - so we can possibly ignore that - sorry!

Let me see if I can update my PHP and get it running locally and I'll post back.

Allan

johnw75

Hi Allan,
possible to reproduce?

Thanks

allan

Apologies, I haven't had a chance to try and do so yet. I'll post back as soon as possible.

Allan