How to convert an integer to a string

[photomonkey]

I am having a problem with a relatively simple sql query, and was
hoping that somebody could help me on this forum.

Basicaly the situation is that I have two tables, a Vendors table
(Vendors) and a ProductCatalogue table (ProdCat).

I wish to select the VendorID, and VendorName from the vendors table.
Additionally I want to run a subquery on (SELECT COUNT(*) FROM ProdCat
WHERE ProdCat.Pcat_VendID=Vendors.Vend_ID). I want to concatenate the
result of the subquery with the VendorName colunm.

I created the following statement:
SELECT Vend_ID, Vend_Name + '(' + (SELECT COUNT(*) FROM ProdCat WHERE
ProdCat.Pcat_VendID=Vendors.Vend_ID)'+ products) AS display FROM
Vendors;

When I run this query I get the following message:
The provider could not determine the Double value. For example, the row
was just created, the default for the Double column was not available,
and the consumer had not yet set a new Double value.

I believe the problem occurs during concatenation, since the Vend_Name
colunm is a string while the subquery result is an integer. I have
tried using the convert function. Unfortunaly it seems that MS Access
does not support this function as I got a (Undefined function 'convert'
in expression.) error message.

I was wondering if anybody knows a way around this problem. I could get
the subquery result in a separate column and then later concatenate it
with the Vend_Name column programmatically, but I think it would be
neater to do it in the sql query.

[Bob Quintal]

"photomonkey" <pa**********@gmail.com> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

I am having a problem with a relatively simple sql query, and
was hoping that somebody could help me on this forum.

I believe the problem occurs during concatenation, since the
Vend_Name colunm is a string while the subquery result is an
integer. I have tried using the convert function. Unfortunaly
it seems that MS Access does not support this function as I
got a (Undefined function 'convert' in expression.) error
message.

The Access help will tell you that to convert a number to a string,
use the Cstr() function.

However, you may want to use the Format() function, to add leading
zeroes, etc. It's described in the help too.

--
Bob Quintal

PA is y I've altered my email address.

[photomonkey]

Thanks Bob quintal, you replied really quickly.

I forgot to mention that I am not using VB. I am programming a page in
ASP.NET and C#. What I was wanting to do was to populate a dropdownlist
with the query result more or less on the following way:

OleDbDataReader dr = command.ExecuteReader();
this.ddlVendID.DataSource = dr;
this.ddlVendID.DataTextField = "display";
this.ddlVendID.DataValueField = "Vend_ID";
this.ddlVendID.DataBind();

Do you have any suggestions for what I might do to only get two columns
back after calling OleDbCommand.ExecuteReader() ?

I guess "ToString()" is the C# equivalent for "Cstr()", but how could
I make use of this and bind the DataReader object directly to the
dropdownlist?

Thanks for your help, I have been spending several hours trying to a
neat way of doing this but have yet not found one.

[David W. Fenton]

"photomonkey" <pa**********@gmail.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

I forgot to mention that I am not using VB.

Then why are you posting in an Access newsgroup? Ypu aren't using
Access, though you may have used Access to create the MDB file
you're storing your data in.

