Question Saving New Selection from Combobox to SQL Database

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
Hello All,

I'm back! Hoping you can help me with this one.

I have an app I'm working on with multiple forms. One of the forms, frmLocationInfo, has textboxes and comboboxes which are automatically populated based on a combobox selection from a previous form, frmLocations. I've been able to get any changes made to frmLocationInfo's textboxes to save to the SQL database, however, when attempting to select a different item from any of its comboboxes, it's not saving to the database and instead, I receive a "Procedure or function...has too many arguments specified" error.

Here is the code for the Save button:
VB.NET:
'Save changes to Locations db 
    Private Sub btnSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveClose.Click 
        'Passing value from controls to variables 
 
        clsMain.Location_str_value = txtLoc.Text 
        'clsMain.Location_refnbr = frmLocations.cboLocations.SelectedValue 
        clsMain.Location_refnbr = lblSiteRefNbr.Text 
        clsMain.AAC_str_value = txtAAC.Text 
        clsMain.Phone_str_value = txtPhone.Text 
        clsMain.Fax_str_value = txtFax.Text 
        clsMain.Email_str_value = txtEmail.Text 
        clsMain.Depot_str_value = cboDepot.Text 
        clsMain.Depot_refnbr = cboDepot.ValueMember 
        clsMain.FrghtFrwrdr_str_value = cboFreight.Text 
        clsMain.Frght_refNbr = cboFreight.SelectedValue 
        clsMain.Dsptchr_str_value = cboDispatcher.Text 
        clsMain.Dsptchr_refNbr = cboDispatcher.SelectedValue 
 
        If Not conn.State = ConnectionState.Open Then conn.Open() 
 
        'Create command object, open stored procedure, and pass connection string 
        Dim myCommand As SqlCommand = New SqlCommand("dbo.sp_OrderTracking_Update_UpdateItem", conn) 
        myCommand.CommandType = Data.CommandType.StoredProcedure 
 
        myCommand.Parameters.AddWithValue("@site", clsMain.Location_str_value) 
        myCommand.Parameters.AddWithValue("@siteRefnbr", clsMain.Location_refnbr) 
        myCommand.Parameters.AddWithValue("@aac", clsMain.AAC_str_value) 
        myCommand.Parameters.AddWithValue("@phoneNbr", clsMain.Phone_str_value) 
        myCommand.Parameters.AddWithValue("@faxNbr", clsMain.Fax_str_value) 
        myCommand.Parameters.AddWithValue("@Email", clsMain.Email_str_value) 
        myCommand.Parameters.AddWithValue("@depot", clsMain.Depot_str_value) 
        myCommand.Parameters.AddWithValue("@depotRefnbr", clsMain.Depot_refnbr) 
        myCommand.Parameters.AddWithValue("@freight", clsMain.FrghtFrwrdr_str_value) 
        myCommand.Parameters.AddWithValue("@freightRefnbr", clsMain.Frght_refNbr) 
        myCommand.Parameters.AddWithValue("@dsptchr", clsMain.Dsptchr_str_value) 
        myCommand.Parameters.AddWithValue("@dispatchRefnbr", clsMain.Dsptchr_refNbr) 
 
        myCommand.ExecuteNonQuery().ToString() 
 
        MessageBox.Show("Location successfully saved.", "Changes Saved", MessageBoxButtons.OK) 
         
    End Sub

I was told that every time a different item is selected from a combobox, the value should be reassigned to the variable and this should happen in the "SelectedIndexChanged" event, so this is what I tried:

VB.NET:
Private Sub cboDepot_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDepot.SelectedIndexChanged 
        clsMain.Depot_str_value = cboDepot.Text 
        clsMain.Depot_refnbr = cboDepot.ValueMember 
    End Sub

As always, your assistance is appreciated. :)
 

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
Hi ss7thirty and All,

Yup, everything in my stored procedure matches up. Here it is; could you please advise:

