Tech Junkie Sandbox: August 2013

Friday, August 23, 2013

ASP.NET: Getting The Inserted ID Back With Scope_Identity()

When you need to do an insert into multiple database table you need to the get the ID of the insert so that you could use that ID for the next insert. Here is how you would do that with the Scope_Identity()which gets the last inserted ID back to you if you execute your query with the ExecuteScalar() method.

                SqlCommand cmd = new SqlCommand("INSERT INTO Users (" +
                    "LoginName," +
                    "FirstName," +
                     "LastName," +
                     "Password," +
                     "Email," +
                     "DOB," +
                     "Sex" +
                     ") VALUES (" +
                    "@Email," +
                    "@FirstName," +
                     "@LastName," +
                     "@Password," +
                     "@Email," +
                     "@DOB," +
                     "@Sex)" + 
                     " Select Scope_Identity();",conn);

Here is how you would execute the query:

int UserId = (int)cmd.ExecuteScalar();

Most of the time you will need to use the Scope_Identity() when you have to deal with foreign key constraints, that's why a Users table is a good example.

Thursday, August 22, 2013

SqlDbType.Date

string dobStr = ddlMonth.SelectedValue + "/" + txtDay.Text + "/" + txtYear.Text;
SqlParameter dob = new SqlParameter("DOB", Convert.ToDateTime(dobStr));
dob.SqlDbType = SqlDbType.Date;
cmd.Parameters.Add(dob);

CSS: Padding Properties

padding: top right bottom left

padding:100px 75px 50px 25px;

Long hand version

padding-top:100px;
padding-right:75px;
padding-bottom:50px;
padding-left:25px;

All four sides 100px

padding:100px;

ASP.NET Get The Current Page File Name

In this blog we will get the current page file name that the user is currently on.

string[] currentUrl = HttpContext.Current.Request.Url.AbsolutePath.Split('/');
string pageFileName = currentUrl[currentUrl.Length-1];

Sunday, August 18, 2013

ADO.NET: Using System.Data.SqlClient.SqlDataAdapter To Fill System.Data.DataTable

In this example we will use the SqlDataAdapter to fill a DataTable then bind the data to a GridView 1. Drag a GridView into your ASP.NET page

2. Use the following code to the fill the dtProducts DataTable with the adapter SqlDataAdapter, then bind it to the GridView1 control.

        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dtProducts = new DataTable();

              string connString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

              using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand("SELECT * FROM Products", conn);
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dtProducts);

                    GridView1.DataSource = dtProducts;
                    GridView1.DataBind();

                }
        }

Tuesday, August 13, 2013

How To Use AJAX Control Toolkit

1. Download the Toolkit at

http://ajaxcontroltoolkit.codeplex.com/

3. Extract the download

4. Add reference to AjaxControlToolKit.DLL

5. Register the DLL on your aspx page

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html>

6. Add ToolkitScriptManager

    <form id="form1" runat="server">
    <div>
    <asp:ToolkitScriptManager ID="ToolKitManager1" runat="server"></asp:ToolkitScriptManager>

How To Use AJAX Control Toolkit

1. Download the Toolkit at

http://ajaxcontroltoolkit.codeplex.com/

3. Extract the download

4. Add reference to AjaxControlToolKit.DLL

5. Register the DLL on your aspx page

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html>

6. Add ToolkitScriptManager

    <form id="form1" runat="server">
    <div>
    <asp:ToolkitScriptManager ID="ToolKitManager1" runat="server"></asp:ToolkitScriptManager>

Monday, August 12, 2013

C#: System.Collections.Generic.Dictionary

Namespace:

System.Collections.Generic

Declaration:

Dictionary<string, string> account = new Dictionary<string, string>();

Adding Items:

account.Add("id","1");
account.Add("username", "jackd");

Use in ArrayList: Namespace:

System.Collections

Add Dictionary to ArrayList:

Dictionary<string, string> account = new Dictionary<string, string>();

ArrayList aList = new ArrayList();

account.Add("id","1");
account.Add("username", "jackd");

aList.Add(account);

account = new Dictionary<string, string>();

account.Add("id","2");
account.Add("username", "janed");

aList.Add(account);

account = new Dictionary<string, string>();

account.Add("id","3");
account.Add("username", "d");

Loop Through ArrayList of Dictionary Type:

foreach (Dictionary<string, string> dic in aList)
{
    Response.Write("id: " + dic["id"] + " username: " + dic["username"] + "<br>");
}

Wednesday, August 7, 2013

SQL: The NOT IN Operator

The NOT IN operator in SQL means that you are retrieving records in the database that does not match the values in a comma separated list. In other words it retrieves the inverse of the IN statement by itself. Here is an example of how you can use the IN operator in the products table in the Northwind database.

SELECT * 
FROM Products
WHERE SupplierID NOT IN (1,2)

The above example all the products will be retrieved except for products with SupplierID of 1 or 2, here are the results

Tuesday, August 6, 2013

SQL: The IN Operator

The IN operator in SQL means that you are retrieving records in the database that matches the values in a comma separated list. Here is an example of how you can use the IN operator in the products table in the Northwind database.

SELECT * 
FROM Products
WHERE SupplierID IN (1,2)

In the above example all the products with the SupplierID of 1 or 2 are retrieved.

Monday, August 5, 2013

SQL: Using Parentheses To The Expected Result

SQL Server as well as other DBMS has an order of evaluation that can throw you off. Especially when you have more than one comparison in the WHERE clause. In this example I will show you the difference between using a parentheses and not using one, and how by using parentheses can give the results that you want. Suppose you want to get the products with CategoryID 1 and 2 that are priced less than 15 dollars in the Products table in the Northwind database. Here is the query without the parentheses:

SELECT CategoryID,ProductName,UnitPrice
FROM Products
WHERE CategoryID = 1 OR CategoryID =2 AND UnitPrice < 15

When you run the query above you would expect that all the records retrieved will have a unit price of less than $15 dollar but that is not the case. Below is the result from the query.

Unexpected results from SQL from queries without parentheses

As you can see several records have unit price that are greater than $15 dollars
Now let's run the query with parantheses

SELECT CategoryID,ProductName,UnitPrice
FROM Products
WHERE (CategoryID = 1 OR CategoryID =2) AND UnitPrice < 15

Below is the result from the query

Get expected SQL results with parentheses

Now you are getting result that you've always wanted in the first place. The parentheses tells SQL Server to ignore the order of evaluation and evaluate what is in the parentheses first then evaluate the second part of the WHERE clause.

Friday, August 2, 2013

SQL: Checking ShippedDate Column For NULL

Retrieve records with NULL value in the ShippedDate column in the Orders table in Northwind

SELECT OrderID, ShippedDate
FROM Orders
WHERE ShippedDate IS NULL

Retrieve records that is does not have NULL value in the ShippedDate column in the Orders table in Northwind

SELECT OrderID, ShippedDate
FROM Orders
WHERE ShippedDate IS NOT NULL

Thursday, August 1, 2013

SQL: SELECT Rows Between Certain Dates

Let's say you want to know the orders that takes place in the Northwind database table Orders tables that occurs during the Christmas Eve 1997-12-24 and the New Years Day the following year in 1998-01-01. Here is the SQL to query the OrderID between those date range:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '1997-12-24' AND '1998-01-01'

Tech Junkie Sandbox

Friday, August 23, 2013

ASP.NET: Getting The Inserted ID Back With Scope_Identity()

Thursday, August 22, 2013

SqlDbType.Date

CSS: Padding Properties

ASP.NET Get The Current Page File Name

Sunday, August 18, 2013

ADO.NET: Using System.Data.SqlClient.SqlDataAdapter To Fill System.Data.DataTable

Tuesday, August 13, 2013

How To Use AJAX Control Toolkit

How To Use AJAX Control Toolkit

Monday, August 12, 2013

C#: System.Collections.Generic.Dictionary

Wednesday, August 7, 2013

SQL: The NOT IN Operator

Tuesday, August 6, 2013

SQL: The IN Operator

Monday, August 5, 2013

SQL: Using Parentheses To The Expected Result

Friday, August 2, 2013

SQL: Checking ShippedDate Column For NULL

Thursday, August 1, 2013

SQL: SELECT Rows Between Certain Dates

Games & Multimedia

SCIENCE & TECHNOLOGY