Datagridview not populating from stored proc with 2 parameters

[Tammy]

Hi!

I am using VB 2008 with SQL Server 2000 and SQL Server 2005 (depending
which server the user selects to connect to).

I have a combox in which the user types the server to connect to.

Another combobox that shows the databases in that particular server.

Another combobox with the filesets from the selected database above.

When the user clicks on button 3, I want the datagridview to populate
data from a stored procedure passing 2 parameters in sql server.

At the moment the datagridview does not populate anything, neither
does it throw an error message. I have no clue what is wrong.

Your help will be greatly appreciated.

Thanks!

Tammy






'==========================================================================================================
'This module populates the datagridview (View Results) with basic
information (if the user has not
'marked any of the checkboxes) or complete information if he/she has
marked one or more checkboxes

'==========================================================================================================


Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click

Cursor.Current = System.Windows.Forms.Cursors.WaitCursor


If Me.comboServers.Text.Length > 0 AndAlso
Me.comboDatabases.Text.Length > 0 AndAlso Me.comboFilesets.Text.Length > 0
Then

Dim cn As New SqlClient.SqlConnection()
Dim cnb As New SqlClient.SqlConnectionStringBuilder
Dim cmd As New SqlClient.SqlCommand()


cnb.DataSource = comboServers.Text
cnb.InitialCatalog = "master"
cnb.IntegratedSecurity = True


cn.ConnectionString = cnb.ConnectionString
cn.Open()

With cmd
.CommandText = "usp_DR_Spam_BB_Search_get_recs"
.CommandTimeout = 0
.CommandType = CommandType.StoredProcedure
.Connection = cn
.Parameters.AddWithValue("@Matter",
comboDatabases.SelectedItem)
.Parameters.AddWithValue("@FileSet",
comboFilesets.SelectedItem)
End With

'cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim ds As New DataSet()
Dim dt As New DataTable("Table1")
ds.Tables.Add(dt)
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
DataGridView1.Font = New Font("SansSerif", 8.25,
FontStyle.Regular)


DataGridView1.DataSource = ds.Tables(0)

If DataGridView1.Rows.Count > 0 Then

If CheckBox1.CheckState = True Then

DataGridView1.Columns("Last Name").Visible = True


ElseIf CheckBox1.CheckState = False Then

DataGridView1.Columns("Last Name").Visible = False

End If

If CheckBox2.CheckState = True Then

DataGridView1.Columns("First Name").Visible = True

ElseIf CheckBox2.CheckState = False Then

DataGridView1.Columns("First Name").Visible = False

End If

If CheckBox3.CheckState = True Then

DataGridView1.Columns("Domain").Visible = True

ElseIf CheckBox3.CheckState = False Then

DataGridView1.Columns("Domain").Visible = False

End If

If CheckBox4.CheckState = True Then

DataGridView1.Columns("Email").Visible = True

ElseIf CheckBox4.CheckState = False Then

DataGridView1.Columns("Email").Visible = False

End If

If CheckBox5.CheckState = True Then

DataGridView1.Columns("Subject").Visible = True

ElseIf CheckBox5.CheckState = False Then

DataGridView1.Columns("Subject").Visible = False

End If

Else

MessageBox.Show("There are no records using the fileset
selected, please try with a different fileset")

End If


Dim rowNumber As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
row.HeaderCell.Value = rowNumber.ToString
rowNumber = rowNumber + 1
Next

End If



DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)

Cursor.Current = System.Windows.Forms.Cursors.Default

'cmd.Connection.Close()

End Sub

 

[Rich P]

Hi Tammy,

Here is some code you can try out


Imports System
Imports System.Data.sqlClient

Private Sub FillDatagridview()
Dim conn as sqlConnection, da As SqlDataAdapter, ds As Dataset

conn = new sqlConnection
da = New SqlDataAdapter
ds = New Dataset

conn.ConnectionString = "Data Source=yourSvr;Initial
Catalog=yourDB;Integrated Security=True"

da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = Conn
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.SelectCommand.CommandText = "yourSP"

da.Fill(ds,"tblSteveDog")
Datagridview1.DataSource = ds.Tables("tblSteveDog")
End Sub

Rich