I am running PHP 5.3.24 (XAMPP package) and Microsoft SQL Server Express 2012 on windows 7 ultimate.
I have created a form to create a question bank for multiple choice tests. When I try to submit a question, I get the following error:
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -14 [code] => -14 [2] => An invalid parameter was passed to sqlsrv_query. [message] => An invalid parameter was passed to sqlsrv_query. ) )
Here's the code I use in the PHP file that handles the post data from the form:
<?PHP
//section 1: define variables from post and clean up for posting.
$Question = $_POST['Question'];
$Question = trim(stripslashes(str_replace("'", "''", $Question)));
$Answer1 = $_POST['Answer1'];
$Answer1 = trim(stripslashes(str_replace("'", "''", $Answer1)));
$Answer2 = $_POST['Answer2'];
$Answer2 = trim(stripslashes(str_replace("'", "''", $Answer2)));
$Answer3 = $_POST['Answer3'];
$Answer3 = trim(stripslashes(str_replace("'", "''", $Answer3)));
$Answer4 = $_POST['Answer4'];
$Answer4 = trim(stripslashes(str_replace("'", "''", $Answer4)));
$Answer5 = $_POST['Answer5'];
$Answer5 = trim(stripslashes(str_replace("'", "''", $Answer5)));
$Answer6 = $_POST['Answer6'];
$Answer6 = trim(stripslashes(str_replace("'", "''", $Answer6)));
$Answer7 = $_POST['Answer7'];
$Answer7 = trim(stripslashes(str_replace("'", "''", $Answer7)));
$Answer8 = $_POST['Answer8'];
$Answer8 = trim(stripslashes(str_replace("'", "''", $Answer8)));
$Answer9 = $_POST['Answer9'];
$Answer9 = trim(stripslashes(str_replace("'", "''", $Answer9)));
$Answer10 = $_POST['Answer10'];
$Answer10 = trim(stripslashes(str_replace("'", "''", $Answer10)));
$QuestionNotes = $_POST['QuestionNotes'];
$QuestionNotes = trim(stripslashes(str_replace("'", "''", $QuestionNotes)));
$CorrectAnsw = $_POST['CorrectAnsw'];
$ProgramArea = $_POST['ProgramArea'];
$Difficulty = $_POST['Difficulty'];
//section 2: define sql statement
$sql= ("INSERT INTO TESTDB.dbo.tblTestQuestions
(Question,Answer1,Answer2,Answer3,Answer4,Answer5,
Answer6,Answer7,Answer8,Answer9,Answer10,CorrectAnsw,
ProgramArea,Difficulty,QuestionNotes,Inactive)
VALUES
( '" . $Question . "','" . $Answer1 . "','" . $Answer2 . "','" . $Answer3 . "','" . $Answer4 . "',
'" . $Answer5 . "','" . $Answer6 . "','" . $Answer7 . "','" . $Answer8 . "','" . $Answer9 . "',
'" . $Answer10 . "'," . $CorrectAnsw . "," . $ProgramArea . "," . $Difficulty . ",
'" . $QuestionNotes . "',0)");
//section 3: call db connnection and post to database
include('C:/webincludes/dbconnect.php'); //this include defines $conn and has been tested to work
//fine and I use it on dozens of other forms that work fine.
if (!SQLSRV_query($conn,$sql))
{die( print_r( sqlsrv_errors(), true));} echo "Record Added";
SQLSRV_close($conn);
//section 4: I added the following to display the variables to see what is being passed to the sql server.
// To use it, I comment out section 3 and try to submit again.
echo "
<html><body><br><font face='calibri' size='5'><b>Output as follows: </font></b><br>
Question: " . $Question . "<br>
Ans1: " . $Answer1 . "<br>
Ans2: " . $Answer2 . "<br>
Ans3: " . $Answer3 . "<br>
Ans4: " . $Answer4 . "<br>
Ans5: " . $Answer5 . "<br>
Ans6: " . $Answer6 . "<br>
Ans7: " . $Answer7 . "<br>
Ans8: " . $Answer8 . "<br>
Ans9: " . $Answer9 . "<br>
Ans10: " . $Answer10 . "<br>
Correct Answ: " . $CorrectAnsw . "<br>
Area: " . $ProgramArea . "<br>
Level: " . $Difficulty . "<br>
Notes: " . $QuestionNotes . "<br>
<b>SQL Statement:</b><br>
" . $sql . "<br>
" . $stmt . "<br>
</font></body></html>
";
exit;
?>
When I submit data, I get the error above. If I comment out section 3 which actually runs the SQL script, and just post the data to display it, I get the following:
Output as follows: Question: What is the airspeed velocity of an unladen swallow
Ans1: 43 bp
Ans2: what do you mean? african or european?
Ans3: Blue, no yellooooooooooooooooow
Ans4: a shrubbery!
Ans5: Ni!
Ans6: Great Peril!
Ans7: Robins minsterals
Ans8: your mother was a hamster and your father smelt of elderberries.
Ans9: its only a model
Ans10: message for you, sir!
Correct Answ: : 2
Area: 10
Level: 3
Notes: we are the knights who say... Ni!
SQL Statement:
INSERT INTO CAR.dbo.tblTestQuestions (Question,Answer1,Answer2,Answer3,Answer4,Answer5, Answer6,Answer7,Answer8,Answer9,Answer10,CorrectAnsw, ProgramArea,Difficulty,QuestionNotes,Inactive) VALUES ( 'What is the airspeed velocity of an unladen swallow','43 bpm','what do you mean? african or european?','Blue, no yellooooooooooooooooow','a shrubbery!', 'Ni!','Great Peril!','Robins minsterals','your mother was a hamster and your father smelt of elderberries.','its only a model', 'message for you, sir!',2,10,3, 'we are the knights who say... Ni!',0)
If I take that sql statement and run it through the management console, it inserts the data just fine. But when I try to run it from the webpage, error.
I'm confounded. Please forgive a newb, but where am I going wrong?