VB.NET:
ALTER PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem
	(
	
	@site varchar(30),
	@siteRefnbr varchar(5),
	@aac varchar(10),
	@phoneNbr varchar(20),
	@faxNbr varchar(20),
	@depot varchar (20),
	@depotRefnbr varchar(2),
	@freight varchar(40),
	@freightRefnbr varchar(2),
	@dsptchr varchar(15)
	@dispatchRefnbr varchar(2),
	@email varchar(30)
	
	)
	
AS
/* Update Locations table */
BEGIN
        UPDATE    locations
        SET              site = @site, site_refnbr = @siteRefnbr, aac = @aac, telephone_nbr = @phoneNbr, fax_nbr = @faxNbr, depot = @depot, depot_refnbr = @depotRefnbr, 
                              freight = @freight, freight_refnbr = @freightRefnbr, dsptchr = @dsptchr, dispatch_refnbr = @dispatchRefnbr, Email = @Email
        WHERE     (site_refnbr = @siteRefnbr) 

	
	END;
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
Is it case-sensitive? Check @email against @Email?
 

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
InertiaM,

Thanks.........I caught that too and changed it to @Email, but it made no difference....any other suggestions, please?
 

Tom

Well-known member
Joined
Aug 23, 2005
Messages
746
Programming Experience
5-10
The parameters look correct (unless I'm missing something) the only thing I see a bit strange is updating the same column that your filtering the records by. Other than that I would confirm that none of your values are breaking the max lengths of the fields
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Here's the easy way to work with stored procedures.. Get the computer to generate the code for you. This also has the pleasant side effect that it generates code that works, in a shorter time than it takes to write a forum posting asking why manual code doesnt work.

Add a new DataSet to your project
Right click on its surfact and choose Add New Query
Go through the first page of the wizard to set up a connection to your database. the connection string is stored in app settings
On the wizard page "how should the tableadapter access the database" choose the "use existing stored procedures" option
Wire up your stored procedure
Call the local function a sensible name (e.g. OrdTrackUpdUpdateItem)
Finish the wizard


You can now in code say:

Dim ta as New QueriesTableAdapter
ta.OrdTrackUpdUpdateItem(...list of relevant parameters to pass...)


About 20 seconds to set up the tableadapter (when you get familiar with it) and 2 lines of code to run the function..
 

MattP

Well-known member
Joined
Feb 29, 2008
Messages
1,206
Location
WY, USA
Programming Experience
5-10
Take a look at the data walkthroughs in cjard's signature. You'll find that the IDE can do most of the work for you leaving you to worry about your program's logic.

If you're a visual learner there's an excellent video series going over the same subject here: Forms Over Data Video Series
 

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
Hello All,

Thanks for your posts. The thing is, my comboboxes are populating correctly. What I need them to do is if another item is selected from any of them, I'd like to save the new item (i.e. new depot, freight forwarder, dispatcher) to the database as an update to that particular location record.

The way I'm trying to do this is in the SelectedIndexChanged event with the following code:

With cbo...
.text = datatable(fieldName from stored procedure which fills dataset)
.SelectedValue = datatable(field name(refNbr) from stored procedure)
End With

However, when I try doing this in the SelectedIndexChanged event for cboDepot, it won't recognize dtdepot. I'm getting a blue squiggly under dtdepot(marked in red below), which says "Class 'System.Data.DataTable' cannot be indexed because it has no default property."

Here is the code I'm trying:

VB.NET:
    Private Sub cboDepot_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDepot.SelectedIndexChanged 
 
        clsMain.Depot_str_value = cboDepot.Text 
        clsMain.Depot_refnbr = cboDepot.ValueMember 
 
        Dim dtdepot As New DataTable("Depot") 
 
        With cboDepot 
 
            .Text = [color="#FF0000"]dtdepot[/color](@depot) 
            .SelectedValue = [color="#FF0000"]dtDepot[/color](@depotRefnbr) 
 
        End With

Any assistance would be greatly appreciated.
 
Top Bottom