Creating a Data Lookup Program..help!!

[awdboxer]

help!!

I'm a VB noob so please bear with me. I'm trying to code a Do While statement that loops through the OleDbDataReader object. Within the Do While statement, I have to build a string composed of the fields returned by the SELECT statement. The fields are as follows: ProductName, Price, InStock, Color, and Manufacturer. All the fields in the table are of the String data type. For example, the following code concatenates the ProductName fields onto a string named srtOutput:
strOutput &= objOleDbDataReader.Item("ProductName")
Attached is a doc (sorry about the quality, i had to bump it way down to fit) of the db and what the output should look like. My code is below.
Anyways if any one can help or give me an example on how to write that Do While statement I'd greatly appreciate it.
Thanks.

Private Const gstrConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=ProductList.mdb"

  Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
		Dim objConnection As OleDbConnection
		Dim objDataReader As OleDbDataReader
		Dim objCommand As OleDbCommand
		Dim stroutput As String

		If cmbColor.Text = String.Empty Then
		 MessageBox.Show("Please select a color from the list.", "No color selected.")
		End If

		If cmbManufacturer.Text = String.Empty Then
		 MessageBox.Show("Please select a manufacturer from the list.", "No manufacturer selected.")
		End If

		objConnection = New OleDbConnection(gstrConnectionString)
		objCommand = New OleDbCommand
		objCommand.Connection = objConnection
		objCommand.Connection.Open()
		objCommand.CommandText = "SELECT ProductName, Price, InStock, Color, Manufacturer FROM ProductList WHERE Color LIKE '" & cmbColor.Text & "' AND Manufacturer LIKE '" & cmbManufacturer.Text & "' ORDER BY Manufacturer"
		objDataReader = objCommand.ExecuteReader

	   While (objDataReader.Read())
		 MessageBox.Show("Product Name :" & objDataReader("ProductName") & ControlChars.NewLine & "Price :" & objDataReader("Price") & ControlChars.NewLine & "In Stock? :" & objDataReader("inStock"))

		End While
		objDataReader.Close()

	   
		
	End Sub
End Class

 

[kulrom]

Try this

While objDataReader.Read
If cmbColor.ValueMember = "Black" Then
			MessageBox.Show("Please Enter a color.", "Error")
		Else 
MessageBox.Show("Congrats.", "Yay")
		End If
End While

actually i would do that as it follows:

While objDataReader.Read
me.cmbColor.items.add(objDataReader("Color"))
me.cmbManufacturer.items.add(objDataReader("Manufacturer"))
End While

then simply

Private Sub blah {...}
If cmbColor.Text = "Black" Then
MessageBox.Show("Please Enter a color.", "Error")
Else 
MessageBox.Show("Congrats.", "Yay")
End If
End Sub

Cheers

 

[awdboxer]

Awesome, thanks for the tip. In that little While statement how would you code it for a blank box? I'm trying to set it up so that it checks to make sure the user selected a value in both ComboBox controls and if not to have the msg box pop up and tell them to select a value.

 

[kulrom]

If combo.text = String.Empty then
MessageBox.Show("Please select an item from blah blah ... !!!")
End If

or

If combo.SelectedIndex < 0 Then 'default selectedindex is (-1)
MessageBox.Show("Please select an item from blah blah ... !!!")
End If

Cheers

 

[awdboxer]

ahh, thanks a ton..that part works like a charm! Now I just have to figure out how to write that SQL code to give me the output in that pic.

 

[kulrom]

Ok i will help you out with this too .... just give me the scenario

Cheers

 

[awdboxer]

Ok, cool. I updated my code up above and linked the pics to the database I'm working with and what the final output should look like. What I don't get is how I'm supposed to write the SQL and link it to the db so when you select a color and manufacturer that msg box will pop up and give you ProductName, Price, InStock, Color, and Manufacturer fields with the values.
Again, thank you very much for all the help.

 

[kulrom]

Now I just have to figure out how to write that SQL code to give me the output in that pic

Which picture ... can you explain that part?

Cheers

 

[awdboxer]

The last pic with the Record Found! msg box.

 

[awdboxer]

I almost have it, I updated the code above. I just can't get that Record Found! message box to pop up.

 

[kulrom]

OK it should look like as it follows:

strSQL = "SELECT * FROM Table1 WHERE Color='" & cmbColor.Text & "' AND manufacturer = '" & cmbManufacturer.Text & "'"
 
While objread.Read
MessageBox("Product Name :" & objread("ProductName") & ControlChars.NewLine & "Price :" & objread("Price") & ControlChars.NewLine & "In Stock? :" & objread("inStock"))
End While
objread.Close()

Cheers

 

[awdboxer]

awesome, i got it all working...thanks for all the help.