Expression (Created) column outputs nothing??? (VB.NET)

Topic

New Reply

Hello!
I’m working on a VB.NET page that interfaces with an Access Database. I usually try to create my queries in Access first, then copy the SQL to my .aspx page. One particular query looks at the name in a column and outputs initials. Here is the SQL statement (which works perfectly in Access):

strSQL = “SELECT ID, ProjName, BidDate, ProjLoc, Engineer, EngCont, Owner, OwnerCont, IIf(ProjEst Like ‘Will*’,’W.T.’,IIf(ProjEst ” & _
“Like ‘Bob*’,’B.G.’,IIf(ProjEst Like ‘Doug*’,’D.M.’,IIf(ProjEst Like ‘Brian*’,’B.C.’,IIf(ProjEst ” & _
“Like ‘Mitch*’,’M.B.’,IIf(ProjEst Like ‘Cyle*’,’C.B.’,”)))))) AS ProEst, IIf(ExcEst ” & _
“Like ‘Kody*’,’K.S.’,IIf(ExcEst Like ‘Mike*’,’M.M.’,IIf(ExcEst Like ‘Doug*’,’D.M.’,IIf(ExcEst ” & _
“Like ‘Ron*’,’R.M.’,”)))) AS ExEst, IIf(UtilEst Like ‘Norm*’,’N.D.’,IIf(UtilEst ” & _
“Like ‘Kevin*’,’K.B.’,IIf(UtilEst Like ‘Trav*’,’T.F.’,”))) AS UtiEst, IIf(ConcEst ” & _
“Like ‘Will*’,’W.T.’,IIf(ConcEst Like ‘Bob*’,’B.G.’,IIf(ConcEst Like ‘Doug*’,’D.M.’,IIf(ConcEst ” & _
“Like ‘Brian*’,’B.C.’,IIf(ConcEst Like ‘Mitch*’,’M.B.’,”))))) AS ConEst, TurnTot, ” & _
“ExcTot, UtilTot, ConcTot FROM tblAllBids ” & _
“WHERE ((BidDate>=#10/27/2005#) AND (ProjStat Is Null) AND ” & _
“(ExcStat Is Null) AND (UtilStat Is Null) AND (ConcStat Is Null)) ORDER BY ID;”

All the columns reproduce fine on the Web page, with the exception of ProEst, ExEst, UtiEst & ConEst, which are all empty. I’ve triple-checked the syntax, and it is identical to what is in Access. Does anyone have any idea why those columns are not generating properly?

Thanks so much in advance!

Reply | Quote

Replies

Does it help if you replace the * in your Like expressions with %?

Reply | Quote

That works! I guess there’s something about going to .aspx that likes the % instead of the *. Now…

I have this going to a DataGrid, with AllowSorting=”true”…here’s one of the columns:

All of these work except for the 4 generated columns, which all produce an error on clicking the column heading to sort. Not a huge deal…I’ve just disallowed sorting on those columns for the mean time.

Thanks so much, I’d love to hear any ideas on the latter problem if you have any.

Reply | Quote

I suspect the Like issue has to do with the communication via ODBC or OLEDB.
I don’t know why you can’t sort on those columns – I will see if I can dig anything out of my books when I get home.

Reply | Quote