troubleshooting Question

MSSQL Server from PHP allowing empty string as a valid numeric insert value

Avatar of jbclelland
jbclelland asked on
Microsoft SQL ServerPHPMicrosoft SQL Server 2005
6 Comments5 Solutions618 ViewsLast Modified:
We have just converted to using MSSQL from MySQL.  We have insert statements like the following:

INSERT INTO proteus_compliance.dbo.users (username, email, displayName, title, firstname, surname, position, phone_office, phone_mobile, phone_fax, notes, site, supplier_id, admin_level, welcome_email_sent, initial_password, language, date_format, ldap, external_only, supplier_ownership) VALUES ('A_COKER', 'ashley.coker@infogov.co.uk', 'Mr Ashley Coker', 'Mr', 'Ashley', 'Coker', 'Principal Developer', '', '', '', '', '3', '0', '0', '0', 'm3xp67vs', 'English', '', '0', '', '');

This worked fine in MySQL, however in MsSQL it reports the error:
"Error converting data type varchar to numeric."

This is caused by the '' fields where the empty string is trying to be inserted into a numeric field. However, if a number is inside the string such as '3' it works fine.  Is there a way to set the server or mssql connection parameter for the mssql library from PHP to allow the empty string to be used as an insert into the database without triggereing an error.  Otherwise, we will have to modify a lot of our code to use the NULL keyword in its place or the appropriate default value.  A global fix to acceot the empty string as a valid numeric value would be better for us.

Thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 5 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros