UPdate DataGridView with Adapter

[SteveInBeloit]

I am having some trouble updating a dataGridView via the toolstrip.

Here is how I loaded the datagridview:

        Dim myConnection As New SqlConnection(strConn)
        Dim selCmd As SqlCommand = myConnection.CreateCommand
        Dim dt As DataTable = New DataTable
        Dim da As SqlDataAdapter = New SqlDataAdapter(selCmd)

        selCmd.CommandText = "spForkLiftTaskStatusSum"
        selCmd.CommandType = CommandType.StoredProcedure

        da.Fill(dt)

        Me.BindingSource2.DataSource = dt
'then in the designer, set the DGV to BindingSource2

All works good.

I added a toolstrip, and added a Text Button to it that says "Save". Then I went to put the code behind the save:

            Me.Validate()
            Me.BindingSource2.EndEdit()
            Me.????.Update(???)

I don't know what to put in the ????. The first set should be the adapter, but I don't think it will be able to see it (out of scope). So I tried to declare them more globably like this:

    Public myConnection1 As New SqlConnection(strConn)
    Public selCmd1 As SqlCommand = myConnection1.createCommand
    Public da1 As SqlDataAdapter = New SqlDataAdapter(selCmd1)

But I get the squiglly line under the New SqlDataAdapter saying Type SqlDataAdapter not defined.

And I am not for sure what to put under the second set of ???'s.
Any help?
Thanks

 

[SteveInBeloit]

Maybe I should ask this a different way:

I declared a data table and adapter:

Public myConnection1 As New SqlConnection(strConn)
Public selCmd2 As SqlCommand = myConnection1.CreateCommand
Public da2 As SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(selCmd2)
Public dt2 As DataTable = New DataTable

I then filled my DGV from a stored procedure:

dt2.Clear()
selCmd2.CommandText = "spAssignedTasksDispatch"
selCmd2.CommandType = CommandType.StoredProcedure
selCmd2.Parameters("@ForkLift").Value = eRowIndx
selCmd2.Parameters("@OpenClosed").Value = intOpenClosed
selCmd2.Parameters("@SortBy").Value = strPrior
selCmd2.Parameters("@AD").Value = sortOrd
da2.Fill(dt2)
Me.BindingSource2.DataSource = dt2

All works good. From the DGV in designer, I set it to the BindingSource2.

Now I want to make changes and write them back to the table. So I added a ToolStrip, and added a Save text button. I put code behind the save:

Try
Me.Validate()
Me.BindingSource2.EndEdit()
Me.da2.Update(Me.dt2)
MsgBox("Update successful")
Catch ex As Exception
MsgBox("Update failed")
End Try

It fails. I think my problem is that it has no idea where the Stored Proc got the data, so does not know what tables to update. I guess this is not ACCESS where you could update through the Stored Proc. Is this correct? I have read so many Data Walkthroughs now, that I don't know what to do. How do I tell it how and what to update?

Thank you.
Steve

 

[cjard]

Here is how I loaded the datagridview:

        Dim myConnection As New SqlConnection(strConn)
        Dim selCmd As SqlCommand = myConnection.CreateCommand
        Dim dt As DataTable = New DataTable
        Dim da As SqlDataAdapter = New SqlDataAdapter(selCmd)
 
        selCmd.CommandText = "spForkLiftTaskStatusSum"
        selCmd.CommandType = CommandType.StoredProcedure
 
        da.Fill(dt)
 
        Me.BindingSource2.DataSource = dt
'then in the designer, set the DGV to BindingSource2

Yeah.. um.. why did you do this? I mean.. I've seen your project, youre using SQL Server, you have a typed DataSet that contains tableadapters... Why on earth would you revert to the .NET 1.1 way of doing things when all the classes you need are there, built for you by the IDE, and not being used?

I added a toolstrip, and added a Text Button to it that says "Save". Then I went to put the code behind the save:

            Me.Validate()
            Me.BindingSource2.EndEdit()
            Me.????.Update(???)

It should be:
Me.whateverTableAdapterName.Update(Me.whateverDataSetName.WhateverDataTableName)

I don't know what to put in the ????. The first set should be the adapter,

TableAdapter, not DataAdapter

but I don't think it will be able to see it (out of scope). So I tried to declare them more globably like this:

