none
Type of Parameters generated by TableAdapter Query Configuration Wizard RRS feed

  • Question

  • My table includes a column of type DATETIME2. All generated standard command like SELECT or INSERT of the corresponding TableAdapter include a parameter of type System.Datetime. But when I add a new query using the Query Configuration Wizard that includes a WHERE clause for the date, the generated parameter is of type string and not of DateTime as I expected.

    This is really strange for me because even in the InitCommandCollection method in MyDataSet.Designer.cs the parameters for my query are added with SqlDbType.DateTime2.

    Is there anything I am missing? Is there a way to control the parameter type?

    Thanks
    Hans-Peter

    Wednesday, January 13, 2010 7:31 PM

Answers

  • Hello Hans,

     

    Welcome to ADO.NET DataSet forum!

     

    Thank you for your posting!  Based on your description, I tried to reproduce the problem at my lab.  Here are my steps:

     

    1.      Create a table named DateTime2Test in the database, here is its structure:

    ColumnName

    Type

    Nuallable

    ID

    Int

    N

    Name

    nvarchar(50)

    Y

    Date2Value

    datetime2

    Y

    DateValue

    datetime

    Y

     

    2.      Add a strongly typed DateSet that contains the DateTime2Test table.

     

    3.      In the DataSet designer, right click the DateTime2TestTableAdapter and select “Add Query…”.

     

    4.      In the “TableAdapter Query Configuration Wizard” à “Query Builder”, when I enter a string value of a date format on the Filter of the Date2Value column, as you said the parameter of the datetime2 column is by default passed as string.  However, the parameter of the datetime typed column is working fine, the Query Builder will automatically use CONVERT(DATETIME, '2008-01-01 00:00:00', 102). 

     

    Is this the problem that you reported?   If there is any misunderstanding here, please be free to point out. 

     

    Since the datetime2 type is newly introduced in SQL Server 2008.   The Query Configuration seems not handle the new types correctly here.  Besides, I think the workaround could be using the CONVERT method on datetime2 typed column as well.   We can manually use such a value in the Filter of Date2Value column in the above sample: CONVERT(DATETIME2, '2008-01-01 00:00:00', 102).   I have tested it and it works fine at my side.   Could this method solve the problem at your side?  

     

    If you have any questions, please feel free to let me know.

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, January 14, 2010 3:40 AM
    Moderator

All replies

  • Hello Hans,

     

    Welcome to ADO.NET DataSet forum!

     

    Thank you for your posting!  Based on your description, I tried to reproduce the problem at my lab.  Here are my steps:

     

    1.      Create a table named DateTime2Test in the database, here is its structure:

    ColumnName

    Type

    Nuallable

    ID

    Int

    N

    Name

    nvarchar(50)

    Y

    Date2Value

    datetime2

    Y

    DateValue

    datetime

    Y

     

    2.      Add a strongly typed DateSet that contains the DateTime2Test table.

     

    3.      In the DataSet designer, right click the DateTime2TestTableAdapter and select “Add Query…”.

     

    4.      In the “TableAdapter Query Configuration Wizard” à “Query Builder”, when I enter a string value of a date format on the Filter of the Date2Value column, as you said the parameter of the datetime2 column is by default passed as string.  However, the parameter of the datetime typed column is working fine, the Query Builder will automatically use CONVERT(DATETIME, '2008-01-01 00:00:00', 102). 

     

    Is this the problem that you reported?   If there is any misunderstanding here, please be free to point out. 

     

    Since the datetime2 type is newly introduced in SQL Server 2008.   The Query Configuration seems not handle the new types correctly here.  Besides, I think the workaround could be using the CONVERT method on datetime2 typed column as well.   We can manually use such a value in the Filter of Date2Value column in the above sample: CONVERT(DATETIME2, '2008-01-01 00:00:00', 102).   I have tested it and it works fine at my side.   Could this method solve the problem at your side?  

     

    If you have any questions, please feel free to let me know.

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, January 14, 2010 3:40 AM
    Moderator
  • Hello Hans,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, January 18, 2010 2:49 AM
    Moderator
  • Hi,
    thank you for your reply. It answers my question if i am missing something. I definitely want to use System.Datetime in my application so I have to convert this date to string when calling the adapter method.

    Thanks
    Hans-Peter
    Wednesday, January 20, 2010 6:54 AM
  • You are welcome, Hans!

    Have a great day!


    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, January 20, 2010 7:03 AM
    Moderator
  • I am facing with the same issue with DateTime data type in SqlServer2008 via VisualStudio2012,

    In the complete discussion above I am unable to understand what has to be done to get the solution.

    can you please elaborate it more deeper.

    My problem is the function of the TableAdapter that created after to Querybuilder configuration takes only string type parameters even at the place of DateTime.

    Sunday, March 16, 2014 5:28 PM
  • Got the Solution just by re entering the data in the format as follows: mm-dd-yyyy.
    Sunday, March 16, 2014 6:43 PM
  • Got the Solution just by re entering the data in the format as follows: mm-dd-yyyy.

    If you are not living in the USA then that is for sure the wrong way (and inside that probably also) 

    However, this thread is 4 years old, nobody is watching it anymore. If you have a problem then create your own question. You are not charged for ti.


    Success
    Cor

    Tuesday, March 18, 2014 10:42 AM