[RESOLVED] Newbie - Stored procedure fires, table on form not updated?

[Larry Hinklestein]

I'm basically trying to copy a column from one table to another and then display it on a form. Here's what I did -


- Created new Windows application and added a service-based database. ( Visual Basic 2010 Express )

- Added two identical tables to database (Table 1, Table 2) each containing one column named "Component" - Table 1 contains a few rows of data, Table 2 is empty.

- Added a dataset and added both tables to dataset.

( The above instructions I got from a video showing how to add a database to a project, it was all done in the designer using no code )


- Added a button and a DataGridView on the form. Datagrid DataSource = DataSet1, DataMember = Table2.

- Created the following stored procedure - doing a test execute returns no errors -

ALTER PROCEDURE dbo.StoredProcedure3

AS
		
 INSERT INTO Table2 (Component)	
 SELECT [Component]
 FROM Table1
	
	RETURN

I then added this code into the form ( sample code from net to which I added the currently existing connection string ) -

Imports System.Data.SqlClient


Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim connectionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet

        connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\bob\Documents\Visual Studio 2010\Projects\DatabaseTest1\DatabaseTest1\Database1.mdf"";Integrated Security=True;User Instance=True"
        connection = New SqlConnection(connectionString)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "StoredProcedure3"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)

        connection.Close()
    End Sub

End Class

I run the program, click the button, the program doesn't freeze or crash or anything, but nothing shows up on the Datagrid on the form.

My guess is that I did the same thing twice by creating a dataset in the designer and then trying to use a different one in the code.

Thanks to everyone for being so patient with me, this stuff can be awfully confusing when you're first starting out.

[Larry Hinklestein]

Added - this is the result from doing a test execute on the query -


Running [dbo].[StoredProcedure3].

(1 row(s) affected)
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[StoredProcedure3].


Looks like it's not returning anything ?

[TysonLPrice]

I'm missing something???

It doesn't look like you are selecting anything. Just inserting into another table.

[Larry Hinklestein]

I'm missing something???

It doesn't look like you are selecting anything. Just inserting into another table.

I thought that was what the SELECT statement did.

INSERT INTO Table2 (Component)
SELECT [Component]
FROM Table1

[Larry Hinklestein]

Stored procedure -

ALTER PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
INSERT INTO Table2
SELECT Component
FROM Table1

RETURN

I right click and execute it, it works, it copies table1 to table2.


Added a button to the form, inserted this code -

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim SQLCONN As New SqlConnection
Dim SQLCMD As New SqlCommand
SQLCONN.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\bob\Documents\Visual Studio 2010\Projects\datatest\datatest\Database1.mdf"";Integrated Security=True;User Instance=True"
SQLCONN.Open()
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.StoredProcedure
SQLCMD.CommandText = "dbo.StoredProcedure1"
SQLCONN.Close()

DataGridView2.Refresh()
End Sub


Click the button, nothing happens. I copied the connection string straight out of the database properties window.

Is VB2010 express buggy ?

[Larry Hinklestein]

Ah - I forgot to execute the thing - added this -

SQLCMD.ExecuteNonQuery()

Clicking the button on the form appears to do nothing, but if I stop the program and start it up again the DataGridView is updated with the added data... I thought DataGridView2.Refresh() would do that right after I clicked the button... gonna go poke around the net some more.

[FunkyDexter]

I thought that was what the SELECT statement did.

INSERT INTO Table2 (Component)
SELECT [Component]
FROM Table1

Nope. The select statement is simply feeding rows into the insert statement. It's not returning rows back to the client.

I looked at your post yesterday but didn't respond because I never use the designer generated stuff so wasn't all that confident I'd give you the best answer. As you don't seem to be getting much response from elsewhere though I'll take a punt. If another member whose more familiar with the designer generated stuff pops in you should probably take their advice over mine.

What your code is doing looks roughly like this:-
1. Read the records from an empty Table 2 into a client side data table
2. Show the contents of the client side data table in a data grid view
3. Copy the contents of Table 1 into Table
4. ... there is no 4

The bit that's missing is that you now want to read the contents of the newly filled Table 2 into a client side data table and then show the contents in your data grid view. I'm not sure if there are shortcuts to doing that when you've created your datasets using the designer but without the designer you would do the following:-
Change your stored procedure slighty so that you select * from Table 2 after you've done the insert (this should not affect the select on your insert - you should have 2 selects, 1 to feed the insert and 1 to return results to the client). Once you've done that you should notice that ds contains data after you fill it with the data adapter. Now rebind the datagridview to ds using myDataGridView.datasource=ds.

