Assistance with datagrid required

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

I have a simple SQL query that is executed when a command button is
clicked. This populates a datagrid and is working fine.

I have three radio buttons on the same form and when one of the radio
buttons is CHECKED and the command button is pressed, a different query is
executed, but the returned records are appended to the first record set.

I would like to clear the contents of the datagrid when the command button
is clicked and the radiobutton is checked, then populate the data with the
recordset returned from the query.

Any ideas?

Aaron
 
Aaron,

Are you using a dataset as the data source for the data grid? If so, you
can just dispose of that dataset and create a new instance with the returns
records, then assign it to the data source when you click the button. This
should show you the current results you want.

Hope that helps

Take care,
Ben S. Stahlhood II
 
I have been trying that to no avail. I am not sure of the syntax. Perhaps
an example would get me on the right track.

I have SqlAdapter1, SQLConnection1, and Dataset11.

I think you are suggesting:

DataSet11.Dispose

But I do not know what I need to do after that. It's those wizards that
make everything too easy and I didn't actually learn how to actually
"build" the dataset. Then I am not sure how you "assign it to the
datasource".

Aaron
 
Here is the code that I have been working with. I currently do not have
any code in place to do the "Clearing" of the datagrid.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ColumnNames
ColumnNames = "Activity_Result_CD, Activity_Activity_CD,
Activity_Date, Activity_FollowUp, Job_Num, App_Name_Full," & _
" Activity_Notes,
Activity_Notes_Confidential "

Dim WhereClause
WhereClause = "SELECT " & ColumnNames & "FROM Searches_Activity
WHERE "
Dim JobNumber
Dim Criteria
Dim Item
Dim JobNumArray
JobNumArray = Split(TextBox1.Text(), ",")

For Each Item In JobNumArray
JobNumber = JobNumber & " Job_num = " & Item & " or"
Next
Criteria = "(Activity_Activity_CD = 1) and (" & JobNumber
Criteria = Mid(Criteria, 1, Len(Criteria) - 2) & ")"
Dim OrderClause
OrderClause = " ORDER BY Activity_FollowUp DESC"

If RadioButton1.Checked Then
Me.SqlSelectCommand1.CommandText = WhereClause & Criteria &
OrderClause
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
ElseIf RadioButton2.Checked Then
DataSet11 = Nothing
Me.DataSet11 = New Efficient.DataSet1()
Me.SqlSelectCommand1.CommandText = WhereClause & Criteria &
OrderClause
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
End If
SqlDataAdapter1.Fill(DataSet11)
End Sub
 
Try doing this:

