Page 1 of 1

Problems with connecting to MS-SQL

Posted: Sun Aug 10, 2014 7:07 am
by Conwayhosting
Probably a problem seen many times before, and I have spent the last three days debugging this but it has me beat.

A form on a web page, passing using a POST command, to this php page, to put the data into a table in my Microsoft SQL server. Server is running on same machine as IIS7. Authentication is SQL authentication. Passwords and usernames are correct (and removed and replaced with XXX's here).

Someone please spot my obvious error. All I get on clicking submit on the form page is a blank screen... :oops: :oops: :oops: :oops: :oops: :banghead: :banghead: :banghead:

Code: Select all

<?php
//pull form fields into php variables
echo "This script is working ...."
$ChapterNo = $_POST['ChapterNo'];
$MeetDay = $_POST['MeetingDay'];
$MeetMonth = $_POST['MeetingMonth'];
$MeetYear = $_POST['MeetingYear'];
$Work = $_POST['Work'];
$uid = "XXXXXX"
$pwd = "XXXXXX"

//connect to sql
$serverName = "(local)";
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"Companions");

$conn = sqlsrv_connect( $serverName, $connectionInfo);


if( $conn )
{
     echo "Connection established.\n";
}
else
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}


// Input into database
$query = "INSERT INTO dbo.Meetings (ChapterNo,MeetDay,MeetMonth,MeetYear,Work) VALUES ('$ChapterNo','$MeetDay','$MeetMonth','$MeetYear','$Work')GO";
$result = sqlsrv_query($dbc,$query)or die('Error querying MSSQL database');

//close to sql
sqlsrv_close($link);

echo $ChapterNo . 'Thanks for using the New Web Form, you submission has been received<br />';
echo 'If you need change this request please log in to the Helpdesk portal<br />';
echo 'Thank you <br />';
echo 'The Helpdesk';
?>




Re: Problems with connecting to MS-SQL

Posted: Sun Aug 10, 2014 7:11 am
by Celauran
For openers, I see missing semicolons at the end of lines 3, 9, and 10.

Not familiar with MSSQL, but the remainder of the PHP syntax looks fine.

Re: Problems with connecting to MS-SQL

Posted: Sun Aug 10, 2014 7:30 am
by Conwayhosting
Oops

Ok, changed that,

also noticed that i had $conn as the connection, but had $dbc in the query string, now fixed
now get

This script is working ....Connection established. Error querying MSSQL database

Re: Problems with connecting to MS-SQL

Posted: Sun Aug 10, 2014 7:34 am
by Celauran
sqlsrv_errors() should be able to shed some light on why/what's going wrong.

Re: Problems with connecting to MS-SQL

Posted: Sun Aug 10, 2014 7:41 am
by Conwayhosting
Thanks for the help so far !!

Getting somewhere, adding in the error print line

This script is working ....Connection established. Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102

Code: Select all

 => 102 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. ) )

Re: Problems with connecting to MS-SQL

Posted: Sun Aug 10, 2014 8:32 am
by Conwayhosting
Sorted it ...

Created an array, then passes the paramaters to it. No clue why that made a difference, but heres the finished script...

Code: Select all

<?php
//pull form fields into php variables
echo "This script is working ....";
$ChapterNo = $_POST['ChapterNo'];
$MeetDay = $_POST['MeetingDay'];
$MeetMonth = $_POST['MeetingMonth'];
$MeetYear = $_POST['MeetingYear'];
$Work = $_POST['Work'];
$uid = "XXXXX";
$pwd = "XXXXXX";

echo $ChapterNo;

//connect to sql
$serverName = "(local)";
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"Companions");

$conn = sqlsrv_connect( $serverName, $connectionInfo);


if( $conn )
{
     echo "Connection established.\n";
}
else
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}


// Input into staff database
$query = "INSERT INTO dbo.Meeting ( ChapterNo, MeetDay, MeetMonth, MeetYear, Work) VALUES (?, ?, ?, ?, ?)";
$param = array( $ChapterNo, $MeetDay, $MeetMonth, $MeetYear, $Work);
$result = sqlsrv_query( $conn, $query, $param);

if( $result )
{
     echo "Row successfully inserted.\n";
}
else
{
     echo "Row insertion failed.\n";
     die( print_r( sqlsrv_errors(), true));
}


//close to sql
sqlsrv_close($conn);

echo $ChapterNo . 'Thanks for using the New Web Form, you submission has been received<br />';
echo 'If you need change this request please log in to the Helpdesk portal<br />';
echo 'Thank you <br />';
echo 'The Helpdesk';
?>