Error during sqlDataSource.Update() instruction

Hi, I have such a problem:

I try to update (or insert) a row in my table and i fail althought i,ve read many posts here. I.ve created a button and "on_click" event to this button and want that event to update or insert a record in my table. I wrote:

protected void selectButton_Click(object sender, EventArgs e)
{
String taskID = projectsGridView.SelectedRow.Cells[0].Text;
usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@.task where [UserID]=1";
usersSqlDataSource.UpdateParameters.Add("task", taskID);
usersSqlDataSource.Update();
}

The application creates error in the last line of code (usersSqlDataSource.Update();) and i receive such an error:

You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty.

Pass in a valid dictionary for update or change your mode to OverwriteChanges.

For me it looks like there is a problem while setting parameters. Shall i change some properties of the sqlDataSource or GridView? Please help.. 

Not seeing the code for your SqlDataSource, it's hard to tell. However, I suspect that you need to change the SqlDataSource.ConflictDetection property to OverwriteChanges instead of CompareAllValues.

|||

Yes!! It works!!!

The beginnings are difficult, thank you very much ;)

Error Description differs when logged with Redirect Rows compared with Debug mode

Hi,

Can any one please tell me how to get the complete error description for example when i dont Redirect Row for Error in OLEDB Source i get a detailed error message with column name as

[RCheck [385]] Error: There was an error with input column "CHECK_STATUS" (456) on input "OLE DB Destination Input" (398). The column status returned was: "The value could not be converted because of a potential loss of data.".

But when I set Redirect Row for error and use the Script component to log them into a Table with ErrorDescription based on ErrorColumnID it only gives me this.

The data value cannot be converted for reasons other than sign mismatch or data overflow.

Thanks

Sat

Can't make the FailComponent and RedirectRow error messages the same with stock OLEDB source component.

If you want to actually redirect AND get the original provider error message that you would get in Fail Component mode, well, that component has yet to be written by Microsoft.

Instead, as you're seeing, when performing redirection using the OLEDB source; only the generic ErrorCode => generic textual description is exposed via ComponentMetaData.GetErrorDescription().

With a custom component of course, you can amend error outputs with additional columns, which is exactly what I have had to do to circumvent this problem.

Error Code if Row is locked

Hallo,
if a row is locked in SQLExpress and an other user want to update this row,
SQLExpress waits until timeout. And than I get the errormessage "timeout".

What I need is an immediately errormessage "row is locked" !

So that the user dont need to wait if the row is locked
and I need the correct errormessage so that I can check
if there is a databaseproblem or only the row locked
because an other user is editing the same rows.

Thx Wolfgang
(please excuse my english)

hi,

SQL Server does not provide this feature as this should be managed on the client side of the application and not server side on SQL Server...

you can thus set an appropriate timeout on the ado/ado.net command you are using, and then take the required decision once the command result in an exception..

but you have to fine tune your timeout, depending on SQL Server load, bandwidth, network trafic, ..

regards

|||

Thank you for your help!

I need a small timeout if the Updatecommand "meets" locked rows.
I do not want to reduce the timeout for the SqlCommand itself.

The solution i have found is to use the keyword NOWAIT in the SQL-Statement.

UPDATE table WITH (NOWAIT) SET ......

I think this works okay, but I have not found something to set timeout for locks to 1 sec
and timeout for SQLCommand to 15 sec.

Thany you for your help.
Wolfgang

Error catch in SQL

Hi everyone, I am using an SQL extended stored procedure to send emails in a
DTS package using a cursor that goes through each row in a table.

Email sending code below
======================
exec master.dbo.xp_smtp_sendmail
@.FROM = @.sFrom,
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
======================
Fetch Next From EmailCursor ...

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would like
to be able to do is "catch the error", something like this (C# code used as
example)

try
{
exec master.dbo.xp_smtp_sendmail
@.FROM = @.sFrom,
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
} catch {
exec master.dbo.xp_smtp_sendmail
@.FROM = "arealaddress@.mybusiness.com",
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
}

Is this possible? Normally I would do all the email validation before the
email is entered into the database but unfortunately, I do not have access
to the application code so I am stuck doing it this way.

