VB.NET Complex table DataGrid Update Problem

  • Thread starter Thread starter Helen
  • Start date Start date
H

Helen

I'm looking for your advice and maybe help with this issue.



So, this is my saga.



1). I got to create a grid, which would look preety much
like an Excel spread sheet. I did it and that's
schematically how it looks (Forget about id for now. It's
Current over New Status):

New Status

id Current Status 010 020 030 040 050
...
1 010 010 020 '030 040 050 ...
2 020 010 020 030 030 030 ...
3 030 040 030 030 040 050 ...
4 040 010 020 180 050 050 ...
5 050 010 020 360 040 050 ...
.... ... ... ... ... ... ... .






2). This grid is a dynamical grid. To do this I had to
create a table. I wrote a SP which "united" 2 tables
which didn't have any keys to unite them. From the SQL
SERVER table tlkpStatus I took all codes to create
a "frame" of my grid, to create just rows' and columns'
headers. Then the content of the grid came from the table
tlkpStatusUpdateRule, ResultStatus column. + Knowing that
SQLCommandBuilder needs the Primary Key, I've created in
my table additional column ("id"). This is my store
procedure to create the whole table:

CREATE PROCEDURE dbo.spx_SummaryStatusUpdate

AS

create table tblSummaryStatus (id int identity primary
key , [Current] varchar(3) not null)

declare @sql varchar(8000)

declare @v char(3)

select @v = ''

while @v < (select max(code) from tlkpStatus)

begin

select @v = min(code) from tlkpStatus where code > @v

select @sql = 'alter table tblSummaryStatus add [' + @v
+ '] char(3) null'

exec (@sql)

end

select @sql = 'insert tblSummaryStatus ([Current]) select
code from tlkpStatus'

exec (@sql)

/* Updating */

select identity(int,1,1) id, i = [CurrentStatus], j =
[NewStatus] into #a from tlkpStatusUpdateRule

declare @id int

select @id = 0

while @id < (select max(id) from #a)

begin

select @id = min(id) from #a where id > @id

select @sql = 'update tblSummaryStatus set [' + j + '] =
ResultStatus from tlkpStatusUpdateRule where
tlkpStatusUpdateRule.[CurrentStatus] =

''' + i + ''' and tlkpStatusUpdateRule.[NewStatus] = ''' +
j + ''' and tblSummaryStatus.[Current] = ''' + i + ''''
from #a where id = @id

exec (@sql)

end

drop table #a

/*end Updating*/

/* Just for my testing purposes

Select * from tblSummaryStatus

Select * from tlkpStatusUpdateRule */

GO

-----------------------------------------------------------
------------

3). Everything works fine here. Now when I make some
changes to the DataSet and click the UPDATE button, I get
this error in VB.NET:

" An unhandled exception of type
System.Data.SqlClient.Exception occurred in
System.data.dll"

And my BREAK point stops here data_adapter.Update
(m_DataSet)

As you recommended, I went to the SQL Server Profile and
traced. I got this Update statement. This is just a
fragment:

"exec sp_executesql N'UPDATE tblSummaryStatus SET 000 =
@p1 WHERE ( (id = @p2) AND ((Current IS NULL AND @p3 IS
NULL) ...."

Instead, it should say:

"exec sp_executesql N'UPDATE tblSummaryStatus SET [000]
= ' @p1' WHERE ( (id = @p2) ..."

Could you please help me and let me know what should I do.

Thank you very much. Appreciate in advance.

Helen.
 
Hi Helen,

Why don't you rather build adapter at design time and see by yourself the
code?
 
Oh, thank you very much for your reply.
See, I'm a very new for the VB.NET. I used to work with asp, asp.net,
SQL Server, Java.

1. How should I build an adapter at the design time?
2. How will it resolve my update problem? I used the SqlCommandBuilder
to update the DataGrid.

Thanks again, Miha. I really appreciate it.
 
Helen, it looks like your problem might not be the grid at all but your
database design. It looks like you need to read a bit about normalization.
Your columns look like repeating series that could/should be placed in a
separate table.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
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.
__________________________________

Helen said:
I'm looking for your advice and maybe help with this issue.



So, this is my saga.



1). I got to create a grid, which would look preety much
like an Excel spread sheet. I did it and that's
schematically how it looks (Forget about id for now. It's
Current over New Status):

New Status

id Current Status 010 020 030 040 050
...
1 010 010 020 '030 040 050 ...
2 020 010 020 030 030 030 ...
3 030 040 030 030 040 050 ...
4 040 010 020 180 050 050 ...
5 050 010 020 360 040 050 ...
... ... ... ... ... ... ... .






2). This grid is a dynamical grid. To do this I had to
create a table. I wrote a SP which "united" 2 tables
which didn't have any keys to unite them. From the SQL
SERVER table tlkpStatus I took all codes to create
a "frame" of my grid, to create just rows' and columns'
headers. Then the content of the grid came from the table
tlkpStatusUpdateRule, ResultStatus column. + Knowing that
SQLCommandBuilder needs the Primary Key, I've created in
my table additional column ("id"). This is my store
procedure to create the whole table:

CREATE PROCEDURE dbo.spx_SummaryStatusUpdate

AS

create table tblSummaryStatus (id int identity primary
key , [Current] varchar(3) not null)

declare @sql varchar(8000)

declare @v char(3)

select @v = ''

while @v < (select max(code) from tlkpStatus)

begin

select @v = min(code) from tlkpStatus where code > @v

select @sql = 'alter table tblSummaryStatus add [' + @v
+ '] char(3) null'

exec (@sql)

end

select @sql = 'insert tblSummaryStatus ([Current]) select
code from tlkpStatus'

exec (@sql)

/* Updating */

select identity(int,1,1) id, i = [CurrentStatus], j =
[NewStatus] into #a from tlkpStatusUpdateRule

declare @id int

select @id = 0

while @id < (select max(id) from #a)

begin

select @id = min(id) from #a where id > @id

select @sql = 'update tblSummaryStatus set [' + j + '] =
ResultStatus from tlkpStatusUpdateRule where
tlkpStatusUpdateRule.[CurrentStatus] =

''' + i + ''' and tlkpStatusUpdateRule.[NewStatus] = ''' +
j + ''' and tblSummaryStatus.[Current] = ''' + i + ''''
from #a where id = @id

exec (@sql)

end

drop table #a

/*end Updating*/

/* Just for my testing purposes

Select * from tblSummaryStatus

Select * from tlkpStatusUpdateRule */

GO

-----------------------------------------------------------
------------

3). Everything works fine here. Now when I make some
changes to the DataSet and click the UPDATE button, I get
this error in VB.NET:

" An unhandled exception of type
System.Data.SqlClient.Exception occurred in
System.data.dll"

And my BREAK point stops here data_adapter.Update
(m_DataSet)

As you recommended, I went to the SQL Server Profile and
traced. I got this Update statement. This is just a
fragment:

"exec sp_executesql N'UPDATE tblSummaryStatus SET 000 =
@p1 WHERE ( (id = @p2) AND ((Current IS NULL AND @p3 IS
NULL) ...."

Instead, it should say:

"exec sp_executesql N'UPDATE tblSummaryStatus SET [000]
= ' @p1' WHERE ( (id = @p2) ..."

Could you please help me and let me know what should I do.

Thank you very much. Appreciate in advance.

Helen.
Click to expand...
 
Hi Helen,

Helen Finkel said:
Oh, thank you very much for your reply.
See, I'm a very new for the VB.NET. I used to work with asp, asp.net,
SQL Server, Java.

1. How should I build an adapter at the design time?
Click to expand...

Connect database in Server Explorer window (normally on the left side of
VS.NET).
Drag&Drop a table on the form - there it is.
You can look through generated code and changed it if you want.
2. How will it resolve my update problem? I used the SqlCommandBuilder
to update the DataGrid.
Click to expand...

It will help you to see the generated code and will let you modify it as you
wish.
Thanks again, Miha. I really appreciate it.
Click to expand...

You might read more about ado.net data access in
Accessing Data with ADO.NET
..net help chapter

HTH,
 
Hi Bill,
I didn't know you're on line.
1. Tables I work with are absolutely not normalized.
They don't even have primary keys. Unfortunately, I can't
change the design. I needed to create a cross product of
those 2 tables. I wrote the SP, as you probably saw, to
create one table with the cross product and a primary key
of INT type.
2. I send an Excel table as a schema. There are no
repeating series. Some times the ResultStatus, or cross
product, is the same as the NewStatus, some times it can
be whatever number.
3. Like I wrote in my first message, the initial tables
don't have integers. Everything is CHAR type.
That's why the Update Statement(see below in my first
message), which SqlCommandBuilder created is wrong.
It updates, saying
Update tbl set A = B where id=C,
Instead of saying
Update tbl set [A] = 'B' where id=C
-----Original Message-----
Helen, it looks like your problem might not be the grid at all but your
database design. It looks like you need to read a bit about normalization.
Your columns look like repeating series that could/should be placed in a
separate table.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
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.
__________________________________

Helen said:
I'm looking for your advice and maybe help with this issue.



So, this is my saga.



1). I got to create a grid, which would look preety much
like an Excel spread sheet. I did it and that's
schematically how it looks (Forget about id for now. It's
Current over New Status):

New Status

id Current Status 010 020 030 040 050
...
1 010 010 020 '030 040 050 ...
2 020 010 020 030 030 030 ...
3 030 040 030 030 040 050 ...
4 040 010 020 180 050 050 ...
5 050 010 020 360 040 050 ...
... ... ... ... ... ... ... .






2). This grid is a dynamical grid. To do this I had to
create a table. I wrote a SP which "united" 2 tables
which didn't have any keys to unite them. From the SQL
SERVER table tlkpStatus I took all codes to create
a "frame" of my grid, to create just rows' and columns'
headers. Then the content of the grid came from the table
tlkpStatusUpdateRule, ResultStatus column. + Knowing that
SQLCommandBuilder needs the Primary Key, I've created in
my table additional column ("id"). This is my store
procedure to create the whole table:

CREATE PROCEDURE dbo.spx_SummaryStatusUpdate

AS

create table tblSummaryStatus (id int identity primary
key , [Current] varchar(3) not null)

declare @sql varchar(8000)

declare @v char(3)

select @v = ''

while @v < (select max(code) from tlkpStatus)

begin

select @v = min(code) from tlkpStatus where code > @v

select @sql = 'alter table tblSummaryStatus add [' + @v
+ '] char(3) null'

exec (@sql)

end

select @sql = 'insert tblSummaryStatus ([Current]) select
code from tlkpStatus'

exec (@sql)

/* Updating */

select identity(int,1,1) id, i = [CurrentStatus], j =
[NewStatus] into #a from tlkpStatusUpdateRule

declare @id int

select @id = 0

while @id < (select max(id) from #a)

begin

select @id = min(id) from #a where id > @id

select @sql = 'update tblSummaryStatus set [' + j + '] =
ResultStatus from tlkpStatusUpdateRule where
tlkpStatusUpdateRule.[CurrentStatus] =

''' + i + ''' and tlkpStatusUpdateRule.[NewStatus] = ''' +
j + ''' and tblSummaryStatus.[Current] = ''' + i + ''''
from #a where id = @id

exec (@sql)

end

drop table #a

/*end Updating*/

/* Just for my testing purposes

Select * from tblSummaryStatus

Select * from tlkpStatusUpdateRule */

GO

-------------------------------------------------------- ---
------------

3). Everything works fine here. Now when I make some
changes to the DataSet and click the UPDATE button, I get
this error in VB.NET:

" An unhandled exception of type
System.Data.SqlClient.Exception occurred in
System.data.dll"

And my BREAK point stops here data_adapter.Update
(m_DataSet)

As you recommended, I went to the SQL Server Profile and
traced. I got this Update statement. This is just a
fragment:

"exec sp_executesql N'UPDATE tblSummaryStatus SET 000 =
@p1 WHERE ( (id = @p2) AND ((Current IS NULL AND @p3 IS
NULL) ...."

Instead, it should say:

"exec sp_executesql N'UPDATE tblSummaryStatus SET [000]
= ' @p1' WHERE ( (id = @p2) ..."

Could you please help me and let me know what should I do.

Thank you very much. Appreciate in advance.

Helen.
Click to expand...


.
Click to expand...
 
Back
Top