You're communicating with the Jet database engine, yes, but Jet is
an entity entirely separate from Access (it ships on every Windows
PC, as it is used for storing Active Directory data, which should
indicate to you that it's not obsolete, nor untrustworthy).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

Given that this newsgroup is devoted to Microsoft Access database software,
I'd think you would have a far better chance of getting good information
about .NET's builtin functions in a .NET newsgroup. There are quite a
number, both in USENET and Microsoft's sponsored newsgroups at the free
newsserver news.microsoft.com.

Because of the subject of this newsgroup, unless told otherwise, we just
naturally assume a question is about Access and the VBA used by Access. It
is a waste of both the asker and answerer's time to ask C# or VB.NET-
specific questions here, without saying that is what you are asking about.

Larry Linson
Microsoft Access MVP

"photomonkey" <pa**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Thanks Bob quintal, you replied really quickly.

I forgot to mention that I am not using VB. I am programming a page in
ASP.NET and C#. What I was wanting to do was to populate a dropdownlist
with the query result more or less on the following way:

OleDbDataReader dr = command.ExecuteReader();
this.ddlVendID.DataSource = dr;
this.ddlVendID.DataTextField = "display";
this.ddlVendID.DataValueField = "Vend_ID";
this.ddlVendID.DataBind();

Do you have any suggestions for what I might do to only get two columns
back after calling OleDbCommand.ExecuteReader() ?

I guess "ToString()" is the C# equivalent for "Cstr()", but how could
I make use of this and bind the DataReader object directly to the
dropdownlist?

Thanks for your help, I have been spending several hours trying to a
neat way of doing this but have yet not found one.

[Tom van Stiphout]

On 15 Aug 2005 15:06:27 -0700, "photomonkey" <pa**********@gmail.com>
wrote:

The *Double* value? That is completely illogical. You did not have a
Double field in this query, did you?

-Tom.

I am having a problem with a relatively simple sql query, and was
hoping that somebody could help me on this forum.

Basicaly the situation is that I have two tables, a Vendors table
(Vendors) and a ProductCatalogue table (ProdCat).

I wish to select the VendorID, and VendorName from the vendors table.
Additionally I want to run a subquery on (SELECT COUNT(*) FROM ProdCat
WHERE ProdCat.Pcat_VendID=Vendors.Vend_ID). I want to concatenate the
result of the subquery with the VendorName colunm.

I created the following statement:
SELECT Vend_ID, Vend_Name + '(' + (SELECT COUNT(*) FROM ProdCat WHERE
ProdCat.Pcat_VendID=Vendors.Vend_ID)'+ products) AS display FROM
Vendors;

When I run this query I get the following message:
The provider could not determine the Double value. For example, the row
was just created, the default for the Double column was not available,
and the consumer had not yet set a new Double value.

I believe the problem occurs during concatenation, since the Vend_Name
colunm is a string while the subquery result is an integer. I have
tried using the convert function. Unfortunaly it seems that MS Access
does not support this function as I got a (Undefined function 'convert'
in expression.) error message.

I was wondering if anybody knows a way around this problem. I could get
the subquery result in a separate column and then later concatenate it
with the Vend_Name column programmatically, but I think it would be
neater to do it in the sql query.

[photomonkey]

Sorry if I posted in the wrong group.

I won't say that I am a very experienced programmer. I am just trying
to get some university work done. I thought the problem was due to the
way Access implements SQL. SQL has a function "Convert" that converts a
number (double) to a string. However, it seems that this function is
not supported by Access. I tried searching the web for the equivalent
of function, but did not get any relevant results.

I was hoping somebody would be able to tell me if there is a way of
converting a double to a string while the query is being executed.
This is why, I thought that the programming languague I use would be
irrelevant, and that I only needed to bother about calling a native MS
Access function.

[photomonkey]

I think I get a *Double* as the subquery result (SELECT COUNT(*) FROM
ProdCat WHERE ProdCat.Pcat_VendID=Vendors.Vend_ID).

If I substitute this subquery with the column name of another
text-based, the sql statement executes without problems. It also
concatenates in the way intended.

This is the reason why I think it is necessary to convert the subquery
result to a string. but c# code does not work on the sql statement, and
I thought it would be likely that access has some function for this. I
have not been able to find this function on the access help file either.

[Larry Linson]

"photomonkey" wrote

This is why, I thought that the program-
ming languague I use would be irrelevant,
and that I only needed to bother about
calling a native MS Access function.

David's response gives the reason, though it may not be obvious. You are
using the Jet database engine to commuincate with a Jet database. It happens
that the Jet database engine is the default DB engine installed by Access
and even Microsoft is not careful to be precise in their terminology.

You _were_ calling a native Access function, but Access is nowhere involved
in your database Access, and Jet has no comparable native SQL function.
<SIGH>

If you were running that Query from Access, it would likely work
just-fine-thankee.

Larry Linson
Microsoft Access MVP

[photomonkey]

OK, I get it now.

I was not aware that Access and Jet were two different things. I guess
I learned something new today. Anyway, thanks a lot for the help.