G
Girish
CREATE TABLE [dbo].[person] (
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]
[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"
Heres the problem:
Im using the following code to create me a insert statement automatically
for inserting new rows into the database
//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();
Im ONLY setting the following fields for an insert
[callsignid] [password] [name] [genderid] [ageid] [roleid]. The
[creationdate] has a default on it, so I dont really care about setting it.
So heres the Insert statement generated in the profiler.
exec sp_executesql N'INSERT INTO person( callsignid , password , name ,
genderid , ageid , roleid , emailaddress , uszipcode , intcity , countryid )
Im extremely surprised. I was under the impression that the command builder
would try and insert into ALL columns. So where is the value for
creationdate?
Reference (Inside CommandBuilder):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp
So now I move onto this bit of code to insert new rows but return me the
AUTONUMBER as well:
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();
SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();
for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}
_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();
BUT, I get an error stating that I cant insert NULL values for creationdate
column. I looked at the profiler and now it seems that the insert statement
generated wants to insert a value (null) for the creationdate. Why exactly?
I want the database to set the default. How can I get around this problem?
Im totally clueless!
Any help would be appreciated.
Thanks,
Girish
[personid] [int] IDENTITY (1, 1) NOT NULL ,
[callsignid] [int] NOT NULL ,
[password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[genderid] [int] NOT NULL ,
[ageid] [int] NOT NULL ,
[roleid] [int] NOT NULL ,
[creationdate] [datetime] NOT NULL ,
[emailaddress] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uszipcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intcity] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryid] [int] NULL
) ON [PRIMARY]
[creationdate] [datetime] NOT NULL HAS A DEFAULT function on it "getdate()"
Heres the problem:
Im using the following code to create me a insert statement automatically
for inserting new rows into the database
//_dataCommand is instance of SqlDataAdapter
// _dataTable is instance of DataTable
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
_dataCommand.InsertCommand = bldr.GetInsertCommand();
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();
Im ONLY setting the following fields for an insert
[callsignid] [password] [name] [genderid] [ageid] [roleid]. The
[creationdate] has a default on it, so I dont really care about setting it.
So heres the Insert statement generated in the profiler.
exec sp_executesql N'INSERT INTO person( callsignid , password , name ,
genderid , ageid , roleid , emailaddress , uszipcode , intcity , countryid )
Im extremely surprised. I was under the impression that the command builder
would try and insert into ALL columns. So where is the value for
creationdate?
Reference (Inside CommandBuilder):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp
So now I move onto this bit of code to insert new rows but return me the
AUTONUMBER as well:
SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new
SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += "; " + getPrimaryKeySQLString();
SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();
for(int i=0 ; i < aParams.Length; i++)
{
cmdInsert.Parameters.Add(aParams);
}
_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);
_dataTable.AcceptChanges();
BUT, I get an error stating that I cant insert NULL values for creationdate
column. I looked at the profiler and now it seems that the insert statement
generated wants to insert a value (null) for the creationdate. Why exactly?
I want the database to set the default. How can I get around this problem?
Im totally clueless!
Any help would be appreciated.
Thanks,
Girish