[aks29921]

 
Dim con As OleDbConnection
	Dim ada As OleDbDataAdapter
	Dim ds As DataSet
	Public Function Patient_Search(ByVal txt As String) As DataSet //txt value is entered by a user in a textbox
		ada = New OleDbDataAdapter("Select * from PatientProfile where PatientNo Like @txt or PatientName Like @txt   or AdmitDate Like @txt", con)
	   * ada.SelectCommand.Parameters.Add("@PatientNo", OleDbType.Integer).Value = "%" & txt & "%"
		ada.SelectCommand.Parameters.Add("@PatientName", OleDbType.VarChar).Value = "%" & txt & "%"
	   * ada.SelectCommand.Parameters.Add("@AdmitDate", OleDbType.Date).Value = "%" & txt & "%"
		ds = New DataSet
		ada.Fill(ds, "PatientProfile")
		Return ds
	End Function

This is the code i have written inside my form.
In the Parameters.Add lines, the one concerning patient name is allright as it is varchar type and i have used this type in my queries before.
But the problem lies in patient no(int type) and admit date(date type), i am in a fix trying to set parameters for these types.

wud anyone suggest appropriate changes in my code in these two lines (have marked them with an *) so that the int and date type parameters are also set successfully and my query works fine??

This post has been edited by aks29921: 15 September 2009 - 06:52 AM

[modi123_1]

Wait, what do you want? To use a written query but also parameters?

[aks29921]

as you can see, i am using a parametrised query.
if i had only string type of values to deal with, there would have been no problem.
but the problem is arising as i have to use integer & date type of parameters and i don't know how to set their values properly as i am working with them on this kind of query for the first time..
do i have to typecast them or use any wildcard/s or something else??

someone plz help

[modi123_1]

You use the parameters part if you were using a stored procedure. You clearly are not using a procedure since your select * statement is above.

what I don't get is why you were trying to set three different parameter types with the same data.

[aks29921]

i have to search a word given by a user in a textbox in my entire database..
so i am passing the text property of my textbox as a parameter while calling this function and in the function i am retrieving it in the 'txt' variable.....
hope you now fully understand what i want to do...

if this is not the correct way, then plz suggest some way to go about my job..
however, had all the data types in my database been text, this method would have worked perfectly...
man, i am confused

[rgfirefly24]

You only need one Parameter as you only reference one.

You are working with Strings. Strings need the ' around them. Also you are looking for exact matches not looking for anything that contains. So you'll want to redo your query like so:

Dim con As OleDbConnection
	Dim ada As OleDbDataAdapter
	Dim ds As DataSet
	Public Function Patient_Search(ByVal txt As String) As DataSet //txt value is entered by a user in a textbox
		ada = New OleDbDataAdapter("Select * from PatientProfile where PatientNo Like '%@txt%' or PatientName Like '%@txt%'   or AdmitDate Like '%@txt%'", con)
	  
		ada.SelectCommand.Parameters.Add("@PatientName", OleDbType.VarChar).Value = "%" & txt & "%"
		ds = New DataSet
		ada.Fill(ds, "PatientProfile")
		Return ds
	End Function

Try this it should work for you


Beyond that what i've always been told and what i like to tell people is that You always want to use Stored Procedures for all database work. Even if its a simple select put that into a stored procedure. This way you can test and rework it with out touching the page itself, and its also more secure then inline sql.

This post has been edited by rgfirefly24: 15 September 2009 - 11:19 AM

[aks29921]

was not looking for exact matches but for anything that contains..
so i tried PatientName Like @txt instead of PatientName Like '%@txt%' for all fields in my query
and it worked f9, nyways, thnx for d help.....

n yeah, i write all my queries in a separate class & call them in other forms wherever i need them.....
is that what you mean by 'stored procedures' or were u talking abt something else?

This post has been edited by aks29921: 15 September 2009 - 12:11 PM

[rgfirefly24]

No stored procedures are created in your database and called just like you would an inline one: Wikki on Stored Procedures