Using Inputbox results to search for a record in a Sql 2008 Database Table

Question

Using Inputbox results to search for a record in a Sql 2008 Database Table

sunnyaz2003

SSC Enthusiast

Points: 123
More actions
December 14, 2008 at 9:10 am

#94849

I am a newbie at this so I would appreciate any insight you may share. I am using Visual Basic and Sql Server 2005
I have created a form in Visual Basic 2008 which has numerous textbox fields related to a particular table populated by a dataset. I have little experience actually hard coding, so I have been using controls and tableadapters to populate the fields.
I would like the user to be able to go to the form and populate the textbox with fields with particular records in the database using some form of search function without actually having to know the complete patient ID. I tried to create a new query using a query wizard but wasn't able to get anything to work unless the user input the complete patientID which defeats the purpose.
So I started to investigate using an inputbox that the user could input as much info as they had, then take the inputbox results as part of a sql string to search the table for the first record that met the string.
I have a button on the form which will allows the user to open an inputbox which asks the user to enter the patient id .
I would like to figure out the source code to allow the input box results to be used in an expression which then acts as a sql query to search the dtabase table which would then populate the fields on the form with the particular record once it has been located.
Is this possible. I searched the Internet and Books and couldn't really find anything.
Any Ideas ?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

One approach is to create a parmaterized query, using the parameter to pass the user input (be sure to trim trailing blank characters from the user's input) to a dynamic SQL statement using the LIKE function. One caveat here, you are more than like going to get multiple rows returned, so you code must be able to handle that.

Something such as:

CREATE PROCEDURE Dbo.GetPatientInfo

@PatientId VARCHAR(50) -- this value is passed by the calling program

AS

DECLARE @sql AS VARCHAR(500)

SET @sql = 'SELECT Col1, Col2, Col3 -- replace Col1 with the actual column name

FROM your tables name

WHERE patentid LIKE (@PatientID)' + '%

EXECUTE @sql

I recommend that you refer to each link for full detailed discussions:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2ef710bd-5504-4e50-aac4-371f4073cefa.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a8d68d72-0f4d-4ecb-ae86-1235b962f646.htm