Creating a Data Lookup Program..help!!

awdboxer

Member
Joined
Jul 2, 2005
Messages
7
Programming Experience
Beginner
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.

VB.NET:
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
 
Last edited:

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
Try this
VB.NET:
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:

VB.NET:
While objDataReader.Read
me.cmbColor.items.add(objDataReader("Color"))
me.cmbManufacturer.items.add(objDataReader("Manufacturer"))
End While
then simply
VB.NET:
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

Member
Joined
Jul 2, 2005
Messages
7
Programming Experience
Beginner
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.
 

awdboxer

Member
Joined
Jul 2, 2005
Messages
7
Programming Experience
Beginner
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.
 

awdboxer

Member
Joined
Jul 2, 2005
Messages
7
Programming Experience
Beginner
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

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
OK it should look like as it follows:

VB.NET:
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 ;)
 
Top Bottom