I've created a Web Form using C# within Visual Web Dev 2010 Express and it contains text boxes / labels (based on check boxes) but when I run the code to fails on running the SQL command. I have gotten the code to work before using only 3 text boxes but doesn't seem to work when I expand the code.
Things I have tried:
When googling someone said something about quotation marks being an issue (and the error reflects that) but if I remove all the quotations from the SQL command it fails but my thoughts were it always had to be '"+datahere+"' is that true?
I will also quote all of my code behind for the web form in question below the error. The error looks like this when I run a 'debug':
Incorrect syntax near 't'. Unclosed quotation mark after the character string ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 't'. Unclosed quotation mark after the character string ')'.
Source Error:
Line 40: }
Line 41: SqlCommand cmd = new SqlCommand("insert into sicknesstest values('"+txtname.Text+"','"+txtgrade.Text+"','"+txtdepot.Text+"','"+txtillness.Text+"','"+txtabsence.Text+"','"+txtcontactby.Text+"','"+txtupdate.Text+"','"+txtdetails.Text+"','"+txtresumedate.Text+"','"+txtdetail.Text+"','"+txtmedonreturn.Text+"','"+txtreporter.Text+"','"+txtdateofcontact.Text+"','"+txtresumeddate.Text+"')", con);
Line 42: cmd.ExecuteNonQuery();
Line 43: con.Close();
Line 44: Label1.Visible = true;
Source File: C:\Users\MYNAME\Documents\Visual Studio 2010\Projects\SicknessDBNewSite\SicknessDBNewSite\WebForm2.aspx.cs Line: 42
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near 't'.
Unclosed quotation mark after the character string ')'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1791910
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5347106
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +546
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1693
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +869
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163
SicknessDBNewSite.WebForm2.Button1_Click(Object sender, EventArgs e) in C:\Users\MYNAME\Documents\Visual Studio 2010\Projects\SicknessDBNewSite\SicknessDBNewSite\WebForm2.aspx.cs:42
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9653178
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724
Code behind: webform2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="SicknessDBNewSite.WebForm2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 72%;
}
.style2
{
width: 443px;
}
.style3
{
text-align: center;
text-decoration: underline;
}
.style4
{
width: 443px;
height: 65px;
}
.style5
{
height: 65px;
}
.style6
{
text-align: center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align: center">
<h1 class="style3">
Test Form Submission
</h1>
<table class="style1" align="center">
<tr>
<td class="style2">
Name</td>
<td>
<asp:TextBox ID="txtname" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Grade</td>
<td>
<asp:TextBox ID="txtgrade" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Depot</td>
<td>
<asp:TextBox ID="txtdepot" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Nature of Illness?</td>
<td>
<asp:TextBox ID="txtillness" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Appointment made to see doctor (Yes or No)?</td>
<td>
<asp:RadioButton ID="RadioAppointmentYes" runat="server" Text="Yes" />
<asp:RadioButton ID="RadioAppointmentNo" runat="server" Text="No" />
<asp:Label ID="LabelAppointment" runat="server" Visible="False"></asp:Label>
</td>
</tr>
<tr>
<td class="style2">
Medication Taken (Yes or No)?</td>
<td class="style6">
<asp:RadioButton ID="RadioMedicationYes" runat="server" Text="Yes" />
<asp:RadioButton ID="RadioMedicationNo" runat="server" Text="No" />
<asp:Label ID="LabelMedication" runat="server" Visible="False"></asp:Label>
</td>
</tr>
<tr>
<td class="style2">
Expected length of absence?</td>
<td>
<asp:TextBox ID="txtabsence" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
If not contacted by staff member, who contacted on their behalf?</td>
<td>
<asp:TextBox ID="txtcontactby" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Likely resumption date?</td>
<td>
<asp:TextBox ID="txtresumedate" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Contact telephone number</td>
<td>
<asp:TextBox ID="txtcontactnum" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Latest update date</td>
<td>
<asp:TextBox ID="txtupdate" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style4">
Details</td>
<td class="style5">
<asp:TextBox ID="txtdetails" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Resumption Date</td>
<td>
<asp:TextBox ID="txtresumeddate" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Detail</td>
<td>
<asp:TextBox ID="txtdetail" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Is the person still on medication at the time of resumption?</td>
<td>
<asp:TextBox ID="txtmedonreturn" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Controller / Planner's name</td>
<td>
<asp:TextBox ID="txtreporter" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Date of contact</td>
<td>
<asp:TextBox ID="txtdateofcontact" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Date and time of next shift</td>
<td>
<asp:TextBox ID="txtdatetimeshift" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
</td>
<td>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Submit" Width="100px" />
</td>
</tr>
</table>
</div>
<a href="http://INTRANET/webform1.aspx">
<asp:Label ID="Label1" runat="server" ForeColor="Green" Visible="False" /></asp:Label>
</a>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionStringTestFormSubmission %>"
SelectCommand="SELECT * FROM [sicknesstest]"></asp:SqlDataSource>
</form>
</body>
</html>
webform2.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
namespace SicknessDBNewSite
{
public partial class WebForm2 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringTestFormSubmission"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
con.Open();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (RadioAppointmentYes.Checked)
{
LabelAppointment.Text = "Yes";
}
else if (RadioAppointmentNo.Checked)
{
LabelAppointment.Text = "No";
}
if (RadioMedicationYes.Checked)
{
LabelAppointment.Text = "Yes";
}
else if (RadioMedicationNo.Checked)
{
LabelAppointment.Text = "No";
}
SqlCommand cmd = new SqlCommand("insert into sicknesstest values('"+txtname.Text+"','"+txtgrade.Text+"','"+txtdepot.Text+"','"+txtillness.Text+"','"+LabelAppointment.Text+"','"+LabelMedication.Text+"','"+txtabsence.Text+"','"+txtcontactby.Text+"','"+txtupdate.Text+"','"+txtdetails.Text+"','"+txtresumedate.Text+"','"+txtdetail.Text+"','"+txtmedonreturn.Text+"','"+txtreporter.Text+"','"+txtdateofcontact.Text+"','"+txtresumeddate.Text+"')", con);
cmd.ExecuteNonQuery();
con.Close();
Label1.Visible = true;
Label1.Text = "Your Data has been stored successfully! Click here to view";
txtname.Text = "";
txtgrade.Text = "";
txtdepot.Text = "";
txtillness.Text = "";
txtabsence.Text = "";
txtcontactby.Text = "";
txtupdate.Text = "";
txtdetails.Text = "";
txtresumedate.Text = "";
txtdetail.Text = "";
txtmedonreturn.Text = "";
txtreporter.Text = "";
txtdateofcontact.Text = "";
txtresumeddate.Text = "";
}
}
}
-- Datesource:
WebConfig :
The database runs locally on my machine as a 'test bay' and works find as far as I can see.
<add name="ConnectionStringTestFormSubmission" connectionString="Data Source=G00069-PC\SQLEXPRESS;Initial Catalog=SicknessDatabase;Persist Security Info=True;User ID=REMOVED;Password=REMOVED"
providerName="System.Data.SqlClient" />
