Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Thumbs down Unanswered: SELECT Statement to display combined fields

    Dear All
    I am trying to populate an OledbDatareader for binding to a ASP datagrid.

    For this I use select statement to display combined fields in a datagrid cell.
    Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on.
    But the problem is if any of the three field is null the combined field 'Address' returns as Null.
    Please help me to overcome this problem.

    Regards
    kalanad ( beginner)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select coalesce(Field1+'<br />', '')
          +coalesce(Field2+'<br />', '')
          +coalesce(Field3, '')   as Address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Hi

    Thank You for the help.
    I tried as you Suggested and now getting the error message
    System.Data.OleDb.OleDbException: Unspecified error: E_FAIL(0x80004005)
    I'll Give the code for the function I used for your reference . Hope You will analyze it.

    Code:
    Protected Function GetDataSource(  ) As System.Collections.ICollection
    
            Dim strConnection As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                                      & server.mappath("MyDb.mdb") & ";"
            Dim cn As OleDbConnection = New OleDbConnection(strConnection)
            cn.Open(  )
    
             Dim StrName as String =  Request.Form("TextBox1")
             TextBox1.text= strName
    
                  Dim StrImg1 as String = "'<p><img src='''"
                  Dim strImg2 As String = "''" & "'<p>'"
    
                  Dim strSql As String = _
                  "SELECT ID, (" & strImg1 & " + images +  " & strimg2 & ") AS Picture, " _
                  & " Fname As Name, Branch," _
                  & " coalesce(AddHome1+'<br />', '')" _
                  & " +coalesce(Addhome2+'<br />', '')" _
                  & " +coalesce(phHome, '') as Address," _
                  & " coalesce(Status+'<br />', '')" _
                  & " +coalesce(AddOff1+'<br />', '')" _
                  & " +coalesce(AddOff2+'<br />', '')" _
                  & " +coalesce(phOff, '') as Office" _
                  & " From tblALl WHERE Fname LIke '" & TextBox1.text & "%' ORDER BY FName"
    
                  Dim da As New oleDBDataAdapter(strSql, cn)
    
                  Dim ds As New System.Data.DataSet(  )
                  da.Fill(ds, "TblAll") ' The error message points this line
    
                  cn.Close(  )
              '
                  Dim dv As New System.Data.DataView(ds.Tables("TblAll"))
    
                 Return dv
    
           End Function ' GetDataSource
    Thank you and expect more help.

    kalanad

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i can't, i don't do whatever language that is, i only do sql and coldfusion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    Does the open indicate any failure to connect? Your strConnection does not specify a username/password or 'trusted connection', so I wonder.

  6. #6
    Join Date
    Oct 2004
    Posts
    7
    Hello..Kaiowas,
    I think there is no Problem with open().. Because it worked fine when I wrote the same function avoiding "coalesce" In SQL statement. As Follows...

    Code:
    Protected Function GetDataSource(  ) As System.Collections.ICollection
    
        Dim strConnection As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                                  & server.mappath("myDB.mdb") & ";"
        Dim cn As OleDbConnection = New OleDbConnection(strConnection)
        cn.Open(  )
    
        Dim StrName as String =  Request.Form("TextBox1")
        TextBox1.text= strName
    
              Dim StrImg1 as String = "'<p><img src='''"
              Dim strImg2 As String = "''" & "'<p>'"
    
              Dim strSql As String = _
              "SELECT ID, (" & strImg1 & " + images +  " & strimg2 & ") AS Picture, " _
              & " Fname As Name, Branch," _
              & "(AddHome1 + '<br/>' + AddHome2 + '<br/>' + Email + '<br/>' + PhHome) As Address, " _
              & "(Status + '<br/>' + Status + '<br/>' +AddOff1 + '<br/>' + AddOff2 + '<br/>'+ PhOff ) As Office " _
              & " From tblAll WHERE Fname LIKE '" & TextBox1.text & "%' ORDER BY FName"
    
              Dim da As New oleDBDataAdapter(strSql, cn)
    
              Dim ds As New System.Data.DataSet(  )
              da.Fill(ds,"TblAll")
    
              cn.Close(  )
          '
              Dim dv As New System.Data.DataView(ds.Tables("TblAll"))
    
             Return dv
    
       End Function ' GetDataSource
    At that time the only bug was... If any one of the fileds cobining is null the whole combiled field returned as Null. But When I re-write the code using "coalesce" as suggested by r937 the error message pops up.

    Any suggestions??

    Kalanad

  7. #7
    Join Date
    Aug 2004
    Posts
    8
    It looks like you're trying to connect to an Access database. I'm not terribly familiar with Access, but perhaps it doesn't support COALESCE? A bit of googling seems to indicate that an equivalent function in this case is "nz". Can anybody confirm this?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, it's NZ..

    You might want to open up Help and maybe try the Access Forum
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ha ha ha ha ha ha

    silly me for suggesting a microsoft sql server function (coalesce) for a problem posted in the microsoft sql server forum

    i'm going to go into a corner and have a good cry now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    silly me for suggesting a microsoft sql server function (coalesce) for a problem posted in the microsoft sql server forum

    i'm going to go into a corner and have a good cry now
    Gee, the nerve of you Rudy!

    You don't have to cry, you really weren't all that naughty.

    I'd offer some more comments, but I'm not sure I can keep playing the straight man on this issue.

    -PatP

  11. #11
    Join Date
    Oct 2004
    Posts
    7
    Hi,

    Thank you.
    Sorry for disturbing you all . I'll post it in Access forum to try my lucK

    Kalanad

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by kalanad
    Hi,

    Thank you.
    Sorry for disturbing you all . I'll post it in Access forum to try my lucK

    Kalanad

    That's OK, most of us are disturbed to begin with...

    Did you try The Access Help and look up NZ yet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and be careful, use & instead of + to do the concatenation in access

    in fact, if you use &, you won't have to bother with nz() because concatenation with & ignores nulls!!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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