PHP表单到MSSQL 2005，自动生成ID（alter table add）

Question

Can someone please help? I do get connection to db and can retrieve data and "post", the only break point is ContactID record which is primary and required. I believe it has to do something with my Alter table line, I just do not know where.

Here is the scenario:

• Form is written in php.
• Form is posting data to MSSQL 2005
• ContactID can not be null and need to be assigned automatically.
• On submit I receive the exception: String or binary data would be truncated. The statement had been terminated.

Here is the php code:

    if(isset($_GET['action']))
    {

    if($_GET['action'] == 'add')

    {
    // this is where inserting data beggins

    $insertSql = "INSERT INTO sys.CONTACT (LASTNAME, FIRSTNAME, EMAIL)

    VALUES (?,?,?)";

    $params = array(("Alter table sys.CONTACT add ProgramID int IDENTITY(9000001,1) NOT NULL, CONTACTID  AS ('CCRMS'+CONVERT(varchar(7),ProgramID,(0)))"),
        &$_POST['lastName'],
        &$_POST['firstName'],
        &$_POST['emailAddress']);

    $stmt = sqlsrv_query($conn, $insertSql, $params);

    if($stmt === false)

    {/*Handle the case of a duplicte e-mail address.*/

        $errors = sqlsrv_errors();

            if($errors[0]['code'] == 2601)

            {
                echo "The e-mail address you entered has already been used.</br>";
            }

            /*Die if other errors occurred.*/

            else
            {
                die(print_r($errors, true));
            }
     }

     else

        {
            echo "Registration complete.</br>";
        }
    }}

Answer 1

Your exception is:

String or binary data would be truncated. The statement had been terminated.

It appears your ALTER string is actually being placed into the LASTNAME column. SQL Server is telling you that your long string has been truncated to the length of the LASTNAME column.

You're basically supplying 4 string values to the array. The first 3 are being accepted as args to the statement. One or more doesn't fit the length of the column it's trying to be fit into on the table.

Check back on the varchar() definitions of those columns. Are they smaller than the strings that are being supplied?

Use this instead:

  $params = array(&$_POST['lastName'],
                  &$_POST['firstName'],
                  &$_POST['emailAddress']);