Solved

Convert string yyyyMMdd to dd/MM/yyyy

Posted on 2009-05-19
12
274 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
  • 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now