search box?

[CGreenMTU]

we are an insurance company and we're keeping track of our claims & claimants using asp.net. We want to be able to display our records on a web form, as well as make them easy to update and insert new records to the database. For a search, we want to be able to search for a claim number in the database, and then let the search results fill the textboxes with the data relating to that particular claim number (name, address, etc...). Instead of a pop-up box, i was reading some other threads and got the idea to put a "Search" command button next to the claim number textbox on the top of the page. A user can type in a claim number, and click "Search". Then when the claim number is found, it will fill the textboxes. Can this be done??? and if so, how?

thanks alot...

 

[BoulderBum]

'%" & strSearch & "%'"

I feel like I've been saying this a lot lately, but the above is a huge security hole. Google "SQL Injection" for more info. 'Better to use parameters.

Also, to save you some typing in the future, you can call this.DataBind() for the page instead calling DataBind() for each control.

 

[CGreenMTU]

thank you very much for that input. i have another question to ask. If i have two tables from which data comes from in a dataset, do i just creat a join in the sql statement when I create the query to search the database?

 

[BoulderBum]

There are two ways to do it with ADO.NET: with a JOIN in the SQL, or by retrieving and storing the two tables into different table of a DataSet, and filtering later.

The JOIN will be the easiest solution.

 

[CGreenMTU]

I'm back again with another question. this one might be easy to answer though. I want to thank everyone again who has helped me with this search box.

If we type in a claim number in the search field and the claim number doesn't exist in the database, how can I get the web form to do NOTHING, rather than give me an error message...???

 

[BoulderBum]

If you're using a DataReader, you can tell because its .Read() method will return false on the first call. If you're using a DataSet, you can tell by checking the appropriate DataTable's Rows.Count property (if there are no rows, you have no data).

Have you considered looking into an intro book on ASP.NET? I liked the earlier edition of this one:

http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=BQv1fTfdqW&isbn=0672325438&itm=1

It should only take a few days for you to go through (it consists of 24 1-hour lessons), and it will give you most of the information you need for your page and beyond.

Good luck!

 

[CGreenMTU]

I tried using the Rows.count property, however, once I click the search button, no results for any records will display. do i have the code in the wrong place?

Private Sub lnkFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkFind.Click
Dim strSearch As String
Dim strQuery As String

'Opens connection
cnNewClaim.Open()

'Get search
strSearch = txtClaimNUmber.Text

'Set up our SQL query text
strQuery = "SELECT [Claim Number], [City of Loss], [Claim Status], [Date Closed], [Date of Loss], Division, [Driver Hire Date], [First Name], INSCompany, [Insurance Claim Number], [Last Name], [License Number], [License State], [Location Code], MI, [Policy Number], Policy_ID, ReClosed, Reopen, [Report Date], [State of Loss], Subrogation, [Time of Loss], TPA, Tractor, Trailer1, TrailorType, [Type of Loss], [Unladen?], VIN FROM TblMasterClaimsRecord WHERE [Claim Number] LIKE '%" & strSearch & "%'"

'Make sure results are received
If DsNewClaim1.Tables("TblMasterClaimsRecord").Rows.Count > 0 Then

'Create the command object, passing in the SQL string
objCommand = New OleDb.OleDbCommand(strQuery, cnNewClaim)

daNewClaim.SelectCommand() = objCommand

'Populate dataset
daNewClaim.Fill(DsNewClaim1)
ddlInsured.DataBind()
ddlDivisions.DataBind()
ddlTrailorType.DataBind()
txtInsCo.DataBind()
txtPolicyID.DataBind()
txtPolicyNumber.DataBind()
txtClaimNUmber.DataBind()
ddlLossType.DataBind()
txtInsClaimNumber.DataBind()
ddlStatus.DataBind()
txtDateClosed.DataBind()
txtReopened.DataBind()
txtReClosed.DataBind()
txtReportDate.DataBind()
txtDateofLoss.DataBind()
txtTimeofLoss.DataBind()
txtCityofLoss.DataBind()
txtStofLoss.DataBind()
ddlLocationType.DataBind()
txtTractor.DataBind()
txtTrailor.DataBind()
txtVIN.DataBind()
ckbUnladen.DataBind()
ckbTPA.DataBind()
ckbSubro.DataBind()
txtLast.DataBind()
txtFirst.DataBind()
txtMiddle.DataBind()
txtLicNum.DataBind()
txtLicSt.DataBind()
txtStartDAte.DataBind()

'Close Connection
cnNewClaim.Close()
End If



End Sub

 

[BoulderBum]

Not meaning to be rude with the question, but are you a programmer by profession?

The DataSet is empty when you're checking it because it has yet to be filled with the DataAdapter. Try the check afterwards.

 

[CGreenMTU]

no, i'm not a programmer by profession. I'm pretty much a newbie. thanks, i'll try that...