Configure data source insertion into SQL Server 2005 database - Express editions

I am attempting to insert information from Visual Web Developer 2005 using either the Gridview or Datalist controls into a SQL Server 2005 database and get stuck when defining the custom statement.

When I enter the text within the insert tab, the <next> button remains greyed out, preventing me from continuing to the next page.

If I copy the same text into the select tab, then I can continue with the wizard, however this raises other problems which may or may not be related (multiple insertions of the data into the SQL Server database table - possibly due to postback functions). I would rather use insert to confirm that my second problem is not because I am using the wrong option.

My question is:

Should I be able to use the insert function within VWD express or is this only available within the standard/pro editions?

Are you using SqlDataSource as the DataSource fo GridView/DataList? I tried that and I can edit customized INSERT command. Can you open QueryBuilder when you enter INSERT command?

|||

I am using SqlDataSource as the datasource as well as a valid connection string to my SQL Server Database.

I launched query builder to construct the sql command for insert. It brings up the appropriate table and columns and I have tried using both parameters and then free text responses with the same result.

If I copy the complete insert command (as constructed from query builder) and paste it into the select tab, then I am able to complete the wizard and my table is populated (albeit with multiple rows).

This implies that the sql statement is valid, however I would have thought that even a simple insert query - insert into table1 values("xxx") - would allow me to progress through the SqlDatasource wizard without any difficulty.

Sadly, I am unable to attach a screenshot which would make the visualisation of the problem that much easier.

To re-iterate my problem, I only get so far within the configure data source wizard - click a tab to create a SQL statement for that operation - <INSERT-TAB>, "sql_statement" before hitting a brick wall as both the next and finish buttons are greyed out.

I would appreciate any assistance you can provide on this matter.

Paul

Configuration of SQL Server

Hello, I am from Brazil, for this my English He/she would like to know how I can

configure SQL Sever Express to Turn in my Local pc. If they can help am very

grateful. Wenderson CastroWhat do you want to do, install SQL Server express on your machine ? You might have a look at the common readme files which are shipped with the product. They provide a straightforward walkthrough for installation. For any errors that might come up you are welcome to post them here. If you don′t know where to download express see this link here.

http://msdn.microsoft.com/vstudio/express/sql/download/

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Castro,

SQL Server supports Unicode so you can store pretty much any language in SQL Express that you would like. If you want SQL to follow the rules of a specific language for things such as sort order you need to set the proper Collation, which can be set at the Server level and for individual datbases. Check out this BOL topic for information about Collation.

For the basic SQL Express engine, we do have a Brazilian - Portuguese version which will return error messages in your native language, but we don't support this localized version in any higher Editions, including SQL Express with Advanced Services. You do not need the Brazilian version of SQL Express to store your data, as I mentioned, localized text can be stored in any language version of SQL. You can download localized versions of Express from the download center, just select the language you want from the dropdown. (Note: If the language isn't listed, we don't support it.)

Mike

Configuing SQLServer Express for internet access

Could someone point me to an article that shows how to configure SQLServer express so I can connect to it over the lan and Internet?

Thanks

These articles should help guide you.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Configuration - Guideline for Connectivity Question Posting
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Concurrent queries in SQL Server 2005 Express

If applicable, what's the limit on concurrent queries supported by SQL Server 2005 Express? Also, if there's a limit, does next connection after the max errors out or merely queues up?

Thanks,

Phillip

hi Pjhillip,

PhillipM wrote:

If applicable, what's the limit on concurrent queries supported by SQL Server 2005 Express? Also, if there's a limit, does next connection after the max errors out or merely queues up?

Thanks,

Phillip

