Link to home
Start Free TrialLog in
Avatar of Arnold Layne
Arnold LayneFlag for United States of America

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.SqlException (0x80131904): Incorrect syntax near '2'. at System.Data.SqlClient.SqlConnection....................in c:\inetpub\wwwroot\add.aspx.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.


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>");
                }
            }
        }
    }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Arnold Layne

ASKER

Thank you Walter!