asked on

What does this error mean?

I start things off by creating a temporary table like this:
$sql_50 = "CREATE TABLE #tmp_Results
      (
            id      int IDENTITY,
            str_ProjectName varchar(150),
            str_Tool varchar(150),
            str_FullName varchar(150),
            str_ProjectType varchar(150),
            str_Description varchar(max),
            str_TargetCompletionDate varchar(50),
            dec_PercentageCompleted decimal(10,2),
            str_ProjectId varchar(150),
            int_ProjectRank int
      )";

Then I run this query:

insert into #tmp_Results (
str_ProjectName,
str_Tool,
str_FullName,
str_Description,
str_ProjectType,
str_TargetCompletionDate,
dec_PercentageCompleted,
str_ProjectId,
int_ProjectRank)
VALUES (
'SA Pipeline Workbook',
'SA Portal (MVC)',
' ',
'Automate the SA Pipeline Report that is currently in xls form. Data currently pulled from multiple datasources including NETWorkflow.',
'New Feature',
'',
'',
'140618b',
'999')

...and I get this error:

Error converting data type varchar to numeric.

Pretty sure it has something to do with my decimal field, but I don't know what or how to fix it.

Thoughts?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce Gust

ASKER

Jim, I think you've nailed it, but how do I substitute NULL on the fly?

Here's what I'm doing:

foreach($result_array as $project)
		{
			if($project['dec_PercentageCompleted']=="")
			{
				$dec_Percentage=NULL;
			}
			else
			{
				$dec_Percentage=$project['dec_PercentageCompleted'];
			}
		$sql_11="insert into #tmp_Results 
		(str_ProjectName, str_Tool, str_FullName, str_Description, str_ProjectType, str_TargetCompletionDate, dec_PercentageCompleted, str_ProjectId, int_ProjectRank)
		VALUES
		('$project[str_ProjectName]',
		'$project[str_Tool]', 
		'$project[str_Full_Name_D]',
		'$project[str_Description]',
		'$project[str_ProjectType]',
		'$project[str_TargetCompletionDate]',
		'$dec_Percentage',
		'$project[str_ProjectId]', 		
		'$project[int_ProjectRank]')";
		$mssql_stmt_11=$mssql_pdo->query($sql_11);
		//echo $sql_11.'<br>';
			if(!$mssql_stmt_11)
			{
				echo "no query";
			}
		}

Open in new window

I'm trying to accommodate that situation where there's no value, yet even when I throw in "NULL," it doesn't seem to do anything.

Thoughts?

Jim Horn

I'm not familiar with the coding language you're using (what is it?), so on that point I'll step back to encourage other experts to respond.

Bruce Gust

ASKER

PHP

SOLUTION

Marco Gasi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Brian Tao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

I suggest trying code below, allowing SQL to do the work of replacing the blank with a NULL:

...
VALUES(
...
'$project[str_TargetCompletionDate]',
NULLIF('$dec_Percentage', ''),
'$project[str_ProjectId]',
...