Multinerd Multinerd - 4 months ago 7
SQL Question

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

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 http://imgur.com/a/cO5FA .

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;
}

Answer

It seems 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.