Problems with connecting to MS-SQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Conwayhosting
Forum Newbie
Posts: 4
Joined: Sun Aug 10, 2014 7:01 am

Problems with connecting to MS-SQL

Post 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';
?>



User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with connecting to MS-SQL

Post 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.
Conwayhosting
Forum Newbie
Posts: 4
Joined: Sun Aug 10, 2014 7:01 am

Re: Problems with connecting to MS-SQL

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with connecting to MS-SQL

Post by Celauran »

sqlsrv_errors() should be able to shed some light on why/what's going wrong.
Conwayhosting
Forum Newbie
Posts: 4
Joined: Sun Aug 10, 2014 7:01 am

Re: Problems with connecting to MS-SQL

Post 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'. ) )
Conwayhosting
Forum Newbie
Posts: 4
Joined: Sun Aug 10, 2014 7:01 am

Re: Problems with connecting to MS-SQL

Post 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';
?>



Post Reply