SQL Query

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
i want to use a Combo Box contain some condition like "<50" , ">100" etc...

then compare to the database

VB.NET:
select * from table1 where Item_Amount = "& cboitemAmount.Text & "

how can i make it works ???
 
take out the "=" so it would look like the following...

VB.NET:
select * from table1 where Item_Amount "& cboitemAmount.Text & "
 
ArizonaRedneck said:
take out the "=" so it would look like the following...

VB.NET:
  select * from table1 where Item_Amount "& cboitemAmount.Text & "
does it work w/out the '=' sign?

rex028: if you want to return a result > and <
try this one:
VB.NET:
  select * from table1 where Item_Amount > " & cboitemAmount.Text & "
  or
  select * from table1 where Item_Amount < " & cboitemAmount.Text & "
 
mzim said:
does it work w/out the '=' sign?

rex028: if you want to return a result > and <
try this one:
VB.NET:
select * from table1 where Item_Amount > " & cboitemAmount.Text & "
or
select * from table1 where Item_Amount < " & cboitemAmount.Text & "
I assumed that he had the ">" or "<" listed in the combo box
rex028 said:
i want to use a Combo Box contain some condition like "<50" , ">100"
 
i have another problem now ...
i'm doing a search Function ..
TextBox:
Item number, item name, item type, item amount and item description

i have a problem is that .. when the Item description text box is empty ...
it will display ALL record ...
how can i avoid this ???

i think my coding is just mess:
VB.NET:
  Private Sub Getim()
		Dim fromdate As String
		Dim fromday, today As Date
		If cbSearchAll.Checked Then
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM ")
			dgimsearch.DataSource = objDS.Tables(0)
		ElseIf cboisearchFD.Text = "" Then
			If txtItemDesc.Text = "" Then
				desc = ""
			Else
				desc = "or Item_Desc LIKE '%" & txtItemDesc.Text & "%'"
			End If
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Item_No = '" & txtItemNo.Text & "' or Item_Type = '" & cboItemType.Text & "'  or Item_Amount " & cboitemAmount.Text & " or Item_Desc LIKE '%" & txtItemDesc.Text & "%'")
		  
			Try
				If objDS.Tables(0).Rows.Count <= 0 Then
					MsgBox("Record Not Found")
				Else
					dgimsearch.DataSource = objDS.Tables(0)
				End If
			Catch ex As Exception
			End Try
	
			Else
				fromday = cboisearchFD.Text & "/" & cboisearchFM.Text & "/" & cboisearchFY.Text
				today = cboisearchTD.Text & "/" & cboisearchTM.Text & "/" & cboisearchTY.Text
				objDS.Clear()
				objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Expire_Day Between #" & fromday & "# and #" & today & "#")
				Try
					If objDS.Tables(0).Rows.Count <= 0 Then
						MsgBox("Record Not Found")
					Else
						dgimsearch.DataSource = objDS.Tables(0)
					End If
				Catch ex As Exception
				End Try
			End If
	End Sub
 
VB.NET:
	Private Function Getim()
 
		If cbSearchAll.Checked Then
 
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM ")
 
			Getrecord()
 
 
		ElseIf cboisearchFD.Text = "" Then
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Item_No = '" & txtItemNo.Text & "' or Item_Type = '" & cboItemType.Text & "' or Item_Amount " & cboitemAmount.Text & " ")
			Getrecord()
		Else
			fromday = cboisearchFD.Text & "/" & cboisearchFM.Text & "/" & cboisearchFY.Text
			today = cboisearchTD.Text & "/" & cboisearchTM.Text & "/" & cboisearchTY.Text
 
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Expire_Day Between #" & fromday & "# and #" & today & "#")
			Getrecord()
 
		End If
 
	End Function
VB.NET:
 Private Function Getrecord()
		Try
			If objDS.Tables(0).Rows.Count <= 0 Then
				MsgBox("Record Not Found")
			Else
				dgimsearch.DataSource = objDS.Tables(0)
			End If
		Catch ex As Exception
		End Try
	End Function

Why it displays every record even there is no input ???????
 
your problem is in:
VB.NET:
or Item_Desc LIKE '%" & txtItemDesc.Text & "%'
with the wild cards on either side, if txtItemDesc.Text is a null string then it is basically saying that it wants any records that have any text in Item_Desc
I would perform an If statement of the line before to make sure there is text in the box. If there's no text, I would leave that part of the statement out.
 
Back
Top