• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

sql update not working correctly using vb.net 2005 winforms

Hi,

I am having trouble with an update statement. When I update the table then run the following code to refresh the combobox with the updated data it still shows the old data. When I close the form then reopen it its ok. The code I am using directly after the update is: -

        Dim ds As New DataSet

        SQL = "SELECT SupplierID, " & _
              "Supplier_Name + ' (' + Supplier_Code + ')' as Supplier_Details " & _
              "FROM Suppliers " & _
              "ORDER BY Supplier_Name"

        dbOpen()
        Dim da As New SqlClient.SqlDataAdapter(SQL, oConn)
        da.Fill(ds, "Suppliers")

        cmbSuppliers.DisplayMember = "Supplier_Details"
        cmbSuppliers.ValueMember = "SupplierID"
        cmbSuppliers.DataSource = ds.Tables("Suppliers")

        dbClose()

Best Regards
Lee
0
ljhodgett
Asked:
ljhodgett
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about adding the call to DataBind:

   Dim ds As New DataSet

        SQL = "SELECT SupplierID, " & _
              "Supplier_Name + ' (' + Supplier_Code + ')' as Supplier_Details " & _
              "FROM Suppliers " & _
              "ORDER BY Supplier_Name"

        dbOpen()
        Dim da As New SqlClient.SqlDataAdapter(SQL, oConn)
        da.Fill(ds, "Suppliers")

        cmbSuppliers.DisplayMember = "Supplier_Details"
        cmbSuppliers.ValueMember = "SupplierID"
        cmbSuppliers.DataSource = ds.Tables("Suppliers")
        cmbSuppliers.DataBind

        dbClose()
0
 
ljhodgettAuthor Commented:
Hi,

cmbSuppliers.DataBind  underlines and says: -

Error      5      'DataBind' is not a member of 'System.Windows.Forms.ComboBox'.      C:\Documents and Settings\lh181365\Desktop\Spares_Database\WindowsApplication10\frmEditSupplier.vb      234      9      Spares_Control

Best Regards
Lee
0
 
grayeCommented:
So, show us where you update the database
0
 
ljhodgettAuthor Commented:
The update is via a stored procedure: -

                Dim myCommand As New SqlCommand("Update_Suppliers", oConn)
                myCommand.CommandType = CommandType.StoredProcedure


                myCommand.Parameters.AddWithValue("@SupplierID", cmbSuppliers.SelectedValue.ToString)
                myCommand.Parameters.AddWithValue("@Supplier_Code", txtSuppliercode.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Name", txtCompanyName.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Add1", txtSupplieradd1.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Add2", txtSupplierAdd2.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Town", txtSupplierTown.Text)
                myCommand.Parameters.AddWithValue("@Supplier_County", txtSupplierCounty.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Postcode", txtSupplierPostcode.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Country", txtSupplierCountry.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Tel", txtSupplierNo.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Ex", txtSupplierExtensionNo.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Fax", txtSupplierFaxNo.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Contact_Name", txtSupplierContact.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Web", txtUrl.Text)
                myCommand.Parameters.AddWithValue("@Supplier_Email", txtEmail.Text)


                myCommand.ExecuteNonQuery()

                dbClose()

The update procedure is: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            LH
-- Create date: 02/08/02007
-- Description:      Update supplier information into "dbo.Suppliers"
-- =============================================
ALTER procedure [dbo].[Update_Suppliers]
      (@SupplierID varchar(100),
      @Supplier_Code varchar(100),
      @Supplier_Name varchar(100),
      @Supplier_Add1  varchar(100),
      @Supplier_Add2 varchar(100),
      @Supplier_Town varchar(100),
      @Supplier_County varchar(100),
      @Supplier_Postcode varchar(100),
      @Supplier_Country varchar(100),
      @Supplier_Tel  varchar(100),
      @Supplier_Ex varchar(100),
      @Supplier_Fax varchar(100),
      @Supplier_Contact_Name varchar(100),
      @Supplier_Web varchar(100),
      @Supplier_Email varchar(100))
AS
BEGIN
   SET NOCOUNT ON
            UPDATE dbo.Suppliers
SET
      Supplier_Code = @Supplier_Code,
      Supplier_Name = @Supplier_Name,
      Supplier_Add1 = @Supplier_Add1,
      Supplier_Add2 = @Supplier_Add2,
      Supplier_Town = @Supplier_Town,
      Supplier_County = @Supplier_County,
      Supplier_Postcode = @Supplier_Postcode,
      Supplier_Country = @Supplier_Tel,
      Supplier_Tel = @Supplier_Ex,
      Supplier_Ex = @Supplier_Ex,
      Supplier_Fax = @Supplier_Fax,
      Supplier_Contact_Name = @Supplier_Contact_Name,
      Supplier_Web = @Supplier_Web,
      Supplier_Email = @Supplier_Email

WHERE SupplierID = @SupplierID

END

Best Regards
Lee
0
 
ljhodgettAuthor Commented:
Hi,

Don't worry. I've found it. The refresh sub was not executing correctly.

Many Thanks
Lee
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now