I suspect there's something wrong with your sql statement，and plz have a try like this：
using(SqlDataAdapter adapter = new SqlDataAdapter("select * from xxx","your conn here")) //Make sure that your xxx table must have a primary key
{
SqlCommandBuilder sub = new SqlCommandBuilder(adapter);
SqlCommand cmd = sub.GetUpdateCommand();
……………………
}
Thanks for your reply. My select statemnet is very simple for testing.
using (SqlDataAdapter adapter = new SqlDataAdapter("select id, f1 from table_1 where id = '1'", sqlConnection)) //Make sure that your xxx table must have a primary key
{
SqlCommandBuilder sub = new SqlCommandBuilder(adapter);
SqlCommand cmd = sub.GetUpdateCommand();
}
id field is the PK of this table. It show the same error.
My SQL environment :
Server 1: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) (Build 7601: Service Pack 1)
This is the primary server and built an synonyms table to Server 2
Server 2: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) (Build 3790: Service Pack 2)
Here is my code and more information about this symption.
Script of synonym in SQL 2008, server 2 is a SQL 2000.
CREATE SYNONYM [dbo].[table_1] FOR [Server2].[db1].[dbo].[table_1]
C# code:
SqlConnection sqlConnection = new SqlConnection("my connection string to server 1");
using (SqlDataAdapter adapter = new SqlDataAdapter("select id, f1 from table_1 where id = 1", sqlConnection)) //Make sure that your xxx table must have a primary key
{
SqlCommandBuilder sub = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds);
ds.Tables[0].Rows[0]["f1"] = "test modification";
SqlCommand cmd = sub.GetUpdateCommand();
}
Sympton of SqlCommandBuilder GetUpdateCommand() :
1. connect & update table for same database - work fine
2. connect to server 1 and in server 1, update synonym table which belong to server 2 - error
If I do the updatecommnad manually without using GetUpdateCommand(), it works fine.
wilsonwu
Member
59 Points
86 Posts
SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 24, 2012 06:40 AM|LINK
Hi,
I got error - "A severe error occurred on the current command. The results, if any, should be discarded." for the following command.
sqlDA.UpdateCommand = sqlCommBuilder.GetUpdateCommand();
The code is fine for one server DB. But it happens when i use synonyms table.
Any suggestion?
Thanks
Wilson
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 26, 2012 12:26 AM|LINK
I suspect there's something wrong with your sql statement，and plz have a try like this：
using(SqlDataAdapter adapter = new SqlDataAdapter("select * from xxx","your conn here")) //Make sure that your xxx table must have a primary key { SqlCommandBuilder sub = new SqlCommandBuilder(adapter); SqlCommand cmd = sub.GetUpdateCommand(); …………………… }wilsonwu
Member
59 Points
86 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 27, 2012 07:14 AM|LINK
Hi,
Thanks for your reply. My select statemnet is very simple for testing.
using (SqlDataAdapter adapter = new SqlDataAdapter("select id, f1 from table_1 where id = '1'", sqlConnection)) //Make sure that your xxx table must have a primary key
{
SqlCommandBuilder sub = new SqlCommandBuilder(adapter);
SqlCommand cmd = sub.GetUpdateCommand();
}
id field is the PK of this table. It show the same error.
My SQL environment :
Server 1: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) (Build 7601: Service Pack 1)
This is the primary server and built an synonyms table to Server 2
Server 2: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) (Build 3790: Service Pack 2)
Thanks
Wilson
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 27, 2012 07:26 AM|LINK
What's the type of id？
wilsonwu
Member
59 Points
86 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 27, 2012 07:57 AM|LINK
Hi,
It is int.
Thanks
Wilson
wilsonwu
Member
59 Points
86 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 27, 2012 08:02 AM|LINK
I have change to char. It is same error.
Thanks
Wilson
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 27, 2012 08:06 AM|LINK
Try int without single quotes。
wilsonwu
Member
59 Points
86 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 28, 2012 04:12 AM|LINK
Hi,
It is same error after changed back to int and remove single quote in where clause.
Thanks
Wilson
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 28, 2012 04:54 AM|LINK
Now show us your full code，and plz don't use GetUpdateCommand but just use adapter.UpdateCommand.Execute instead to have a try
wilsonwu
Member
59 Points
86 Posts
Re: SqlCommandBuilder GetUpdatecommand error for synonyms table
Feb 28, 2012 06:22 AM|LINK
Hi,
Here is my code and more information about this symption.
Script of synonym in SQL 2008, server 2 is a SQL 2000.
CREATE SYNONYM [dbo].[table_1] FOR [Server2].[db1].[dbo].[table_1]
C# code:
SqlConnection sqlConnection = new SqlConnection("my connection string to server 1");
using (SqlDataAdapter adapter = new SqlDataAdapter("select id, f1 from table_1 where id = 1", sqlConnection)) //Make sure that your xxx table must have a primary key
{
SqlCommandBuilder sub = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds);
ds.Tables[0].Rows[0]["f1"] = "test modification";
SqlCommand cmd = sub.GetUpdateCommand();
}
Sympton of SqlCommandBuilder GetUpdateCommand() :
1. connect & update table for same database - work fine
2. connect to server 1 and in server 1, update synonym table which belong to server 2 - error
If I do the updatecommnad manually without using GetUpdateCommand(), it works fine.
Thanks
Wilson