Get results from two tables - asp.net/SQL Server 2008R2

Question

I have a search form with 2 fields(first name and last name) from one table (Just has person's information) and 4 (Incident number, date, place, created by) from the other (has one or more incidents for the person in the first table) linked through foreign key(nameID). I think the problem is what kind of join to use and how to use the WHERE clause.

Thanks.

More information: @Tim - Isn't the user input into one or more fields the filter or it is the WHERE Clause? The user doesn't have to fill in all the fields. Thats where I am getting lost. The user is trying to find the incident to update it. Does this help?

Also I have to use "Like%LName%" in the Where clause to get all the names if they don't enter the entire name.

My query looks like this:

Protected Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
    Dim strSearch As String
    strSearch = "SELECT tblPatron.LName, tblPatron.FName, tblIncident.CreatedBy, "
    strSearch = strSearch + "tblIncident.Inci_ID, tblIncident.Inci_date, tblIncident.Inci_type, tblIncident.Library, "
    strSearch = strSearch + "tblIncident.PatronName, tblIncident.Location "
    strSearch = strSearch + "FROM tblPatron INNER JOIN tblIncident ON tblPatron.PatronID = tblIncident.PatronID "
    strSearch = strSearch + "WHERE "
    strSearch = strSearch + "(tblPatron.LName Like '%" + txtLName.Text.ToString() + "%') "
    strSearch = strSearch + "AND (tblPatron.FNAME Like '%" + txtFName.Text.ToString() + "%')"
    strSearch = strSearch + "AND (tblIncident.Inci_ID ='" + strInciNum.Text.ToString() + "')"
    strSearch = strSearch + "AND (tblIncident.Inci_date = '" + txtInciDate.Text.ToString() + "')"
    strSearch = strSearch + "AND (tblIncident.Inci_type = '" + ddInciCat.SelectedValue.Trim + "')"
    strSearch = strSearch + "AND (tblIncident.Library = '" + ddLib.SelectedValue.Trim + "')"
    SearchPDS.SelectCommand = strSearch
    SearchPDS.DataBind()
    GridSearchResults.DataBind()
    GridSearchResults.Visible = True
End Sub

Use sql-parameters instad of string concatenation. That's an invitation for sql-injection. — Tim Schmelter, Nov 21 '13 at 21:14

score 1 · Answer 1 · answered Nov 21 '13 at 20:53

1

do this:

SELECT A.FirstName, A.LastName, B.IncidentNumber, B.Date, B.Place, B.CreatedBy
FROM Name A  INNER JOIN Incident B
ON A.NameID = B.NameID

answered Nov 21 '13 at 20:53

Phani

796
7
21

score 1 · Answer 2 · answered Nov 21 '13 at 21:10

SELECT firstname, lastname, incidentnumber, date, place, createdby
FROM name n
INNER JOIN incident i ON n.nameID = i.nameID
WHERE firstname LIKE '%'+@firstname+'%' 
  AND lastname  LIKE '%'+@lastname+'%'

Where @firstname and @lastname are parameters containing values from the search fields

In your string concatenation style, just add txtFName.Text.ToString() and txtLName.Text.ToString() into the string in place of those parameters.

score 1 · Accepted Answer · answered Nov 22 '13 at 17:21

1

I took the suggestion of logixologist. On the click event I added multiple if statements to check for the null value and then add build the query string. At the same time I made one of the dropdown to be a default value instead of "Select" and that would be my starting Where parameter. This works for me now. There might be a better way of writing the query, I am just beginner with asp.net

Thanks for all your replies. I love this forum.

answered Nov 22 '13 at 17:21

Nita

195
3
8
20

In programming there are always multiple approaches to the same task. As you will find I have seen queries written 5 different ways and all work. Just different approaches. What they are telling you with regards to sql injection is in asp.net there is a `parameter.addwithvalue` in your SqlCommand. This allows you to send parameters to the SQL Server and in your query you can just refer to them as @nameOfVariable, avoiding all the "'," mess you can get when you have to put apostrophes around it. Your way can allow someone to put malicious code in the text box and hack your app. – logixologist Nov 22 '13 at 17:31
Once your solution has been implemented, feel free to Select the Checkbox to show the question as answered. Until you do that it appears its an unresolved question. – logixologist Nov 22 '13 at 18:01
Thanks I did not know that I was supposed to do that. – Nita Nov 25 '13 at 15:42

score 0 · Answer 4 · answered Nov 21 '13 at 21:16

0

What you need is dynamic sql. Basically you start by declaring a varchar(max)

DECLARE @Sql as varchar(max)

Then you will set it to the base SQL Statement:

SET @SQL = 'SELECT A.FirstName, A.LastName, B.IncidentNumber, B.Date, B.Place, B.CreatedBy
FROM Name A  INNER JOIN Incident B
ON A.NameID = B.NameID  where lastname IS NOT null '  -- PUT IN A WHERE CLAUSE THAT WILL ALWAYS BE TRUE

---Here is the concept in pseudo code

IF @lastname is not null
BEGIN
SET @SQL = @SQL + 'and lastname = '%' + @lastname + '%'
END

IF @FIRSTNAMEis not null
BEGIN
SET @SQL = @SQL + 'and FIRSTNAME = '%' + @FIRSTNAME+ '%'
END

At the end

EXEC (@SQL)

This will give you any option they put in.

answered Nov 21 '13 at 21:16

logixologist

3,694
4
28
46

This answer was created before seeing your latest update. I didnt see your .net code. This is a stored procedure solution. – logixologist Nov 21 '13 at 21:18
I don't know how to create and run stored procedure. I will just create a string and try. Thank you. – Nita Nov 21 '13 at 21:28
@logixologist You don't need dynamic SQL for what you describe anyway, you could just say `WHERE (@lastname IS NOT NULL AND lastname = '%'+@lastname+'%') AND...` and repeat for first name.. – OGHaza Nov 21 '13 at 21:39
@oghaza the reason I was suggesting dynamic sql was because you dont know what parameters in the search field they will enter, firstname, lastname, or any other field. what if I entered the last 4 digits of the ticket number, or a partial text of the incident. You cant do it that way. – logixologist Nov 21 '13 at 21:45
or what if they entered the last name and part of the incident. There are n possible combinations of things that could be entered into the WHERE clause hence I suggested Dynamic SQL. This is the op's actual question: `The user doesn't have to fill in all the fields. Thats where I am getting lost. The user is trying to find the incident to update it.` – logixologist Nov 21 '13 at 21:47
@Nita you can do the same concept with .NET as well. Basically instead of setting @SQL. You are on the right track with the strSearch. Instead check to see if the parameter is set, if it is then add a `" AND @paramatername = "something"`. Does that make sense? – logixologist Nov 21 '13 at 21:52
I am not convinced that for a set of 6 fields (as in OP, or even more), the time taken to construct this query dynamically and then execute it could rival the time it would take the to execute a query using `(@lastname IS NOT NULL AND lastname = '%'+@lastname+'%') AND...` for each field. And this is far less simple to write and read. – OGHaza Nov 22 '13 at 17:51
How about we agree to disagree :). Its a matter of perspective. I was showing her another option. She used a hybrid of mine with her own idea in the actual solution. I am not a huge fan of dynamic sql when its not necessary but it can get equally messy using nested ifs as well. – logixologist Nov 22 '13 at 17:59

Get results from two tables - asp.net/SQL Server 2008R2

4 Answers4