Error: Invalid column name 'B'. at System.Data.SqlClient.SqlConnection
I'm trying to create a simple registration page with ASP.NET C# in Visual Studio 2013.
I've encountered the following error upon clicking on the submit button to ensure that the registration details get into the database that I've tried to connect to.
Error:System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'B'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Registration.submitButton_Click(Object sender, EventArgs e) in d:\Desktop\IIPWebsite\Registration.aspx.cs:line 56 ClientConnectionId:41bb1e5f-3298-4b70-89d6-b12d0519ea55
My code for the aspx page are as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Registration.aspx.cs" Inherits="Registration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>NHH Consultants</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<link href="http://fonts.googleapis.com/css?family=Source+Sans+Pro:200,300,400,600,700,900" rel="stylesheet" />
<link href="default.css" rel="stylesheet" type="text/css" media="all" />
<link href="fonts.css" rel="stylesheet" type="text/css" media="all" />
<!--[if IE 6]>
< link href="default_ie6.css" rel="stylesheet" type="text/css" />
<![endif]-->
<style type="text/css">
.style1
{
font-size: 1em;
}
.style3
{
font-size: 4em;
}
.style4
{
width: 208px;
}
.style2
{
width: 126px;
}
.style6
{
font-size: medium;
color: #FFFFFF;
}
.style7
{
text-align: center;
font-size: 30pt;
color: #FFFFFF;
}
.style8
{
color: #FF9933;
}
.auto-style1 {
width: 261px;
}
.auto-style2 {
width: 126px;
height: 27px;
}
.auto-style3 {
width: 208px;
height: 27px;
}
.auto-style4 {
width: 261px;
height: 27px;
}
.auto-style12 {
width: 152px;
height: 26px;
}
.auto-style14 {
width: 261px;
height: 26px;
}
.auto-style23 {
width: 152px;
height: 29px;
}
.auto-style25 {
width: 261px;
height: 29px;
}
.auto-style26 {
width: 206px;
}
.auto-style27 {
width: 206px;
height: 26px;
}
.auto-style28 {
width: 206px;
height: 29px;
}
.auto-style29 {
font-size: medium;
color: #FFFFFF;
width: 152px;
}
.auto-style30 {
width: 152px;
}
</style>
</head>
<body>
<form id="form" runat="server">
<div id="header-wrapper"
style="background-image: url('images/NYC_Night.jpg')">
<div id="header-wrapper2">
<div id="header" class="container">
<div id="logo">
<h1 style="color: #FFFFFF">NHH Consultants</h1>
</div>
<div id="menu">
<ul>
<li class="current_page_item"><a href="#" accesskey="1" title="">Homepage</a></li>
<li><a href="#" accesskey="2" title="">LOGIN</a></li>
<li><a href="#" accesskey="3" title="">About Us</a></li>
<li><a href="#" accesskey="4" title="">Contact Us</a></li>
<li><a href="#" accesskey="4" title="">Services</a></li>
<li></li>
</ul>
</div>
</div>
<div class="major">
<h2 class="style3">reliability, Anytime, anywhere</h2>
<span class="style1">Your number one electrical & mechanical engineering
consultant</span> </div>
</div>
</div>
<div id="wrapper1" class="style7">
<strong>Registration
<br />
"*" Denotes field is compulsory</strong></div>
<table class="style1">
<tr>
<td class="auto-style29">
*Username:</td>
<td class="style4">
<asp:TextBox ID="userTB" runat="server" Width="180px"> </asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="userTB" ErrorMessage="Username is required!"
ForeColor="Red" CssClass="style8"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="auto-style29">
*Password:</td>
<td class="style4">
<asp:TextBox ID="passTB" runat="server" Width="180px" TextMode="Password" ViewStateMode="Disabled"> </asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="passTB" ErrorMessage="Password is required!"
ForeColor="Red" CssClass="style8"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="auto-style29">
*Confirm Password:</td>
<td class="style4">
<asp:TextBox ID="confirmTB" runat="server" Width="180px" TextMode="Password"> </asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
ControlToValidate="confirmTB" ErrorMessage="Confirmation of password is required!"
ForeColor="Red" CssClass="style8"></asp:RequiredFieldValidator>
<br />
<asp:CompareValidator ID="CompareValidator1" runat="server"
ControlToCompare="passTB" ControlToValidate="confirmTB"
ErrorMessage="Passwords do not match!" ForeColor="Red" CssClass="style8"></asp:CompareValidator>
</td>
</tr>
<tr>
<td class="auto-style29">
*Email:</td>
<td class="style4">
<asp:TextBox ID="emailTB" runat="server" Width="180px"> </asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"
ControlToValidate="emailTB" ErrorMessage="Email is required!"
ForeColor="Red" CssClass="style8"></asp:RequiredFieldValidator>
<br />
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
ControlToValidate="emailTB" ErrorMessage="Email is invalid!" ForeColor="Red"
ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
CssClass="style8"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td class="auto-style29">
*Company name:</td>
<td class="style4">
<asp:TextBox ID="companyTB" runat="server" Width="180px"> </asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server"
ControlToValidate="companyTB" ErrorMessage="Company is required!"
ForeColor="Red" CssClass="style8"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="auto-style30" style="color: #FFFFFF">
*First name:</td>
<td class="style4">
<asp:TextBox ID="firstnameTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style1" title="First name is required!">
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="firstnameTB" ErrorMessage="First name is required!" ForeColor="Red"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
<table class="style1">
<tr>
<td class="auto-style30" style="color: #FFFFFF">*Last name:</td>
<td class="auto-style26">
<asp:TextBox ID="lastnameTB" runat="server" Width="179px"></asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ErrorMessage="Last name is required!" ControlToValidate="lastnameTB" ForeColor="Red"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="auto-style12" style="color: #FFFFFF">*Address:</td>
<td class="auto-style27">
<asp:TextBox ID="addressTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style14">
<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ErrorMessage="Address is required!" ControlToValidate="addressTB" ForeColor="Red"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="auto-style30" style="color: #FFFFFF">*Postal Code:</td>
<td class="auto-style26">
<asp:TextBox ID="postalcodeTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style1">
<asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" ErrorMessage="Postal Code is required!" ControlToValidate="postalcodeTB" ForeColor="Red"></asp:RequiredFieldValidator>
<br />
<asp:RegularExpressionValidator ID="RegularExpressionValidator4" runat="server" ControlToValidate="postalcodeTB" ErrorMessage="Postal code is invalid!" ForeColor="Red" ValidationExpression="\d{6}(\d{4})?$"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td class="auto-style12" style="color: #FFFFFF">*Contact Number:</td>
<td class="auto-style27">
<asp:TextBox ID="contactnumberTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style14">
<asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server" ErrorMessage="Contact number is required!" ControlToValidate="contactnumberTB" ForeColor="Red"></asp:RequiredFieldValidator>
<br />
<asp:RegularExpressionValidator ID="RegularExpressionValidator3" runat="server" ControlToValidate="contactnumberTB" ErrorMessage="Contact number is invalid!" ForeColor="Red" ValidationExpression="^[689]\d{7}$"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td class="auto-style23" style="color: #FFFFFF">
<br />
Date of Birth:</td>
<td class="auto-style28" style="color: Red">
<asp:Label ID="dateLabel" runat="server" ForeColor="#FFFF66" Text="Date in YYYY-MM-DD format" Visible="True"></asp:Label>
<br />
<asp:TextBox ID="dateTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style25" style="color: Red">
<br />
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="dateTB" ErrorMessage="Date is invalid!" ForeColor="Red" ValidationExpression="^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td class="auto-style30" style="color: #FFFFFF">City:</td>
<td class="auto-style26">
<asp:TextBox ID="cityTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style1"> </td>
</tr>
<tr>
<td class="auto-style30" style="color: #FFFFFF">Fax number:</td>
<td class="auto-style26">
<asp:TextBox ID="faxTB" runat="server" Width="180px"></asp:TextBox>
</td>
<td class="auto-style1"> </td>
</tr>
<tr>
<td class="auto-style30"> </td>
<td class="auto-style26"> </td>
<td class="auto-style1"> </td>
</tr>
</table>
<table class="style1">
<tr>
<td class="auto-style2">
</td>
<td class="auto-style3">
</td>
<td class="auto-style4">
<asp:Button ID="submitButton" runat="server" onclick="submitButton_Click"
Text="Submit" Height="23px" Width="97px" />
<asp:Button ID="resetButton" runat="server" onclick="resetButton_Click" Text="Reset fields" Height="23px" Width="97px" />
</td>
</tr>
</table>
</form>
<div id="wrapper">
</div>
</form>
</body>
</html>
While my codes for the aspx.cs page are as follows:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Registration : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegisterConnectionString"].ConnectionString);
conn.Open();
string checkuser = "select count(*) from Client where cUserName= '" + userTB.Text + "'";
SqlCommand com = new SqlCommand(checkuser, conn);
int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
if (temp == 1)
{
Response.Write("Username is already taken! Please choose another username.");
}
conn.Close();
}
}
protected void submitButton_Click(object sender, EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegisterConnectionString"].ConnectionString);
conn.Open();
string insertQuery = "insert into Client (cFirstName, cLastName, cD.O.B, cCompanyName, cAddress, cCity, cZipCode, cPhoneNo, cFax, cEmail, cUsername, cPassword) values (@firstname,@lastname,@dob,@companyname,@address,@city,@zipcode,@phoneno,@fax,@email,@username,@password)";
SqlCommand com = new SqlCommand(insertQuery, conn);
com.Parameters.AddWithValue("@firstname", firstnameTB.Text);
com.Parameters.AddWithValue("@lastname", lastnameTB.Text);
com.Parameters.AddWithValue("@dob", dateTB.Text);
com.Parameters.AddWithValue("@companyname", companyTB.Text);
com.Parameters.AddWithValue("@address", addressTB.Text);
com.Parameters.AddWithValue("@city", cityTB.Text);
com.Parameters.AddWithValue("@zipcode", postalcodeTB.Text);
com.Parameters.AddWithValue("@phoneno", contactnumberTB.Text);
com.Parameters.AddWithValue("@fax", faxTB.Text);
com.Parameters.AddWithValue("@email", emailTB.Text);
com.Parameters.AddWithValue("@username", userTB.Text);
com.Parameters.AddWithValue("@password", passTB.Text);
com.ExecuteNonQuery();
Response.Write("Congratulations! Your registration is successful!");
Response.Redirect("ClientLogin.aspx");
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error:" + ex.ToString());
}
}
protected void resetButton_Click(object sender, EventArgs e)
{
Response.Redirect("Registration.aspx");
}
}
Sarah Collins
shA.t1 Answer
You are trying to insert data into a field called c.D.O.B. in the database. Wrap it in square brackets and things should work, i.e.
insert into Client (cFirstName, cLastName, [cD.O.B], ...
If you have any influence on the database design, make sure to change that column name into something a little more sensible. Periods in column names don't play well with a lot of tooling, and have very little added value here.
answered on Stack Overflow Jan 3, 2015 by 
Paul-Jan
Paul-JanUser contributions licensed under CC BY-SA 3.0