Arnold Layne
asked on
SQL query in ASP.NET page fails.
I think I am misunderstanding how certain datatypes are handled in SQL operations in C#, because the code below generates the following error. "System.Data.SqlClient.Sql Exception (0x80131904): Incorrect syntax near '2'. at System.Data.SqlClient.SqlC onnection. .......... .........i n c:\inetpub\wwwroot\add.asp x.cs:line 33
line 33 is the execution of the SQLCommand
myInsert.ExecuteNonQuery() ;
Seems like it is failing from a wrong data type trying to be added to the database. userID and companyID are session variables, but they are values that will be added to int fields in the DB. That could be the reason for the failure or it's the datetime trying to be inserted. The code used to work until I added an insert for "Created" (datetime) "CreatedBy" (session variable to be added to field of type int of DB) and companyID (session variable to be added to field of type int of DB). So I know exactly where the error is, but I'm not sure what exactly is causing it.
line 33 is the execution of the SQLCommand
myInsert.ExecuteNonQuery()
Seems like it is failing from a wrong data type trying to be added to the database. userID and companyID are session variables, but they are values that will be added to int fields in the DB. That could be the reason for the failure or it's the datetime trying to be inserted. The code used to work until I added an insert for "Created" (datetime) "CreatedBy" (session variable to be added to field of type int of DB) and companyID (session variable to be added to field of type int of DB). So I know exactly where the error is, but I'm not sure what exactly is causing it.
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;
public partial class add : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int userID = (int.Parse(Session["userID"].ToString()));
int companyID = (int.Parse(Session["companyID"].ToString()));
if (userID !=null && companyID != null)
{
String phone = Request["PhoneNumber"];
String address = Request["Address"];
String dollarAmt = Request["DollarAmt"];
if (address == null) { address = ""; }
decimal dollars = 0;
if (dollarAmt != null) {dollars = decimal.Parse(dollarAmt);}
if (phone != null)
{
try {
SqlConnection myConnection = new SqlConnection("server=localhost;" +
"Trusted_Connection=yes;" +
"database=mydb;" +
"connection timeout=30");
myConnection.Open();
try {
SqlCommand myInsert = new SqlCommand("INSERT INTO Temp (PhoneNumber, Address, DollarAmt, Created, CreatedBy, DispatcherID) Values ('" + phone + "', '" + address + "', '" + dollars + "', '" + DateTime.Now + "', '" + userID + "', '" + companyID + "'", myConnection);
myInsert.ExecuteNonQuery();
}
catch (Exception err)
{
Response.Write("<p>" + err + "</p>");
myConnection.Close();
}
}
catch (Exception err)
{
Response.Write("<p>" + err + "</p>");
}
}
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER