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 ???
 
Joined
Dec 17, 2004
Messages
22
Location
Phoenix
Programming Experience
3-5
take out the "=" so it would look like the following...

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

mzim

Well-known member
Joined
Jun 3, 2004
Messages
187
Location
Other side of the rock
Programming Experience
1-3
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 & "
 
Joined
Dec 17, 2004
Messages
22
Location
Phoenix
Programming Experience
3-5
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"
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
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
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
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 ???????
 
Joined
Dec 17, 2004
Messages
22
Location
Phoenix
Programming Experience
3-5
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.
 
Top Bottom