调用存储过程时不会刷新DataSet [英] DataSet Does Not Refresh when Stored Procedure Is Called
问题描述
我在Windows 7企业版中运行VS2012并且已经尝试解决此问题几个小时。我有一个带有组合框和几个文本框的Windows窗体。用户可以从组合框中选择名称。当用户单击按钮时，
中的名称组合框作为参数传递给我的SQL Server数据库中的选择存储过程，并且具有该名称的数据库中的任何记录将在表单上返回。有代码可以创建调用存储过程的参数。我使用sqlDataAdapter的fill方法
来填充dataTable。表单上的字段已在设计时绑定到dataTable（请参阅下面的代码）。
I'm running VS2012 in Windows 7 Enterprise and have been trying to resolve this issue for several hours. I have a Windows form with a combo box and several textboxes. The user can select a name from the combo box. When the user clicks a button the name in the combo box is passed as a parameter to a select stored procedure in my SQL Server database and any record in the database with that name will be return on the form. There is code to create the parameters to call the stored procedure. I use the fill method of the sqlDataAdapter to fill the dataTable. The fields on the form have been bound to the dataTable at design time (see code below).
我遇到的问题是第一次将存储过程称为数据集填充并且记录显示在表单上，但是第二次和随后的用户在组合框中选择不同的名称，数据集不会刷新，数据集中显示相同的记录，表单上的数据保持不变相同。
The problem that I am having is that the first time the stored procedure is called the data set is filled and the record appears on the form, however the second and subsequent times the user selects a different name in the combo box the dataset does not refresh, the same records appear in the dataset and the data on the form stays the same.
然后，每次调用存储过程时，我都会更改代码以创建新数据集。当用户在组合框中选择其他名称时，数据集会显示新记录，但数据绑定不起作用且表单上不显示任何数据。
I then changed my code to create a new dataset each time the stored procedure is called. When the user selects a different name in the combo box the dataset shows the new records however the data bindings do not work and no data appears on the form.
我需要知道如何每次调用存储过程时，我都可以使用新记录刷新相同的数据集。请举个例子。感谢您的时间。
I need to know how I can refresh the same dataset with the new records each time the stored procedure is called. Please provide an example. Thank you for your time.
数据集最初显示正确记录的代码，但后续调用存储过程时数据集不会更新：
Code where the dataset shows the correct records initially, but dataset does not update in subsequent calls to the stored procedure:
private void btnShow_Click（object sender，EventArgs e）
      {
          sqlConnection1.Open（）;
          SqlCommand cmd1 = new SqlCommand（" Insurer_Select"，sqlConnection1）;
          string GroupName = cboGroups.Text;
          cmd1.Parameters.Clear（）;
          cmd1.Parameters.Add（new SqlParameter（" @ InsurerGroup"，GroupName））;
          cmd1.CommandType = CommandType.StoredProcedure;
          sqlDataAdapter1.SelectCommand = cmd1;
          sqlDataAdapter1.Fill（dsPartC_NonHorizon，" Insurer_Select"）;
          sqlConnection1.Close（）;
      }
private void btnShow_Click(object sender, EventArgs e)
{
sqlConnection1.Open();
SqlCommand cmd1 = new SqlCommand("Insurer_Select", sqlConnection1);
string GroupName = cboGroups.Text;
cmd1.Parameters.Clear();
cmd1.Parameters.Add(new SqlParameter("@InsurerGroup", GroupName));
cmd1.CommandType = CommandType.StoredProcedure;
sqlDataAdapter1.SelectCommand = cmd1;
sqlDataAdapter1.Fill(dsPartC_NonHorizon,"Insurer_Select");
sqlConnection1.Close();
}
存储过程：
CREATE PROCEDURE [dbo] .Insurer_Select
（
@InsurerGroup nvarchar（32）='Cigna' - Cigna的默认值
）
AS
SET NOCOUNT ON;
SELECT PartC_NonHorizon_Master。 relHICN，PartC_NonHorizon_Master.relRelation，PartC_NonHorizon_Master.relInsurerName，PartC_NonHorizon_Master.relMSPCode，
                PartC_NonHorizon_Master.relMSPEffDate，PartC_NonHorizon_Master.relMSPTermDate，PartC_NonHorizon_Master.relActMSPEffDate，
                PartC_NonHorizon_Master.relActMSPTermDate，PartC_NonHorizon_Master.relCCID，PartC_NonHorizon_Master.relSubscriberFirst，
                PartC_NonHorizon_Master.relSubscriberLast，PartC_NonHorizon_Master.relInsurerType，PartC_NonHorizon_Master.relInsurerAddress1，
                PartC_NonHorizon_Master.relInsurerCity，PartC_NonHorizon_Master.relInsurerSt，PartC_NonHorizon_Master.relInsurerZip，PartC_NonHorizon_Master.relPolicyNumber，
                PartC_NonHorizon_Master.relInsGroup，PartC_NonHorizon_Master.relInsurerGroupName，PartC_NonHorizon_Master.relSourceCode，
                PartC_NonHorizon_Master.relDateProcessed，PartC_NonHorizon_Master.relECRSFileDate，PartC_NonHorizon_Master.relRevisedEffDate，
                PartC_NonHorizon_Master.relRevisedTermDate，PartC_NonHorizon_Master.relSR，PartC_NonHorizon_Master.relCallStatus，PartC_NonHorizon_Master.relComment，
                PartC_NonHorizon_Master.relRecordUpdated，PartC_NonHorizon_Master.relUserId，PartC_NonHorizon_Master.relActMSPCode，
                PartC_NonHorizon_Master.relActInsurerType，PartC_NonHorizon_Master.relActInsurerName，PartC_NonHorizon_Master.relActPolicyNumber，
                PartC_NonHorizon_Master.relActRelation，PartC_NonHorizon_Master.relActInsGroup，PartC_NonHorizon_Master.relActInsurerGroupName，
                PartC_NonHorizon_Master.relActInsurerAddress1，PartC_NonHorizon_Master.relOccuranceStatus，PartC_NonHorizon_Master.relIKAEffDate，
                PartC_NonHorizon_Master.relActSource，PartC_NonHorizon_Master.relActMSPStatus，PartC_NonHorizon_Master.relIKATermDate，tblInsurerXref。[Group Name]