Thanks in advance
MarkMark (markjones@.n0Sp8mTAIRAWHITIdotAC.NZ) writes:
> Now the problem I have is that if an individual email address in invalid
> then an error occurs and the whole DTS package falls over. What I would
> like to be able to do is "catch the error", something like this (C# code
> used as example)

In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
coming version SQL2005 has the TRY CATCH you are looking for.

Presumably you should be able to have the DTS package to swallow the
error, but I don't know DTS so I can't give any details. The nice and
friendly people in microsoft.public.sqlserver.dts may have some ideas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland
Regards
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9554F1DAF21E5Yazorman@.127.0.0.1...
> Mark (markjones@.n0Sp8mTAIRAWHITIdotAC.NZ) writes:
> > Now the problem I have is that if an individual email address in invalid
> > then an error occurs and the whole DTS package falls over. What I would
> > like to be able to do is "catch the error", something like this (C# code
> > used as example)
> In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
> coming version SQL2005 has the TRY CATCH you are looking for.
> Presumably you should be able to have the DTS package to swallow the
> error, but I don't know DTS so I can't give any details. The nice and
> friendly people in microsoft.public.sqlserver.dts may have some ideas.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

error Cannot create a row of size xxxx - my fix doesnt work

Been doing some research after getting this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Cannot create a row of size 8297 which is greater than the allowable maximum of 8060.

And realised that my nice responsive varchars had a maximum total size. so I changed them for 'slower' text data types. but my DB still won't allow any more input.

has the limit been reached now regardless of what I change or can I rebuild the DB to recover the space or something?First, what version are you on? 2000 or 2005?

Second, post the DDL for your tables with the 8000 byte columns.|||That message is only a warning. The table is still created, I believe.

EDIT: Unless you are actually trying to stuff that much data in a row. Then it becomes an error.|||That message is only a warning. The table is still created, I believe.

EDIT: Unless you are actually trying to stuff that much data in a row. Then it becomes an error.

