Issues Inserting into SQL Database from ASP.NET C#

See more:

C#

ASP.NET

SQL

SQL-Server

I have a lot more columns than what I have here for you, but I'm just trying to get to post the primary key column TrackingID (nchar[10]) as a test - if I can get that to work, I can get the rest of them to work. I have a windows forms application that makes a similar query and runs perfectly, so seeing as how it's both C# I'm having a hard time understanding what's going wrong. Is it the connection string or the sql query? The table name is Full. There are 23 columns total, but the only primary key is Tracking ID which cannot be null. In all instances, these queries are a OnClick button event. As you will be able to see I have tried several routes. I am hoping to set this up to be parameterized to prevent SQL Injections. The first bit of code you will see is from a Windows Forms Application in C# that (while is vulnerable) works consistently. Below, you will see the asp.net code.

private void licensesubmitbutton_Click(object sender, EventArgs e)
    {
        try
        {
            System.Data.SqlClient.SqlConnection sqlConnection1 =
    new System.Data.SqlClient.SqlConnection("Data Source=data99;Initial Catalog=LicenseInventoryMgr;Integrated Security=True;Connect Timeout=0;Trusted_Connection=Yes");

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT LIMTable (Software,Host,AssetTag,ActivationDate,LicenseNumber) VALUES ('" + softwarecombobox.Text + "','" + hostnamebox.Text + "','" + assettagcombobox.Text + "','" + dateactivateddatetimepicker.Value.ToString("yyyy-MM-dd") + "','" + licensekeytextbox.Text + "')";
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();
            cmd.ExecuteNonQuery();
            sqlConnection1.Close();
            MessageBox.Show("Successfully Submitted!");
        }
        catch (System.Exception )
        {
            MessageBox.Show("Submission unsuccessful. Try saving and refreshing first!");
        }
    }

And here is the ASP.NET code I've tried.

protected void submit_Click(object sender, EventArgs e)
   {
       try
       {
           SqlConnection conn = new SqlConnection(@"Data Source=data99;Initial Catalog=LFM_Archive;Integrated Security=True");
           string sql = "INSERT INTO Full (TrackingID) values (@TrackingID)";
           conn.Open();
           SqlCommand cmd = new SqlCommand(sql, conn);
           cmd.Parameters.Add("@TrackingID", SqlDbType.NChar);
           cmd.Parameters["@TrackingID"].Value = 6789;
           cmd.ExecuteNonQuery();
           conn.Close();

           //System.Data.SqlClient.SqlConnection sqlConnection1 =
           //    new System.Data.SqlClient.SqlConnection("Data Source=data99;Initial Catalog=LFM_Archive;Integrated Security=True;Connect Timeout=0;Trusted_Connection=Yes");
           //System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
           //cmd.CommandType = System.Data.CommandType.Text;
           //cmd.CommandText = "INSERT INTO LFM_Archive (TrackingID) VALUES ('" + empfirst.Text + "')";
           //cmd.Connection = sqlConnection1;

           //sqlConnection1.Open();
           //cmd.ExecuteNonQuery();
           //sqlConnection1.Close();

           //SqlConnection conn = new SqlConnection("Data Source=data99;Initial Catalog=LFM_Archive;Integrated Security=True;Connect Timeout=0;Trusted_Connection=Yes");
           //SqlCommand cmd = new SqlCommand("INSERT INTO Full (TrackingID) VALUES (12345)");
           ////cmd.CommandType = CommandType.StoredProcedure;
           ////    cmd.Parameters.Add("@trackingid)", SqlDbType.NChar, 10);
           ////    cmd.Connection.Open();
           ////    cmd.ExecuteNonQuery();
           ////    cmd.Connection.Close();

           //("sender","reciever" html can be enabled for body.)
           MailMessage m1 = new MailMessage("y@x.net", "x@x.net");
           m1.Subject = "SQL Query Success";
           m1.Body = "SQL Query Success :)";
           m1.IsBodyHtml = true;
           SmtpClient smtp = new SmtpClient();
           smtp.Host = "mailserver99";
           smtp.EnableSsl = false;
           //System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();
           //NetworkCred.UserName = "username";
           //NetworkCred.Password = "password";
           smtp.UseDefaultCredentials = true;
           //smtp.Credentials = NetworkCred;
           smtp.Port = 25;
           smtp.Send(m1);

       }
       catch
       {
           MailMessage m2 = new MailMessage("y@x.net", "x@x.net");
           m2.Subject = "SQL Query Failed";
           m2.Body = "SQL Query Failed :(";
           m2.IsBodyHtml = false;
           SmtpClient smtp = new SmtpClient();
           smtp.Host = "mailserver99";
           smtp.EnableSsl = false;
           smtp.UseDefaultCredentials = true;
           smtp.Port = 25;
           smtp.Send(m2);
       }
   }

The end result displays query success or failure in the form window that this pulls from, and/or sends an email. When I no longer have to test it, the emails will be simple notifications that the form has been posted to the archive. Commented code in the asp.net application only means that I tried it and when it didn't work, I commented it and tried a new method. At one point I knew it was the connection string because I had called a User Instance in the string, which I was then notified by our Server guy that an error was being generated since User Instance is a SQL-Express ONLY command and does not work on SQL Server 2008 which we have.

**UPDATE** The problem is that this code always sends a failed message and never actually inserts into the db.

**UPDATE** Added better code to the catch so it now tells me about the exception:

Quote:

Incorrect syntax near the keyword 'Full'.

**UPDATE** "Full" is apparently a keyword, so I edited the code to show instead, [Full] so it would literally interpret it. I ran the code through and got a success message, but nothing was inserted into the DB so there was an uncaught exception somewhere in there. I am trying to get it to insert the numbers 6789 as a test into the TrackingID column of the Full table in the LFM_Archive database. If I can get 6789 to post there, then I'm good to go.

**UPDATE** Changing to [Full] was good, I needed to also add single quotations to the column after the values keyword to display as follows: INSERT INTO [Full] (TrackingID) values ('@TrackingID'). However, I then recieved the following exception:

Quote:

String or binary data would be truncated. The statement has been terminated.

**UPDATE** I took the single quotes out of the ('@TrackingID') and that resulted with the following exception:

Quote:

An SqlParameter with ParameterName '@trackingid' is not contained by this SqlParameterCollection.