如何使用C#.NET在SQL SERVER 2008中插入日期 [英] how to insert date in SQL SERVER 2008 using C#.NET

问题描述

下午好

我正在使用C#.NET和SQL Server 2008制作桌面应用程序.......我有一种形式，其中我使用了一个标签名称日期，并使用了一个工具箱日期时间选择器.还使用了SQL数据库中的字段名称日期. ......... nw我想使用datetimepicker插入date throgh表单，并在数据库中插入get .......

使用代码

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

问题:它无法转换日期时间选择器错误
发生..... Convert.ToDateTime(dateTimePicker1.Text)
Operand Ttype clash:int与日期不兼容
请为此提供解决方案

SQL数据库

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

推荐答案

您最好的选择是使用经过参数化的查询，诸如此类

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

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.

DateTimePicker中有一个名为Value的属性.使用它而不是将文本转换为DateTime.该值直接返回一个包含DateTimePicker值的DateTime对象.另外，使用SqlParameter通过SQL发送数据.

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.

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 */ }
                }
            }

如果发生SQL异常，说明某些参数的格式不正确，请将其强制转换为相应的数据类型，例如(int)cbocampaign.Text;

注意:这就是您真正使用这些SqlConnection和SqlCommand对象的方式.希望您能从中学到一些东西.另外，您应该真正以一种可读的方式开始命名控件:)

希望对您有所帮助:)问候

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

最好的解决方案是使用SQLParameter
但是，如果您不想使用SQLParameter来执行此操作，并且想要连接起来，则无法以这种方式进行操作

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

这篇关于如何使用C#.NET在SQL SERVER 2008中插入日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！