inserting textbox values into database

Question

im a newbie here and would like some advice on C# programming

i would like to store values from a textbox into a database. so far, i have the following:

string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Customers.mdf;Integrated Security=True;User Instance=True";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

string query = "INSERT INTO ProjectList (ProjectName, BiddingDueDate, Status, ProjectStartDate, ProjectEndDate, AssignedTo, PointsWorth, StaffCredits) VALUES ('"+projName+"', '"+bidDueDate+"', '"+status+"', '"+projectStartDate+"', '"+projectEndDate+"', '"+assignedTo+"', '"+pointsWorth+"', '"+aStaffCredits+"')";
SqlCommand command = new SqlCommand(query, connection);

command.ExecuteNonQuery();
connection.Close();

There are no errors in the code, but i cannot seem to figure out why nothing is being stored in the database.

Solution 1

First, your code is ripe for SQL Injection attacks - you really should be using parameterized queries.

Also, if you use parameters, you can have some type safety and the values will be translated correctly to SQL Server.

It is difficult to tell what is wrong here, since the values you are concatenating are unknown to us (for instance, what does bidDueDate look like?, What does thisQuery look like before you execute it?).

I would normally write this as a stored procedure taking the parameters you need for inserting a record, in my C# I would create the command object add the correct parameters (and types) to it.

See the example on this MSDN page (SqlCommand.Parameters).

Solution 2

At least your code should look like this:

void SaveData(string projectName, DateTime biddingDueDate, string status, DateTime projectStartDate, string assignedTo, int pointsWorth, string staffCredits)
{
    try
    {
        string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Customers.mdf;Integrated Security=True;User Instance=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO ProjectList (ProjectName, BiddingDueDate, Status, ProjectStartDate, ProjectEndDate, AssignedTo, PointsWorth, StaffCredits) VALUES (@projectName, @biddingDueDate, @status, @projectStartDate, @projectStartDate, @assignedTo, @pointsWorth, @staffCredits)";

            command.Parameters.AddWithValue("@projectName", projectName);
            command.Parameters.AddWithValue("@biddingDueDate", biddingDueDate);
            command.Parameters.AddWithValue("@status", status);
            command.Parameters.AddWithValue("@projectStartDate", projectStartDate);
            command.Parameters.AddWithValue("@assignedTo", assignedTo);
            command.Parameters.AddWithValue("@pointsWorth", pointsWorth);
            command.Parameters.AddWithValue("@staffCredits", staffCredits);

            connection.Open();
            command.ExecuteNonQuery();
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine(ex.Message);
    }

}

Parameter's type can be determined (tried to be) automatically:

command.Parameters.AddWithValue("@biddingDueDate", biddingDueDate);

or specified manually:

command.Parameters.Add("@biddingDueDate", System.Data.SqlDbType.DateTime).Value = biddingDueDate;

also you can convert date to string with specified format to minimize the risk of mistaken parsing (because of culture dependent specificity, etc) on database side:

command.Parameters.Add("@biddingDueDate", System.Data.SqlDbType.DateTime).Value = biddingDueDate.ToString("yyyy-MM-dd"); // also you can use just yyyyMMdd

Comments

[Nikos Steiakakis]

+1 for sql injection from me as well. Since jill claims to be a newbie, I think it's best that new devs should learn about security from the really early stages.

[Oded]

@Conspicuous Compiler - if you mention Bobby, at least provide a link...

[abatishchev]

Always use sql parameters aka parametrized queries

[Admin]

thanks for showing me how the code should look like! another question though, is the line: command.Parameters.AddWithValue("@biddingDueDate", biddingDueDate); only if the biddingDueDate is a string?

[abatishchev]

@jill: btw, you can use tildes to highlight syntax into comments: var hello = "world!";

[Christian M]

I know... but this was just to help him out and get on with his problem :)