Invalid precision value error

Yii 1.1 General Discussion
1

Hi, I am a new Yii user and am learning PHP as well. Whenever a user leaves a non-required field blank, and submit a form the following error is thrown: "CDbCommand failed to execute the SQL statement: SQLSTATE[HY104]: [Microsoft][SQL Server Native Client 10.0]Invalid precision value. If all fields are completed, the error is not thrown. Also, I imported data into the database and when I view a record in Yii all the <NULL> fields are showing as "not set" Any assistance with handling MSSQL fields that "allow null" is greatly appreciated. I am not sure what, I am doing wrong here…

Error in executing SQL: INSERT INTO [dbo].[grantees] ([awardno],

[granteeName], [address1], [address2], [city], [state], [zipcode],

[granteeContactFirstName], [granteeContactLastName],

[granteeContactDegree], [granteeContactLicense], [granteeContactPhone],

[granteeContactEmail], [programcode], [projectOfficerFirstName],

[projectOfficerLastName], [projectOfficerPhone]) VALUES (:yp0, :yp1, :yp2,

:yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10, :yp11, :yp12, :yp13,

:yp14, :yp15, :yp16)

in

…\protected\controllers\GranteesController.php

(72)

in …\index.php (15)

2

Hi,

I am not a MSSQL expert, but what I usually do to verify any problems with database is to view the log.

In my /protected/config/main.php, I have in my components:




'db' => array(

            'connectionString' => 'mysql:host=localhost;dbname=xxx',

            'emulatePrepare' => true,

            'username' => 'yyy',

            'password' => 'xxx',

            'charset' => 'utf8',

            'tablePrefix' => 'tbl_',

            'enableProfiling' => true,

            'enableParamLogging' => true,

        ),

'log' => array(

            'class' => 'CLogRouter',

            'routes' => array(

                array(

                    'class' => 'CFileLogRoute',

                    'categories' => 'system.db.*',

                    'levels' => 'trace, info, error, warning',

                ),

        ...

And by simply opening the application.log under the runtime folder, I can see the sql statement that created the error, examine it, and maybe try to implement it directly to the database. Maybe what was being inserted in your table is not NULL but a ‘’. Alternatively, you can uncomment the CWebLogRoute portion in the same php file




 array(

              'class'=>'CWebLogRoute',

              'categories'=>'system.db.CDbCommand',

              //'showInFireBug'=>true,

              ),

to see the logs in your browser

Also in the viewing of records, "Not Set" is the default text for NULL values if you are using CDetailView (see nullDisplay here).

3

Thanks so much for the advise. I am going to add the necessary code to my components (as suggested) to verify the database issue. Just as you have indicated, I have a hunch that empty strings are being inserted rather than nulls. So, I probably need to update all my varchar database fields as needed. The CDetailView information is very helpful as well. Hopefully, I will be able to resolve this issue.

Thanks, again!

4

Just wanted to say that I resolved this issue. In MSSQL, I merely needed to set a default value for varchar fields to allow for empty strings. Thanks, again!

5

Glad you got it solved. And welcome to Yii community!