Dragging the grid to the form from the datasources window will create a tableadapter, dataset and bindingsource for you, at the form class level. You must have already done this, unless you dropped your bindingsources on and configured them manually..

Which leads me to ask the question, why?

The IDE does so much for you:

Make a dataset
Opent he Data Sources window, NOT the Toolbox
Drag a grid from it, to the form
A BindingSource, BindingNavigator, DataSet, DataGridView and TableAdapter are all added for you and configured correctly in a matter of seconds.


If youre really lost as to what Im talking about then read the DW2 link in my signature, section on creating a simple application.. It is a perfect example of everything youre trying to achieve here with the sole exception that it uses a straight query rather than a stored procedure. This difference is negligible to the overall teaching concept that is going on

Maybe I should ask this a different way

Umm.. more like asking in a different forum.. As you may have detected, I answer quite a few data access queries; I dont have much to do with stuff in the winforms grids forums because the focus is (should be) more about bending grids to do wacky things like rendering a treeview in every cell, not about plain and simple ADO.NET problems (which have their own forum)

 

[cjard]

It fails. I think my problem is that it has no idea where the Stored Proc got the data, so does not know what tables to update. I guess this is not ACCESS where you could update through the Stored Proc. Is this correct? I have read so many Data Walkthroughs now, that I don't know what to do. How do I tell it how and what to update?

This is why you should be using your typed dataset, not dataadapters.. Think about this for a second:

When you use a stored procedure to read data, it isnt really guaranteed that .NET is going to be able to work out what to do to send the data back again.

When you use SQL, its easy. You give an SQL like:

SELECT a,b,c FROM table


It reads the schema and finds out the primary key. Knowing the PK, it can write update, insert and delete queries:

INSERT INTO table VALUES(@a,@b,@c) 'each @ is a parameter...
UPDATE table SET a = @a, b = @b, c = @c WHERE <primary key col> = @original_<primary key col>
DELETE FROM table where WHERE <primary key col> = @original_<primary key col>


But it cannot do this for stored procedures;; it cannot know whether you want to use straight queries, other stored procedures, a mix.. So it needs telling. As a human, I cant even always be sure, looking at the stored procedure code, which table is being selected from or what is in the result set. That the IDE can map a datatable at all is some kind of voodoo

Doing this in code, yourself is a HUGE pain in the arse, involving a lot of donkeywork setting up the procedure, the parameters, mapping the parameters to table columns.. So boring and unnecessary, just do it once, with the IDE, in the dataset designer.

After you read the DW2 simple app section, read the DW2 sections on stored procedures

 

[SteveInBeloit]

cjard,

Believe me man, I am trying hard. I have been through the walkthroughs in your signature lots in the last two weeks. On the other forms and DGVs, I have dragged everything over from the DataSource tab, and it created everything for me. All works good.

On this particular one, the stored procedure is not in the datasources tab. Like you have told me, it did not know what table to update to build it all for me, I now fully understand why it did not show up. This DGV has to be based on this stored proc, so I have to go from here. So I think that I have to set up the table adapter with the four stored procs, C/I/U/D.

I *think* there are two ways to do this. Open data set designer and somehow create a datatable and add a tableAdapter indicating to use my four stored procs, or, do it all in code somehow.

I don't want to make things any harder than they have to be, and was thrilled when I caught on to how good it can be when it can all be done from the DataSource tab. But for this particular one, it doesn't appear to be that way.

Thanks again,
I really appriciate your help.

 

[cjard]

On this particular one, the stored procedure is not in the datasources tab.

Stored Procedures are never in the datasources tab. You will only find datasets, datatables and datarelations there

Like you have told me, it did not know what table to update to build it all for me, I now fully understand why it did not show up. This DGV has to be based on this stored proc, so I have to go from here. So I think that I have to set up the table adapter with the four stored procs, C/I/U/D.

If there are four stored procedures, one for select, one for insert, one for update and one for delete then yep, you will have to manually set them ALL, and you will have to manually ensure that the SourceColumn of each parameter matches the parameter itself..

So your datatable has columns A, B, C
You tell the IDE that the update command is MyXYZStoredProcedure
It reads the parameter spec which is @pA, @pB, @pC
You must then go to the Parameters collection, and for each parameter, set the SourceColumn to the relevant A, B or C

If you dont do this, then the tableadapter will not know which columns in a particular row, to pass to the sproc

