TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Henry Vuong
NA
27
0
Cannot update database with datatable
Aug 5 2012 12:47 PM
I am trying to update the database with a datatable loaded into a datagridview. Whenever I make some changes in the datagridview and click the "Save" buttton, I got the error message "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." Searching the net for this error, I see that the error is caused by the missing of primary key in the selected table. To be sure, the table I selected from the database DOES have a primary key column. I notice that this error only occurs when I make some changes in the datagridview. If I just load the datagridview and click "Save" without changing anything, there's no error. Here's my code:
//These object instances are created at class level
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet()
// In the form_load method:
string strSql = "SELECT * FROM Inventory";
try
{
SqlCommand sqlCmd = new SqlCommand(strSql, Conn);
da.SelectCommand = sqlCmd;
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
da.Fill(ds, "Inventory");
BindingSource bSource = new BindingSource();
bSource.DataSource = ds;
bSource.DataMember = "Inventory";
datagridview1.DataSource = bSource;
//This is where I get trouble, when I click the "Save" button on the form to save changes in datagridview back to database,
// I got the error "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
//To be sure, the "Inventory" table DOES have a primary key column.
//If I do not make any changes in the datagridview, I do not get error message when clicking save button
private void btnSave_Click(object sender, EventArgs e)
{
SqlCommandBuilder sqlCmdBldr = new SqlCommandBuilder(da);
da.Update(ds, "Inventory");
}
The dataset actually contains other tables, since they are irrelevant I do not mention them here.
And here's the Sql command that I used to create the said Sql Server table:
CREATE TABLE Inventory
(
Custom_SKU nvarchar(100) not null primary key,
Custom_Title nvarchar(255) null,
Custom_QtyOnHand int null,
BT_QtyOnHand int null,
BT_QtyScheduled int null,
BT_QtyRunning int null,
BT_QtySoldWaiting int null,
BT_QtyClearedToShip int null,
Custom_QtyAvailable int null,
Custom_TrackInventory bit not null,
Custom_Notes nvarchar(255) null
)
Reply
Answers (
2
)
c# code for get a 3d mesh from 3d point cloud and create a 3d model in wpf?
Open Form inside another Form