Search By Date

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
VB.NET:
Private Sub GetPO()

		Dim fromdate As Date
		fromdate = cboPOsearchTD.Text + "/" + cboPOsearchTM.Text + "/" + cboPOsearchTY.Text
		

		objDS.Clear()
		objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE where Order_Date = 'fromdate'")
	  
		Try
			dgPOSearch.DataSource = objDS.Tables(0)
		Catch ex As Exception

		End Try

	End Sub
I've got a Format Error while execute this function !!

How Come ??
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Well you textboxs could easily cause you a problem in the future (I'd use a date selector), but I'd say the error is because your SQL DB wants a long datetime. Try using fromdate.tolongdatetime.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Oh I see the problem:

objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE where Order_Date = '" & fromdate & "')"

The way you had it it was looking for fromdate not formdate's value.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
:S
Which line are you getting the format error on?
fromdate = ...
or
Select ...

Also what is the exact error?
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
i think possible error in this line

Dim fromdate As Date
fromdate = cboPOsearchTD.Text + "/" + cboPOsearchTM.Text + "/" + cboPOsearchTY.Text
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
cboPosearchTD etc are comboboxes containg number right? Your +'s should be &'s but I don't think that's the problem. Add a break in your code and step through each line to find which line is giving you the error, and see what your Dim's are set too.
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
i have a Search Function in a DataAdapter below:
VB.NET:
	Public Function SearchDataSet(ByVal strSQL As String) As DataSet
		SetConnectString()
		'Initialize DataAdapter with SQL and connection string
		mobjDA = New OleDb.OleDbDataAdapter(strSQL, strConnect)
		mobjCB = New OleDb.OleDbCommandBuilder(mobjDA)
		objDS.Clear()
 
		'Fill in DataSet
		Try
			mobjDA.Fill(objDS)
			SearchDataSet = objDS 'Return dataset
		Catch ex As Exception
			str = "Error in SearchDataSet. Error No. " & Err.Number & " : " & Err.Description
			GetErrorMessage(str)
			Exit Function
		End Try
	End Function
End Class

A Search Function like this :
VB.NET:
 Private Sub btnPOSearchSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPOSearchSearch.Click
		GetPO()
	End Sub
	Private Sub GetPO()
		Dim fromdate As String
		Dim fd As Date
		fromdate = cboPOsearchTD.Text + cboPOsearchTM.Text + cboPOsearchTY.Text
		txtPOsearchBranchID.Text = fromdate
		fd = Date.Parse(fromdate)
		objDS.Clear()
		objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE where Order_Date = 'fd'")
		Try
			dgPOSearch.DataSource = objDS.Tables(0)
		Catch ex As Exception
		End Try
	End Sub
End Class

Which will call the Class to do :

VB.NET:
 	 Public Function Search_tblUser(ByVal strSQL As String) As DataSet
		Try
			Search_tblUser = objDataAdapter.SearchDataSet(strSQL)
		Catch ex As Exception
		End Try
	End Function
End Class
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Try incorporating the suggestions given above by TPM:

use a dateTimePicker (or atleast concatenate the comboBox text values so it represents a date, ie dd/mm/yyyy, ie be sure to include the slashs).

dynamically create the SELECT statement (you've hardcoded it to look for the string value 'fd' instead of the value of the variable fd).

add a break and debug the code so you can see exactly where the error is and exactly what it is.
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
how will the code look like ???

i'm not very familiar with VB.NET !!

Please give me some example please ...

i really need your help !!
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
The datetimepicker is in the toolbox, it's fairly straight forward to use. To correct your select statment change it to:

objDS = objUser.Search_tblUser("Select * from [tbl_PURCHASE] where Order_Date = '" & fd & "'")

TPM
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
no no ~ i'm not using the Datetimepicker ..

i'm using the textbox that insert by user ...
three Text boxes contains "Day" / "Month" / "Year"
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Did you do this? : Add a break in your code and step through each line to find which line is giving you the error, and see what your Dim's are set too.

If so which line is causeing the error etc?
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
Error in SearchDataSet Error No. 5 : Format mismatch

VB.NET:
  Private Sub GetPO()
		Dim fromdate As String
		Dim fromday As Date
		fromday = cboPOsearchTD.Text & "/" & cboPOsearchTM.Text & "/" & cboPOsearchTY.Text
		txtPOsearchBranchID.Text = fromday

		If cbSearchAll.Checked Then
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE ")
		Else
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE where Order_Date = '" & fromday & "'")
			Try
				dgPOSearch.DataSource = objDS.Tables(0)
			Catch ex As Exception
			End Try
		End If
	End Sub
VB.NET:
 Public Function Search_tblUser(ByVal strSQL As String) As DataSet
		Try
			Search_tblUser = objDataAdapter.SearchDataSet(strSQL)
		Catch ex As Exception
		End Try
	End Function

VB.NET:
   Public Function SearchDataSet(ByVal strSQL As String) As DataSet
		SetConnectString()
		'Initialize DataAdapter with SQL and connection string
		mobjDA = New OleDb.OleDbDataAdapter(strSQL, strConnect)
		mobjCB = New OleDb.OleDbCommandBuilder(mobjDA)
		objDS.Clear()
 
		'Fill in DataSet
		Try
			mobjDA.Fill(objDS)
			SearchDataSet = objDS 'Return dataset
		Catch ex As Exception
			str = "Error in SearchDataSet. Error No. " & Err.Number & " : " & Err.Description
			GetErrorMessage(str)
			Exit Function
		End Try
	End Function
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Dates often need to be enclosed in pound signs (#). Try changing this line:
VB.NET:
objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE where Order_Date = '" & fromday & "'")
To this:
VB.NET:
objDS = objUser.Search_tblUser("Select * from tbl_PURCHASE where Order_Date = #" & fromday & "#")
 
Top Bottom