as in MSDE 2000 (and full blown SQL Server's editions), there's a "tecnical" limit of 32767 connections... and this can not be workarounded..

MSDE has another "limit", a built in Governer, but it does not prevent futher connections when the magic number of 8 concurrent workloads is reached.. it simply linearly slows down every I/O activity for all active connections...

SQLExpress does not include this query governor, but addresses the edition limitation reducing memory the process can address to 1gb (instead of 2gb for MSDE)...

this 1gb of memory includes all memory segments used by the server, so that data (and pages) cahed as long as query plans, memory structures for locks and additional providers are held within this "container"...
connections cost as well in term of memory strucures, so they are held within the very same limited "container"...

so, when no additional resources are available for the "next connection", the connecting process will hang up waiting for released resources... as soon as this task times out (connection timeout property), an exception will be raised...

regards

|||

Andrea,

Thanks a lot! I should extend my query timeout property then to be on the safe side.

Phillip

concurrent connection limits

I'm new using SQL 2005 Express

I'm planning to use SQL Server Express 2005 for a asp net web application for a video rental store.

Is there a concurrent user conection limit that I should care?

TIA

Gerardo

You can find more information here.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Concatentate sorted strings

Hi All,

I'm new to SQL Server.
I have the following table in a database of SQL Server 2005 Express.

ColA ColB
-- --
A 12
A 10
B 50
B 13

What I want to achieve is the following result :

Col A Aggr
--
A 2,12
B 13, 50

I tried the following query :

SELECT ColA, dbo.Concatenate(ColB) as Aggr
FROM
(SELECT TOP(100) PERCENT ColA, ColB
FROM TABLE_A
ORDER BY ColB) AS Derived_A
GROUP BY ColA

where Concatenate is the CLR User-defined aggreate function written in C#.

What I want to do is to first sort the rows on ColB and then to execute aggregate function over the sorted rows so as to get the above-mentioned results. However, what I got is the following

Col A Aggr

--

A 2,12

B 50,13 -- not sorted

I learnt from the SQL Server documentation that even though the ORDER BY clause is presented in the subquery, the query result is not guaranteed to be sorted. Only ORDER BY clause used in the outer query should work. So, how should this problem be solved? One way of which I'm thinking is to do the sorting in the aggregate function, but I'm worrying if this is harmful to the performance. Could anyone help me to solve the problem?

Thanks!

Regards,
Nathan

I think, it's dbo.Concatenate function which should perform this sorting.|||

You can't do this using SQLCLR UDFs right now. The ORDER BY in the derived table only applies to that scope. And there is no guarantee that rows/values will be sent in sorted order to the UDF within each group. You could do the sorted concatenation using a SQL technique like below:

select pt.ColA, pt.[1] + coalesce(',' + pt.[2], '') + coalesce(',' + pt.[3], '') as Aggr

from (

select ColA, ColB, ROW_NUMBER() OVER(PARTITION BY Col1A ORDER BY ColB) as seq

from tbl

) as t

pivot (min(ColB) for seq in ([1], [2], [3] /* as many items per group that you want to support */)) as pt

Concatenation headache SQL Server 2005 Express

ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' + tbl_Employees.FirstName
As WDS FROM tbl_Employees"
I am trying to concatenate two fields in SQL Server Express 2005. Employees
number is int (Number) and Employees Firstname is varchar(String)
I get the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Jason' to data type
int.
Please help... Thanks very much in advance
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
It seems your EmployeeNumber column is an int. Because int has a higher
data type precedence than nvarchar, FirstName is implicitly converted to int
and this fails because you have FirstName values that are not integers.
You can explicitly cast EmployeeNumber to nvarchar in order to perform
concatenation instead of addition and avoid the conversion error:
SELECT
CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
N' ' +
tbl_Employees.FirstName As WDS
FROM dbo.tbl_Employees
However, I suggest you do this concatenation in your application code rather
than SQL Server. Formatting data for display purposes is a task better done
in the presentation layer.
See the data type precedence topic in the SQL Server Books Online for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>
|||Great!!! Worked!!! Thanks for your help and insight. I appreciate it
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6EC71182-59F9-4780-B6DE-E64BA2AA4D64@.microsoft.com...
> It seems your EmployeeNumber column is an int. Because int has a higher
> data type precedence than nvarchar, FirstName is implicitly converted to
> int and this fails because you have FirstName values that are not
> integers. You can explicitly cast EmployeeNumber to nvarchar in order to
> perform concatenation instead of addition and avoid the conversion error:
> SELECT
> CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
> N' ' +
> tbl_Employees.FirstName As WDS
> FROM dbo.tbl_Employees
> However, I suggest you do this concatenation in your application code
> rather than SQL Server. Formatting data for display purposes is a task
> better done in the presentation layer.
> See the data type precedence topic in the SQL Server Books Online for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>
|||If you want to return a numeric value concatenated with alpha data implicit
conversions fail. So you need to convert() or cast(), both are very similar
although I prefer convert as you can explicitly define the full data type
including the length. I've included an example below.
SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
OR
SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
NOTE: you need to define the length of the nvarchar, which is typically the
length of the number.
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>
|||Thanks very much... appreciate your help

> NOTE: you need to define the length of the nvarchar, which is typically
> the length of the number.
What if the length of nvarchar is unknown?
"D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
> If you want to return a numeric value concatenated with alpha data
> implicit conversions fail. So you need to convert() or cast(), both are
> very similar although I prefer convert as you can explicitly define the
> full data type including the length. I've included an example below.
> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
> OR
> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
>
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>
|||Or, better yet, do the conversion on the client. It will help your query run
faster (as the SQL engine does not have to do the conversions and
concatenation).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
www.hitchhikerguides.net
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thanks very much... appreciate your help
>
> What if the length of nvarchar is unknown?
> "D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
> news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
>
|||Thanks...
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:000D6EA4-77BC-4D8E-8A41-1D52639C99B4@.microsoft.com...
> Or, better yet, do the conversion on the client. It will help your query
> run faster (as the SQL engine does not have to do the conversions and
> concatenation).
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> www.hitchhikerguides.net
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
>

Concatenation headache SQL Server 2005 Express

ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' + tbl_Employees.FirstName
As WDS FROM tbl_Employees"
I am trying to concatenate two fields in SQL Server Express 2005. Employees
number is int (Number) and Employees Firstname is varchar(String)
I get the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Jason' to data type
int.
Please help... Thanks very much in advance> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
It seems your EmployeeNumber column is an int. Because int has a higher
data type precedence than nvarchar, FirstName is implicitly converted to int
and this fails because you have FirstName values that are not integers.
You can explicitly cast EmployeeNumber to nvarchar in order to perform
concatenation instead of addition and avoid the conversion error:
SELECT
CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
N' ' +
tbl_Employees.FirstName As WDS
FROM dbo.tbl_Employees
However, I suggest you do this concatenation in your application code rather
than SQL Server. Formatting data for display purposes is a task better done
in the presentation layer.
See the data type precedence topic in the SQL Server Books Online for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>|||Great!!! Worked!!! Thanks for your help and insight. I appreciate it
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6EC71182-59F9-4780-B6DE-E64BA2AA4D64@.microsoft.com...
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
> It seems your EmployeeNumber column is an int. Because int has a higher
> data type precedence than nvarchar, FirstName is implicitly converted to
> int and this fails because you have FirstName values that are not
> integers. You can explicitly cast EmployeeNumber to nvarchar in order to
> perform concatenation instead of addition and avoid the conversion error:
> SELECT
> CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
> N' ' +
> tbl_Employees.FirstName As WDS
> FROM dbo.tbl_Employees
> However, I suggest you do this concatenation in your application code
> rather than SQL Server. Formatting data for display purposes is a task
> better done in the presentation layer.
> See the data type precedence topic in the SQL Server Books Online for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>> Please help... Thanks very much in advance
>>
>>
>|||If you want to return a numeric value concatenated with alpha data implicit
conversions fail. So you need to convert() or cast(), both are very similar
although I prefer convert as you can explicitly define the full data type
including the length. I've included an example below.
SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
OR
SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
NOTE: you need to define the length of the nvarchar, which is typically the
length of the number.
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>|||Thanks very much... appreciate your help
> NOTE: you need to define the length of the nvarchar, which is typically
> the length of the number.
What if the length of nvarchar is unknown?
"D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
> If you want to return a numeric value concatenated with alpha data
> implicit conversions fail. So you need to convert() or cast(), both are
> very similar although I prefer convert as you can explicitly define the
> full data type including the length. I've included an example below.
> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
> OR
> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
>
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data type
>> int.
>> Please help... Thanks very much in advance
>>
>>
>|||Or, better yet, do the conversion on the client. It will help your query run
faster (as the SQL engine does not have to do the conversions and
concatenation).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
www.hitchhikerguides.net
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thanks very much... appreciate your help
>> NOTE: you need to define the length of the nvarchar, which is typically
>> the length of the number.
> What if the length of nvarchar is unknown?
> "D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
> news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
>> If you want to return a numeric value concatenated with alpha data
>> implicit conversions fail. So you need to convert() or cast(), both are
>> very similar although I prefer convert as you can explicitly define the
>> full data type including the length. I've included an example below.
>> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>> OR
>> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>>
>> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
>> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data
>> type int.
>> Please help... Thanks very much in advance
>>
>>
>|||Thanks...
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:000D6EA4-77BC-4D8E-8A41-1D52639C99B4@.microsoft.com...
> Or, better yet, do the conversion on the client. It will help your query
> run faster (as the SQL engine does not have to do the conversions and
> concatenation).
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> www.hitchhikerguides.net
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
>> Thanks very much... appreciate your help
>> NOTE: you need to define the length of the nvarchar, which is typically
>> the length of the number.
>> What if the length of nvarchar is unknown?
>> "D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
>> news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
>> If you want to return a numeric value concatenated with alpha data
>> implicit conversions fail. So you need to convert() or cast(), both are
>> very similar although I prefer convert as you can explicitly define the
>> full data type including the length. I've included an example below.
>> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>> OR
>> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
>> tbl_Employees.FirstName As WDS
>> FROM tbl_Employees
>>
>> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
>> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
>> tbl_Employees.FirstName As WDS FROM tbl_Employees"
>> I am trying to concatenate two fields in SQL Server Express 2005.
>> Employees number is int (Number) and Employees Firstname is
>> varchar(String)
>> I get the error
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the nvarchar value 'Jason' to data
>> type int.
>> Please help... Thanks very much in advance
>>
>>
>>
>

Concatenation headache SQL Server 2005 Express

ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' + tbl_Employees.FirstName
As WDS FROM tbl_Employees"
I am trying to concatenate two fields in SQL Server Express 2005. Employees
number is int (Number) and Employees Firstname is varchar(String)
I get the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Jason' to data type
int.
Please help... Thanks very much in advance> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
It seems your EmployeeNumber column is an int. Because int has a higher
data type precedence than nvarchar, FirstName is implicitly converted to int
and this fails because you have FirstName values that are not integers.
You can explicitly cast EmployeeNumber to nvarchar in order to perform
concatenation instead of addition and avoid the conversion error:
SELECT
CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
N' ' +
tbl_Employees.FirstName As WDS
FROM dbo.tbl_Employees
However, I suggest you do this concatenation in your application code rather
than SQL Server. Formatting data for display purposes is a task better done
in the presentation layer.
See the data type precedence topic in the SQL Server Books Online for more
information.
Hope this helps.
Dan Guzman
SQL Server MVP
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>|||Great!!! Worked!!! Thanks for your help and insight. I appreciate it
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6EC71182-59F9-4780-B6DE-E64BA2AA4D64@.microsoft.com...
> It seems your EmployeeNumber column is an int. Because int has a higher
> data type precedence than nvarchar, FirstName is implicitly converted to
> int and this fails because you have FirstName values that are not
> integers. You can explicitly cast EmployeeNumber to nvarchar in order to
> perform concatenation instead of addition and avoid the conversion error:
> SELECT
> CAST(tbl_Employees.EmployeeNumber AS nvarchar(10)) +
> N' ' +
> tbl_Employees.FirstName As WDS
> FROM dbo.tbl_Employees
> However, I suggest you do this concatenation in your application code
> rather than SQL Server. Formatting data for display purposes is a task
> better done in the presentation layer.
> See the data type precedence topic in the SQL Server Books Online for more
> information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>|||If you want to return a numeric value concatenated with alpha data implicit
conversions fail. So you need to convert() or cast(), both are very similar
although I prefer convert as you can explicitly define the full data type
including the length. I've included an example below.
SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
OR
SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
tbl_Employees.FirstName As WDS
FROM tbl_Employees
NOTE: you need to define the length of the nvarchar, which is typically the
length of the number.
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
> ssql = "SELECT tbl_Employees.EmployeeNumber + ' ' +
> tbl_Employees.FirstName As WDS FROM tbl_Employees"
> I am trying to concatenate two fields in SQL Server Express 2005.
> Employees number is int (Number) and Employees Firstname is
> varchar(String)
> I get the error
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the nvarchar value 'Jason' to data type
> int.
> Please help... Thanks very much in advance
>
>|||Thanks very much... appreciate your help

> NOTE: you need to define the length of the nvarchar, which is typically
> the length of the number.
What if the length of nvarchar is unknown?
"D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
> If you want to return a numeric value concatenated with alpha data
> implicit conversions fail. So you need to convert() or cast(), both are
> very similar although I prefer convert as you can explicitly define the
> full data type including the length. I've included an example below.
> SELECT convert(nvarchar(10),tbl_Employees.EmployeeNumber) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
> OR
> SELECT cast(tbl_Employees.EmployeeNumber as nvarchar) +
> tbl_Employees.FirstName As WDS
> FROM tbl_Employees
>
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:%230S1Cn4yHHA.5408@.TK2MSFTNGP02.phx.gbl...
>|||Or, better yet, do the conversion on the client. It will help your query run
faster (as the SQL engine does not have to do the conversions and
concatenation).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
www.hitchhikerguides.net
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thanks very much... appreciate your help
>
> What if the length of nvarchar is unknown?
> "D@.t@.Mill" <andrewrobertmiller@.gmail.com> wrote in message
> news:6398A987-FEE8-4923-9127-CB5883D4C28A@.microsoft.com...
>|||Thanks...
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:000D6EA4-77BC-4D8E-8A41-1D52639C99B4@.microsoft.com...
> Or, better yet, do the conversion on the client. It will help your query
> run faster (as the SQL engine does not have to do the conversions and
> concatenation).
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> www.hitchhikerguides.net
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "JP Bless" <jp3BlessNoSpam@.hotmail.com> wrote in message
> news:eIyx9O7yHHA.3916@.TK2MSFTNGP02.phx.gbl...
>