The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.
The patterns that you can choose from are:
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character
Examples using % wildcard
The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
You can also using the wildcard multiple times within the same string. For example,
SELECT * FROM suppliers
WHERE supplier_name like '%bob%';
More ...
http://www.techonthenet.com/sql/like.php
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Wednesday, November 12, 2008
Sunday, September 7, 2008
Getting the Wrong Identity in Microsoft SQL Server identity Columns?
By Don Schlichting
This article will explore Microsoft SQL server identity columns, including their problems, use and scope.
Introduction
An Identity column is used in SQL server to create a surrogate key value for a table. This will be a unique identifier usually in sequential order. Starting at some predefined number, the Identity column increments every time a new record is added to the table. For MS Access users, this is comparable to an Auto Numbering field. For Oracle users, the Identity column can be thought of as a sequence built into a table.
Creating
The Identity key word is supported in both the create and alter table statements. The following statement will create a new table with the product_id column as an identity field.
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'products')
DROP TABLE products
GO
CREATE TABLE products
(
product_id int IDENTITY(10,2),
product_name varchar(50)
)
The first number in the Identity function is for the seed value. The seed will be the first number used as an identity. In our case, the first value in the table will be 10. The second number is the increment. The products table id will count up by twos.
To create an identity field from Enterprise Manager, set the Identity to Yes, and enter a seed and increment. A seed of 1 with an increment of 1 is the default.
Inserting
Enter three product names into the new table using an insert into statement. We will only enter the product_name, letting sql create the identity id.
INSERT INTO products
(product_name)
VALUES
('computer')
INSERT INTO products
(product_name)
VALUES
('monitor')
INSERT INTO products
(product_name)
VALUES
('printer')
Selecting the new rows out will show our first seed of 10. Additional product names will increment by two.
By default, values cannot be inserted into an identity field. The statement:
INSERT INTO products
(product_id, product_name)
VALUES
(18, 'printer')
Will fail with error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in
table 'products' when IDENTITY_INSERT is set to OFF.
To force our value, the IDENTITY_INSERT needs to be set ON:
SET IDENTITY_INSERT products ON
INSERT INTO products
(product_id, product_name)
VALUES
(18, 'printer')
SELECT *
FROM products
The new printer with an id of 18 has been successfully entered.
We now have a gap in our numbering, from identity 14 to 18. This will not create any problems for SQL. The next identity used will be 20. However, if gaps will cause a problem for your particular application, search BOL for "Use generic syntax for finding gaps in identity values" for detailed examples of how to find gaps.
More..
http://www.databasejournal.com/features/mssql/article.php/3307541
This article will explore Microsoft SQL server identity columns, including their problems, use and scope.
Introduction
An Identity column is used in SQL server to create a surrogate key value for a table. This will be a unique identifier usually in sequential order. Starting at some predefined number, the Identity column increments every time a new record is added to the table. For MS Access users, this is comparable to an Auto Numbering field. For Oracle users, the Identity column can be thought of as a sequence built into a table.
Creating
The Identity key word is supported in both the create and alter table statements. The following statement will create a new table with the product_id column as an identity field.
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'products')
DROP TABLE products
GO
CREATE TABLE products
(
product_id int IDENTITY(10,2),
product_name varchar(50)
)
The first number in the Identity function is for the seed value. The seed will be the first number used as an identity. In our case, the first value in the table will be 10. The second number is the increment. The products table id will count up by twos.
To create an identity field from Enterprise Manager, set the Identity to Yes, and enter a seed and increment. A seed of 1 with an increment of 1 is the default.
Inserting
Enter three product names into the new table using an insert into statement. We will only enter the product_name, letting sql create the identity id.
INSERT INTO products
(product_name)
VALUES
('computer')
INSERT INTO products
(product_name)
VALUES
('monitor')
INSERT INTO products
(product_name)
VALUES
('printer')
Selecting the new rows out will show our first seed of 10. Additional product names will increment by two.
By default, values cannot be inserted into an identity field. The statement:
INSERT INTO products
(product_id, product_name)
VALUES
(18, 'printer')
Will fail with error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in
table 'products' when IDENTITY_INSERT is set to OFF.
To force our value, the IDENTITY_INSERT needs to be set ON:
SET IDENTITY_INSERT products ON
INSERT INTO products
(product_id, product_name)
VALUES
(18, 'printer')
SELECT *
FROM products
The new printer with an id of 18 has been successfully entered.
We now have a gap in our numbering, from identity 14 to 18. This will not create any problems for SQL. The next identity used will be 20. However, if gaps will cause a problem for your particular application, search BOL for "Use generic syntax for finding gaps in identity values" for detailed examples of how to find gaps.
More..
http://www.databasejournal.com/features/mssql/article.php/3307541
Wednesday, September 3, 2008
Simple MySQL Database test
Sample code fragment:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
public partial class Test_Page : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Simple MySQL Database test
string MyConString = "Server=yourMySQLServer;Port=yourPort#;Database=YourDB;Uid=yourID;Pwd=yourPWD;";
MySqlConnection MyConnection = new MySqlConnection(MyConString);
string queryString = "SELECT * FROM YOUR_TABLE";
MySqlCommand MyCommand = new MySqlCommand();
MyCommand.CommandText = queryString;
MyCommand.Connection = MyConnection;
MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
dataAdapter.SelectCommand = MyCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
try
{
dataAdapter.Fill(dataSet);
GridView1.DataSource = dataSet;
GridView1.DataBind();
}
catch (Exception ex)
{
Label_Error.Text = ex.ToString();
}
}
}The sample above is a simple code fragment I used to test the MySQL connection. Very similar to the one before, but is setup to be a parameterized command eventually where I can add conditional statements like so to the queryString:
"... WHERE UserName = ?UserName"
and add the parameter like so:
MySqlParameter dbParam_UserName = new MySqlParameter();
dbParam_UserName.ParameterName = "?UserName";
dbParam_UserName.Value = UserName;
dbParam_UserName.DbType = System.Data.DbType.String;
MyCommand.Parameters.Add(dbParam_UserName);
I use parameterized commands instead of just imbeding the user input string into the SQL query string directly to help prevent SQL injection... at least I read somewhere it is supposed to help prevent that, combined with replacing any single quotes ' to double single quotes '', though I probably have a bit to learn about all the tricks to look out for.
More..
http://forums.asp.net/t/999456.aspx?PageIndex=2
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
public partial class Test_Page : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Simple MySQL Database test
string MyConString = "Server=yourMySQLServer;Port=yourPort#;Database=YourDB;Uid=yourID;Pwd=yourPWD;";
MySqlConnection MyConnection = new MySqlConnection(MyConString);
string queryString = "SELECT * FROM YOUR_TABLE";
MySqlCommand MyCommand = new MySqlCommand();
MyCommand.CommandText = queryString;
MyCommand.Connection = MyConnection;
MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
dataAdapter.SelectCommand = MyCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
try
{
dataAdapter.Fill(dataSet);
GridView1.DataSource = dataSet;
GridView1.DataBind();
}
catch (Exception ex)
{
Label_Error.Text = ex.ToString();
}
}
}The sample above is a simple code fragment I used to test the MySQL connection. Very similar to the one before, but is setup to be a parameterized command eventually where I can add conditional statements like so to the queryString:
"... WHERE UserName = ?UserName"
and add the parameter like so:
MySqlParameter dbParam_UserName = new MySqlParameter();
dbParam_UserName.ParameterName = "?UserName";
dbParam_UserName.Value = UserName;
dbParam_UserName.DbType = System.Data.DbType.String;
MyCommand.Parameters.Add(dbParam_UserName);
I use parameterized commands instead of just imbeding the user input string into the SQL query string directly to help prevent SQL injection... at least I read somewhere it is supposed to help prevent that, combined with replacing any single quotes ' to double single quotes '', though I probably have a bit to learn about all the tricks to look out for.
More..
http://forums.asp.net/t/999456.aspx?PageIndex=2
An ASP.NET Application Using a MySQL Database
Abstract
.NET is the new distributed computing platform developed by Microsoft and ASP.NET is its programming model for web development. I'm creating a Web Form with a DataGrid for data entry using C#. How can I make a DataGrid cell display a textbox for showing a list of products in all the rows. This solution will teach you how to use a MySQL database with ASP.NET. I have developed this sample with ASP.NET 1.1 using C# as the code-behind using MySQL 4.1. I think this will be a more useful article for beginners studying .NET. In this sample, I have taken the DataGrid control for demonstration. All the basic operations of the DataGrid are explained in this article.
Overview of the Solution
MySQL:
MySQL Server 4.0 laid the foundation for the new features implemented in MySQL 4.1, such as subqueries and Unicode support, which were desired by many of our customers. The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available. Clients can connect to a MySQL server using TCP/IP sockets on any platform. On Windows systems belonging the NT family (NT, 2000, XP, or 2003), clients can connect using named pipes. On UNIX systems, clients can connect using UNIX domain socket files.
In MySQL 4.1, we cannot write stored procedures, functions, or views. The following are sample SELECT, UPDATE, and DELETE queries in MySQL.
SELECT column_names from table_name [WHERE ...conditions];
UPDATE table_name SET column_names = ‘’ WHERE ...conditions;
DELETE FROM table_name WHERE ...conditions;
ODBC:
The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems. The ODBC interface permits maximum interoperability — an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS. Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.
More...
http://www.codeproject.com/KB/database/mysqlinaspnet.aspx
.NET is the new distributed computing platform developed by Microsoft and ASP.NET is its programming model for web development. I'm creating a Web Form with a DataGrid for data entry using C#. How can I make a DataGrid cell display a textbox for showing a list of products in all the rows. This solution will teach you how to use a MySQL database with ASP.NET. I have developed this sample with ASP.NET 1.1 using C# as the code-behind using MySQL 4.1. I think this will be a more useful article for beginners studying .NET. In this sample, I have taken the DataGrid control for demonstration. All the basic operations of the DataGrid are explained in this article.
Overview of the Solution
MySQL:
MySQL Server 4.0 laid the foundation for the new features implemented in MySQL 4.1, such as subqueries and Unicode support, which were desired by many of our customers. The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available. Clients can connect to a MySQL server using TCP/IP sockets on any platform. On Windows systems belonging the NT family (NT, 2000, XP, or 2003), clients can connect using named pipes. On UNIX systems, clients can connect using UNIX domain socket files.
In MySQL 4.1, we cannot write stored procedures, functions, or views. The following are sample SELECT, UPDATE, and DELETE queries in MySQL.
SELECT column_names from table_name [WHERE ...conditions];
UPDATE table_name SET column_names = ‘’ WHERE ...conditions;
DELETE FROM table_name WHERE ...conditions;
ODBC:
The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems. The ODBC interface permits maximum interoperability — an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS. Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.
More...
http://www.codeproject.com/KB/database/mysqlinaspnet.aspx
Using Parameterized Queries in ASP.Net
There are two impelling reasons everyone should learn about Parameterized Queries. One entails one keyboard character and is more of a hassle reliever than anything else. That character, in code, can become either of two different objects, the single quote and the apostrophe. When you're coding, either one can make your life truely miserable at times. The second, and MOST compelling reason to learn Parameterized Queries is to protect your database from SQL Injection Injection Attacks. If you have never heard of them, you need to hear about them now. These attacks can reak havoc on your server and, more importantly, your data. Check out these articles on SQL Injection Attacks:
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/
http://www.tech-faq.com/sql-injection-attack.shtml
http://www.codeproject.com/aspnet/SqlInjection.asp
Simply by using Parameterized Queries, this becomes a first line of defense, and SQL Injection attacks are stopped in their tracks.
Anyone who has ever put together a long, involved SQL statement with variables, juggling single quotes, along with the double quotes (Tutorial on Single and Double Quotes), will tell you that it's not much fun. And - on top of that, when we then talk about the apostrophe, it gets even more complicated. Of course, as most of you know, in an Insert statement, if the last Name is O'Hara, the engine looks at the string, and only sees the apostrophe as a single quote, thereby truncating your perfectly structured SQL statement. Naturally, there are fixes for the latter (Replace statement to double up on the apostrophes, which tells the DataBase engine to interpret the two apostrophes as only one true apostrophe and not a single quote), but that's extra care you must take that's really not needed - but only if you use Parameterized Queries.
Let's put together a scenario - we're searching Employees of the Northwind Trading Company by last name. We have a DropDownList (DDL) with all the last names. From that, the end user will choose one, so that the rest of that employee's information will appear. This, naturally, is a fairly simple scenario to start. We don't want to get too complicated too quickly.
More...
http://aspnet101.com/aspnet101/tutorials.aspx?id=1
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/
http://www.tech-faq.com/sql-injection-attack.shtml
http://www.codeproject.com/aspnet/SqlInjection.asp
Simply by using Parameterized Queries, this becomes a first line of defense, and SQL Injection attacks are stopped in their tracks.
Anyone who has ever put together a long, involved SQL statement with variables, juggling single quotes, along with the double quotes (Tutorial on Single and Double Quotes), will tell you that it's not much fun. And - on top of that, when we then talk about the apostrophe, it gets even more complicated. Of course, as most of you know, in an Insert statement, if the last Name is O'Hara, the engine looks at the string, and only sees the apostrophe as a single quote, thereby truncating your perfectly structured SQL statement. Naturally, there are fixes for the latter (Replace statement to double up on the apostrophes, which tells the DataBase engine to interpret the two apostrophes as only one true apostrophe and not a single quote), but that's extra care you must take that's really not needed - but only if you use Parameterized Queries.
Let's put together a scenario - we're searching Employees of the Northwind Trading Company by last name. We have a DropDownList (DDL) with all the last names. From that, the end user will choose one, so that the rest of that employee's information will appear. This, naturally, is a fairly simple scenario to start. We don't want to get too complicated too quickly.
More...
http://aspnet101.com/aspnet101/tutorials.aspx?id=1
Sunday, August 31, 2008
An ASP.NET Application Using a MySQL Database
An ASP.NET Application Using a MySQL Database
By vivekthangaswamy
How to use a MySQL 4.1 database from an ASP.NET application and some ODBC basics.
Abstract
.NET is the new distributed computing platform developed by Microsoft and ASP.NET is its programming model for web development. I'm creating a Web Form with a DataGrid for data entry using C#. How can I make a DataGrid cell display a textbox for showing a list of products in all the rows. This solution will teach you how to use a MySQL database with ASP.NET. I have developed this sample with ASP.NET 1.1 using C# as the code-behind using MySQL 4.1. I think this will be a more useful article for beginners studying .NET. In this sample, I have taken the DataGrid control for demonstration. All the basic operations of the DataGrid are explained in this article.
Overview of the Solution
MySQL:
MySQL Server 4.0 laid the foundation for the new features implemented in MySQL 4.1, such as subqueries and Unicode support, which were desired by many of our customers. The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available. Clients can connect to a MySQL server using TCP/IP sockets on any platform. On Windows systems belonging the NT family (NT, 2000, XP, or 2003), clients can connect using named pipes. On UNIX systems, clients can connect using UNIX domain socket files.
In MySQL 4.1, we cannot write stored procedures, functions, or views. The following are sample SELECT, UPDATE, and DELETE queries in MySQL.
SELECT column_names from table_name [WHERE ...conditions];
UPDATE table_name SET column_names = ‘’ WHERE ...conditions;
DELETE FROM table_name WHERE ...conditions;
ODBC:
The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems. The ODBC interface permits maximum interoperability — an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS. Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.
The following will explain to you the architecture of ODBC:
Namespace used for ODBC:
using System.Data.Odbc;
The System.Data.Odbc namespace is the .NET Framework Data Provider for ODBC.
The .NET Framework Data Provider for ODBC describes a collection of classes used to access an ODBC data source in the managed space. Using the OdbcDataAdapter class, you can fill a memory-resident DataSet, which you can use to query and update a data source.
http://www.codeproject.com/KB/database/mysqlinaspnet.aspx
By vivekthangaswamy
How to use a MySQL 4.1 database from an ASP.NET application and some ODBC basics.
Abstract
.NET is the new distributed computing platform developed by Microsoft and ASP.NET is its programming model for web development. I'm creating a Web Form with a DataGrid for data entry using C#. How can I make a DataGrid cell display a textbox for showing a list of products in all the rows. This solution will teach you how to use a MySQL database with ASP.NET. I have developed this sample with ASP.NET 1.1 using C# as the code-behind using MySQL 4.1. I think this will be a more useful article for beginners studying .NET. In this sample, I have taken the DataGrid control for demonstration. All the basic operations of the DataGrid are explained in this article.
Overview of the Solution
MySQL:
MySQL Server 4.0 laid the foundation for the new features implemented in MySQL 4.1, such as subqueries and Unicode support, which were desired by many of our customers. The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available. Clients can connect to a MySQL server using TCP/IP sockets on any platform. On Windows systems belonging the NT family (NT, 2000, XP, or 2003), clients can connect using named pipes. On UNIX systems, clients can connect using UNIX domain socket files.
In MySQL 4.1, we cannot write stored procedures, functions, or views. The following are sample SELECT, UPDATE, and DELETE queries in MySQL.
SELECT column_names from table_name [WHERE ...conditions];
UPDATE table_name SET column_names = ‘’ WHERE ...conditions;
DELETE FROM table_name WHERE ...conditions;
ODBC:
The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems. The ODBC interface permits maximum interoperability — an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS. Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.
The following will explain to you the architecture of ODBC:
Namespace used for ODBC:
using System.Data.Odbc;
The System.Data.Odbc namespace is the .NET Framework Data Provider for ODBC.
The .NET Framework Data Provider for ODBC describes a collection of classes used to access an ODBC data source in the managed space. Using the OdbcDataAdapter class, you can fill a memory-resident DataSet, which you can use to query and update a data source.
http://www.codeproject.com/KB/database/mysqlinaspnet.aspx
Subscribe to:
Posts (Atom)
Posts
