question

Chama avatar image
Chama asked

RaddatePicker including sqlStament

This is my statement... I use calendar that is RDPEndDate , i want to make option if user select calendar selectdate but if null insert null...I did like that but both give me null....also only go to first stamentent.. Rdpendate1 that is raddatepicker doesnt have the properties selectvalue or lenght...I dodnt know how fix this.. Someone give some advice to do this.. Thanks have a good day if (this.RDPEndDate1.SelectedDate == null) { //String EndDate1 = RDPEndDate1.SelectedDate.ToString(); string sql = "Insert into Con_Number(" + "Conn_No,Conn_Status_Code,Conn_Dateavailable,Conn_Time,Conn_Signature," + "Conn_Remarks,Conn_Datereceived,InputDate,InputTime,DepotID)" + "values (" + "@Conn_No,@Conn_Status_Code,@Conn_Dateavailable,@Conn_Time,@Conn_Signature," + "@Conn_Remarks,@Conn_Datereceived ,Getdate(),@InputTime,@DepotID)"; cmd = new SqlCommand(sql, con); cmd.Parameters.AddWithValue("@Conn_No", txtConnNo1.Text.Trim()); cmd.Parameters.AddWithValue("@Conn_Status_Code", ddlStatus1.SelectedValue); cmd.Parameters.AddWithValue("@Conn_Dateavailable"RDPStartDate1.SelectedDate.ToString()); cmd.Parameters.AddWithValue("@Conn_Time", RMTTime1.Text); cmd.Parameters.AddWithValue("@Conn_Signature", txtSignature1.Text.Trim()); cmd.Parameters.AddWithValue("@Conn_Remarks", txtRemarks1.Text.Trim()); cmd.Parameters.AddWithValue("@Conn_Datereceived", RDPEndDate1.SelectedDate.ToString()); cmd.Parameters.AddWithValue("@InputTime", lblTime.Text.Trim()); cmd.Parameters.AddWithValue("@DepotID", dldDepot.SelectedValue); cmd.ExecuteNonQuery();
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
This seems to be question related to the Component you use. But from what I can see on the first look is the use of `cmd.Parameters.AddWithValue("@Conn_Datereceived", RDPEndDate1.SelectedDate.ToString())` Why you pass the parameters converting to string? It should be enought to pass the DateTime paramater if the destination field is DateTime. `cmd.Parameters.AddWithValue("@Conn_Datereceived", RDPEndDate1.SelectedDate)` The same applies for all DateTime parameters.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Pavel Pawlowski This is very true, it is totally bad to call ToString() when the datetime is expected. The problem in the code was that the check for null branched into the if block and in the same block the variable is specified and attempted to be set. Since it is null, the inserted value is null. However, when the code branches to else block (SelectedDate is not null) then the block does not even include the variable and the parameter :) I think that my snippet will address the problem.
0 Likes 0 ·
Oleg avatar image
Oleg answered
I believe that when there is no value in the date picker then SelectedDate maybe null or may be DateTime.MinValue depending on whether ther user ever clicked the control or just never touched it. However, lets assume that SelectedDate == null is a sufficient check. There is a problem in your code because when you check for null and branch into the if block then you try to assign a variable value but this is null, so you get null inserted in any case. Additionally, as you can see, your code is almost indentical in your if and else parts, so it would be better if you only use the one from your if block with one slight modification. Here is the sample: string sql = "Insert into Con_Number(" + "Conn_No,Conn_Status_Code,Conn_Dateavailable,Conn_Time,Conn_Signature," + "Conn_Remarks,Conn_Datereceived,InputDate,InputTime,DepotID)" + "values (" + "@Conn_No,@Conn_Status_Code,@Conn_Dateavailable,@Conn_Time,@Conn_Signature," + "@Conn_Remarks,@Conn_Datereceived ,Getdate(),@InputTime,@DepotID)"; cmd = new SqlCommand(sql, con); cmd.Parameters.AddWithValue("@Conn_No", txtConnNo1.Text.Trim()); cmd.Parameters.AddWithValue("@Conn_Status_Code", ddlStatus1.SelectedValue); cmd.Parameters.AddWithValue("@Conn_Dateavailable", RDPStartDate1.SelectedDate); cmd.Parameters.AddWithValue("@Conn_Time", RMTTime1.Text); cmd.Parameters.AddWithValue("@Conn_Signature", txtSignature1.Text.Trim()); cmd.Parameters.AddWithValue("@Conn_Remarks", txtRemarks1.Text.Trim()); // just do the check here and set the value accordingly: if (this.RDPEndDate1.SelectedDate == null) { cmd.Parameters.AddWithValue("@Conn_Datereceived", DBNull.Value); } else { cmd.Parameters.AddWithValue("@Conn_Datereceived", this.RDPEndDate1.SelectedDate); } cmd.Parameters.AddWithValue("@InputTime", lblTime.Text.Trim()); cmd.Parameters.AddWithValue("@DepotID", dldDepot.SelectedValue); cmd.ExecuteNonQuery();
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Chama Place a breakpoint on the line just before that if statement and see what do you get from reading **this.RDPEndDate1.SelectedDate** This is what I tried to warn about in my answer: if the value is not selected by the user and the picker is blank then it might mean that SelectedDate is null but might also mean that SelectedDate is by default set to something which is not null but is nevertheless out of the SQL datetime range. For example, **this.RDPEndDate1.SelectedDate** could be set to DateTime.MinValue which is January 1st 0001. This value is out of range and will give you a problem. If this is the case then you can change your IF condition to something like this instead:
if (this.RDPEndDate1.SelectedDate == null || 
    this.RDPEndDate1.SelectedDate == DateTime.MinValue)
{
    cmd.Parameters.AddWithValue(
        "@Conn_Datereceived", DBNull.Value);
}
else 
{
    cmd.Parameters.AddWithValue(
        "@Conn_Datereceived", this.RDPEndDate1.SelectedDate);
}
1 Like 1 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
@oleg, I think the main problem will the in the `RDPEndDate1.SelectedDate.ToString()`. The `ToString()` method will conver the DateTime by default to the national format defined in the national settings. And if it is a non standart format accepted by SQL server than it can have consequences. Also if the value will be null the `RDPEndDate1.SelectedDate.ToString()` will fail. You have removed it from the RDPEndDate1, but the same problem can be with RDPStartDate1. But when used without the `ToString()` method, it should be enought to pass the `SelectedDate` property as by default it should be converted to the `DBNull.Value` if the `SelectedDate` property is null.
0 Likes 0 ·
Chama avatar image Chama commented ·
No,it is becuase StarDate is required , if it mean the user always have to select something... when i did... when i dont select any for endDate, it is going to second stametrighway... If give this error SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. if (this.RDPEndDate1.SelectedDate == null) { cmd.Parameters.AddWithValue("@Conn_Datereceived", DBNull.Value); } else { cmd.Parameters.AddWithValue("@Conn_Datereceived", this.RDPEndDate1.SelectedDate); }
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Pavel Pawlowski There is no **.SelectedDate.ToString()** in my code. It is in the code in question but of course I removed it from the query in my answer. There is no way I would ever use the ToString() when I need to pass a datetime parameter and I already have a control exposing the property as DateTime struct. As far as your comment about not even bothering with check that I placed in the code, I believe that you are correct, but there is nothing wrong with the check just for sanity check. True that according to the [Telerik help page][1], the **SelectedDate** is authored as
public virtual Nullable
 
   SelectedDate {get; set;}
 
However, since I do not have a control handy, and cannot guarantee that when **SelectedDate** is null then **AddWithValue** is going to be able to safely resolve it to **DBNull.Value**, I think that it is OK to do so explicitly just in case. [1]: http://www.sitefinity.com/help/developer-manual/radcalendar.net2-telerik.webcontrols.raddatepicker-selecteddate.html
0 Likes 0 ·
Chama avatar image Chama commented ·
Thanks you for all your answer.. So great..Although still stuck in this...
0 Likes 0 ·
Show more comments
Chama avatar image
Chama answered
Thanks for all your answer...Have great day :)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.