Solved

Convert string yyyyMMdd to dd/MM/yyyy

Posted on 2009-05-19
12
286 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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