Datagridview requery

[Chris Strug]

Hi,

As a learning exercise I have created a simple form which contains a
DataGridView, a Textbox and a Command Button. The DGW is populated from a
stored procedure which optionally takes a parameter to filter the data
retrieved.

When the form is first loaded, the DGV is populated from the procedure with
no parameter - I've managed to get this bit working fine.

What I'd like to happen next is for the user to enter a reference ("visit")
in the text box and when the user clicks the command button, the data in the
DGV is refreshed as per the procedure executing with the parameter.

However, I'm unable to do this, I just can't get the data to refresh. I'm
not sure at what stage I should be refreshing the data and who I achieve
it...

My form consists of a single DGV, one textbox and one command button. I
haven't included any error handling or such like.

I'd be much appreciative if someone could give me a few pointers as to how
to get the form to refresh as described above.

I hope I've managed to explain this clearly enough!

Many thanks

Chris.

**************************
Public Class Form1

Dim cnn As New SqlClient.SqlConnection( _
"Data Source=COMPUTER19\CSTEST;" & _
"Initial Catalog=ShipsPerf_Test;" & _
"Integrated Security=True")
Dim bs As New BindingSource()
Dim cmd As New SqlClient.SqlCommand()
Dim prm As New SqlClient.SqlParameter
Dim da As New SqlClient.SqlDataAdapter()
Dim data As New DataSet()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cmd.Connection = cnn
cmd.CommandText = "spGangsInvolvedWithVisitSelect"
cmd.CommandType = CommandType.StoredProcedure

da.SelectCommand = cmd
da.Fill(Data, "Visits")

bs.DataSource = Data
bs.DataMember = "Visits"

Me.DataGridView1.DataSource = bs

With Me.DataGridView1
.AutoResizeColumns()
.AutoResizeRows()
End With
End Sub

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdRefresh.Click
cmd.Connection = cnn
cmd.CommandText = "spGangsInvolvedWithVisitSelect"
cmd.CommandType = CommandType.StoredProcedure

prm = cmd.Parameters.Add("@Visit", SqlDbType.Int)
prm.Value = Me.txtVisit.Text

da.SelectCommand = cmd

bs.DataSource = data
bs.DataMember = "Visits"

Me.DataGridView1.DataSource = bs
Me.DataGridView1.Refresh()
End Sub
End Class

[Ken Tucker [MVP]]

Chris,

In the cmdRefresh click procedure you change the cmd for the
dataadapter. You forgot to clear the dataset and call the dataadapters fill
method again to get the data you are looking for.

Here is a link to an example.
http://www.vb-tips.com/default.aspx?...f-ba7a1d82b540

Ken
------------------

"Chris Strug" wrote:

Hi,

As a learning exercise I have created a simple form which contains a
DataGridView, a Textbox and a Command Button. The DGW is populated from a
stored procedure which optionally takes a parameter to filter the data
retrieved.

When the form is first loaded, the DGV is populated from the procedure with
no parameter - I've managed to get this bit working fine.

What I'd like to happen next is for the user to enter a reference ("visit")
in the text box and when the user clicks the command button, the data in the
DGV is refreshed as per the procedure executing with the parameter.

However, I'm unable to do this, I just can't get the data to refresh. I'm
not sure at what stage I should be refreshing the data and who I achieve
it...

My form consists of a single DGV, one textbox and one command button. I
haven't included any error handling or such like.

I'd be much appreciative if someone could give me a few pointers as to how
to get the form to refresh as described above.

I hope I've managed to explain this clearly enough!

Many thanks

Chris.

**************************
Public Class Form1

Dim cnn As New SqlClient.SqlConnection( _
"Data Source=COMPUTER19\CSTEST;" & _
"Initial Catalog=ShipsPerf_Test;" & _
"Integrated Security=True")
Dim bs As New BindingSource()
Dim cmd As New SqlClient.SqlCommand()
Dim prm As New SqlClient.SqlParameter
Dim da As New SqlClient.SqlDataAdapter()
Dim data As New DataSet()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cmd.Connection = cnn
cmd.CommandText = "spGangsInvolvedWithVisitSelect"
cmd.CommandType = CommandType.StoredProcedure

da.SelectCommand = cmd
da.Fill(Data, "Visits")

bs.DataSource = Data
bs.DataMember = "Visits"

Me.DataGridView1.DataSource = bs

With Me.DataGridView1
.AutoResizeColumns()
.AutoResizeRows()
End With
End Sub

Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdRefresh.Click
cmd.Connection = cnn
cmd.CommandText = "spGangsInvolvedWithVisitSelect"
cmd.CommandType = CommandType.StoredProcedure

prm = cmd.Parameters.Add("@Visit", SqlDbType.Int)
prm.Value = Me.txtVisit.Text

da.SelectCommand = cmd

bs.DataSource = data
bs.DataMember = "Visits"

Me.DataGridView1.DataSource = bs
Me.DataGridView1.Refresh()
End Sub
End Class

[Chris Strug]

"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in message
news:C3**********************************@microsof t.com...

Chris,

In the cmdRefresh click procedure you change the cmd for the
dataadapter. You forgot to clear the dataset and call the dataadapters
fill
method again to get the data you are looking for.

Here is a link to an example.
http://www.vb-tips.com/default.aspx?...f-ba7a1d82b540

Ken
------------------

"Chris Strug" wrote:

Ken,

Many thanks for your reply - that's certainly cleared things up!

Thanks again

Chris