I *think* there are two ways to do this. Open data set designer and somehow create a datatable and add a tableAdapter indicating to use my four stored procs, or, do it all in code somehow.

Do it in the designer, the walkthrough are in my sig, but heres a brief:

Right click surface
Add TableAdapter
"Use existing stored procedures"
Pick all 4
Map all the columns..

Adding a new one is easier than modifying an existing TA to use sprocs, because the wizard guides you...

 

[SteveInBeloit]

From the designer, after I have chosen the four stored procs, it lists the parameters and wants the source column, but the drop downs are empty, and it won't let you type in there. It does not know which dataTable I want it to be referenced with.

 

[cjard]

Then I guess whichever sproc you picked for the select didnt return the values properly for generating the datatable..

Suppose I have a table Customer that has 4 columns: FirstName, LastName, Datum and ID
Here is a script that will make some nice stored procedures:

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'CustomerSelectCommand' AND user_name(uid) = 'dbo')
 DROP PROCEDURE dbo.CustomerSelectCommand
GO
CREATE PROCEDURE dbo.CustomerSelectCommand
AS
 SET NOCOUNT ON;
select id, firstname, lastname, datum from customer
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'CustomerInsertCommand' AND user_name(uid) = 'dbo')
 DROP PROCEDURE dbo.CustomerInsertCommand
GO
CREATE PROCEDURE dbo.CustomerInsertCommand
(
 @firstname nvarchar(50),
 @lastname nvarchar(50),
 @datum smalldatetime
)
AS
 SET NOCOUNT OFF;
INSERT INTO [customer] ([firstname], [lastname], [datum]) VALUES (@firstname, @lastname, @datum);
 
SELECT ID, FirstName, LastName, Datum FROM Customer WHERE (ID = SCOPE_IDENTITY())
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'CustomerUpdateCommand' AND user_name(uid) = 'dbo')
 DROP PROCEDURE dbo.CustomerUpdateCommand
GO
CREATE PROCEDURE dbo.CustomerUpdateCommand
(
 @firstname nvarchar(50),
 @lastname nvarchar(50),
 @datum smalldatetime,
 @Original_id int,
 @ID int
)
AS
 SET NOCOUNT OFF;
UPDATE [customer] SET [firstname] = @firstname, [lastname] = @lastname, [datum] = @datum WHERE (([id] = @Original_id));
 
SELECT ID, FirstName, LastName, Datum FROM Customer WHERE (ID = @ID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'CustomerDeleteCommand' AND user_name(uid) = 'dbo')
 DROP PROCEDURE dbo.CustomerDeleteCommand
GO
CREATE PROCEDURE dbo.CustomerDeleteCommand
(
 @Original_id int
)
AS
 SET NOCOUNT OFF;
DELETE FROM [customer] WHERE (([id] = @Original_id))
GO

I didnt write this; i got the designer to write it. Think of it as a reference for the perfect set of sprocs that the designer will like. I didnt generate commands with optimistic concurrency options for clarity.


Right.. Now I have my commands...

And I go:

Add TA
Existing SProcs
Select Sproc -> Pick it (now i see a list of available columns that this sproc returns)
Insert -> pick it. (now i see a list of params and another list of blank boxes that I can pick the source columns from)

...

Actually, the designer can work out which columns to map to which params by reading the sproc text, because it is nice and simple

If youre not finding it this easy, then examine your sprocs. If youre still really lost with it, then dump the sprocs and get the IDE to make you some new ones (then apply any optimizations to them and all is good to go)

 

[SteveInBeloit]

I created several other select sprocs and they all worked. Mine doesn't, the IDE must not be able to figure it out enough. It does a lot of stuff, several selects along the way, builds and sql string and does an exec(string), maybe that is it. Thanks a lot for the help, I think it would all work if my sproc was different. I could look into re-writing it, but it is a large existing system, and I didn't really want to re-engineer it.
Steve

 

[cjard]

builds and sql string and does an exec(string),

As far as your IDE is aware, that SProc doesnt have a select statement at the end, it has an EXEC.. That's like asking McAfee Virus Scan to read the source code of a trojan you wrote, and report it as a trojan; it wont - its looking for a exe trojan, not the source code to a trojan.. Do you know what I mean?

Perhaps, if you posted the SProc text I could help you re-engineer it