Listviews and SQL Server

[Papa_Don]

Group,

I've read through the tutorial regarding Listviews and SQL's (http://www.daniweb.com/software-development/vbnet/code/445801/avoiding-sql-injection-attacks). Thanks for the article. It was very imformative.

So I thought I might try this with the project I'm doing. I really like how these listview look and what they can do. But I realize I need to know more and understand it better. So.... my first question:

The code in the example reads:

cmd.CommandText = "SELECT au_lname,au_fname,phone " _
                        & "  FROM authors                 " _
                        & " WHERE au_lname like @lastName " _
                        & "   AND phone    like @phone    " _
                        & " ORDER BY au_lname             "

It appears to me that what you are doing here is two things: 1) you are creating your search criteria and, 2) you are defining to "au_last" to named "@lastName" and the "phone (number) to be named "@phone". I am also presuming you want the list to order it alphabetically by last name. Can you confirm that my assumptions are true? If not, I need more clarification.

Now the second question: What does these lines of code do?

        cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)
        cmd.Parameters.AddWithValue("@phone   ", txtPhone.Text)

Some of you may have responded to some questions about populating data within a DataGridView. Having now seen how the listview looks, I really like it much better..... and it seems you can do more with it.

I look forward to the answers. I'd like to try to use these snippets as code within the project I'm working.

Thanks for all each of you do!

Don

[scudzilla]

1) Yes, we define the search criteria. WHERE specifies conditions for the search. @lastName and @phone are parameters. au_lname like @lastName means that we are looking for records such that au_lname field is similar to whatever @lastName holds. AND phone like @phone, we are looking for records where phone field is similar @phone value in addition to the lastname condition. For more information on SQL syntax, click me.

2) Those lines basically define the parameters and set their values.

[Reverend Jim]

The @ values are just place holders. The names, such as @phone, don't have to be the same as (or even remotely close to) the actual fields in the database. It's just good practice to make them the same or similar. As I pointed out in the example, when using OleDb you use ? in the actual query and each successive AddWithValue replaces the next ? with the actual value you provide. When using SqlClient the placeholders in the query must have the same name as that given in the AddWithValue and the parameters can be added in any order.

If you were to code a query such as

qry = "INSERT INTO mytable (LastName, FirstName) VALUES(" _
    & "'" & txtLast.Text & "'," _
    & "'" & txtFirst.Text & "')"

and the name just happened to be Conan O'Brien then your actual query string would be

INSERT INTO mytable (LastName, FirstName) VALUES('O'Brien','Conan')

which would be invalid because of the single quote in the last name. If, however, you used

qry = "INSERT INTO mytable (LastName, FirstName) VALUES(@lname,@fname)"
cmd.Parameters.AddWithValue("@lname", txtLast.Text)
cmd.Parameters.AddWithValue("@fname", txtFirst.Text)

the resulting query would be

INSERT INTO mytable (LastName, FirstName) VALUES('O''Brien','Conan')

Notice how changing the single quote to two single quotes was done for you. Notice also how much easier it is to code the template query and much clearer it is when reading the code.

[Papa_Don]

Then I have to ask - where is (or was) "@lname" and "@phone" defined? Is it something that is done via a Dim Statement? i.e.

    Dim @lname As String = txbLastName.Text

Or is it done with

    cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)

In what I'm doing, I (assume) that I want to do the same.... or do I? My current search statement says:

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim search1 As String = string1 & "%"
        cmd.CommandText = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1

Here, I'm using a wildcard as I want the user to be able to type in the first characters of a part number such that listview will report any part number that begins with those characters. In my test case, I'm typing "AO". I want to search the part number database for parts that begin with "AO" (AO%) and return any that it finds. In my test database, there are two examples that should be reported.

So, to further clarify the questions, should I be using the following in this manner:

        cmd.Parameters.AddWithValue("@search", search1)

I hope I'm making sense!!

Don

[scudzilla]

Yea, that's good. Just remember to use the parameter name in the query.

You don't need to define the parameters in the front-end, so no need for Dims. They need to be defined in the back-end, and the Parameters.AddWithValue does exactly that.

[Papa_Don]

So if I'm understanding you correctly, I can write the command line as:

cmd.CommandText = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like @search"

(is the syntax correct - like @search"? Or should it be like" @search?

and later:

cmd.Parameters.AddWithValue("@search", search1)

Again, I need for that command line to recognize that I'm using the wildcard.

Don

[Papa_Don]

Group,

I found my error. It was in how I was combining my wildcard with the inputs from the textbox. I've been writing

Dim search1 As String = string1 & "%"

However I have learned that SQL reads different from VB.net. So I needed it to write it this way:

Dim search1 As String = "'" & string1 & "%" & "'"

That has been a hard lesson!

Rev. Jim, thanks for the tutorial and the addition information. Scudzilla, thanks for your input as well. This has been a great learning experience.

Don