Solved

Convert string yyyyMMdd to dd/MM/yyyy

Posted on 2009-05-19
12
277 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

DevOps Toolchain Recommendations

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

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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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