Search Form store result in DataGridView

Make a search form store result in DataGridView

Page 1 of 1

2 Replies - 9378 Views - Last Post: 25 November 2009 - 02:34 AM Rate Topic: -----

#1 mihaispr  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 114
  • Joined: 29-September 09

Search Form store result in DataGridView

Posted 24 November 2009 - 01:37 AM

Hi everyone and thanks for reading my question!

I experience some difficulties with property Item of DataGridView component.

The application is made in Visual Basic 2008 Express Edition and I'm using Sql Server Management Studio as DBMS.

I have made a query starting from my tables and I want to add the result of query in a datagridview in reference with what the user selects: (I have 3 textboxes and 2 datetimepickers).


Here's the code in button search:


(don't know how to add the 5 components in the dategridview and integrate the query in datagridview).


My form is this:

Posted Image


Here's the full code of search button:(where I have problems) (I made a reader to read my query and I need to store the result of query in a datagridview related with what user types in the 3 textboxes and selects from the 2 datetimepickers.)


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conexiune As SqlConnection 'for connection with sql server
		Dim comandasql As SqlCommand 'cerere -for query sql
		Dim msg As String ' msg- variabila dk suntem conectati sau nu

		Dim dgvwItem As New DataGridView() 'variable for item of a datagridview

		'connection - server name,username ',password,name_database


		conexiune = New SqlConnection("server=HOME-D16ABD96A8\PRIMAVERA;uid=mihaispr;pwd=mihai;database=ParcAuto")

		conexiune.Open() ' open the connection

' my query that I want to store it to the DataGridView

		comandasql = New SqlCommand("(SELECT tab1.NumarMasina,tab2.DataOraPlecare,tab2.DataOraSosire,tab2.LocalitatePlecare,tab2.LocalitateSosire FROM PatrimoniuMasini tab1 left join FoaieParcursMasina tab2 on tab1.IdMasina = tab2.IdMasina  " & "WHERE tab1.NumarMasina = '" & TextBox1.Text & "' " & "AND tab2.DataOraSosire = '" & DateTimePicker1.Value & "'" & "AND tab2.DataOraPlecare = '" & DateTimePicker2.Value & "'" & "AND tab2.LocalitatePlecare = '" & TextBox2.Text & "'" & "AND tab2.LocalitateSosire = '" & TextBox3.Text & "' )", conexiune)

	  'verify if it is connected to sql server (Conectat if it is 'connected)

		If conexiune.State = ConnectionState.Open Then
			MessageBox.Show("Conectat!" & msg) 'daca suntem conectati
		Else 'daca nu suntem conectati
			MessageBox.Show("Deconectat!" & msg)
		End If



		'make a reader and add in a datagridview the query

'datagridview has indexes for row and column


		Dim reader As SqlDataReader = comandasql.ExecuteReader()

		'store the query in datagridview


		While reader.Read()
			'3 textbox-uri , 2 datetimepicker
			dgvwItem = DataGridView1.Item.Add(reader.GetString(0))
			'DataGridView1.Item(row_index,column_index)
			dgvwItem.Item.Add(reader.GetString(1))

			dgvwItem.Item.Add(reader.GetString(2))

			dgvwItem.Item.Add(reader.GetString(3))

			dgvwItem.Item.Add(reader.GetString(4))


		End While
		conexiune.Close() ' end of connection
	End Sub



General syntax:

'DataGridView1.Item(row_index,column_index)


Don't know how to add the 5 things that the user completes(in the 3 textboxes and 2 datetimepickers). Don't know exactly how to use property Item for DataGridView component.

So the problem is from here:

'store the query in datagridview

  'store the query in datagridview


		While reader.Read()
			'3 textbox-uri , 2 datetimepicker
			dgvwItem = DataGridView1.Item.Add(reader.GetString(0))
			'DataGridView1.Item(row_index,column_index)
			dgvwItem.Item.Add(reader.GetString(1))

			dgvwItem.Item.Add(reader.GetString(2))

			dgvwItem.Item.Add(reader.GetString(3))

			dgvwItem.Item.Add(reader.GetString(4))
				 End While

 If conexiune.State = 0 Then
			MessageBox.Show("The record isn't found in the 2 tables")
		End If 'in the case there isn't a record in tables



I list the code in button search that is putting me problems but I also attach you the 2 files (for sql server 2 tables and the form with the storage of result in the component datagridview).

The user searches like this:

Posted Image


When it presses the search button the results will be stored in datagridview(first textbox is the field DB-20-RTS from table PatrimoniuMasini and the rest are fields from the specified columns (name of labels) from the second table FoaieParcursMasina -is the second row from this second table).

To better understand the effect of my search button I want to make a similar thing like in this application:
To better understand what I'm intending to do I try to make a similar thing like this application:

http://www.rajputbro...ource-code.html

The difference is that I connect to a database called ParcAuto(from sql server not msaccess) in this example he is connecting to a databse which lies in msaccess.

So he is making a form search through msaccess.

I want to make a similar thing through sql server as you see.

Attached File(s)


This post has been edited by mihaispr: 24 November 2009 - 04:07 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Search Form store result in DataGridView

#2 mihaispr  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 114
  • Joined: 29-September 09

Re: Search Form store result in DataGridView

Posted 24 November 2009 - 04:48 AM

I forgot to add in datagridview 5 columns(type textbox with names of my columns :

NumarMasina,DataOraPlecare,DataOraSosire,LocalitatePlecare and LocalitateSosire)

So the index must be 0,0; 0,1;0,2; 0,3; 0,4 on line 1

I add columns with specified names above by viewing the tutorial on this link:

http://www.codeproje...ineCounter.aspx



DataGridView.Item(row_index,column_index) as a general syntax what I found in help.


Just need also a Dataset component to place on my form?


in search button could be add also this:


' Create a data set to be used to store results of the subsequent queries

Dim data As New DataSet()

Try

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1")

DataGridView1.Visible = True

DataGridView1.Focus()

Catch ex As Exception

MsgBox(ex.Message)

Finally

ds.Clear()

ds.Dispose()

da.Dispose()

End Try

This post has been edited by mihaispr: 24 November 2009 - 04:57 AM

Was This Post Helpful? 0
  • +
  • -

#3 mihaispr  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 114
  • Joined: 29-September 09

Re: Search Form store result in DataGridView

Posted 25 November 2009 - 02:34 AM

Hi again my friends!

Sorry for coming back again but I made with parameterized queries the search form.


However even if I don't have error syntax in my code doesn't store the values from my specified labels in specified columns of the DataGridView (search button).


I attach here the full source so it's more simpler to see what I'm missing.


Thanks in advance!

Mike

My problem is in the search button code:

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


This is the best method to make a search form I would be glad if someone has a clue of what I'm missing truly.

Attached File(s)


This post has been edited by mihaispr: 25 November 2009 - 02:37 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1