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...

 

[adonet]

Create a dataset/datareader, pass SQL statement as:

"select * from mytable where xxx = userinput",

use:

txtFirstName.Text = MyDataset.Tables("mytable").Rows(0)("FirstName")

to fill the text box or lable.text...

 

[CGreenMTU]

could somebody give me an example of the SQL code? as far as how to format it? thanks...

 

[checkai]

the sql code would just be returning a result with (usually) a 'like' statement..

select field1, field2 from table where searchfield like '%'+ userinput +'%'

this is for areas that you could get multiple search results...otherwise, if it's straight claim number you can do this

select field1, field2 from table where searchfield = userinput

 

[CGreenMTU]

I'm still a little confused. Say I want to use a textbox called txtClaimNumber.text as the search box. I want to bring back the values of First Name, Last Name, etc...to other textboxes on the web form.

how do i format that?

 

[CGreenMTU]

actually, i may have figured out the format, however i'm getting an error at search time...

this is how i'm doing it:

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

'Get search
strSearch = txtClaimNUmber.Text

'Set up our SQL query text
strQuery = "SELECT * FROM TblMasterClaimsRecord WHERE Claim Number LIKE '%" & strSearch & "%'"


txtInsCo.Text = DsNewClaim1.Tables("TblMasterClaimsRecord").Rows(0)("INSCompany")

End Sub

but, i get this error:

Exception Details: System.IndexOutOfRangeException: There is no row at position 0.

Source Error:


Line 684:
Line 685: 'ddlDivisions.SelectedValue() = DsNewClaim1.Tables("TblMasterClaimsRecord").Rows(0)("Division")
Line 686: txtInsCo.Text = DsNewClaim1.Tables("TblMasterClaimsRecord").Rows(0)("INSCompany")
Line 687:
Line 688: End Sub


Source File: C:\Inetpub\

http://wwwroot\MTU2\NewClaim.aspx.vb

Line: 686

 

[checkai]

First off, I would explicity select the fields...not using 'Select *', second I would make sure that you've gotten results back..

if DsNewClaim1.Tables("TblMasterClaimsRecord").Rows.Count >0 then

finally, this is how I bind data from a dataset to fields...

Me.txtAddress.Text = CStr(DataBinder.Eval(dsClient, "Tables[CLT].defaultView.[0].ADDRESS"))

Me.ddAnnualRevenue.SelectedIndex = Me.ddAnnualRevenue.Items.IndexOf(Me.ddAnnualRevenue.Items.FindByValue(CInt(DataBinder.Eval(dsClient, "Tables[CLT].defaultview.[0].REV"))))

 

[CGreenMTU]

In the:
Me.txtAddress.Text = CStr(DataBinder.Eval(dsClient, "Tables[CLT].defaultView.[0].ADDRESS"))

Is CLT the name of your table?

 

[CGreenMTU]

i just realized that i already have the boxes binded thru their databinding properties..

If i run it with this code and use the search button the form, the form only refreshes, but doesn't display any data...

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

'Get search
strSearch = txtClaimNUmber.Text

'Set up our SQL query text
strQuery = "SELECT [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 & "%'"

If DsNewClaim1.Tables("TblMasterClaimsRecord").Rows.Count > 0 Then
Me.txtInsCo.Text = CStr(DataBinder.Eval(DsNewClaim1, "Tables[TblMasterClaimsRecord].defaultView.[0].INSCompany"))

End If
End Sub

 

[checkai]

That looks good, does it work? I've never used bindings in any text fields before...

dlc

 

[CGreenMTU]

no. when i put the number to search for in the txtClaimNUmber.text, i dont get an error message, however, the page just refreshes and no data is displayed...

 

[checkai]

did you take off any data bindings you had with your fields?? if yes, post your code here for the button click event...to see what it is all doing.

 

[CGreenMTU]

I put the databinding property of txtInsCo.text to Unbound (to remove the bind), and manually binded it in my code like you had supplied code for....

Here is the code for the button click event Search:

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

'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 & "%'"

If DsNewClaim1.Tables("TblMasterClaimsRecord").Rows.Count > 0 Then
Me.txtInsCo.Text = CStr(DataBinder.Eval(DsNewClaim1, "Tables[TblMasterClaimsRecord].defaultView.[0].INSCompany"))
End If

End Sub

 

[CGreenMTU]

i tried it without the:

If DsNewClaim1.Tables("TblMasterClaimsRecord").Rows.Count > 0 Then
End If

and I got an error:
Exception Details: System.IndexOutOfRangeException: Index 0 is not non-negative and below total rows count.

Source Error:

Line 686: Me.txtInsCo.Text = CStr(DataBinder.Eval(DsNewClaim1, "Tables[TblMasterClaimsRecord].defaultView.[0].INSCompany"))

Source File: C:\Inetpub\

http://wwwroot\MTU2\NewClaim.aspx.vb

Line: 686

 

[checkai]

You're not executing the query and filling the dataset!!

dlc

 

[CGreenMTU]

how would i go about executing the query?

 

[CGreenMTU]

What is wrong with this? i'm having a heck of a time trying to figure out this search!!!

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

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 & "%'"
objCommand = New OleDb.OleDbCommand(strQuery, cnNewClaim)

daNewClaim.SelectCommand() = objCommand

If DsNewClaim1.Tables("TblMasterClaimsRecord").Rows.Count > 0 Then
Me.txtInsCo.Text = CStr(DataBinder.Eval(DsNewClaim1, "Tables[TblMasterClaimsRecord].defaultView.[0].INSCompany"))
End If
cnNewClaim.Close()
End Sub

 

[CGreenMTU]

alright...i think this adventure has come to an end...I got it to work FINALLY!!! thank you so much to everyone to helpled me out!!

Here is how i got it to work:

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 & "%'"

'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()

cnNewClaim.Close()
End Sub

 

[checkai]

Good work!!

 

[JCruz063]

CGreenMTU,
I regret the fact that I came late and your adventure has already ended, but there are a couple of things I would like to point out for your next adventure:

1 - It's efficient to use a DataReader instead of a DataSet object. adonet suggested it in his first post but no one mentioned it afterwards.

2 - Assuming that [Claim Number] is a numerical type, and since you're searching for ONE claim number, your WHERE clause should be WHERE [Claim Number] = " & strSearch, not WHERE [Claim Number] LIKE '%" & strSearch & "%'".

Keep those in mind for your next adventure!

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...