asked on

Convert string yyyyMMdd to dd/MM/yyyy

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

RamanaChoudary

TRY

ddPayperiod.DataTextFormatString = "dd/MM/yyyy"

ITHelper80

ASKER

That fills my dropdown box with the letters dd/MM/yyyy...:(

RamanaChoudary

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()

RamanaChoudary

If you want date format in British
use 103 insted of 101

ITHelper80

ASKER

Thanks but I get an error about there not being a "max" column when I try to run the qry

RamanaChoudary

use 60 insted of MAX

RamanaChoudary

sorry use 10

ITHelper80

ASKER

Now I get this SQL0204 CONVERT in *LIBL type *N not found.

RamanaChoudary

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"

ASKER CERTIFIED SOLUTION

RamanaChoudary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITHelper80

ASKER

Still same error message :(

srikanthreddyn143

if you do as Mr.Ramana said, remove the Format string for ddlist