True enough...but it can't be a good design|||well you get no prizes for guessing it's not the best designed DB in the world, I basically said that at the top. But it has been going fine for two years delivering words to a huge web site, right up to the point where it filled up.
so I changed some data types from varchar to text in the hope of freeing up some space but it still seems to be full.
so back to my Q: is it possible that I need to do something else to free up the space or is it something els?
I think the DB is 2000 (2005 doesn't suffer from this same varchar limit does it?)
here's the DDL - could well be all wrong, but that's why I'm here.
[id] int IDENTITY(1, 1) NOT NULL,
[dest_url] varchar(32),
[country] varchar(32),
[continent] varchar(16),
[intro] varchar(2048),
[quote1] varchar(512),
[title1] varchar(512),
[body1] varchar(2048),
[quote2] varchar(512),
[title2] varchar(512),
[body2] varchar(2048),
[quote3] varchar(512),
[title3] varchar(64),
[body3] text,
[quote4] varchar(512),
[title4] varchar(64),
[body4] text,
[quote5] text,
[title5] varchar(64),
[body5] varchar(2048),
[quote6] varchar(512),
[image1] varchar(255),
[image2] varchar(255),
[image3] varchar(255),
[image4] varchar(255),
[image5] varchar(255),
[image6] varchar(255),
[submissionDate] smalldatetime,
[altTag1] varchar(255),
[altTag2] varchar(255),
[altTag3] varchar(255),
[altTag4] varchar(255),
[altTag5] varchar(255),
[altTag6] varchar(255),
I understood that text data types are slow and varchar is fast and only uses the space it needs but I suspect that is wrong.|||Holy denormalization, Batman!

Yeah, that is a mess and really needs to be normalized into subtables.
Let me guess...you can't change the schema, right?

Or you could upgrade to 2005 and use the new varchar(max) datatype.
But let me guess...upgrading is not an option right now, right?

The maximum data size of a record cannot exceed 8000 bytes (give or take). Replacing some of your columns with text datatypes should have resolved the issue for you, as the text pointers are only 16 bytes.

Post the DDL for your table after you changed the datatypes.|||So that's considered a big ugly table then? you live and learn, I thought SQL Server was immensely powerful and nothing I chucked at it would give it a problem
When I 'designed' it, it seemed like all the columns were not duplicated or needed anywhere else so should all go into one table.
I don't really know about normalisation, can it be done retrospectively (and link internally) or will I have to change all my ASP code to find all the different tables?
In any case, back to the original problem, is there a 'rebuild' option somewhere to reclaim empty space?
Seems my DB is V7! could be upgraded to 2000 (oooo wow, thanks a bunch Mr. host)|||you seem to have missed this crucial request of blindman's:

"Post the DDL for your table after you changed the datatypes."|||That table should really be three or 4 tables

Anytime you see a column with a name that ends in an incrementing #, thos solumns should be 1 column in a child table...not only is it a better design, it provides you with more flexibility in case you need more than n rows

You basically are making rows into columns in your design

So I guess, I guessed right|||Thanks Brett, you guessed right. I know nothing.
OK so normalising my table will also free up space because the records will be shorter, is that right?
But this table only holds data on a country page and the incrementing numbers just tell the page where to put the block of text. It seems massive overkill (in itself) to create a table for each column (if it has a #), it's basically unique data which isn't called up anywhere else. and wouldn't I have to do complex joins etc or at least new sql strings to create the entire page.
I don't really want to go back and rewrite the CMS code and it just seems wrong when I have a very simple database that just happens to have about 5000 chars of text for some countries.

Isn't this a fairly crappy limitation for an expensive 'enteprise level database' ?

I didn't repost my DDL because the first one WAS the new layout with the text fields put in to try and free up some space. They used to be varchar(2048).|||Splendid intro to database design & normalisation. I plan to work my way through his site myself.
Problem you are having is specificly with first normal form.
http://www.tonymarston.net/php-mysql/database-design.html

Also - even though you have introduced text if you total up all the VARCHARS you still well exceed 8000 bytes.|||Well, rewriting code would suck...BUT...you could normalize the data, THEN create a view that looks like your existing table, and name the view what the name of the existing table is...no rewrite needed

This is how I would have designed this...without really knowing what the app does

CREATE TABLE MAIN (
[Mainid] int IDENTITY(1, 1) NOT NULL,
[dest_url] varchar(32),
[country] varchar(32),
[continent] varchar(16),
[intro] varchar(2048),
[submissionDate] smalldatetime,
)

CREATE TABLE Body (
[BodyId] int,
[Mainid] int,
[quote] varchar(512),
[title] varchar(512),
[body] varchar(2048)
)

CREATE TABLE Images (
[ImageId] int,
[Mainid] int,
[image] varchar(255)
)

CREATE TABLE Tags (
[TagId] int,
[Mainid] int,
[altTag] varchar(255)
)|||OH, and just because there's an 8k limit is a physical limitation at the time because of the page size...they removed this in 2005

Your easiest path to a soultion really is an upgrade to 2k5

BUT..this is a very good example of how important data modeling is before app development|||excellent resource thanks.
but then the answer to my Q is yes I will need to rewrite my SQL to join all the split tables.
and No, it isn't really necessary to split up my table because the data is unique and only inputted once.
and yes, SQL Server 7 sucks and I need a new DB host.
And Yes, I can be a bit slow - I now see what is meant by the various limits and how all my varchars have stacked up.
ta|||OK so normalising my table will also free up space because the records will be shorter, is that right?Normalising will not free up space. In fact, it will likely require more "space", but the size of each individual record will be reduced and thus you will not be hitting the 8k data limit per row.
It seems massive overkill (in itself) to create a table for each column (if it has a #),You should create a subtable to hold the Quote, Title, and Body data.
You should create another subtable to hold image data.
You should create another subtable to hold altTag data.
If image and tag data are related, both should be stored in the same table.
What you call overkill, we call good design.
I don't really want to go back and rewrite the CMS code and it just seems wrong when I have a very simple database that just happens to have about 5000 chars of text for some countries.Lesson learned: design the database first. Write the application code second.
Isn't this a fairly crappy limitation for an expensive 'enteprise level database' ?Sorry, but your ignorance of the product is not a reflection upon its quality.
I didn't repost my DDL because the first one WAS the new layout with the text fields put in to try and free up some space. They used to be varchar(2048).So change the remaining varchar(2048) columns to text. The point is, you should be able to add up the maximum sizes of all your data columns and stay under 8k.|||and yes, SQL Server 7 sucks and I need a new DB host.No. It is your database design that sucks. SQL Server 7 could easily handle these application requirements with a properly designed schema. Stop blaming the software and/or hardware for your poor design.|||You should create a subtable to hold the Quote, Title, and Body data.
You should create another subtable to hold image data.
You should create another subtable to hold altTag data.

Brilliant

The message you enetered is too short|||No, it isn't really necessary to split up my table because the data is unique and only inputted once.No - it isn't necessary but also that isn't the definition of good design. I could put the universe into a single single table single column single row database but it would not be good design. Uniqueness is not enough.

Anyway - see how you get on with the article :)|||This is 7???

I thought M$ stop supporting that...or is that <7

Oh, and blind dude, you forgot a Main table|||EAV...EAV...EAV...

http://www.dbforums.com/showthread.php?t=1619660

and OTLT...OTLT...OTLT|||looks like our last two posts overlapped.
I now get it.
but if that limitation is acceptable why has MS changed it in V-2005?

if you have a moment for us newbies, Can you just clarify: if I was using 2005 or the correct data types for this amount of text is there any other reason to complicate the structure on my table?
as splitting a table of unique data only used on one page still seems like overkill even on a good design.|||Oh, and blind dude, you forgot a Main tableNo. I said he should create those tables. He already has a main table.

I could put the universe into a single single table single column single row database but it would not be good design.Using the XML datatype, I assume? I can just see one of my developers coming up with this design.|||as splitting a table of unique data only used on one page still seems like overkill even on a good design.The thing to remember is that normalisation principles are nothing to do with the size of datapages, or anything physical. They are methodologies designed to eliminate (or at least minimise) data modification anomolies. They also typically result in much more easily queried data structures.|||Using the XML datatype, I assume? I can just see one of my developers coming up with this design.

Funny you mentioned that...I just went through this..they pass me XML, but damned if I was gonna store this as XML

DECLARE @.idoc int

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.ReqData

CREATE TABLE #FutureRent (
[Year] [int] NOT NULL
, [NumMonths] [int] NOT NULL
, [NetRate] [money] NULL
, [OtherExp] [money] NULL
, [A_D] [money] NULL
, [FreeRent] [money] NULL
)

INSERT INTO #FutureRent (
[Year]
, [NumMonths]
, [NetRate]
, [OtherExp]
, [A_D]
, [FreeRent])
SELECT *
FROM OPENXML (@.idoc, '/FutureRent/Data',1)
WITH(
[Year] [int]
, [NumMonths] [int]
, [NetRate] [money]
, [OtherExp] [money]
, [A_D] [money]
, [FreeRent] [money]
)

