Inserting a default Value

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value.

Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset.

Any suggestions or examples where I can do this.

Many thanks in advance

A DataColumn has a DefaultValue property - seeMSDN for usage:

private void MakeTable()
{
// Create a DataTable.
DataTable table = new DataTable("Product");

// Create a DataColumn and set various properties.
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Decimal");
column.AllowDBNull = false;
column.Caption = "Price";
column.ColumnName = "Price";
column.DefaultValue = 25;

// Add the column to the table.
table.Columns.Add(column);

// Add 10 rows and set values.
DataRow row;
for(int i = 0; i < 10; i++)
{
row = table.NewRow();
row["Price"] = i + 1;

// Be sure to add the new row to the
// DataRowCollection.
table.Rows.Add(row);
}
}

Insert statement with todays date in one of the field

How do I write an Insert SQL statement with a default today's date inserted into one of the field?

Help is apreciated.

INSERT INTO [TableName]

(

DateField

)

VALUES

(

getdate()

)

|||

sorry didn't see the word default

you would set a paramter of type date and set the default value to getdate()

@.DateField as DateTime = getdate()

If you pass a paramter it will override this default value

|||

Thank you so much for the immediate response. Actually it was an update not an insert but it is similar. Here's what I've tried.

UpdateCommand="UPDATE [myAlumni] SET [constID] = @.constID, [hideState] = @.hideState, [userName] = @.userName, [lstName] = @.lstName, [mdnName] = @.mdnName, [fstName] = @.fstName, [mdlName] = @.mdlName, [nckName] = @.nckName, [classOf] = @.classOf, [semester] = @.semester, [address] = @.address, [city] = @.city, [state] = @.state, [zip] = @.zip, [country] = @.country, [phone] = @.phone, [address2] = @.address2, [email1] = @.email1, [email2] = @.email2, [email3] = @.email3, [website] = @.website, [mdfyDate] =<%# DateTime.Now%> WHERE [dirID] = @.dirID">

I am using SqlDataSource for this. The error I got from the above statement is:

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.

So I tried this:

UpdateCommand="UPDATE [myAlumni] SET [constID] = @.constID, [hideState] = @.hideState, [userName] = @.userName, [lstName] = @.lstName, [mdnName] = @.mdnName, [fstName] = @.fstName, [mdlName] = @.mdlName, [nckName] = @.nckName, [classOf] = @.classOf, [semester] = @.semester, [address] = @.address, [city] = @.city, [state] = @.state, [zip] = @.zip, [country] = @.country, [phone] = @.phone, [address2] = @.address2, [email1] = @.email1, [email2] = @.email2, [email3] = @.email3, [website] = @.website, [mdfyDate] =<%# getDate()%> WHERE [dirID] = @.dirID">

And then in the getDate method, I do this:

protected string getDate() {string strDate = Convert.ToString(DateTime.Now);return strDate; }

But I still get the same error.|||You don't need the <%# %> tags. getdate() is a function inside sql, not c#|||Thank you so much! That works great.|||

I am trying to get this to work as well without much success. I want to add today's date in a field called "Created". This field is not used in the form but should add the date created automatically.

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, getDate())"

getDate() function in script

protectedstring getDate()

{

string strDate =Convert.ToString(DateTime.Now);return strDate;

}

|||

I am trying to get this to work as well without much success. I want to add today's date in a field called "Created". This field is not used in the form but should add the date created automatically.

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, getDate())"

getDate() function in script

protectedstring getDate()

{

string strDate =Convert.ToString(DateTime.Now);return strDate;

}

Undefined function 'getDate' in expression

|||

You need to seperate the getdate function from the string

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, " + getdate() + ")"

|||

Sorry for the earlier double post. I needed Date() function as it is an access database.