That should be it.

[techgnome]

Ah - I forgot to execute the thing - added this -

SQLCMD.ExecuteNonQuery()

Clicking the button on the form appears to do nothing, but if I stop the program and start it up again the DataGridView is updated with the added data... I thought DataGridView2.Refresh() would do that right after I clicked the button... gonna go poke around the net some more.

Why would it? you're updating the database directly... not the datagrid... if you update the database directly, then you need to re-load the data into the grid...

-tg

[Larry Hinklestein]

Why would it? you're updating the database directly... not the datagrid... if you update the database directly, then you need to re-load the data into the grid...

-tg

Yes, I understand that - the database itself is updated so now I need to update the table in the dataset - can't figure out how to do that though.

[techgnome]

well think about it... how did you populate the grid in the first place?

-tg

[Larry Hinklestein]

well think about it... how did you populate the grid in the first place?

-tg

By dragging the datagridview from the toolbox onto the form. A box then popped up asking what to use for the data source from which I selected Table2BindingSource.

Looking at the properties for the datagridview it also says DataSource = Table2BindingSource.

Okay, then it looks like all I have to do is update the Table2BindingSource.

So I look at the properties for Table2BindingSource and it says -

DataSource - DataSet1
DataMember - Table2


Okay, well this is what I'm trying to do, update the table in the DataSet.


I type DataSet1.Table2. - and look at the options in the popup box.

No refresh option, can't do that. Looks like the only thing that would do the job would be the Load
option, so I type that in, add the first parenthesis, and it says I have to use a DataReader.

Before I spend a few hours trying to learn how to use a DataReader, can you tell me if I'm on the right track?


Thanks.

[Larry Hinklestein]

Another evening with no progress.

This simply has to be a bug, there's no other answer.

I know for certain the query is being executed. In the Database Explorer I can right-click on the tables and see that the data has changed. In the Data Sources windows I can right-click on the tables and see that the data has changed.

The DataGridView is bound to the Table2BindingSource, and the Table2BindingSource is bound to Dataset/Table2.

If the data in Dataset/Table2 changes, then DataGridView.refresh() should update the datagridview but it does not.

Spock would be pulling his hair out by now.

Maybe I got a bad download, don't know. I'll try again and make sure to check the MD5sum or whatever it is.

Later.

[techgnome]

If the data in Dataset/Table2 changes, then DataGridView.refresh() should update the datagridview but it does not.

Right... the refresh method (in any control) causes a repaint... not a reloading of the data...

Spock would be pulling his hair out by now.

No he wouldn't...

Let me ask this... is there a reason for inserting the data directly? Rather than using the dataset/datatable? Normally people go one way or the other... using pure Typed Datasets start to finish, or using commands directly... but rarely mixed. I've never liked typed datasets so I don't use them often.

But...

You use the TableAdaptor to fill the datatable...

        Me.ConstitTestTableAdapter.ClearBeforeFill = True
        Me.ConstitTestTableAdapter.Fill(Me.SandboxDataSet.ConstitTest)

-tg

[Larry Hinklestein]

Right... the refresh method (in any control) causes a repaint... not a reloading of the data...



No he wouldn't...

Let me ask this... is there a reason for inserting the data directly? Rather than using the dataset/datatable? Normally people go one way or the other... using pure Typed Datasets start to finish, or using commands directly... but rarely mixed. I've never liked typed datasets so I don't use them often.

But...

You use the TableAdaptor to fill the datatable...

        Me.ConstitTestTableAdapter.ClearBeforeFill = True
        Me.ConstitTestTableAdapter.Fill(Me.SandboxDataSet.ConstitTest)

-tg

The datatable reloads fine, the DataViewGrid on the form doesn't.

This is what I have -


The database contains 2 tables, Table1 and Table2. The tables are identical and contain one column each. Table1 has a few lines of data, Table2 is empty.

These 2 tables were dragged over into to the DataSet.

There are 2 DataGridViews on the form, DataGridView1 is binded to Table1BindingSource, DataGridView2 is binded to Table2BindingSource.

There are 2 buttons on the form, Button1 to copy the data from Table1 to Table2 and Button2 to clear the data from Table2.

