Error Parsing Name String

I''m reposting this problem because I may not have explained it well enough
before. in example 1 below, i've successfully extracted the last name from a
full name field, although I've hard-coded the fullname or userName.
Example 2 gives the listed error when i run it on my table with data in the
userName field. What I can't figure is Example 2 will work if I don't
include the "-1" part, buh returns the lastname and it's trailing comma.
What am I doing wrong? My data looks like the DATA section below.
--EXAMPLE 1: works , hard-coded name
SELECT 'Miles, Michael L' as userName,
LEFT('Miles, Michael L',CHARINDEX(',','Miles, Michael L')-1) as userLast
-- EXAMPLE 2
-- Error Msg 536, Level 16, State 3, Line 1
-- Invalid length parameter passed to the substring function.
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
DATA Table***********************************
****************
USERNAME
Smith, John R
Walker, L.T.
Adams, Charles
Weathersby, D.J.On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
(snip)
>-- EXAMPLE 2
>-- Error Msg 536, Level 16, State 3, Line 1
>-- Invalid length parameter passed to the substring function.
>SELECT name as userName,
>LEFT(name,CHARINDEX(',',name)-1) as userLast
>FROM sc_employee
Hi Scott,
You probably have at least one row where there is no comma in the Name
column. To find the offenders, use
SELECT name
FROM sc_employee
WHERE name NOT LIKE '%,%'
And to exclude the offending rows from your query, change it to
SELECT name as userName,
LEFT(name,CHARINDEX(',',name)-1) as userLast
FROM sc_employee
WHERE name LIKE '%,%'
Hugo Kornelis, SQL Server MVP|||I found a null record. thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:6nm0321acbpk9am57qii9nk8p23dinp6a3@.
4ax.com...
> On Sun, 2 Apr 2006 17:56:42 -0500, scott wrote:
> (snip)
> Hi Scott,
> You probably have at least one row where there is no comma in the Name
> column. To find the offenders, use
> SELECT name
> FROM sc_employee
> WHERE name NOT LIKE '%,%'
> And to exclude the offending rows from your query, change it to
> SELECT name as userName,
> LEFT(name,CHARINDEX(',',name)-1) as userLast
> FROM sc_employee
> WHERE name LIKE '%,%'
> --
> Hugo Kornelis, SQL Server MVP

Error on DataAdapter.fill

i have this code :

if(! IsPostBack)
{
string strConnection = "server=localhost; uid=sa;pwd=sasasa; database=northwind";
string strCommand = "Select * from Customers";

SqlDataAdapter dataAdapter = newSqlDataAdapter(strCommand, strConnection);

DataSet dataset = new DataSet();

dataAdapter.Fill(dataset, "Products");
SqlCommandBuilder bldr = newSqlCommandBuilder(dataAdapter);

DataTable dataTable = dataset.Tables[0];
dgCustomer.DataSource = dataTable;
dgCustomer.DataBind();
}

when i run this code, error like this appear :

Server Error in '/Registeration' Application.

---

SQL Server does not exist or access denied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error:

Line 41: DataSet dataset = new DataSet();
Line 42:
Line 43: dataAdapter.Fill(dataset, "Products");
Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);
Line 45:

Source File:e:\asp.net\registeration\register.aspx.cs Line:43

Stack Trace:

[SqlException: SQL Server does not exist or access denied.]
 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
 System.Data.SqlClient.SqlConnection.Open()
 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
 System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
 Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:\asp.net\registeration\register.aspx.cs:43
 System.Web.UI.Control.OnLoad(EventArgs e)
 System.Web.UI.Control.LoadRecursive()
 System.Web.UI.Page.ProcessRequestMain()

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

how can i solve this problem? thank you...

Either the connectionstring is invalid or you didn't start the SQL service.

Start | Administrative tools | Services | Find the service and start it.

Regards

Error of max length of string using sql DTC to import DATA

Hi,

I am trying to use DTC to import about 500 records from an Excelsheet to an SQL database.

Some fields contain strings with a length of about 1700 characters.

When i'm trying to execute the DTC generated code it is giving an error and saying that the maximum string length of 255 has been reached. Althoug the column in the database is defined as nchar(1750).

Does anybody know an answer to this problem ?

Thanks in advance,

Goofythe max length of 255 is probably referring to the max length of text in a spreadsheet cell.

i know when i export to excel, all cell text gets clipped at 255 characters.|||Hi Thanks for your suggestion.

But till now no succes. I tried using .CSV as import type but still the same problem. Also if i use HTML as input type although the complete text (1700 char) is between the <DATA> and </DATA> tags.

Anybody with other suggestions ?

