When I try to submit data to my web application, I get the following error:
System.Data.SqlClient.SqlException: 'Incorrect syntax near 'bit'. Must declare the scalar variable "@"
I checked, and I don't have 'bit' anywhere in the code. Four of the columns I'm writing to are now bit columns. This is based of the question I asked "I'm trying write a Checkbox List to a Database, what am I missing?."
I implemented the discussed changes and now I get an error and I wasn't sure if I needed to ask a new question, which shows the new code, or add to that question.
HTML:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Revocations.Default_2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="Styles/StyleSheet1.css" rel="stylesheet" />
</head>
<body>
<form id="form2" runat="server">
<div class="t_row_header">
<asp:Image ID="Image1" runat="server" CssClass="logo" ImageUrl="~/Images/logo.png" />
</div>
<h1>Transfer Revocations</h1>
<div class="form-fields">
<div class="form-group-left">
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtSchoolYear" CssClass="ErrorMessage" ErrorMessage="Please enter a school year." SetFocusOnError="True">*</asp:RequiredFieldValidator>
School Year:
<asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="txtSchoolYear" CssClass="ErrorMessage" ErrorMessage="Please enter a school year >= 2017." Operator="GreaterThanEqual" SetFocusOnError="True" ValueToCompare="2017">*</asp:CompareValidator>
</label>
<asp:TextBox ID="TextBox1" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtStudentID" CssClass="ErrorMessage" ErrorMessage="Please enter a student ID." SetFocusOnError="True">*</asp:RequiredFieldValidator>
Student ID:</label> <asp:TextBox ID="TextBox2" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtFirstName" CssClass="ErrorMessage" ErrorMessage="Please enter a first name." SetFocusOnError="True">*</asp:RequiredFieldValidator>
First Name:</label> <asp:TextBox ID="TextBox3" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="txtLastName" CssClass="ErrorMessage" ErrorMessage="Please enter a last name" SetFocusOnError="True">*</asp:RequiredFieldValidator>
Last Name:</label> <asp:TextBox ID="TextBox4" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="txtRevokedSchool" CssClass="ErrorMessage" ErrorMessage="Please enter the revoking school" SetFocusOnError="True">*</asp:RequiredFieldValidator>
Revoked School:</label> <asp:TextBox ID="TextBox5" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="txtRevocationDate" CssClass="ErrorMessage" ErrorMessage="Please enter a revocation date" SetFocusOnError="True">*</asp:RequiredFieldValidator>
Revocation Date:</label> <asp:TextBox ID="TextBox6" runat="server" TextMode="Date" CssClass="txtBox"></asp:TextBox>
</div>
<!-- form-group-left -->
<div class="form-group-right">
<label>
Reason:<asp:CheckBoxList ID="CheckBoxList1" runat="server">
<asp:ListItem text="Academics" Value="1"></asp:ListItem>
<asp:ListItem text="Attendance" Value="2"></asp:ListItem>
<asp:ListItem text="Behavior" Value ="3"></asp:ListItem>
<asp:ListItem text="Cooperative Relationship" Value="4"></asp:ListItem>
</asp:CheckBoxList>
<br />
</label>
<label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="txtNotes" CssClass="ErrorMessage" ErrorMessage="Please enter a note." SetFocusOnError="True">*</asp:RequiredFieldValidator>
Notes:</label> <asp:TextBox ID="TextBox7" runat="server" height="218px" TextMode="MultiLine" CssClass="InsideShadow"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="SUBMIT" CssClass="btn" OnClick="btnSubmit_Click" />
</div>
</div>
<asp:ValidationSummary ID="ValidationSummary2" runat="server" CssClass="ErrorMessage" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPSS_RevocationConnection %>" DeleteCommand="SELECT
[SchoolYear]
,[Student_ID]
,[Last_Name]
,[First_Name]
,[Revoked_School]
,CAST([Revocation_Date] AS DATE) AS [Revocation_Date]
,[Notes]
,[Academics]
,[Attendance]
,[Behavior]
,[Cooperative Relationship]
FROM [PPSS_Work].[Transfers].[Revocations]"
SelectCommand="SELECT
[SchoolYear]
,[Student_ID]
,[Last_Name]
,[First_Name]
,[Revoked_School]
,CAST([Revocation_Date] AS DATE) AS [Revocation_Date]
,[Notes]
,[Academics]
,[Attendance]
,[Behavior]
,[Cooperative Relationship]
FROM [PPSS_Work].[Transfers].[Revocations]"
UpdateCommand="SELECT
[SchoolYear]
,[Student_ID]
,[Last_Name]
,[First_Name]
,[Revoked_School]
,CAST([Revocation_Date] AS DATE) AS [Revocation_Date]
,[Notes]
,[Academics]
,[Attendance]
,[Behavior]
,[Cooperative Relationship]
FROM [PPSS_Work].[Transfers].[Revocations]"></asp:SqlDataSource>
</form>
</body>
</html>
C# code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace Revocations
{
public partial class Default_2 : System.Web.UI.Page
{
protected void btnSubmit_Click(object sender, EventArgs e)
{
// Establish SQL Connection
SqlConnection sqlCon = new SqlConnection("Data Source = acctsql; Initial Catalog = PPSS_Work; Integrated Security = True") ;
{
// Write INSERT SQL statement
SqlCommand sqlCom = new SqlCommand("INSERT INTO [PPSS_Work].[Transfers].[t_Revocations](SchoolYear, Student_ID, Last_Name, First_Name, Revoked_School,Revocation_Date, Academics, Attendance, Behavior, [Cooperative Relationship], Notes) VALUES(@SchoolYear, @Student_ID, @First_Name, @Last_Name, @Revoked_School, @Revocation_Date,@Academics, @Attendance, @Behavior, @[Cooperative Relationship], @Notes)", sqlCon);
sqlCom.Parameters.AddWithValue("@SchoolYear", txtSchoolYear.Text);
sqlCom.Parameters.AddWithValue("@Student_ID", txtStudentID.Text);
sqlCom.Parameters.AddWithValue("@First_Name", txtFirstName.Text);
sqlCom.Parameters.AddWithValue("@Last_Name", txtLastName.Text);
sqlCom.Parameters.AddWithValue("@Revoked_School", txtRevokedSchool.Text);
sqlCom.Parameters.AddWithValue("@Revocation_Date", txtRevocationDate.Text);
sqlCom.Parameters.AddWithValue("@Notes", txtNotes.Text);
foreach (ListItem item in chkbxlst1.Items)
{
if (item.Selected)
sqlCom.Parameters.AddWithValue("@" + item.Text, true);
}
sqlCon.Open();
sqlCom.ExecuteNonQuery();
sqlCon.Close();
if (IsPostBack)
{
txtSchoolYear.Text = "";
txtStudentID.Text = "";
txtFirstName.Text = "";
txtLastName.Text = "";
txtRevokedSchool.Text = "";
txtRevocationDate.Text = "";
chkbxlst1.Text = "";
txtNotes.Text = "";
}
}
}
}
}
What is causing these errors please?
