Insert a data in MS Sql Server Managment studio thorugh php

Question

Insert a data in MS Sql Server Managment studio thorugh php

zsultan 53790

SSC Veteran

Points: 224
More actions
March 13, 2018 at 5:50 am

#329848

Hi,
I am using MS SQL server management through PHP. I have already connection successful but the problem is when I am going to insert data into a table through Excel sheet with the help of PHP. So it's showing me an error: "Count field error or syntax error".
please help me to resolve this error.
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

zsultan 53790 - Tuesday, March 13, 2018 5:50 AM
Hi,
I am using MS SQL server management through PHP. I have already connection successful but the problem is when I am going to insert data into a table through Excel sheet with the help of PHP. So it's showing me an error: "Count field error or syntax error".
please help me to resolve this error.
Thanks,

Why use SSMS at all? Make a direct connection instead.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

zsultan 53790 SSC Veteran Points: 224 More actions · Answer 2

Phil Parkin - Tuesday, March 13, 2018 5:53 AM
zsultan 53790 - Tuesday, March 13, 2018 5:50 AM
Hi,
I am using MS SQL server management through PHP. I have already connection successful but the problem is when I am going to insert data into a table through Excel sheet with the help of PHP. So it's showing me an error: "Count field error or syntax error".
please help me to resolve this error.
Thanks,
Why use SSMS at all? Make a direct connection instead.

Sorry i am connecting directly to sql server through php.
Here is my code:

$serverName = "xxxx\xx";
    $connectionInfo = array( "Database"=>"xx", "UID"=>"xx", "PWD"=>"xx" );    $connectionInfo = array( "Database"=>"xx", "UID"=>"xx", "PWD"=>"xx" );
    $conn = sqlsrv_connect( $serverName, $connectionInfo);    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    if( $conn === false ) {    if( $conn === false ) {
      die( print_r( sqlsrv_errors(), true));      die( print_r( sqlsrv_errors(), true));
    }    }
    else{    else{
        echo 'Connection Established';        echo 'Connection Established';
    }    }
    include 'excel_reader\excel_reader.php';    include 'excel_reader\excel_reader.php';
    $excel = new PhpExcelReader;    $excel = new PhpExcelReader;

    $newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls';    $newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls';
    $excel->read($newfile);    $excel->read($newfile);
    $nr_sheets = count($excel->sheets);   // gets the number of sheets    $nr_sheets = count($excel->sheets);   // gets the number of sheets
    $data = $excel->sheets;    $data = $excel->sheets;
    echo '<pre>';    echo '<pre>';
    //echo print_r($data[0]['cells']);    //echo print_r($data[0]['cells']);
    echo '</pre>';    echo '</pre>';

    $rows = $data[0]['cells'];    $rows = $data[0]['cells'];
    $sql = "INSERT INTO [dbo].[table1] ([nameID],[familyID],[foodID],[relationship],[parent_last_name], [parent_first_name]) VALUES (?,?,?,?,?,?) ";    $sql = "INSERT INTO [dbo].[table1] ([nameID],[familyID],[foodID],[relationship],[parent_last_name], [parent_first_name]) VALUES (?,?,?,?,?,?) ";
    $new = "";    $new = "";
    for($i=1; $i<sizeof($rows);$i++){    for($i=1; $i<sizeof($rows);$i++){
        $new .= " ( '{$rows[$i][1]}', '{$rows[$i][2]}', '{$rows[$i][3]}' , '{$rows[$i][4]}' ,'".addslashes($rows[$i][5])."' ,'".addslashes($rows[$i][5])."' ),";        $new .= " ( '{$rows[$i][1]}', '{$rows[$i][2]}', '{$rows[$i][3]}' , '{$rows[$i][4]}' ,'".addslashes($rows[$i][5])."' ,'".addslashes($rows[$i][5])."' ),";
    }    }
    $new = rtrim($new,',');    $new = rtrim($new,',');
    $sql1 = $sql . $new;    $sql1 = $sql . $new;
    $stmt = sqlsrv_query( $conn, $sql1);    $stmt = sqlsrv_query( $conn, $sql1);
        if( $stmt === false ) {        if( $stmt === false ) {
          die( print_r( sqlsrv_errors(), true));          die( print_r( sqlsrv_errors(), true));
        }        }
        else{        else{
            echo ' <br> Inserted';            echo ' <br> Inserted';
        }        }
    sqlsrv_close($conn);    sqlsrv_close($conn);   ?> ?>

This code makes connection successful but after connections its show error:

Array ( [0] => Array ( [0] => 07002 [SQLSTATE] => 07002 [1] => 0 => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error [message] => [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error ) )

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

I do not know PHP, so cannot be of much help.
Are you able to detect exactly which line of code generates the error?
Is there a reason why exactly the same code is sometimes repeated? Eg
$newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls'; $newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls';

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Thom A SSC Guru Points: 99126 More actions · Answer 4

Out of interest, why do you want to use PHP to do the loading of an xls(x) file? A proper ETL tool would be better instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

Phil Parkin - Tuesday, March 13, 2018 6:48 AM
I do not know PHP, so cannot be of much help.
Are you able to detect exactly which line of code generates the error?
Is there a reason why exactly the same code is sometimes repeated? Eg
$newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls'; $newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls';

If this is the statement which runs the INSERT
$stmt = sqlsrv_query( $conn, $sql1)
Can you describe how the parameters are being passed to this statement?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

zsultan 53790 SSC Veteran Points: 224 More actions · Answer 6

Phil Parkin - Tuesday, March 13, 2018 7:04 AM
Phil Parkin - Tuesday, March 13, 2018 6:48 AM
I do not know PHP, so cannot be of much help.
Are you able to detect exactly which line of code generates the error?
Is there a reason why exactly the same code is sometimes repeated? Eg
$newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls'; $newfile = 'C:\xampp\htdocs\asureid\excel\abc.xls';
If this is the statement which runs the INSERT
$stmt = sqlsrv_query( $conn, $sql1)
Can you describe how the parameters are being passed to this statement?

sqlsrv_query is the function which executes queries of sql with the parameters of connection string ($conn) and other paramter is for SQL query

Phil Parkin SSC Guru Points: 246695 More actions · Answer 7

zsultan 53790 - Tuesday, March 13, 2018 7:08 AM
Phil Parkin - Tuesday, March 13, 2018 7:04 AM
If this is the statement which runs the INSERT
$stmt = sqlsrv_query( $conn, $sql1)
Can you describe how the parameters are being passed to this statement?
sqlsrv_query is the function which executes queries of sql with the parameters of connection string ($conn) and other paramter is for SQL query

Understood. How are the data values which have been retrieved from Excel passed to $sql1? Can you somehow post the contents of $sql1 just before it is executed?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.