Error Msg

String or binary data would be truncated.
The statement has been terminated.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

Source Error:

Line 32: Line 33: Dim add As New SqlCommand(change, conn2)Line 34: add.ExecuteNonQuery()Line 35: conn2.Close()Line 36: Response.Redirect("ThankUReg.aspx")

hi there,

I have this error msg when i try to execute my register page..

What do they mean by string or binary data will be truncated? i've set my database value type to varchar(MAX).

still this error appears.. Looking forward for ur reply.

Yini

well it Does in fact mean that you are trying to insert data into a column that is not big enough for the data. Without knowing your db structure or seeing the SQL your actually using to insert i can't offer much more help. debug the process and maybe copy and paste your values into excel or something so you can run a LEN Function on them and see which one is too big.

hth,
mcm

|||

yinibabe:

i've set my database value type to varchar(MAX).

Have you set the data type to be varchar (max) for every string type column ? My advice is to again check all the columns for their data types, I think you are missing one column which may not allow the amount of data you are trying to insert for that column.

Hope this will help.

Error message: String or binary data truncated

Hi, all!
I encountered that error message while testing my application using
MSDE. It appears when I issue this type of statement:
INSERT INT atable (Col1, Col2, ...)
VALUES (Val1, Val2,...)
The string columns are of type 'char'.
The strange is that the message does not appear always. In one case I
had error in two consecutive executions of the query. After that, in the
3rd execution *with exactly the same data!* all went OK!? I cannot
understand where lies the problem.
The same thing does not happen at all when testing with MS SQL Server 2000.
Please, can somebody give me a hint about what probably goes on.
hi Lazar,
Lazar Videnov wrote:
> Hi, all!
> I encountered that error message while testing my application using
> MSDE. It appears when I issue this type of statement:
> INSERT INT atable (Col1, Col2, ...)
> VALUES (Val1, Val2,...)
> The string columns are of type 'char'.
> The strange is that the message does not appear always. In one case I
> had error in two consecutive executions of the query. After that, in
> the 3rd execution *with exactly the same data!* all went OK!? I cannot
> understand where lies the problem.
> The same thing does not happen at all when testing with MS SQL Server
> 2000.
> Please, can somebody give me a hint about what probably goes on.
are you sure this is not depending on the provided values exceeding the
maximum storage of the columns?
DECLARE @.t TABLE (
c char(5)
)
INSERT INTO @.t VALUES ( 'abcdefghi' )
--<--
Server: Msg 8152, Level 16, State 9, Line 4
String or binary data would be truncated.
The statement has been terminated.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi,
Actually, I don't do any checks for exceeding the storage space in the
string columns. I thought about that, but the strange is that this error
happens only sometimes. That is why I'm curious. And, as I wrote, it
works all fine in MS SQL Server 2000. I thought that the server just
trims the data and everything is OK.
Now, I'll make sure that I pass strings with length less than the
definition of the column, and I'll test again.
thanks

Error message help

I have a report parameter called @.OffenseCode and it is just a string
and also allows null values.
For some reason, I am getting the below error message, which I don't
know how to decipher. Any ideas?
"The value expression for the query parameter '@.OffenseCode'
contains an error: [BC30456] 'OffenseCode' is not a member of
'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters'."
Thank you!!!To access a Parameter value from an expression you will need to use the
following snytax.
=Parameters!@.OffenseCode.Value
if your parameter name is @.OffenseCode it may simply be OffenseCode in
which case you would need to use
=Parameters!OffenseCode.Value
You can check by going to the Report Menu, Report Parameters, this will
list all your parameters for the report and their names

error message 8152

When I am run a Stored Procedure -
dbo.sh_UploadPreTransfer task on SQL 7 and try to upload
PreTransfer tables, it showed a erroe message "String or
binary data would be truncated (message 8152)", could you
please give some advice? thanksIt means that your stored procedure is trying to insert more data than it
would fit into a character column. For example, you have a column called
TaskURL, which is of length char(128). But you try to insert a string of
length 200 characters, into this column, and you get this error.
It is bad that the error message will not show you which column is resulting
in the error.
You could use Profiler to trap the events SP:StmtStarting, and Error (with a
filter on Error for error number 8152), to see which statement is causing
the error.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"YanLing" <luo_yanling@.hotmail.com> wrote in message
news:02da01c3bcd7$e890a550$a001280a@.phx.gbl...
When I am run a Stored Procedure -
dbo.sh_UploadPreTransfer task on SQL 7 and try to upload
PreTransfer tables, it showed a erroe message "String or
binary data would be truncated (message 8152)", could you
please give some advice? thanks