DataGrid does not recognize that ID is an identity

  • Thread starter Thread starter Douglas Buchanan
  • Start date Start date
D

Douglas Buchanan

What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR [ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR [hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource ORDER BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
 
Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity key, since
you only get 256 values.
 
Earl,

With all due respects, you did not address my question (see the
subject line).

My question had to do with why my configuration does not allow me to
enter data into the dataGrid. This has everything to do with why my
dataSet does not recognize the ID as an identity. It is likely related
to why the dataSet does not recognize two of the fields as having
default values.

The problem was not my fill code.
My fill code follows the same structure as yours.

Mine: Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")

Yours: da.Fill(ds, "dt")

The problem was unlikely the way I built...
(a) the DataAdapter or the connection string - becuase I dragged the
table to the form.
(b) the SqlCommand - because I had the wizard build it.
(c) the DataSet - because I had the wizard build that too.

The problem I believe had something to do with some setting I failed
to make or am not aware of or (less likely) some glitch.

As an aside:
Tinyint is used because the number of records in this particular table
will max out at less than twenty-five.

Again. what is the problem with the dataset? (see original post for
background data).


Earl said:
Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity key, since
you only get 256 values.



Douglas Buchanan said:
What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR [ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR [hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource ORDER BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
Click to expand...
Click to expand...
 
I'm sure I found the problem. I just don't know how it happened.

The VB.Net Data Adapter Configuration Wizard sometimes creates the
incorrect syntax for the Insert stored proceedure.

It is all in the stored proceedure. First of all I sent the wrong
stored proceedure with the original post. I sent the

'Select' rather than the 'Insert' Stored Proceedure. It is the
'Insert' that controls writing back new records to the database.

Observe the difference between the two stored procedures.

===== The Stored Procedure in error ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSourceID tinyint,
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSourceID, RefSource, ord, hide) VALUES
(@RefSourceID, @RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @RefSourceID) ORDER BY ord,

RefSource
GO

===== A Correct Stored Procedure ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide)
VALUES (@RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide
FROM lkp01RefSource WHERE (RefSourceID = @@IDENTITY)
GO

===========================================
The error was generated by the VB.Net Data Adapter Configuration
Wizard. I have had the Wizard create the store proceedure both
correctly and incorrectly. I did several experiments creating the
stored proceedures in different ways. I was not able to reproduce the
problem.

I can only throw out the caution that the Data Adapter Configuration
Wizard sometimes creates the Insert proceedure with the incorrect
syntax. Incidentally the syntax checker cannot detect errors on this
level.

Good luck all,
Doug

Earl,

With all due respects, you did not address my question (see the
subject line).

My question had to do with why my configuration does not allow me to
enter data into the dataGrid. This has everything to do with why my
dataSet does not recognize the ID as an identity. It is likely related
to why the dataSet does not recognize two of the fields as having
default values.

The problem was not my fill code.
My fill code follows the same structure as yours.

Mine: Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")

Yours: da.Fill(ds, "dt")

The problem was unlikely the way I built...
(a) the DataAdapter or the connection string - becuase I dragged the
table to the form.
(b) the SqlCommand - because I had the wizard build it.
(c) the DataSet - because I had the wizard build that too.

The problem I believe had something to do with some setting I failed
to make or am not aware of or (less likely) some glitch.

As an aside:
Tinyint is used because the number of records in this particular table
will max out at less than twenty-five.

Again. what is the problem with the dataset? (see original post for
background data).


Earl said:
Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity key, since
you only get 256 values.



Douglas Buchanan said:
What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR [ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR [hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource ORDER BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
Click to expand...
Click to expand...
Click to expand...
 
Here seems to be the reason the Stored Proceedure is written
incorrectly by the Wizard:
If the table's ID is named anything other than 'ID' the Data Adapter
Configuration Wizard will not recognize it as the ID and will write
the 'Insert' stored proceedure incorrectly.

Doug

I'm sure I found the problem. I just don't know how it happened.

The VB.Net Data Adapter Configuration Wizard sometimes creates the
incorrect syntax for the Insert stored proceedure.

It is all in the stored proceedure. First of all I sent the wrong
stored proceedure with the original post. I sent the

'Select' rather than the 'Insert' Stored Proceedure. It is the
'Insert' that controls writing back new records to the database.

Observe the difference between the two stored procedures.

===== The Stored Procedure in error ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSourceID tinyint,
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSourceID, RefSource, ord, hide) VALUES
(@RefSourceID, @RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @RefSourceID) ORDER BY ord,

RefSource
GO

===== A Correct Stored Procedure ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide)
VALUES (@RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide
FROM lkp01RefSource WHERE (RefSourceID = @@IDENTITY)
GO

===========================================
The error was generated by the VB.Net Data Adapter Configuration
Wizard. I have had the Wizard create the store proceedure both
correctly and incorrectly. I did several experiments creating the
stored proceedures in different ways. I was not able to reproduce the
problem.

I can only throw out the caution that the Data Adapter Configuration
Wizard sometimes creates the Insert proceedure with the incorrect
syntax. Incidentally the syntax checker cannot detect errors on this
level.

Good luck all,
Doug

Earl,

With all due respects, you did not address my question (see the
subject line).

My question had to do with why my configuration does not allow me to
enter data into the dataGrid. This has everything to do with why my
dataSet does not recognize the ID as an identity. It is likely related
to why the dataSet does not recognize two of the fields as having
default values.

The problem was not my fill code.
My fill code follows the same structure as yours.

Mine: Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")

Yours: da.Fill(ds, "dt")

The problem was unlikely the way I built...
(a) the DataAdapter or the connection string - becuase I dragged the
table to the form.
(b) the SqlCommand - because I had the wizard build it.
(c) the DataSet - because I had the wizard build that too.

The problem I believe had something to do with some setting I failed
to make or am not aware of or (less likely) some glitch.

As an aside:
Tinyint is used because the number of records in this particular table
will max out at less than twenty-five.

Again. what is the problem with the dataset? (see original post for
background data).


Earl said:
Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity key, since
you only get 256 values.



What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR [ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR [hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource ORDER BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
Click to expand...
Click to expand...
Click to expand...
 
The DACW does not create perfect Insert or Update code, but it gets you in
the ballpark. Among other idiosyncrasies, it tries to insert timestamps. But
I've had no problems with it determining what the ID column was --
regardless of how it is named.

Douglas Buchanan said:
Here seems to be the reason the Stored Proceedure is written
incorrectly by the Wizard:
If the table's ID is named anything other than 'ID' the Data Adapter
Configuration Wizard will not recognize it as the ID and will write
the 'Insert' stored proceedure incorrectly.

Doug

(e-mail address removed) (Douglas Buchanan) wrote in message
I'm sure I found the problem. I just don't know how it happened.

The VB.Net Data Adapter Configuration Wizard sometimes creates the
incorrect syntax for the Insert stored proceedure.

It is all in the stored proceedure. First of all I sent the wrong
stored proceedure with the original post. I sent the

'Select' rather than the 'Insert' Stored Proceedure. It is the
'Insert' that controls writing back new records to the database.

Observe the difference between the two stored procedures.

===== The Stored Procedure in error ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSourceID tinyint,
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSourceID, RefSource, ord, hide) VALUES
(@RefSourceID, @RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @RefSourceID) ORDER BY ord,

RefSource
GO

===== A Correct Stored Procedure ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide)
VALUES (@RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide
FROM lkp01RefSource WHERE (RefSourceID = @@IDENTITY)
GO

===========================================
The error was generated by the VB.Net Data Adapter Configuration
Wizard. I have had the Wizard create the store proceedure both
correctly and incorrectly. I did several experiments creating the
stored proceedures in different ways. I was not able to reproduce the
problem.

I can only throw out the caution that the Data Adapter Configuration
Wizard sometimes creates the Insert proceedure with the incorrect
syntax. Incidentally the syntax checker cannot detect errors on this
level.

Good luck all,
Doug

(e-mail address removed) (Douglas Buchanan) wrote in message
Earl,

With all due respects, you did not address my question (see the
subject line).

My question had to do with why my configuration does not allow me to
enter data into the dataGrid. This has everything to do with why my
dataSet does not recognize the ID as an identity. It is likely related
to why the dataSet does not recognize two of the fields as having
default values.

The problem was not my fill code.
My fill code follows the same structure as yours.

Mine: Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")

Yours: da.Fill(ds, "dt")

The problem was unlikely the way I built...
(a) the DataAdapter or the connection string - becuase I dragged the
table to the form.
(b) the SqlCommand - because I had the wizard build it.
(c) the DataSet - because I had the wizard build that too.

The problem I believe had something to do with some setting I failed
to make or am not aware of or (less likely) some glitch.

As an aside:
Tinyint is used because the number of records in this particular table
will max out at less than twenty-five.

Again. what is the problem with the dataset? (see original post for
background data).


Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity key,
since
you only get 256 values.



What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR [ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR [hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and
dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource ORDER
BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
Click to expand...
Click to expand...
Click to expand...
 
Earl,

Do you use the DACW or resort to code alone?

What do you do about it's mistakes in creating DataSets?
For instance if I choose to edit the Insert stored procedure with
Scope_Identity() in stead of @@Identity DACW creates the dataset with
'AutoIncrement' = FALSE

This results in a DataSet that CANNOT successfully add new records to
in the datagrid.

I don't know how to edit the 'AutoIncrement' property value within a
data set. Is is possible?


Earl said:
The DACW does not create perfect Insert or Update code, but it gets you in
the ballpark. Among other idiosyncrasies, it tries to insert timestamps. But
I've had no problems with it determining what the ID column was --
regardless of how it is named.

Douglas Buchanan said:
Here seems to be the reason the Stored Proceedure is written
incorrectly by the Wizard:
If the table's ID is named anything other than 'ID' the Data Adapter
Configuration Wizard will not recognize it as the ID and will write
the 'Insert' stored proceedure incorrectly.

Doug

(e-mail address removed) (Douglas Buchanan) wrote in message
I'm sure I found the problem. I just don't know how it happened.

The VB.Net Data Adapter Configuration Wizard sometimes creates the
incorrect syntax for the Insert stored proceedure.

It is all in the stored proceedure. First of all I sent the wrong
stored proceedure with the original post. I sent the

'Select' rather than the 'Insert' Stored Proceedure. It is the
'Insert' that controls writing back new records to the database.

Observe the difference between the two stored procedures.

===== The Stored Procedure in error ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSourceID tinyint,
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSourceID, RefSource, ord, hide) VALUES
(@RefSourceID, @RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @RefSourceID) ORDER BY ord,

RefSource
GO

===== A Correct Stored Procedure ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide)
VALUES (@RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide
FROM lkp01RefSource WHERE (RefSourceID = @@IDENTITY)
GO

===========================================
The error was generated by the VB.Net Data Adapter Configuration
Wizard. I have had the Wizard create the store proceedure both
correctly and incorrectly. I did several experiments creating the
stored proceedures in different ways. I was not able to reproduce the
problem.

I can only throw out the caution that the Data Adapter Configuration
Wizard sometimes creates the Insert proceedure with the incorrect
syntax. Incidentally the syntax checker cannot detect errors on this
level.

Good luck all,
Doug

(e-mail address removed) (Douglas Buchanan) wrote in message
Earl,

With all due respects, you did not address my question (see the
subject line).

My question had to do with why my configuration does not allow me to
enter data into the dataGrid. This has everything to do with why my
dataSet does not recognize the ID as an identity. It is likely related
to why the dataSet does not recognize two of the fields as having
default values.

The problem was not my fill code.
My fill code follows the same structure as yours.

Mine: Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")

Yours: da.Fill(ds, "dt")

The problem was unlikely the way I built...
(a) the DataAdapter or the connection string - becuase I dragged the
table to the form.
(b) the SqlCommand - because I had the wizard build it.
(c) the DataSet - because I had the wizard build that too.

The problem I believe had something to do with some setting I failed
to make or am not aware of or (less likely) some glitch.

As an aside:
Tinyint is used because the number of records in this particular table
will max out at less than twenty-five.

Again. what is the problem with the dataset? (see original post for
background data).


Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity key,
since
you only get 256 values.



What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR [ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR [hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and
dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource ORDER
BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
Click to expand...
Click to expand...
Click to expand...
 
I use the DACW to generate the parameters. Check out this article here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp

Despite being an excellent guide on how to do this, note there is a
technical error in the Update section of the code and you should disregard
this paragraph:

"As with the InsertCommand, the UpdateCommand includes a second SELECT that
returns the current row contents. Frankly, this is pretty silly, as it must
match what we just wrote there. In (pretty typical) situations where you
don't write all of the columns, this would be useful, but here it's just a
waste of time."

The second SELECT command is absolutely required, preventing concurrency
errors when doing multiple edits.


Douglas Buchanan said:
Earl,

Do you use the DACW or resort to code alone?

What do you do about it's mistakes in creating DataSets?
For instance if I choose to edit the Insert stored procedure with
Scope_Identity() in stead of @@Identity DACW creates the dataset with
'AutoIncrement' = FALSE

This results in a DataSet that CANNOT successfully add new records to
in the datagrid.

I don't know how to edit the 'AutoIncrement' property value within a
data set. Is is possible?


Earl said:
The DACW does not create perfect Insert or Update code, but it gets you
in
the ballpark. Among other idiosyncrasies, it tries to insert timestamps.
But
I've had no problems with it determining what the ID column was --
regardless of how it is named.

Douglas Buchanan said:
Here seems to be the reason the Stored Proceedure is written
incorrectly by the Wizard:
If the table's ID is named anything other than 'ID' the Data Adapter
Configuration Wizard will not recognize it as the ID and will write
the 'Insert' stored proceedure incorrectly.

Doug

(e-mail address removed) (Douglas Buchanan) wrote in message
I'm sure I found the problem. I just don't know how it happened.

The VB.Net Data Adapter Configuration Wizard sometimes creates the
incorrect syntax for the Insert stored proceedure.

It is all in the stored proceedure. First of all I sent the wrong
stored proceedure with the original post. I sent the

'Select' rather than the 'Insert' Stored Proceedure. It is the
'Insert' that controls writing back new records to the database.

Observe the difference between the two stored procedures.

===== The Stored Procedure in error ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSourceID tinyint,
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSourceID, RefSource, ord, hide) VALUES
(@RefSourceID, @RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @RefSourceID) ORDER BY ord,

RefSource
GO

===== A Correct Stored Procedure ========

CREATE PROCEDURE dbo.RefSourceInsert
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide)
VALUES (@RefSource, @ord, @hide);
SELECT RefSourceID, RefSource, ord, hide
FROM lkp01RefSource WHERE (RefSourceID = @@IDENTITY)
GO

===========================================
The error was generated by the VB.Net Data Adapter Configuration
Wizard. I have had the Wizard create the store proceedure both
correctly and incorrectly. I did several experiments creating the
stored proceedures in different ways. I was not able to reproduce the
problem.

I can only throw out the caution that the Data Adapter Configuration
Wizard sometimes creates the Insert proceedure with the incorrect
syntax. Incidentally the syntax checker cannot detect errors on this
level.

Good luck all,
Doug

(e-mail address removed) (Douglas Buchanan) wrote in message
Earl,

With all due respects, you did not address my question (see the
subject line).

My question had to do with why my configuration does not allow me to
enter data into the dataGrid. This has everything to do with why my
dataSet does not recognize the ID as an identity. It is likely
related
to why the dataSet does not recognize two of the fields as having
default values.

The problem was not my fill code.
My fill code follows the same structure as yours.

Mine: Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")

Yours: da.Fill(ds, "dt")

The problem was unlikely the way I built...
(a) the DataAdapter or the connection string - becuase I dragged the
table to the form.
(b) the SqlCommand - because I had the wizard build it.
(c) the DataSet - because I had the wizard build that too.

The problem I believe had something to do with some setting I failed
to make or am not aware of or (less likely) some glitch.

As an aside:
Tinyint is used because the number of records in this particular
table
will max out at less than twenty-five.

Again. what is the problem with the dataset? (see original post for
background data).


Generally your fill code would look something like this:

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("YourSelectSP", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLServer
strSQLServer.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "dt")

As an aside, not sure why you would use tinyint for an identity
key,
since
you only get 256 values.



What am I doing wrong?

I cannot enter new data into my datagrid because the DataSet (or
StoredProcedute) does not recognize that the ID is an identity.
Therefore I get the following error;

Error: Column 'RefSourceID' does not allow nulls. Do you want to
correct the value?

It also does not recognize the default value of two of the
fields.

===== What I see in the DataGrid ==

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
(null), (null), (null), (null)

===== What I should see ===========

RefSourceID, RefSource, ord, hide
1, Newspaper, 0, 0
2, Television, 1, 0
3, (null), 0, 0

===== The table (sqls2k) ==========

CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lkp01RefSource] ADD
CONSTRAINT [DF__lkp01RefSou__ord__77BFCB91] DEFAULT (0) FOR
[ord],
CONSTRAINT [DF__lkp01RefSo__hide__78B3EFCA] DEFAULT (0) FOR
[hide],
CONSTRAINT [PKRefSourceID] PRIMARY KEY CLUSTERED
(
[RefSourceID]
) ON [PRIMARY]
GO


I used vb.net to create the dataAdapter, storedProcedure, and
dataSet.

===== The Stored Procedure ========

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.NewSelectCommandRefS
AS
SET NOCOUNT ON;
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource
ORDER
BY
ord, RefSource
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

===== The Code ====================

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e
As
System.EventArgs) Handles btnFill.Click
Me.SqlDataAdapter1.Fill(DsRefSource1, "lkp01RefSource")
End Sub

===== End of sample code ==========

What am I doing wrong? What should I be doing?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top