Why is my tableadaptor forcing a cast to string on my date column

Question

I have a table with dates, and varying data about that date like so.

WKDWKDDATE8 WKDDAYNM    WKDAGENO    WKDDAYNO    WKDDATE6    WEEKNO  WEEKRANGE       AWEEK
2019-05-15  WEDNESDAY   43580       3           AJ0515      384     05/12 - 05/18   NULL
2019-05-16  THURSDAY    43581       4           AJ0516      384     05/12 - 05/18   NULL

and heres what my table looks like

CREATE TABLE [dbo].[WKD](
[WKDDATE8] [date] NOT NULL,
[WKDDAYNM] [varchar](10) NOT NULL,
[WKDAGENO] [int] NULL,
[WKDDAYNO] [int] NULL,
[WKDDATE6] [varchar](6) NOT NULL,
[WEEKNO] [int] NULL,
[WEEKRANGE] [varchar](20) NULL,
[AWEEK] [varchar](1) NULL,

as you can see my WKDDATE8 field is a Date. In my C# application, im using tableadaptors to query this table by WKDDATE8 using SELECT * FROM WKD WHERE WKDDATE8 = @WKDDATE8, but for some reason my table wants a string https://i.stack.imgur.com/CUbkl.jpg .

now, this is where it gets fustrating. I can manually change the designer from string to datetime and run, it works fine, but then i run it again and i get a build error because the query wants a string, HOWEVER, if i let it go as a string, i get the obvious cannot convert from 'System.DateTime' to 'string'.

i am completely dumbfounded and lost as to why my program wants a string.

E: this is the code thats causing me problems. i didnt write this code, it was generated. changing it is pointless as it will change back on the second build, and i can see it casting my column as a string when it really shouldnt

        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
    [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
    [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Select, false)]
    public virtual CACTUSDataSet.WKDDataTable GetDataByWKD8(string WKDDATE8) {
        this.Adapter.SelectCommand = this.CommandCollection[1];
        if ((WKDDATE8 == null)) {
            throw new global::System.ArgumentNullException("WKDDATE8");
        }
        else {
            this.Adapter.SelectCommand.Parameters[0].Value = ((string)(WKDDATE8));
        }
        CACTUSDataSet.WKDDataTable dataTable = new CACTUSDataSet.WKDDataTable();
        this.Adapter.Fill(dataTable);
        return dataTable;
    }

Make sure that your column in your TableAdaptor is set to "DateTime" type. If you are using the visual editor, just click on the column name and there should be a type option in the properties window. — Ben Abraham, Aug 01 '16 at 20:11
Take a look at your `TableAdapter`, in `GetDataByWKD8` in parameters, if the type of `@WKDDATE8` parameter is `NVARCHAR` or `CHAR`, change it to `DateTime`. — Reza Aghaei, Aug 01 '16 at 20:30
@RezaAghaei i had it set to `Date`, figured id change it to `DateTime` as you mention and it seems that was the cause. Does VS treat `Date` as a string? kinda strange if it does. if you want some rep, please repost it as a answer. thanks for your help — highboi, Aug 01 '16 at 20:37
@Multinerd I checked it and it seems the Dataset Designer creates string argument for date parameters. — Reza Aghaei, Aug 01 '16 at 20:45

score 3 · Accepted Answer · edited Nov 08 '16 at 11:41

3

The Dataset Designer creates string argument for date parameters.

Take a look at your DataSet, for the table adapter and GetDataByWKD8 command. In parameters, check if the DbType of @WKDDATE8 is a string type like AnsiString then change the Dbtype to Date and ProviderType to DateTime, then your fill command will accept an input of type DateTime.

Highlight the query in the Designer and look at the properties tab to do this. Just hit the Properties collection and you'll get a dialogue where the DbType is set to AnsiString. You'll find DateTime2 near the bottom of the list if you need that one.

edited Nov 08 '16 at 11:41

Richard Griffiths

758
1
11
23

answered Aug 01 '16 at 20:44

Reza Aghaei

120,393
18
203
398

This doesn't get around the designer problem - this is a recent change I think because I'm having this problem with legacy XSDs that were fine. Now all DateTime2 columns have this happen to them regardless of anything manual I may do. It's annoying. – Richard Griffiths Nov 08 '16 at 11:34
Never mind: this answer clarified it - http://stackoverflow.com/questions/33888920/correct-parameter-types-for-select-queries-that-use-table-valued-functions – Richard Griffiths Nov 08 '16 at 11:36
Surely you should not change the xml or c# code, I also mean using designer :) – Reza Aghaei Nov 08 '16 at 11:37
I'd agree - I done enough XSD hacking to really not recommend it. I've added a little to your answer as I needed that help, it wasn't obvious to me :). – Richard Griffiths Nov 08 '16 at 11:40
Oops! I thought it's obvious! Sorry about that :) – Reza Aghaei Nov 08 '16 at 11:43
1

@RichardGriffiths Thanks for the feedback and editing to make it more clear :) – Reza Aghaei Nov 08 '16 at 12:35
Btw I notice if your query does something like isnull(somefield, someotherfield) = SomeParameter then the XSD designer can't determine what type the SomeParameter is hence the AnsiString failsafe. – Richard Griffiths Nov 15 '16 at 12:53

Why is my tableadaptor forcing a cast to string on my date column

1 Answers1