Default Methods created with Drag & Drop attempt to edit Primary K

[Greg P]

I'm using VB 2005, and have drug and dropped datagridviews onto my form. The
default code that is generated for inserts tries to insert a value for the
auto generated primary key. Below is the generated code. I put ***** around
the problematic code. I've tried just removing this and it doesn't seem to
work. There may be more generated code I don't know about. I could remove
it but I am planning on doing a lot of refreshing of the data source which
would mean I would need to edit a ton of code each time I refreshed. Is this
just an oversight by microsoft? I would seem that VS 2005 would check to see
if a column is auto incremented and if it was it wouldn't try to insert a
value into it?

Is there a setting I can place on my table so that this is generated
correctly? My Idenitty specification for the ID column is seet to yes, with
a 1 increment. In my generated Update statments there are similar issues.
The auto generated code tires to update a primary key that has Identity
Specification set to yes. I would think This shouldn't happen? Is this just
an oversight by Microsoft?

<System.Diagnostics.DebuggerNonUserCodeAttribute() , _

System.ComponentModel.Design.HelpKeywordAttribute( "vs.data.TableAdapter"), _

System.ComponentModel.DataObjectMethodAttribute(Sy stem.ComponentModel.DataObjectMethodType.Insert, true)> _
Public Overloads Overridable Function Insert(ByVal CompositionID As
Integer, ByVal CompositionName As String, ByVal CompDesc As String, ByVal
Density As Double) As Integer
****** Me.Adapter.InsertCommand.Parameters(0).Value =
CType(CompositionID,Integer) *******
If (CompositionName Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(1).Value =
System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(1).Value =
CType(CompositionName,String)
End If
If (CompDesc Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(2).Value =
System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(2).Value =
CType(CompDesc,String)
End If
Me.Adapter.InsertCommand.Parameters(3).Value =
CType(Density,Double)
Dim previousConnectionState As System.Data.ConnectionState =
Me.Adapter.InsertCommand.Connection.State
If ((Me.Adapter.InsertCommand.Connection.State And
System.Data.ConnectionState.Open) _
<> System.Data.ConnectionState.Open) Then
Me.Adapter.InsertCommand.Connection.Open
End If
Try
Dim returnValue As Integer =
Me.Adapter.InsertCommand.ExecuteNonQuery
Return returnValue
Finally
If (previousConnectionState =
System.Data.ConnectionState.Closed) Then
Me.Adapter.InsertCommand.Connection.Close
End If
End Try
End Function

Thanks,
Greg

[Greg P]

Please do not preach about using an identity as a primary key in response to
this post. I understand that it isn't the best form. I understand that it
is "storing part of the application in the database", but I'm not ready to
rebuild my entire database at this time. Is there a way to get this to work
the way I want it to?

"Greg P" wrote:

I'm using VB 2005, and have drug and dropped datagridviews onto my form. The
default code that is generated for inserts tries to insert a value for the
auto generated primary key. Below is the generated code. I put ***** around
the problematic code. I've tried just removing this and it doesn't seem to
work. There may be more generated code I don't know about. I could remove
it but I am planning on doing a lot of refreshing of the data source which
would mean I would need to edit a ton of code each time I refreshed. Is this
just an oversight by microsoft? I would seem that VS 2005 would check to see
if a column is auto incremented and if it was it wouldn't try to insert a
value into it?

Is there a setting I can place on my table so that this is generated
correctly? My Idenitty specification for the ID column is seet to yes, with
a 1 increment. In my generated Update statments there are similar issues.
The auto generated code tires to update a primary key that has Identity
Specification set to yes. I would think This shouldn't happen? Is this just
an oversight by Microsoft?

<System.Diagnostics.DebuggerNonUserCodeAttribute() , _

System.ComponentModel.Design.HelpKeywordAttribute( "vs.data.TableAdapter"), _

System.ComponentModel.DataObjectMethodAttribute(Sy stem.ComponentModel.DataObjectMethodType.Insert, true)> _
Public Overloads Overridable Function Insert(ByVal CompositionID As
Integer, ByVal CompositionName As String, ByVal CompDesc As String, ByVal
Density As Double) As Integer
****** Me.Adapter.InsertCommand.Parameters(0).Value =
CType(CompositionID,Integer) *******
If (CompositionName Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(1).Value =
System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(1).Value =
CType(CompositionName,String)
End If
If (CompDesc Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(2).Value =
System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(2).Value =
CType(CompDesc,String)
End If
Me.Adapter.InsertCommand.Parameters(3).Value =
CType(Density,Double)
Dim previousConnectionState As System.Data.ConnectionState =
Me.Adapter.InsertCommand.Connection.State
If ((Me.Adapter.InsertCommand.Connection.State And
System.Data.ConnectionState.Open) _
<> System.Data.ConnectionState.Open) Then
Me.Adapter.InsertCommand.Connection.Open
End If
Try
Dim returnValue As Integer =
Me.Adapter.InsertCommand.ExecuteNonQuery
Return returnValue
Finally
If (previousConnectionState =
System.Data.ConnectionState.Closed) Then
Me.Adapter.InsertCommand.Connection.Close
End If
End Try
End Function

Thanks,
Greg

[Luke Zhang [MSFT]]

Hello,

Thank you for the posting. Currently we are finding proper resource to
assist you on this issue and will update you as soon as possible.

Regards,

Luke Zhang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Luke,

Thanx, I hope I wasn't too frank with the post on the Identity column bieing
a primary key. How would VB 2005 handle any identity column whether it is a
primary key or not?

Thanks,
Greg P

"Luke Zhang [MSFT]" wrote:

Hello,

Thank you for the posting. Currently we are finding proper resource to
assist you on this issue and will update you as soon as possible.

Regards,

Luke Zhang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Greg,

Yes, as you know, if the column in database is a Primary Key and is
auto-increment, VS.NET designer will not generate parameter for this
column. Because this column is readonly, and the value in it can only be
generated by SQL Server itself. This is by design, and unfortunately there
is no option that we can set to change the behavior.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Kevin,

That is exactly what I would expect, but not what I see. This should be a
very simple test, create a table that has an auto incremented indentity
column, use the datasource wizard, drag and drop a dataGridView and look at
the code. You will see in the insert and update that it refences the field
as shown in my code posted above.

Thanks for your time,
Greg

"Kevin Yu [MSFT]" wrote:

Hi Greg,

Yes, as you know, if the column in database is a Primary Key and is
auto-increment, VS.NET designer will not generate parameter for this
column. Because this column is readonly, and the value in it can only be
generated by SQL Server itself. This is by design, and unfortunately there
is no option that we can set to change the behavior.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Kevin,

Just to be sure I wasn't missing something I just ran this test and saw the
same result. Then I removed the table from the datasource and added it back
and it worked correctly. Maybe I don't understand when the designer code
gets refreshed? I thought that everytime you connected to the database the
designer code gets rewritten? The issue here was that I changed the database
after I had added the datasource. I then used the wizard to go and get those
changes and it didn't. I had to remove the table from the dataset and add it
again. Is this the expected behavior?

The reason I ask is if I change some of the designer code I thought it would
be overwritten when I refreshed the dataset though the wizard? If this isn't
the case then that gives me more flexability in changing some of the auto
generated code.

Thanks,

Greg P.

"Kevin Yu [MSFT]" wrote:

Hi Greg,

Yes, as you know, if the column in database is a Primary Key and is
auto-increment, VS.NET designer will not generate parameter for this
column. Because this column is readonly, and the value in it can only be
generated by SQL Server itself. This is by design, and unfortunately there
is no option that we can set to change the behavior.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Greg,

As I tried on my machine, the auto-increment columns will not be included
in the column list in insert and update commands. The generated code will
not get refreshed when the database table changes. To make the changes
reflect in DataSet designer, the only way to to go throught the wizard
again. Because drag and drop from the server explorer is only for designer
convenience. The client code is not assured to change with the server table.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Kevin,

I think this may be a bug then, becuase after I have changed the table and
then go through the designer again the generated code doesn't change. The
column is still there. The only way I can change it is to remove the table
through the designer and then re-add it. Is this the expected behavior?

Thanks,
Greg

"Kevin Yu [MSFT]" wrote:

Hi Greg,

As I tried on my machine, the auto-increment columns will not be included
in the column list in insert and update commands. The generated code will
not get refreshed when the database table changes. To make the changes
reflect in DataSet designer, the only way to to go throught the wizard
again. Because drag and drop from the server explorer is only for designer
convenience. The client code is not assured to change with the server table.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Greg,

If the column is already there, the wizard will not automatically remove
it. You can try to remove the table in the query build and re-add it. Or
remove the table in the DataSet designer and re-add it. They seem to be the
only way here.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Kevin,

I just wanted to make sure I follow up on this and I am clear. This isn't a
bug and this is the expected behavior for the dataset designer? If you make
changes to a table after you have already created a dataset you would have to:

1) Open the designer, uncheck table finsih the designer
2) Then reopen it again an select the table a second time and finish again.

I would have expected that if you have a table already in your Dataset and
then you selected the table through the dataset designer the first time, VS
would delete any generated code and regenerate the code based on the updated
table?

If not that cool too, I just want to make sure this isn't a bug and I
understand it correctly.

Thanks,
Greg

"Kevin Yu [MSFT]" wrote:

Hi Greg,

If the column is already there, the wizard will not automatically remove
it. You can try to remove the table in the query build and re-add it. Or
remove the table in the DataSet designer and re-add it. They seem to be the
only way here.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Greg,

Yes, this is not a bug an it is by design. And your steps are correct.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)