IF EXISTS (SELECT * FROM FutureRent WHERE MEPRecID = @.MEPRecId)
UPDATE a SET
a.[NumMonths] = b.[NumMonths]
, a.[NetRate] = b.[NetRate]
, a.[OtherExp] = b.[OtherExp]
, a.[A_D] = b.[A_D]
, a.[FreeRent] = b.[FreeRent]
, a.[ModifiedBy] = @.APPUSER
, a.[ModifiedDate]= GetDate()
FROM FutureRent a
INNER JOIN #FutureRent b
ON a.[MEPRecID] = @.MEPRecID
AND a.[Year] = b.[Year]
ELSE
INSERT INTO FutureRent (
[MEPRecID]
, [Year]
, [NumMonths]
, [NetRate]
, [OtherExp]
, [A_D]
, [FreeRent]
, [CreatedBy]
, [CreateDate]
, [ModifiedBy]
, [ModifiedDate])
SELECT
@.MEPRecID
, [Year]
, [NumMonths]
, [NetRate]
, [OtherExp]
, [A_D]
, [FreeRent]
, @.APPUSER
, GetDate()
, @.APPUSER
, GetDate()
FROM #FutureRent|||Using the XML datatype, I assume? I can just see one of my developers coming up with this design.

better to base64 encode the xml and store the entire blob as varchar(max). that way you are "secure" too! do I win?

:)|||Jez, you are scaring me. One of the projects I am working on is doing exactly what you describe.|||haha! I guess you win then. :)