FROM   PartC_NonHorizon_Master INNER JOIN
                tblInsurerXref ON PartC_NonHorizon_Master.relInsurerName = tblInsurerXref。[保险公司名称]
WHERE （tblInsurerXref。[Group Name] = @InsurerGroup）
CREATE PROCEDURE [dbo].Insurer_Select
(
@InsurerGroup nvarchar(32) = 'Cigna' --Default value of Cigna
)
AS
SET NOCOUNT ON;
SELECT PartC_NonHorizon_Master.relHICN, PartC_NonHorizon_Master.relRelation, PartC_NonHorizon_Master.relInsurerName, PartC_NonHorizon_Master.relMSPCode,
PartC_NonHorizon_Master.relMSPEffDate, PartC_NonHorizon_Master.relMSPTermDate, PartC_NonHorizon_Master.relActMSPEffDate,
PartC_NonHorizon_Master.relActMSPTermDate, PartC_NonHorizon_Master.relCCID, PartC_NonHorizon_Master.relSubscriberFirst,
PartC_NonHorizon_Master.relSubscriberLast, PartC_NonHorizon_Master.relInsurerType, PartC_NonHorizon_Master.relInsurerAddress1,
PartC_NonHorizon_Master.relInsurerCity, PartC_NonHorizon_Master.relInsurerSt, PartC_NonHorizon_Master.relInsurerZip, PartC_NonHorizon_Master.relPolicyNumber,
PartC_NonHorizon_Master.relInsGroup, PartC_NonHorizon_Master.relInsurerGroupName, PartC_NonHorizon_Master.relSourceCode,
PartC_NonHorizon_Master.relDateProcessed, PartC_NonHorizon_Master.relECRSFileDate, PartC_NonHorizon_Master.relRevisedEffDate,
PartC_NonHorizon_Master.relRevisedTermDate, PartC_NonHorizon_Master.relSR, PartC_NonHorizon_Master.relCallStatus, PartC_NonHorizon_Master.relComment,
PartC_NonHorizon_Master.relRecordUpdated, PartC_NonHorizon_Master.relUserId, PartC_NonHorizon_Master.relActMSPCode,
PartC_NonHorizon_Master.relActInsurerType, PartC_NonHorizon_Master.relActInsurerName, PartC_NonHorizon_Master.relActPolicyNumber,
PartC_NonHorizon_Master.relActRelation, PartC_NonHorizon_Master.relActInsGroup, PartC_NonHorizon_Master.relActInsurerGroupName,
PartC_NonHorizon_Master.relActInsurerAddress1, PartC_NonHorizon_Master.relOccuranceStatus, PartC_NonHorizon_Master.relIKAEffDate,
PartC_NonHorizon_Master.relActSource, PartC_NonHorizon_Master.relActMSPStatus, PartC_NonHorizon_Master.relIKATermDate, tblInsurerXref.[Group Name]
FROM PartC_NonHorizon_Master INNER JOIN
tblInsurerXref ON PartC_NonHorizon_Master.relInsurerName = tblInsurerXref.[Insurer name]
WHERE (tblInsurerXref.[Group Name] = @InsurerGroup)
这是代码每次创建新数据集时都会加载新记录，但绑定不起作用，因此表单上没有记录：
This is the code that loaded the new records each time a new dataset was created, but the binding did not work so no records appeared on the form:
推荐答案
如果你调试并进入代码，你是否看到新的选择值被传递给show_click事件？它是否被传递到SQL中，我现在没有代码在我面前，但我在过去经历过类似的问题..对我而言，看起来很简单
...
If you debug and step into the code, are you seeing the new selection value get passed to the show_click event? Is it being passed into the SQL, i dont have code in front of me right now, but i experienced similar issues in the past.. for me it was simple over looked things..
这篇关于调用存储过程时不会刷新DataSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
