How to customise date formats

[goolawah]

In setting up an ODBC link to some external data I have a couple of tables I cannot open if I include fields that have a Date format. VB.net rejects the format.

The data is in a Pervasive database and the date format used it mm/dd/yyyy. If I omit these fields from my ODBC Data Adapter query, everything else reads OK.

I am wondering is there is a way of specifying a custom format so that the VB.net Data Adapter query can handle it?

Any help with this greatly appreciated.

 

[DotNetBlocks]

Please post your code.

 

[goolawah]

SELECT AccountCode, ItemCode, Qty1, Qty2, Qty3, Price1, Price2, Price3, ExpDate, IncPrice1, IncPrice2, IncPrice3 FROM CustomerDiscount

This is the SQL Code generated by the Query Builder (i'm still new to this so using wizards etc to learn.

The ExpDate field is the one that causes the problem. Is there a way I can specify that the format is mm/dd/yyyy?

 

[153]

The SQL Query Seems ok!
When did the VB.Net Reject the format? (when executing the query or when the result of the query was bound in vb.net control?)

By the way, if U want to change the date format. U can use format function in vb.net.

Here is some sample from microsoft msdn.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp

Hope this can help U.

 

[chrissie1]

The format of a date is not important, as long as they are dates the database will understand.

Dates are not stored in any kind of format they are stored as an integer or long.

you really need to give more code and a better explenation of the problem.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[goolawah]

OK, I'm not too sure of this as I am still finding my way around using wizards. The only apparently relevant code I can find is by expanding
#Region " Windows Form Designer generated code "
The following is all that seems relevant in that section.

'OdbcDataAdapter1
        '
        Me.OdbcDataAdapter1.InsertCommand = Me.OdbcInsertCommand1
        Me.OdbcDataAdapter1.SelectCommand = Me.OdbcSelectCommand1
        Me.OdbcDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "CustomerDiscount", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("AccountCode", "AccountCode"), New System.Data.Common.DataColumnMapping("ItemCode", "ItemCode"), New System.Data.Common.DataColumnMapping("Qty1", "Qty1"), New System.Data.Common.DataColumnMapping("Qty2", "Qty2"), New System.Data.Common.DataColumnMapping("Qty3", "Qty3"), New System.Data.Common.DataColumnMapping("Price1", "Price1"), New System.Data.Common.DataColumnMapping("Price2", "Price2"), New System.Data.Common.DataColumnMapping("Price3", "Price3"), New System.Data.Common.DataColumnMapping("ExpDate", "ExpDate"), New System.Data.Common.DataColumnMapping("IncPrice1", "IncPrice1"), New System.Data.Common.DataColumnMapping("IncPrice2", "IncPrice2"), New System.Data.Common.DataColumnMapping("IncPrice3", "IncPrice3")})})
        '
        'OdbcConnection1
        '
        Me.OdbcConnection1.ConnectionString = "AutoDoubleQuote=0;ArrayBufferSize=8;ClientVersion=8.10.121.061;DSN=Pastel;OpenMod" & _
        "e=1;DBQ=HERITAGE;ArrayFetchOn=1;CodePageConvert=1252;UID=;DecimalSymbol=."
        '
        'OdbcSelectCommand1
        '
        Me.OdbcSelectCommand1.CommandText = "SELECT AccountCode, ItemCode, Qty1, Qty2, Qty3, Price1, Price2, Price3, ExpDate, " & _
        "IncPrice1, IncPrice2, IncPrice3 FROM CustomerDiscount"
        Me.OdbcSelectCommand1.Connection = Me.OdbcConnection1
        '
        'OdbcInsertCommand1
        '
        Me.OdbcInsertCommand1.CommandText = "INSERT INTO CustomerDiscount(AccountCode, ItemCode, Qty1, Qty2, Qty3, Price1, Pri" & _
        "ce2, Price3, ExpDate, IncPrice1, IncPrice2, IncPrice3) VALUES (?, ?, ?, ?, ?, ?," & _
        " ?, ?, ?, ?, ?, ?)"
        Me.OdbcInsertCommand1.Connection = Me.OdbcConnection1
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("AccountCode", System.Data.Odbc.OdbcType.VarChar, 6, "AccountCode"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("ItemCode", System.Data.Odbc.OdbcType.VarChar, 15, "ItemCode"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Qty1", System.Data.Odbc.OdbcType.Double, 0, "Qty1"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Qty2", System.Data.Odbc.OdbcType.Double, 0, "Qty2"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Qty3", System.Data.Odbc.OdbcType.Double, 0, "Qty3"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Price1", System.Data.Odbc.OdbcType.Double, 0, "Price1"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Price2", System.Data.Odbc.OdbcType.Double, 0, "Price2"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Price3", System.Data.Odbc.OdbcType.Double, 0, "Price3"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("ExpDate", System.Data.Odbc.OdbcType.Date, 0, "ExpDate"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("IncPrice1", System.Data.Odbc.OdbcType.Double, 0, "IncPrice1"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("IncPrice2", System.Data.Odbc.OdbcType.Double, 0, "IncPrice2"))
        Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("IncPrice3", System.Data.Odbc.OdbcType.Double, 0, "IncPrice3"))

Thanks for help so far.

 

[goolawah]

Replying to "153"

Again, using the wizards, I can set up the Data Adapter Ok. When I come to Preview Data | Fill Dataset I get the error message

If I OK on that it does actually load the data. If I leave the date field out I don't get the error message.

Thanks for help so far...

 

[153]

The Code seems ok to me.

I still can get where the problem is.

The problem occurs when U want to fill the dataset.
Is There something error in your data? (U can check the data in your ExpDate field.)

Hope this can help U.
Sorry, just can help with this sugesstion.