Datagridview not populating from stored proc with 2 parameters

[=?Utf-8?B?VGFtbXk=?=]

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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button3.Click

Cursor.Current = System.Windows. Forms.Cursors.W aitCursor


If Me.comboServers .Text.Length 0 AndAlso
Me.comboDatabas es.Text.Length 0 AndAlso Me.comboFileset s.Text.Length 0
Then

Dim cn As New SqlClient.SqlCo nnection()
Dim cnb As New SqlClient.SqlCo nnectionStringB uilder
Dim cmd As New SqlClient.SqlCo mmand()


cnb.DataSource = comboServers.Te xt
cnb.InitialCata log = "master"
cnb.IntegratedS ecurity = True


cn.ConnectionSt ring = cnb.ConnectionS tring
cn.Open()

With cmd
.CommandText = "usp_DR_Spam_BB _Search_get_rec s"
.CommandTimeout = 0
.CommandType = CommandType.Sto redProcedure
.Connection = cn
.Parameters.Add WithValue("@Mat ter",
comboDatabases. SelectedItem)
.Parameters.Add WithValue("@Fil eSet",
comboFilesets.S electedItem)
End With

'cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteRead er()
Dim ds As New DataSet()
Dim dt As New DataTable("Tabl e1")
ds.Tables.Add(d t)
ds.Load(reader, LoadOption.Pres erveChanges, ds.Tables(0))
DataGridView1.F ont = New Font("SansSerif ", 8.25,
FontStyle.Regul ar)


DataGridView1.D ataSource = ds.Tables(0)

If DataGridView1.R ows.Count 0 Then

If CheckBox1.Check State = True Then

DataGridView1.C olumns("Last Name").Visible = True


ElseIf CheckBox1.Check State = False Then

DataGridView1.C olumns("Last Name").Visible = False

End If

If CheckBox2.Check State = True Then

DataGridView1.C olumns("First Name").Visible = True

ElseIf CheckBox2.Check State = False Then

DataGridView1.C olumns("First Name").Visible = False

End If

If CheckBox3.Check State = True Then

DataGridView1.C olumns("Domain" ).Visible = True

ElseIf CheckBox3.Check State = False Then

DataGridView1.C olumns("Domain" ).Visible = False

End If

If CheckBox4.Check State = True Then

DataGridView1.C olumns("Email") .Visible = True

ElseIf CheckBox4.Check State = False Then

DataGridView1.C olumns("Email") .Visible = False

End If

If CheckBox5.Check State = True Then

DataGridView1.C olumns("Subject ").Visible = True

ElseIf CheckBox5.Check State = False Then

DataGridView1.C olumns("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.R ows
If row.IsNewRow Then Continue For
row.HeaderCell. Value = rowNumber.ToStr ing
rowNumber = rowNumber + 1
Next

End If



DataGridView1.A utoResizeRowHea dersWidth(DataG ridViewRowHeade rsWidthSizeMode .AutoSizeToAllH eaders)

Cursor.Current = System.Windows. Forms.Cursors.D efault

'cmd.Connection .Close()

End Sub

[Rich P]

Re: Datagridview not populating from stored proc with 2 parameters

Hi Tammy,

Here is some code you can try out


Imports System
Imports System.Data.sql Client

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

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

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

da.SelectComman d = New SqlCommand
da.SelectComman d.Connection = Conn
da.SelectComman d.CommandType = CommandType.Sto redProcedure
da.SelectComman d.CommandText = "yourSP"

da.Fill(ds,"tbl SteveDog")
Datagridview1.D ataSource = ds.Tables("tblS teveDog")
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***