Why UpdateCommand in adapter is null - see code segment

[James Shen]

I have the following code which uses SqlCommand, sqlCommandBuilder and
SqlDataAdapter. According to the doc (MSDN), Update will use the
command builder to build SQL.

However, debugger shows UpdateCommand is null.

This code updates the db correctly (after removing the toString line).
test table has two columns (id int not null, value nvarchar(20)).

Any ideas?

Thanks in advance
James

[WebMethod]
public DataSet TestSqlCmd()
{
string myConnectString = "user id=sa;password=sa;initial
catalog=playground;data source=localhost;Connect Timeout=30";

try
{
SqlConnection conn = new SqlConnection(myConnectString);
conn.Open();

SqlCommand cmd = new SqlCommand("SELECT * from test", conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();

adapter.Fill(dt);

ds.Tables.Add(dt);

SqlCommandBuilder b = new SqlCommandBuilder(adapter);

dt.Rows[0]["value"] = "x";

adapter.Update(dt);

SqlCommand uc = adapter.UpdateCommand;
string st = uc.CommandText;

return ds;
}
catch(Exception ex)
{
string s = ex.ToString();
}
return null;
}

 

[William Ryan]

James:

Just wondering.. is there an PK on the underlying table?

 

[James Shen]

Yes, it is the id column

It must build the comand correctly as the update works.

James

James:

Just wondering.. is there an PK on the underlying table?

I have the following code which uses SqlCommand, sqlCommandBuilder and
SqlDataAdapter. According to the doc (MSDN), Update will use the
command builder to build SQL.

However, debugger shows UpdateCommand is null.

This code updates the db correctly (after removing the toString line).
test table has two columns (id int not null, value nvarchar(20)).

Any ideas?

Thanks in advance
James

[WebMethod]
public DataSet TestSqlCmd()
{
string myConnectString = "user id=sa;password=sa;initial
catalog=playground;data source=localhost;Connect Timeout=30";

try
{
SqlConnection conn = new SqlConnection(myConnectString);
conn.Open();

SqlCommand cmd = new SqlCommand("SELECT * from test", conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();

adapter.Fill(dt);

ds.Tables.Add(dt);

SqlCommandBuilder b = new SqlCommandBuilder(adapter);

dt.Rows[0]["value"] = "x";

adapter.Update(dt);

SqlCommand uc = adapter.UpdateCommand;
string st = uc.CommandText;

return ds;
}
catch(Exception ex)
{
string s = ex.ToString();
}
return null;
}

 

[David Sceppa]

James,

The CommandBuilder doesn’t set the Insert, Update, or
DeleteCommand properties on the DataAdapter. Instead, it listens
to the DataAdapter’s events and uses its own logic to submit the
pending changes. You can see this logic by calling
CommandBuilder.GetUpdateCommand().

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.