private void Button1_Click(object sender, System.EventArgs e)
{
if (yourRadioButton.Checked == true)
{
//
//some of your code goes here.....
//


//Create a new dataset
System.Data.DataSet dsTemp = new System.Data.DataSet();

//Fill your new dataset
try
{
//Open your connection
SqlConnection1.Open();
//Populate dataset with records
SqlAdapter1.Fill (dsTemp);
}
catch (Exception exception)
{
throw exeption;
}
finally
{
//Check if your connection is open
if (SqlConnection1.State == ConnectionState.Open)
{
SqlConnection1.Close();
}
}

//Remove records from your original dataset
yourDataSet.Clear();

//Merge with your original dataset
yourDataSet.Merge(dsTemp);

//Set your grid's data source
dataGrid1.SetDataBinding(yourDataSet,
yourDataSet.Tables["TableName"]);
}


HTH

--A
 
Here is the new code I am using, but it still does not work correctly. I'm
sorry, but I am a newbie to .NET. I just want to clear a datagrid, run a
query, and then populate the datagrid.

I would think it would be as easy as

If RadioButton1.Checked then
Datagrid1.Clear <-------This is what I cant find.....
Me.SqlSelectCommand1.CommandText = WhereClause & Criteria & OrderClause
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
SqlDataAdapter1.Fill(DataSet11)
End If

But I guess it is not...


Public Class fromDesignTimeCoded
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents DataSet11 As Efficient.DataSet1
Friend WithEvents RadioButton1 As System.Windows.Forms.RadioButton
Friend WithEvents RadioButton2 As System.Windows.Forms.RadioButton
Friend WithEvents RadioButton3 As System.Windows.Forms.RadioButton
Friend WithEvents Button2 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.TextBox1 = New System.Windows.Forms.TextBox()
Me.Button1 = New System.Windows.Forms.Button()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
Me.DataSet11 = New Efficient.DataSet1()
Me.RadioButton1 = New System.Windows.Forms.RadioButton()
Me.RadioButton2 = New System.Windows.Forms.RadioButton()
Me.RadioButton3 = New System.Windows.Forms.RadioButton()
Me.Button2 = New System.Windows.Forms.Button()
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(48, 32)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(416, 20)
Me.TextBox1.TabIndex = 1
Me.TextBox1.Text = ""
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(488, 32)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 2
Me.Button1.Text = "Retrieve"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Searches_Activity", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("Activity_Result_CD",
"Activity_Result_CD"), New System.Data.Common.DataColumnMapping
("Activity_Activity_CD", "Activity_Activity_CD"), New
System.Data.Common.DataColumnMapping("Activity_Date", "Activity_Date"), New
System.Data.Common.DataColumnMapping("Activity_FollowUp",
"Activity_FollowUp"), New System.Data.Common.DataColumnMapping("Job_Num",
"Job_Num"), New System.Data.Common.DataColumnMapping("App_Name_Full",
"App_Name_Full"), New System.Data.Common.DataColumnMapping
("Client_Company", "Client_Company"), New
System.Data.Common.DataColumnMapping("Client_Name_Full",
"Client_Name_Full"), New System.Data.Common.DataColumnMapping("App_Phone",
"App_Phone"), New System.Data.Common.DataColumnMapping("App_Cell",
"App_Cell"), New System.Data.Common.DataColumnMapping("Activity_Notes",
"Activity_Notes"), New System.Data.Common.DataColumnMapping
("Activity_Notes_Confidential", "Activity_Notes_Confidential")})})
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO Searches_Activity
(Activity_Result_CD, Activity_Activity_CD, Activity_" & _
"Date, Activity_FollowUp, Job_Num, App_Name_Full, Client_Company,
Client_Name_Ful" & _
"l, App_Phone, App_Cell, Activity_Notes,
Activity_Notes_Confidential) VALUES (@Ac" & _
"tivity_Result_CD, @Activity_Activity_CD, @Activity_Date,
@Activity_FollowUp, @Jo" & _
"b_Num, @App_Name_Full, @Client_Company, @Client_Name_Full,
@App_Phone, @App_Cell" & _
", @Activity_Notes, @Activity_Notes_Confidential); SELECT
Activity_Result_CD, Act" & _
"ivity_Activity_CD, Activity_Date, Activity_FollowUp, Job_Num,
App_Name_Full, Cli" & _
"ent_Company, Client_Name_Full, App_Phone, App_Cell,
Activity_Notes, Activity_Not" & _
"es_Confidential FROM Searches_Activity"
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Activity_Result_CD",
System.Data.SqlDbType.VarChar, 3, "Activity_Result_CD"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Activity_Activity_CD",
System.Data.SqlDbType.VarChar, 3, "Activity_Activity_CD"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Activity_Date",
System.Data.SqlDbType.DateTime, 8, "Activity_Date"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Activity_FollowUp",
System.Data.SqlDbType.DateTime, 8, "Activity_FollowUp"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Job_Num", System.Data.SqlDbType.Int,
4, "Job_Num"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@App_Name_Full",
System.Data.SqlDbType.VarChar, 50, "App_Name_Full"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Client_Company",
System.Data.SqlDbType.VarChar, 75, "Client_Company"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Client_Name_Full",
System.Data.SqlDbType.VarChar, 50, "Client_Name_Full"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@App_Phone",
System.Data.SqlDbType.VarChar, 10, "App_Phone"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@App_Cell",
System.Data.SqlDbType.VarChar, 10, "App_Cell"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Activity_Notes",
System.Data.SqlDbType.VarChar, 2147483647, "Activity_Notes"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Activity_Notes_Confidential",
System.Data.SqlDbType.VarChar, 2147483647, "Activity_Notes_Confidential"))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=BOCA2;initial
catalog=Pursuit;integrated security=SSPI;persist securi" & _
"ty info=False;workstation id=WS118;packet size=4096"
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT Activity_Result_CD,
Activity_Activity_CD, Activity_Date, Activity_FollowUp" & _
", Job_Num, App_Name_Full, Client_Company, Client_Name_Full,
App_Phone, App_Cell," & _
" Activity_Notes, Activity_Notes_Confidential FROM
Searches_Activity WHERE (Activ" & _
"ity_Activity_CD = 1) AND (Job_Num = 1920) OR (Activity_Activity_CD
= 1) AND (Job" & _
"_Num = 1958) ORDER BY Activity_FollowUp DESC"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.DataSource = Me.DataSet11.Searches_Activity
Me.DataGrid1.Dock = System.Windows.Forms.DockStyle.Bottom
Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(0, 117)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(720, 336)
Me.DataGrid1.TabIndex = 3
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
Me.DataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd"
'
'RadioButton1
'
Me.RadioButton1.Checked = True
Me.RadioButton1.Location = New System.Drawing.Point(80, 80)
Me.RadioButton1.Name = "RadioButton1"
Me.RadioButton1.TabIndex = 4
Me.RadioButton1.TabStop = True
Me.RadioButton1.Text = "RadioButton1"
'
'RadioButton2
'
Me.RadioButton2.Location = New System.Drawing.Point(208, 80)
Me.RadioButton2.Name = "RadioButton2"
Me.RadioButton2.TabIndex = 5
Me.RadioButton2.Text = "RadioButton2"
'
'RadioButton3
'
Me.RadioButton3.Location = New System.Drawing.Point(344, 80)
Me.RadioButton3.Name = "RadioButton3"
Me.RadioButton3.TabIndex = 6
Me.RadioButton3.Text = "RadioButton3"
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(488, 64)
Me.Button2.Name = "Button2"
Me.Button2.TabIndex = 7
Me.Button2.Text = "Button2"
'
'fromDesignTimeCoded
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(720, 453)
Me.Controls.AddRange(New System.Windows.Forms.Control()
{Me.Button2, Me.RadioButton3, Me.RadioButton2, Me.RadioButton1,
Me.DataGrid1, Me.Button1, Me.TextBox1})
Me.Name = "fromDesignTimeCoded"
Me.Text = "Efficiency"
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub

#End Region



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ColumnNames
ColumnNames = "Activity_Result_CD, Activity_Activity_CD,
Activity_Date, Activity_FollowUp, Job_Num, App_Name_Full," & _
" Activity_Notes,
Activity_Notes_Confidential "

Dim WhereClause
WhereClause = "SELECT " & ColumnNames & "FROM Searches_Activity
WHERE "
Dim JobNumber
Dim Criteria
Dim Item
Dim JobNumArray
JobNumArray = Split(TextBox1.Text(), ",")

For Each Item In JobNumArray
JobNumber = JobNumber & " Job_num = " & Item & " or"
Next
Criteria = "(Activity_Activity_CD = 1) and (" & JobNumber
Criteria = Mid(Criteria, 1, Len(Criteria) - 2) & ")"
Dim OrderClause
OrderClause = " ORDER BY Activity_FollowUp DESC"

If RadioButton1.Checked Then
Dim dstemp
DataSet11.Dispose()
dstemp = New System.Data.DataSet()
SqlConnection1.Open()
SqlDataAdapter1.Fill(dstemp)
If (SqlConnection1.State = ConnectionState.Open) Then
SqlConnection1.Close()


DataSet11.Clear()
DataSet11.Merge(dstemp)
Dim Searches_Activity
DataGrid1.SetDataBinding(DataSet11, Searches_activity)


Me.SqlSelectCommand1.CommandText = WhereClause &
Criteria & OrderClause
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
ElseIf RadioButton2.Checked Then
DataSet11 = Nothing
Me.DataSet11 = New Efficient.DataSet1()
Me.SqlSelectCommand1.CommandText = WhereClause &
Criteria & OrderClause
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
End If
SqlDataAdapter1.Fill(DataSet11)
End If
End Sub

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

End Sub
End Class
 
Clear the DataTable that is being used to populate the DataGrid. So there
must be a DataTable in your DataSet that is being used as the main
DataSource for the DataGrid. Whatever this table is just call it's Clear
method (Me.DataSet11.Searches_Activity.Clear()).
 
Back
Top