Here is the entire code -

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim cmd As SqlCommand
        Dim sql As String

        ' The connection string connects to the database itself
        connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\bob\Documents\Visual Studio 2010\Projects\datatest4\datatest4\Database1.mdf"";Integrated Security=True;User Instance=True"

        ' This SQL command copies Table1 to Table2
        sql = "INSERT INTO Table2 SELECT * FROM Table1"

        ' Open the connection and execute the SQL command
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        cmd = New SqlCommand(sql, cnn)
        cmd.ExecuteNonQuery()

        ' Reload data from database into dataset, works fine
        Me.Table2TableAdapter.ClearBeforeFill = True
        Me.Table2TableAdapter.Fill(Me.Database1DataSet.Table2)

        DataGridView2.Refresh()

        cnn.Close()
    End Sub



    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim connectionString2 As String
        Dim cnn2 As SqlConnection
        Dim cmd2 As SqlCommand
        Dim sql2 As String

        ' The connection string connects to the database itself
        connectionString2 = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\bob\Documents\Visual Studio 2010\Projects\datatest4\datatest4\Database1.mdf"";Integrated Security=True;User Instance=True"

        ' This SQL command clears Table2
        sql2 = "DELETE FROM Table2"

        ' Open the connection and execute the SQL command
        cnn2 = New SqlConnection(connectionString2)
        cnn2.Open()
        cmd2 = New SqlCommand(sql2, cnn2)
        cmd2.ExecuteNonQuery()

        ' Reload data from database into dataset, works fine
        Me.Table2TableAdapter.ClearBeforeFill = True
        Me.Table2TableAdapter.Fill(Me.Database1DataSet.Table2)

        DataGridView2.Refresh()

        cnn2.Close()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Database1DataSet.Table2' table. You can move, or remove it, as needed.
        Me.Table2TableAdapter.Fill(Me.Database1DataSet.Table2)
        'TODO: This line of code loads data into the 'Database1DataSet.Table1' table. You can move, or remove it, as needed.
        Me.Table1TableAdapter.Fill(Me.Database1DataSet.Table1)

    End Sub

End Class

Before running the program I check Table2 in both the DataBaseExplorer and the DataSet and they're both empty.

I run the program and see that DataGridView1 shows the few lines of data from Table1, and DataGridView2 is empty (they both show the column header).

I run the program, click Button1 to copy the data, nothing seems to happen and I exit the program.

I check Table2 again in the DataBaseExplorer and the DataSet and they now both contain the data from Table1.

I start the program again and DataGridView2 now shows the new data that was copied to Table2.

I click Button2 to clear Table2, nothing seems to happen and I exit the program.

I check Table2 again in the DataBaseExplorer and the DataSet and they're both empty.

I start the program again once more, and DataGridView2 is now empty.



Everything works fine except the DataGridView, it simply will not update and show the new data while the program is running.

[techgnome]

In doing some searches on the interweb for "reload typed dataset" ... I found some posts about the grid not refreshing... seems the solution was to change the datasource...

        ' Reload data from database into dataset, works fine
        Me.Table2TableAdapter.ClearBeforeFill = True
        Me.Table2TableAdapter.Fill(Me.Database1DataSet.Table2)

        DataGridView2.DataSource = new List(Of String)
        DataGridView2.DataSource = Me.Database1DataSet.Table2

        DataGridView2.Refresh()

Seems like a bit of a hack to me, but the general consensus is that it works...

-tg

[Larry Hinklestein]

In doing some searches on the interweb for "reload typed dataset" ... I found some posts about the grid not refreshing... seems the solution was to change the datasource...

        ' Reload data from database into dataset, works fine
        Me.Table2TableAdapter.ClearBeforeFill = True
        Me.Table2TableAdapter.Fill(Me.Database1DataSet.Table2)

        DataGridView2.DataSource = new List(Of String)
        DataGridView2.DataSource = Me.Database1DataSet.Table2

        DataGridView2.Refresh()

Seems like a bit of a hack to me, but the general consensus is that it works...

-tg

Doesn't work on mine.

Went to Microsoft, did all the updates ( which took 6 frickin' hours on Vista - geez ), no change.

If it's any consolation I loaded up C# and it doesn't work there either LOL.

Thanks anyway for the help, I'll find something else to use. Visual Studio Express is for 'Evaluation Purposes Only', well for me it didn't evaluate very well at all.

I'll mark this thread as closed.