Solved

Convert string yyyyMMdd to dd/MM/yyyy

Posted on 2009-05-19
12
289 Views
Last Modified: 2012-05-07
I am trying to perform a databind to a dropdown box with the below code.

However when i run my app I get this error on the databind command

Format specifier was invalid

I cant figure out whats the problem
Dim strSQL9 As String = "SELECT THRU AS Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
        Dim objRS9 As New IBM.Data.DB2.iSeries.iDB2DataAdapter(strSQL9, AS400conn)
        Dim readerobjrs9 As IBM.Data.DB2.iSeries.iDB2DataReader
        readerobjrs9 = objRS9.SelectCommand.ExecuteReader()
 
 
 
        ddPayperiod.DataTextField = "Date"
        ddPayperiod.DataValueField = "Date"
        ddPayperiod.DataTextFormatString = "{0:d}"
        ddPayperiod.DataSource = readerobjrs9
        ddPayperiod.DataBind()

Open in new window

0
Comment
Question by:ITHelper80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 24425958
TRY

ddPayperiod.DataTextFormatString = "dd/MM/yyyy"
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24426007
That fills my dropdown box with the letters dd/MM/yyyy...:(
0
 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 24426134
try to format in u r sql query only..

Dim strSQL9 As String = "SELECT CONVERT(varchar(Max), THRU,101) as Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
 

and

        ddPayperiod.DataTextField = "Date"
        ddPayperiod.DataValueField = "Date"
        ddPayperiod.DataSource = readerobjrs9
        ddPayperiod.DataBind()

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 24426154
If you want date format in British
use 103 insted of 101
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24426241
Thanks but I get an error about there not being a "max" column when I try to run the qry
0
 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 24426259
use 60 insted of MAX
0
 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 24426268
sorry use 10
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24426335
Now I get this SQL0204 CONVERT in *LIBL type *N not found.
0
 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 24426478
Dim strSQL9 As String = "SELECT CONVERT(varchar(10), THRU) as Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
0
 
LVL 4

Accepted Solution

by:
RamanaChoudary earned 500 total points
ID: 24426555
or

Dim strSQL9 As String = "SELECT CONVERT(varchar, THRU,103) as Date, CUTOFF, CHECK, CLOSED FROM LOCALDTA.ESLECF1 WHERE THRU <= " & EndDate & " AND Substr(DIGITS(From),1,4) = " & sYear & " ORDER BY Date DESC"
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24426568
Still same error message :(
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24426909
if you do as Mr.Ramana said, remove the Format string for ddlist
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question