Updating a database using sql

[Ldragon99]

I am creating an application and am having a hard time finding an example of a simple was of taking some text from a combobox, and updating that row in the database. I have the application displaying the info in a listview, and when selected breaking the items into textboxes that can be edited. I just for the life of me can not get the details back to the database

Please help before I pull all my hair out, or at least point me in the direction of a good link with some easy examples.

 

[jmcilhinney]

Here is an example of some common data access scenarios. The fourth and fifth code snippets should interest you. Note that what you do with the DataTable in between populating it and saving its changes is completely up to you. It could be anything, including displaying the data in a ListView and updating it in TextBoxes and ComboBoxes.

That said, I stringly recommend that you use a DataGridView rather than a ListView. You can bind your data to a BindingSource and the BindingSource to both the grid and the other controls, then any changes made will be pushed back to the DataTable automatically, with no need for you to write any code.

 

[Ldragon99]

Ok.That is pretty clear. Letsjust say that I wanted to use the exicutenonquerry. But I wanted to update existing data. Not insert new data. How would I change the syntax? I have one field named OrderStatus from a table named OrderDetails. OrderStatus is in a combo box populated with different options for the job. Like completed, On Site, Pending. When I change the existing option It fires a event that I want to use to change the database.

Thanks. I don't kow why I am having so many issues with the update. Everything else up to this point went pretty easy.

 

[jmcilhinney]

If you want to insert data you execute an INSERT statement. If you want update data you execute an UPDATE statement. The fourth code snippet includes examples of INSERT, UPDATE and DELETE statements. SQL is SQL, regardless of how you're executing it.

 

[Ldragon99]

What am I missing

Ok lets try again. I don't know what happened there. Here is a function from my application. When the combo box status chages this script is called. The calling sub sends the OrderID (The primary key) of the OrderDetails Table. I want the function to use this intOrderID to update the status of the order in the combobox. here is the Function:

Private Function UpdateOrderStatus(ByVal intOrderID)
Dim con As New SqlConnection
Dim cmd As SqlCommand = New SqlCommand
Dim intRowsAffected As Integer

con.ConnectionString = My.Settings.TechTrackerConnectionString

cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE OrderDetails SET Status = @Status WHERE OrderID = @OrderID"
cmd.Connection = con

cmd.Parameters.Add("@Status", SqlDbType.VarChar, 25, cboMonitorSelectedStatus.Text)
cmd.Parameters.Add("OrderID", SqlDbType.Int, 4, intOrderID)

con.Open()

intRowsAffected = cmd.ExecuteNonQuery()
MessageBox.Show(intRowsAffected)

con.Close()


End Function

I am getting the follwing error at the intRowsAffected line:

The parameterized query '(@Status varchar(25),@OrderID int)UPDATE OrderDetails SET Status' expects the parameter '@Status', which was not supplied.

PLEASE help. I am at a loss as to what I am missing

 

[jmcilhinney]

Is there a question in either of those last two posts?

 

[Ldragon99]

yes, question was not clear!

Sorry. I edited the original post. I am not sure what happened. What am I missing from my code above? I cannot seem to get it to work. If I run the sql statment from my sql manager it works ok. I am missing something in my .net. Please help> I am at a stand still and canot seem to move past this.

 

[jmcilhinney]

You aren't setting the value of either parameter, nor are you using the correct name for the second. Go back and read the documentation for the Add method you're using and pay speciall attention to what the fourth argument actually is. Then go read the documentation for the AddWithValue method. Both those methods are used properly in the example code I provided.

 

[cjard]

The 5 minute solution to your problem would be to read the DW2 link in my signature, section "Creating A Simple Data App"

Also read the DNU link in ym sig for a common trap newbies fall into