how to insert date in SQL SERVER 2008 using C#.NET

Question

3.50/5 (2 votes)

See more:

.NET3.5

Good Afternoon

I am making desktop application using C#.NET and SQL Server 2008.......i have one form in which i took one label name date and had a toolbox datetime picker.and also took field name date in SQL database...........nw i want to insert the date throgh form using datetimepicker and get get inserted in datatbase.......

using code

SqlConnection con = new SqlConnection("Data Source=SW-PC-20;Integrated security =SSPI;Initial catalog=PSM");
con.Open();
SqlCommand cmd = new SqlCommand("insert into Campaign(Campaign_id,List_of_thresholds,Duration,Starting_Date,Ending_Date,Total_Budget_of_all_thresholds) values (" + Convert.ToInt32(cbocampaign.Text) + ",'" + cbolist_threshold.Text + "','" + txtduration.Text + " '," + this.DateTimePicker1.Value.ToString("dd/MM/yyyy") + "," + this.DateTimePicker2.Value.ToString("dd/MM/yyyy") + "," + Convert.ToInt32(txtbudget.Text) + ")", con);
            
 cmd.ExecuteNonQuery();
  MessageBox.Show("Insertion successfully done");

PROBLEM :it is not able to convert datetime picker error
occuring .....Convert.ToDateTime(dateTimePicker1.Text)
Operand Ttype clash:int is incompatible with date
Please give me solution for this

SQL DATABASE

create table PSM.dbo.Campaign(Campaign_id int primary key,List_of_thresholds varchar(50),Starting_Date date,Ending_Date date ,Duration varchar(50),Total_Budget_of_all_thresholds int);

I havent inserted any value from database and want to insert through form only

Posted 28-Apr-11 21:02pm

shivani 2013

Add a Solution

5 solutions

Solution 4

Well the best solution is to use SQLParameter
but if you want to do it without using SQLParameter and want to concatenate u cant do it in this way

SqlConnection con = new SqlConnection("Data Source=SW-PC-20;Integrated security =SSPI;Initial catalog=PSM");
con.Open();
SqlCommand cmd = new SqlCommand("insert into Campaign(Campaign_id,List_of_thresholds,Duration,Starting_Date,Ending_Date,Total_Budget_of_all_thresholds) values (" + Convert.ToInt32(cbocampaign.Text) + ",'" + cbolist_threshold.Text + "','" + txtduration.Text + " ','" + this.DateTimePicker1.Value.ToString() + "','" + this.DateTimePicker2.Value.ToString() + "'," + Convert.ToInt32(txtbudget.Text) + ")", con);
            
 cmd.ExecuteNonQuery();
  MessageBox.Show("Insertion successfully done");

you might think what is the diff the diff is that i have passed the date in Single Quotes

Posted 5-May-11 23:03pm

Steven.Pinto2000

Solution 1

Could be two proble
try both
1. Dont convert the dates into "dd/MM/yyyy", convert them into "MM/dd/yyyy" and then save.
e.g. this.DateTimePicker1.Value.ToString("MM/dd/yyyy")

2. Second approach may be. Try like that

Convert.ToDateTime(this.DateTimePicker1.Value).ToString("MM/dd/yyyy")

Posted 28-Apr-11 21:14pm

nit_singh

Comments

Kim Togo 29-Apr-11 3:30am

No, do not use this.

If you really what to use .ToString on a DateTime and Sql. Then use .ToString("s")
"s" = Gets the format pattern for a sortable date and time value, "yyyy'-'MM'-'dd'T'HH':'mm':'ss" and it is always the same, regardless of the culture used or the format provider supplied.

SqlParameter is the best way.

nit_singh 29-Apr-11 3:58am

Yes you are right Kim, but converting into 'MM/dd/yyyy' will also work, sometime we dont need to save HH, mm, and ss.

Solution 5

this.DateTimePicker1.Value.ToString("MM/dd/yyyy")

Posted 10-Oct-12 15:19pm

bnd2277

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wayne Gaylard · Accepted Answer · 2011-04-28T21:18:00

Your best bet would be to use a parametrized query something like this

C#

