Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.0 .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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
Comments
Kim Togo at 29-Apr-11 3:30am
   
My 5. The correct way.
Wayne Gaylard at 29-Apr-11 3:40am
   
Thanks:smile:
Member 7882565 at 29-Apr-11 6:01am
   
thank you sir it helped me a lot........
Member 7882565 at 29-Apr-11 7:55am
   
i have created a form in which there are 4 labels teacher id ,teacher name and department and description along with respective textboxes.i have inserted the values in the database through ADD button (using insert query).I have taken datagrid view to display the data .The problem is now i want that one i select one row of datagridview then it should display in respective text boxes. i have tried following code but not able to get desired write properly.
 
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=SW-PC-20;Integrated security =SSPI;Initial catalog=institute");
con.Open();
SqlCommand com = new SqlCommand("select * from teacher2", con);
SqlDataReader dr = com.ExecuteReader();
DataTable dt = new DataTable();

dt.Load(dr);
dr.Close();
dataGridView1.DataSource = dt;

if (dr.HasRows)

dr.Read();
txtteacherid.Text = dr[0].ToString();
txtteachername.Text = dr[1].ToString();
txtdepartment.Text = dr[2].ToString();
txtdescription.Text = dr[3].ToString();
}
please help
yesotaso at 6-May-11 5:55am
   
This question seems [Solved] I suggest Submit new question with new problem.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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 */ }
                }
            }
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 Smile | :)
 
Hope this helps Smile | :) Regards
  Permalink  
Comments
PasanRatnayake at 29-Apr-11 3:25am
   
Also, you should check whether the return value of ExecuteNonQuery
 
if (affectedRows == 1)
MessageBox.Show("Successful");
else
MessageBox.Show("Failed");
 
In case of an error also, the same :)
Kim Togo at 29-Apr-11 3:32am
   
My 5 for SqlParameter use!
PasanRatnayake at 29-Apr-11 3:36am
   
Thank you :)
Member 7882565 at 29-Apr-11 6:01am
   
thank you sir it helped me a lot........
Member 7882565 at 29-Apr-11 7:57am
   
i have created a form in which there are 4 labels teacher id ,teacher name and department and description along with respective textboxes.i have inserted the values in the database through ADD button (using insert query).I have taken datagrid view to display the data .The problem is now i want that one i select one row of datagridview then it should display in respective text boxes. i have tried following code but not able to get desired write properly.
 
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=SW-PC-20;Integrated security =SSPI;Initial catalog=institute");
con.Open();
SqlCommand com = new SqlCommand("select * from teacher2", con);
SqlDataReader dr = com.ExecuteReader();
DataTable dt = new DataTable();

dt.Load(dr);
dr.Close();
dataGridView1.DataSource = dt;

if (dr.HasRows)

dr.Read();
txtteacherid.Text = dr[0].ToString();
txtteachername.Text = dr[1].ToString();
txtdepartment.Text = dr[2].ToString();
txtdescription.Text = dr[3].ToString();
}
please help
yesotaso at 6-May-11 5:55am
   
This question seems [Solved] I suggest Submit new question with new problem.
srigates at 7-Mar-13 4:19am
   
i tried to insert string date value into datetime datatype , but it throw err..
please suggest any idea
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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")
  Permalink  
Comments
Kim Togo at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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

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



Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 10 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100