Can't concatenate for data adapters

Discussion in 'ASP .Net' started by =?Utf-8?B?am9uZWZlcg==?=, Oct 11, 2005.

  1. Just starting with ASP.NET and Pulling from data from an Access Database so
    I'm using the OleDbDataAdapter

    I'm trying to create two data adapters...

    daActorNames

    daActor

    I've successfully created daActor

    But for daActorNames the following (which works in T-SQL) just doesn't work

    SELECT ActorID, LastName + N', ' + FirstName + N' ' + ISNULL(MiddleName,
    N'') AS Actor
    FROM tblActors
    ORDER BY LastName

    I'm trying to produce the following

    ActorID Actor
    123 LastName, FirstName MI (if there is one... nothing if there
    isn't)
    Why doesnt' this work in or out of the query builder?
     
    =?Utf-8?B?am9uZWZlcg==?=, Oct 11, 2005
    #1
    1. Advertising

  2. Jonefer,

    I'm not positive if access understands the "N", but I know you don't need
    it. Also I'm unclear whether your ISNULL is allowed or not but I don't
    understand why you'd need it. If MiddleName is NULL then it just won't be
    included. Give this a try:

    SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
    Actor
    FROM tblActors
    ORDER BY LastName

    --
    Sincerely,

    S. Justin Gengo, MCP
    Web Developer / Programmer

    www.aboutfortunate.com

    "Out of chaos comes order."
    Nietzsche
    "jonefer" <> wrote in message
    news:...
    > Just starting with ASP.NET and Pulling from data from an Access Database
    > so
    > I'm using the OleDbDataAdapter
    >
    > I'm trying to create two data adapters...
    >
    > daActorNames
    >
    > daActor
    >
    > I've successfully created daActor
    >
    > But for daActorNames the following (which works in T-SQL) just doesn't
    > work
    >
    > SELECT ActorID, LastName + N', ' + FirstName + N' ' +
    > ISNULL(MiddleName,
    > N'') AS Actor
    > FROM tblActors
    > ORDER BY LastName
    >
    > I'm trying to produce the following
    >
    > ActorID Actor
    > 123 LastName, FirstName MI (if there is one... nothing if there
    > isn't)
    > Why doesnt' this work in or out of the query builder?
    >
    >
    >
    >
    >
     
    S. Justin Gengo, Oct 11, 2005
    #2
    1. Advertising

  3. Ok, I was hoping someone else would say that...
    I tried what you said, and then this is how it interpreted that:

    SELECT ActorID, LastName + ',
    ' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY LastName

    My resultant set looks like this

    ActorID Actor
    ===== =======
    123 LastName,
    124 LastName,
    125 Null
    126 Null

    No matter what I try, I haven't been able to get the firstname to show...
    unless I completely eliminate the comma.

    But another KEY thing you said is that you're not sure "ACCESS" understands
    'N'
    so... should I do what I already know works in Access?

    SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")

    I tried that, and it doesn't like the & (ampersand)







    "S. Justin Gengo" wrote:

    > Jonefer,
    >
    > I'm not positive if access understands the "N", but I know you don't need
    > it. Also I'm unclear whether your ISNULL is allowed or not but I don't
    > understand why you'd need it. If MiddleName is NULL then it just won't be
    > included. Give this a try:
    >
    > SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
    > Actor
    > FROM tblActors
    > ORDER BY LastName
    >
    > --
    > Sincerely,
    >
    > S. Justin Gengo, MCP
    > Web Developer / Programmer
    >
    > www.aboutfortunate.com
    >
    > "Out of chaos comes order."
    > Nietzsche
    > "jonefer" <> wrote in message
    > news:...
    > > Just starting with ASP.NET and Pulling from data from an Access Database
    > > so
    > > I'm using the OleDbDataAdapter
    > >
    > > I'm trying to create two data adapters...
    > >
    > > daActorNames
    > >
    > > daActor
    > >
    > > I've successfully created daActor
    > >
    > > But for daActorNames the following (which works in T-SQL) just doesn't
    > > work
    > >
    > > SELECT ActorID, LastName + N', ' + FirstName + N' ' +
    > > ISNULL(MiddleName,
    > > N'') AS Actor
    > > FROM tblActors
    > > ORDER BY LastName
    > >
    > > I'm trying to produce the following
    > >
    > > ActorID Actor
    > > 123 LastName, FirstName MI (if there is one... nothing if there
    > > isn't)
    > > Why doesnt' this work in or out of the query builder?
    > >
    > >
    > >
    > >
    > >

    >
    >
    >
     
    =?Utf-8?B?am9uZWZlcg==?=, Oct 11, 2005
    #3
  4. Jonefer,

    Did you remove the parentheses I had in the select I showed you?

    Those are key. I know that concatenation works in Access because I tried it.
    And the only difference between the select I sent you and the one you show
    here is that yours doesn't have the parentheses...

    --
    Sincerely,

    S. Justin Gengo, MCP
    Web Developer / Programmer

    www.aboutfortunate.com

    "Out of chaos comes order."
    Nietzsche
    "jonefer" <> wrote in message
    news:...
    > Ok, I was hoping someone else would say that...
    > I tried what you said, and then this is how it interpreted that:
    >
    > SELECT ActorID, LastName + ',
    > ' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY
    > LastName
    >
    > My resultant set looks like this
    >
    > ActorID Actor
    > ===== =======
    > 123 LastName,
    > 124 LastName,
    > 125 Null
    > 126 Null
    >
    > No matter what I try, I haven't been able to get the firstname to show...
    > unless I completely eliminate the comma.
    >
    > But another KEY thing you said is that you're not sure "ACCESS"
    > understands
    > 'N'
    > so... should I do what I already know works in Access?
    >
    > SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")
    >
    > I tried that, and it doesn't like the & (ampersand)
    >
    >
    >
    >
    >
    >
    >
    > "S. Justin Gengo" wrote:
    >
    >> Jonefer,
    >>
    >> I'm not positive if access understands the "N", but I know you don't need
    >> it. Also I'm unclear whether your ISNULL is allowed or not but I don't
    >> understand why you'd need it. If MiddleName is NULL then it just won't be
    >> included. Give this a try:
    >>
    >> SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
    >> Actor
    >> FROM tblActors
    >> ORDER BY LastName
    >>
    >> --
    >> Sincerely,
    >>
    >> S. Justin Gengo, MCP
    >> Web Developer / Programmer
    >>
    >> www.aboutfortunate.com
    >>
    >> "Out of chaos comes order."
    >> Nietzsche
    >> "jonefer" <> wrote in message
    >> news:...
    >> > Just starting with ASP.NET and Pulling from data from an Access
    >> > Database
    >> > so
    >> > I'm using the OleDbDataAdapter
    >> >
    >> > I'm trying to create two data adapters...
    >> >
    >> > daActorNames
    >> >
    >> > daActor
    >> >
    >> > I've successfully created daActor
    >> >
    >> > But for daActorNames the following (which works in T-SQL) just doesn't
    >> > work
    >> >
    >> > SELECT ActorID, LastName + N', ' + FirstName + N' ' +
    >> > ISNULL(MiddleName,
    >> > N'') AS Actor
    >> > FROM tblActors
    >> > ORDER BY LastName
    >> >
    >> > I'm trying to produce the following
    >> >
    >> > ActorID Actor
    >> > 123 LastName, FirstName MI (if there is one... nothing if
    >> > there
    >> > isn't)
    >> > Why doesnt' this work in or out of the query builder?
    >> >
    >> >
    >> >
    >> >
    >> >

    >>
    >>
    >>
     
    S. Justin Gengo, Oct 14, 2005
    #4
  5. Justin,
    No, I didn't remove the parenthesis. VS.NET removes it.
    That's what I meant by:

    "and then this is how it interpreted that"

    I ended up going to my Access Database and creating a field called
    ActorName, updated the table and am now using that field.

    ....but there must be away to do this in Visual Studio.NET
    Try it if you can... you won't be able to keep the parenthesis if you use
    the Query Builder... maybe I need to set up the adapter through code.




    "S. Justin Gengo" wrote:

    > Jonefer,
    >
    > Did you remove the parentheses I had in the select I showed you?
    >
    > Those are key. I know that concatenation works in Access because I tried it.
    > And the only difference between the select I sent you and the one you show
    > here is that yours doesn't have the parentheses...
    >
    > --
    > Sincerely,
    >
    > S. Justin Gengo, MCP
    > Web Developer / Programmer
    >
    > www.aboutfortunate.com
    >
    > "Out of chaos comes order."
    > Nietzsche
    > "jonefer" <> wrote in message
    > news:...
    > > Ok, I was hoping someone else would say that...
    > > I tried what you said, and then this is how it interpreted that:
    > >
    > > SELECT ActorID, LastName + ',
    > > ' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY
    > > LastName
    > >
    > > My resultant set looks like this
    > >
    > > ActorID Actor
    > > ===== =======
    > > 123 LastName,
    > > 124 LastName,
    > > 125 Null
    > > 126 Null
    > >
    > > No matter what I try, I haven't been able to get the firstname to show...
    > > unless I completely eliminate the comma.
    > >
    > > But another KEY thing you said is that you're not sure "ACCESS"
    > > understands
    > > 'N'
    > > so... should I do what I already know works in Access?
    > >
    > > SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")
    > >
    > > I tried that, and it doesn't like the & (ampersand)
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > > "S. Justin Gengo" wrote:
    > >
    > >> Jonefer,
    > >>
    > >> I'm not positive if access understands the "N", but I know you don't need
    > >> it. Also I'm unclear whether your ISNULL is allowed or not but I don't
    > >> understand why you'd need it. If MiddleName is NULL then it just won't be
    > >> included. Give this a try:
    > >>
    > >> SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName) AS
    > >> Actor
    > >> FROM tblActors
    > >> ORDER BY LastName
    > >>
    > >> --
    > >> Sincerely,
    > >>
    > >> S. Justin Gengo, MCP
    > >> Web Developer / Programmer
    > >>
    > >> www.aboutfortunate.com
    > >>
    > >> "Out of chaos comes order."
    > >> Nietzsche
    > >> "jonefer" <> wrote in message
    > >> news:...
    > >> > Just starting with ASP.NET and Pulling from data from an Access
    > >> > Database
    > >> > so
    > >> > I'm using the OleDbDataAdapter
    > >> >
    > >> > I'm trying to create two data adapters...
    > >> >
    > >> > daActorNames
    > >> >
    > >> > daActor
    > >> >
    > >> > I've successfully created daActor
    > >> >
    > >> > But for daActorNames the following (which works in T-SQL) just doesn't
    > >> > work
    > >> >
    > >> > SELECT ActorID, LastName + N', ' + FirstName + N' ' +
    > >> > ISNULL(MiddleName,
    > >> > N'') AS Actor
    > >> > FROM tblActors
    > >> > ORDER BY LastName
    > >> >
    > >> > I'm trying to produce the following
    > >> >
    > >> > ActorID Actor
    > >> > 123 LastName, FirstName MI (if there is one... nothing if
    > >> > there
    > >> > isn't)
    > >> > Why doesnt' this work in or out of the query builder?
    > >> >
    > >> >
    > >> >
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
    =?Utf-8?B?am9uZWZlcg==?=, Oct 14, 2005
    #5
  6. Jonefer,

    In that case how about specifying your select as a query in the access
    database and then calling that query name from .NET.

    That way you'll be calling it the same way you would a stored procedure...

    --
    Sincerely,

    S. Justin Gengo, MCP
    Web Developer / Programmer

    www.aboutfortunate.com

    "Out of chaos comes order."
    Nietzsche
    "jonefer" <> wrote in message
    news:...
    > Justin,
    > No, I didn't remove the parenthesis. VS.NET removes it.
    > That's what I meant by:
    >
    > "and then this is how it interpreted that"
    >
    > I ended up going to my Access Database and creating a field called
    > ActorName, updated the table and am now using that field.
    >
    > ...but there must be away to do this in Visual Studio.NET
    > Try it if you can... you won't be able to keep the parenthesis if you use
    > the Query Builder... maybe I need to set up the adapter through code.
    >
    >
    >
    >
    > "S. Justin Gengo" wrote:
    >
    >> Jonefer,
    >>
    >> Did you remove the parentheses I had in the select I showed you?
    >>
    >> Those are key. I know that concatenation works in Access because I tried
    >> it.
    >> And the only difference between the select I sent you and the one you
    >> show
    >> here is that yours doesn't have the parentheses...
    >>
    >> --
    >> Sincerely,
    >>
    >> S. Justin Gengo, MCP
    >> Web Developer / Programmer
    >>
    >> www.aboutfortunate.com
    >>
    >> "Out of chaos comes order."
    >> Nietzsche
    >> "jonefer" <> wrote in message
    >> news:...
    >> > Ok, I was hoping someone else would say that...
    >> > I tried what you said, and then this is how it interpreted that:
    >> >
    >> > SELECT ActorID, LastName + ',
    >> > ' + FirstName + ' ' + MiddleName AS Actor FROM tblActors ORDER BY
    >> > LastName
    >> >
    >> > My resultant set looks like this
    >> >
    >> > ActorID Actor
    >> > ===== =======
    >> > 123 LastName,
    >> > 124 LastName,
    >> > 125 Null
    >> > 126 Null
    >> >
    >> > No matter what I try, I haven't been able to get the firstname to
    >> > show...
    >> > unless I completely eliminate the comma.
    >> >
    >> > But another KEY thing you said is that you're not sure "ACCESS"
    >> > understands
    >> > 'N'
    >> > so... should I do what I already know works in Access?
    >> >
    >> > SELECT LastName & ", " & FirstName & & " " nz(MiddleName, "")
    >> >
    >> > I tried that, and it doesn't like the & (ampersand)
    >> >
    >> >
    >> >
    >> >
    >> >
    >> >
    >> >
    >> > "S. Justin Gengo" wrote:
    >> >
    >> >> Jonefer,
    >> >>
    >> >> I'm not positive if access understands the "N", but I know you don't
    >> >> need
    >> >> it. Also I'm unclear whether your ISNULL is allowed or not but I don't
    >> >> understand why you'd need it. If MiddleName is NULL then it just won't
    >> >> be
    >> >> included. Give this a try:
    >> >>
    >> >> SELECT ActorID, (LastName + ', ' + FirstName + ' ' + MiddleName)
    >> >> AS
    >> >> Actor
    >> >> FROM tblActors
    >> >> ORDER BY LastName
    >> >>
    >> >> --
    >> >> Sincerely,
    >> >>
    >> >> S. Justin Gengo, MCP
    >> >> Web Developer / Programmer
    >> >>
    >> >> www.aboutfortunate.com
    >> >>
    >> >> "Out of chaos comes order."
    >> >> Nietzsche
    >> >> "jonefer" <> wrote in message
    >> >> news:...
    >> >> > Just starting with ASP.NET and Pulling from data from an Access
    >> >> > Database
    >> >> > so
    >> >> > I'm using the OleDbDataAdapter
    >> >> >
    >> >> > I'm trying to create two data adapters...
    >> >> >
    >> >> > daActorNames
    >> >> >
    >> >> > daActor
    >> >> >
    >> >> > I've successfully created daActor
    >> >> >
    >> >> > But for daActorNames the following (which works in T-SQL) just
    >> >> > doesn't
    >> >> > work
    >> >> >
    >> >> > SELECT ActorID, LastName + N', ' + FirstName + N' ' +
    >> >> > ISNULL(MiddleName,
    >> >> > N'') AS Actor
    >> >> > FROM tblActors
    >> >> > ORDER BY LastName
    >> >> >
    >> >> > I'm trying to produce the following
    >> >> >
    >> >> > ActorID Actor
    >> >> > 123 LastName, FirstName MI (if there is one... nothing if
    >> >> > there
    >> >> > isn't)
    >> >> > Why doesnt' this work in or out of the query builder?
    >> >> >
    >> >> >
    >> >> >
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
    S. Justin Gengo, Oct 17, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Morys Kenworthy

    .NET Version 1.1 - Data Adapters

    Morys Kenworthy, Apr 1, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,508
    Cowboy \(Gregory A. Beamer\)
    Apr 1, 2004
  2. Robin

    ASP.Net and Data Adapters

    Robin, Nov 8, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    333
    James Thomas
    Nov 9, 2004
  3. dimpy
    Replies:
    0
    Views:
    331
    dimpy
    Jan 28, 2006
  4. J

    2 data adapters, 1 dataset

    J, Mar 25, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    283
    bruce barker
    Mar 25, 2008
  5. Carlos

    Concatenate/De-Concatenate

    Carlos, Oct 12, 2012, in forum: VHDL
    Replies:
    10
    Views:
    899
Loading...

Share This Page