SqlCommand cmd = new SqlCommand("INSERT INTO" + 
                                                 " Campaign(Campaign_id,List_of_thresholds,Duration,Starting_Date,Ending_Date,Total_Budget_of_all_thresholds)" + 
                                           " VALUES" + 
                                                " (@Campaign_id,@List_of_thresholds,@Duration,@Starting_Date,@Ending_Date,@Total_Budget_of_all_thresholds)",con);               
            cmd.Parameters.Add(new SqlParameter("@Campaign_id", Convert.ToInt32(cbocampaign.Text)));
            cmd.Parameters.Add(new SqlParameter("@List_of_thresholds", cbolist_threshold.Text));
            cmd.Parameters.Add(new SqlParameter("@Duration", txtduration.Text));
            cmd.Parameters.Add(new SqlParameter("@Starting_Date", this.DateTimePicker1.Value));
            cmd.Parameters.Add(new SqlParameter("@Ending_Date", this.DateTimePicker2.Value));
            cmd.Parameters.Add(new SqlParameter("@Total_Budget_of_all_thresholds", Convert.ToInt32(txtbudget.Text)));

This would solve all your data conversion problems, as well as preventing any injection attacks.

CodeHawkz · Accepted Answer · 2011-04-28T21:21:00

There is a property named Value in the DateTimePicker. Use that instead of converting the text to DateTime. The value directly returns a DateTime object containing the value of the DateTimePicker. Also, use SqlParameter to send the data with the SQL.

C#

using (SqlConnection connection = new SqlConnection())
            {
                try
                {
                    connection.ConnectionString = "Data Source=SW-PC-20;Integrated security =SSPI;Initial catalog=PSM";

                    // This creates an object with which you can execute sql
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO Campaign(Campaign_id,List_of_thresholds,Duration,Starting_Date,Ending_Date,Total_Budget_of_all_thresholds) VALUES(@Campaign_id,@List_of_thresholds,@Duration,@Starting_Date,@Ending_Date,@Total_Budget_of_all_thresholds)";
                        command.CommandType = CommandType.Text;

                        // This is how you add a parameter to your sql command
                        // This way you are protected against SQL injection attacks
                        SqlParameter campainIdParameter = command.CreateParameter();
                        campainIdParameter.ParameterName = "@Campaign_id";
                        campainIdParameter.Value = this.cbocampaign.Text;
                        command.Parameters.Add(campainIdParameter);

                        SqlParameter listOfThresholdsParameter = command.CreateParameter();
                        listOfThresholdsParameter.ParameterName = "@List_of_thresholds";
                        listOfThresholdsParameter.Value = this.cbolist_threshold.Text;
                        command.Parameters.Add(listOfThresholdsParameter);

                        SqlParameter durationParameter = command.CreateParameter();
                        durationParameter.ParameterName = "@Duration";
                        durationParameter.Value = this.txtduration.Text;
                        command.Parameters.Add(durationParameter);

                        SqlParameter startingDateParameter = command.CreateParameter();
                        startingDateParameter.ParameterName = "@Starting_Date";
                        startingDateParameter.Value = this.DateTimePicker1.Value;
                        command.Parameters.Add(startingDateParameter);

                        SqlParameter endingDateParameter = command.CreateParameter();
                        endingDateParameter.ParameterName = "@Ending_Date";
                        endingDateParameter.Value = this.DateTimePicker2.Value;
                        command.Parameters.Add(endingDateParameter);

                        SqlParameter totalBudgetParameter = command.CreateParameter();
                        totalBudgetParameter.ParameterName = "@Total_Budget_of_all_thresholds";
                        totalBudgetParameter.Value = this.txtbudget.Text;
                        command.Parameters.Add(totalBudgetParameter);

                        connection.Open();

                        int affectedRows = command.ExecuteNonQuery();
                    }
                }
                finally
                {
                    if ((connection != null) && (connection.State == ConnectionState.Open))
                        try { connection.Close(); }
                        catch { /* Do nothing */ }
                }
            }

If a sql exception occurs saying some parameter is not in correct format, just cast it to the respective data type like (int)cbocampaign.Text;

NOTE: This is how you really use these SqlConnection and SqlCommand objects. Hope you would learn something out of this. Also, you should really start naming your controls in a readable manner :)

Hope this helps :) Regards