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

LVL 9
BobHavertyComhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Try this:
SqlCommand myInsert = new SqlCommand("INSERT INTO Temp (PhoneNumber, Address, DollarAmt, Created, CreatedBy, DispatcherID) Values ('" + phone + "', '" + address + "', '" + dollars + "', '" +  DateTime.Now + "', '" + userID + "', '" + companyID + ''')", myConnection);

Open in new window

I think you are just missing to close the parentesis for the values clause.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobHavertyComhAuthor Commented:
Thank you Walter!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.