Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

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

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <asp:BoundColumn HeaderText="Contact" HeaderStyle-Font-Bold="true" HeaderStyle-HorizontalAlign="center" HeaderStyle-BorderColor="#000000"
    DataField="OwnerCont"
    ItemStyle-HorizontalAlign="center"
    ItemStyle-BorderColor="#000000"
    SortExpression="OwnerCont"
    />

    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.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •