C# and SQL SELECT Problem

[GilbertB]

Currently I am working on a project regarding C# and SQL and I have a problem regarding the SELECT function and I cannot find any solutions on-line.

The scenario is regard searching query from C# through SQL server and display the results in a Data Grid View at C#.

I'm using Visual Studio 2008 and SQL Server Studio 2008.

Before starting the actual project I just did a quick Windows form from Visual studio and just did a datagridview, 2 text boxes and a Search Button.

*And the problems started ! *

At SQL Server I have a a database with a table DVD and I want to search, from this Windows form with the DVD ID and Name.

I Started with the DVD ID and implemented this code :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Project1
{
    public partial class Form1 : Form
    {

        SqlConnection c = new SqlConnection(@"Data Source=GILBERTB-PC\SQLEXPRESS;Initial Catalog=DVDandGameBooking;Integrated Security=True");

        public Form1()
        {
            InitializeComponent();

        }

        private void btnView_Click(object sender, EventArgs e)
        {



           DataTable t = new DataTable();

           string sqlString = "SELECT * From DVD where Id ='" + txtID.Text+ "'";
           SqlDataAdapter dt = new SqlDataAdapter(sqlString, c);        

           dt.Fill(t);


           dtgv1.DataSource = t;

        }


        }
    }

and it worked :)

Then I Changed the code to `string sqlString = "SELECT * From DVD where Name ='" + txtName.Text+ "'";

so that I can search with Name of the DVD but when I started the program and searched with the Name it just showed a blank database :

Is there a reason ?

-----** Also is there any way that I can change the code so that I can either search with the ID or with the Name ?**

Thanks for your help and time

[lolafuertes]

In order to search by name, the text in the txtName must mach some entry in the DVD table in SQL.

If you defined the Name field in the SQL to be char or nchar, the legth is significant, because the content is filled with blancs in the db, so comparing without blancs can fail.

If you defined the field Name to be a BLOB (text, image, etc), is not serchable

If you defined the field to be char, varchar, nchar or nvarchar, the compare occurs as defined in the comparation method of the db: Binary, dictionary, case sensitive or not, etc, and for the nchar and nvarchar the compare is based on unicode while for the others is based on ascii. Also the base language for the SQL table plays in this scenario.

On the other question, you can add an option group with both possibilities: Search by ID, Serach by Name. Then, when constructing the select you can adjust the where clause accorging to the selection made asking if the option is checked.

Hope this helps

[GilbertB]

Thanks for your post

the

txtName.Text

is the name of the TextBox in the Windows Form.
This explains that it searches in SQL Database Table - column Name with the information that was inputted in the text box during the running of the program and the result will be viewed in the data grid view. --- BUT it does not work ..

The Name was defined as varchar(100)

Therfore what should defined it as so that it can be searchable ?

Regarding the other question, preferabbly i want the option of having two text boxes and one Search Button and search either by DVD ID and you will get the answer or else with the DVD Name and will get an answer.

Hope there is a solutionn :)

Thanks

[nmaillet]

Give this a try:

        SqlDataAdapter dt = new SqlDataAdapter();
        SqlCommand command = new SqlCommand("select * from DVD where Name = @name", c);
        command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = txtName.Text;
        dt.SelectCommand = command;

Couple of things, first it should handle any encoding issues or data type mismatches since the parameter is defined (double check and make sure Name is varchar(100)). Secondly it should prevent SQL injections, according to this article. I haven't tested it out myself, but you may want to look into SQL injections if anybody else is going to be using this.

[Mitja Bonca]

WHERE Clause is the condition of what the inquiry will be based on.
If you do:
"SELECT * FROM MyTable" - it will return all data from database table (data are meant all rows, since we use * simbol)
If you do:
"SELECT * FROM MyTable WHERE Name = "John"; - it will return ONLY rows where in the Name column is John (same as your example).

This means the whole name "John" not only "Jon", or "J".

Maybe you entered only "J" or "Joh" and thats why you didnt get any results.
Thats why sql querying supporty partial values (or starting from beginning, or in form somewhere in the middle, or to the end).
Thats why we must use LIKE keyword and % simbol.
If you do:
... WHERE Name = 'J%' - will return all rows that STARTS with J
... WHERE Name = '&n' - will return all rows that ENDS with n
... WHERE NAme = '&hn&' - will return all rows that CONTAINS hn in some where in the middle!

I hope I was clear enough about using LIKE keyword in sql queries.
So I your case I assume you wanna get all rows that starts with some name, so you do:

 "SELECT * From DVD where Name LIKE ='" + txtName.Text + "%'";