c# connect to SQL database through webconfig

1:  protected void Page_Load(object sender, EventArgs e)  
2:    {  
3:      String UserID, UserName, UserEmail, UserFullName;  
4:      //==== Value Assignment ================================================  
5:      //----USER ID  
6:      UserID = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();  
7:      txtUserName_Def.Text = UserID;  
8:      int backslashIndex = UserID.IndexOf("\\") + 1;  
9:      int userCharNum = UserID.Length;  
10:      UserID = UserID.Substring(backslashIndex, userCharNum - backslashIndex);  
11:      //----USER NAME & USER EMAIL  
12:      String strConnectionString = ConfigurationManager.ConnectionStrings["EmpSale2009ConnectionString"].ConnectionString;  
13:      SqlConnection myConnection = new SqlConnection(strConnectionString);  
14:      myConnection.Open();  
15:      SqlCommand command = new SqlCommand("SELECT userName, userEmail, FullName FROM users WHERE userName='" + UserID + "'", myConnection);  
16:      command.ExecuteNonQuery();  
17:      SqlDataReader myreader = command.ExecuteReader();  
18:      while (myreader.Read())  
19:      {  
20:        UserName = (string)myreader["userName"];  
21:        UserEmail = (string)myreader["userEmail"];  
22:        UserFullName = (string)myreader["FullName"];  
23:        txtUserName_Def.Text = UserFullName;  
24:      }  
25:      myreader.Close();  
26:      myConnection.Close();      
27:    }  

search database for a value

 
 

CREATE PROC SearchAllTables  
 (  
      @SearchStr nvarchar(100)  
 )  
 AS  
 BEGIN  
      -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.  
      -- Purpose: To search all columns of all tables for a given search string  
      -- Written by: Narayana Vyas Kondreddi  
      -- Site: http://vyaskn.tripod.com  
      -- Tested on: SQL Server 7.0 and SQL Server 2000  
      -- Date modified: 28th July 2002 22:50 GMT  
      CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
      SET NOCOUNT ON  
      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)  
      SET @TableName = ''  
      SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  
      WHILE @TableName IS NOT NULL  
      BEGIN  
           SET @ColumnName = ''  
           SET @TableName =   
           (  
                SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
                FROM      INFORMATION_SCHEMA.TABLES  
                WHERE           TABLE_TYPE = 'BASE TABLE'  
                     AND     QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName  
                     AND     OBJECTPROPERTY(  
                               OBJECT_ID(  
                                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
                                     ), 'IsMSShipped'  
                                   ) = 0  
           )  
           WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)  
           BEGIN  
                SET @ColumnName =  
                (  
                     SELECT MIN(QUOTENAME(COLUMN_NAME))  
                     FROM      INFORMATION_SCHEMA.COLUMNS  
                     WHERE           TABLE_SCHEMA     = PARSENAME(@TableName, 2)  
                          AND     TABLE_NAME     = PARSENAME(@TableName, 1)  
                          AND     DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
                          AND     QUOTENAME(COLUMN_NAME) > @ColumnName  
                )  
                IF @ColumnName IS NOT NULL  
                BEGIN  
                     INSERT INTO #Results  
                     EXEC  
                     (  
                          'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)   
                          FROM ' + @TableName + ' (NOLOCK) ' +  
                          ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
                     )  
                END  
           END       
      END  
      SELECT ColumnName, ColumnValue FROM #Results  
 END  

Dynamic Cross-Tabs/Pivot Tables

PROCEDURE:

 
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)


USAGE:
execute crosstab
'select ItemNo from vtbl_ModelStock Group By ItemNo', 'sum(ModelStockQty)', 'CompanyID', 'vtbl_ModelStock'
SET ANSI_WARNINGS ON
SOURSE:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

microsoft office access was unable to create an mde database

MS Access Error :
"microsoft office access was unable to create an mde database"
Solution:
- Run MSACCESS.EXE /decompile
- Edit a form that contains a code/event
- On Microsoft VB code click Debug then Compile the project - this will remove all the unnecessary codes.
- Save it back then recreate an MDE

Credit Card Number validation C#

using System.Text;


public bool IsValidNumber(string cardNumber)

{

const string allowed = "0123456789";

int i;

StringBuilder cleanNumber = new StringBuilder();

for (i = 0; i < cardNumber.Length; i++)

{

if (allowed.IndexOf(cardNumber.Substring(i, 1)) >= 0)

cleanNumber.Append(cardNumber.Substring(i, 1));

}

if (cleanNumber.Length <> 16)

return false;

for (i = cleanNumber.Length + 1; i <= 16; i++)

cleanNumber.Insert(0, "0");

int multiplier, digit, sum, total = 0;

string number = cleanNumber.ToString();

for (i = 1; i <= 16; i++)

{

multiplier = 1 + (i % 2);

digit = int.Parse(number.Substring(i - 1, 1));

sum = digit * multiplier;

if (sum > 9)

sum -= 9;

total += sum;

}

return (total % 10 == 0);

}

Replication Techniques

There are three different replication techniques available. They are snapshot replication, merge replication and transactional replication.

Snapshot replication is a single unidirectional push of data. When updated data is fed from the publisher to the subscribers, all of the data is sent each time.

Merge replication is a bidirectional replication that transmits data either in real time, or on a schedule. Merge replication is the only bidirectional replication technique available.

Transactional replication is unidirectional from the publisher to the subscribers. Data can be sent on a schedule or in real time. As data is transmitted to the subscriber, all data changes are processed in the order they were made on the publisher.

clean database log.

dump transaction GeminiExperiments with no_log

dbcc checkdb(GeminiExperiments)

search object in DB

Select * From sysobjects Where name like '%objectname%'

search for column in DB

SELECT b.TABLE_NAME AS TableName, a.COLUMN_NAME

FROM (SELECT TABLE_NAME, COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS) AS a INNER JOIN

(SELECT TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

WHERE (TABLE_TYPE = 'base table')) AS b ON a.TABLE_NAME = b.TABLE_NAME

WHERE (a.COLUMN_NAME = 'ColumnSearched')

send query from email from database

EXEC msdb.dbo.sp_send_dbmail

@recipients='maia.eliozashvili@vancleef.com',

@subject = 'SQL Mail Test',

@body_format = 'html', --text or html

@body = 'body1' ,

@query = 'use vcadata

select

itemno

from itemmaster

where

year(createdate)=year(getdate()) and month(createdate)=month(getdate())',

@attach_query_result_as_file = 0

--@query_attachment_filename ='Results.txt'

http://msdn.microsoft.com/en-us/library/ms190307.aspx

email Query result

MailMessage mail = new MailMessage();

mail.To = HLBLUserEmail.Text;

mail.From = "'Sender<sendere@domain.com>";

//mail.Headers.Add("Reply-To", "sender@domain.com");

mail.Subject = "Subject";

mail.BodyFormat = MailFormat.Html;

mail.Body = "<div style='position:relative; left: 50px;'><p>";

mail.Body += "" + DateTime.Now + "<br />";

mail.Body += "<br/>Dear, " + userName + "<br />";

mail.Body += "<br />Your order has been submitted.<br />";

mail.Body += "Order Number is : " + orderID + ";";

mail.Body += "<br />Following is the list of ordered items:<br /></p>";

String strConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection myConnection = new SqlConnection(strConnectionString);

string ItemNo, Description, Price, qty;

mail.Body += "<table border='1' style='font-size: 8pt;'>";

foreach (GridViewRow row in GridView1.Rows)

{

ItemNo = (row.FindControl("ItemNo") as Label).Text;

Description = (row.FindControl("Description") as Label).Text;

Price = (row.FindControl("Price") as Label).Text;

qty = (row.FindControl("SelectedQty") as Label).Text;

mail.Body += "<tr>";

mail.Body += "<td>" + ItemNo + "</td><td>" + Description + "</td><td>" + Price + "</td><td>" + qty + "</td><td>" + Convert.ToInt32(Price) * Convert.ToInt32(qty) + "</td>";

mail.Body += "</tr>";

}

mail.Body += "</table></div><br /><br /><br />";

SmtpMail.SmtpServer = "smtp.domain.com";

SmtpMail.Send(mail);

Search for Active Directory information

snippet for searching active directory for name, email, memberof(useless) and datecreated.

// String UserID = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

String UserID = Environment.UserName;

string account = UserID;

Label3.Text = "Search for this accont: " + account;

DirectoryEntry entry = new DirectoryEntry("LDAP://DOMAIN", "username", "password");

DirectorySearcher search = new DirectorySearcher(entry);

search.Filter = "(SAMAccountName=" + account + ")";

search.PropertiesToLoad.Add("displayName");

search.PropertiesToLoad.Add("mail");

search.PropertiesToLoad.Add("memberof");

search.PropertiesToLoad.Add("whencreated");

SearchResult result = search.FindOne();

if (result != null)

{

Label4.Text = "displayName: " + result.Properties["displayname"][0].ToString();

Label5.Text = "mail: " + result.Properties["mail"][0].ToString();

Label6.Text = "memberof: " + result.Properties["memberof"][0].ToString();

Label7.Text = "whencreated: " + result.Properties["whencreated"][0].ToString();

}

else

{

Label4.Text = "Unknown User";

}

Agile Programming

Just something i found. indeed, i am glad it has a name. i should put it on my resume.

ASP.NET Validator Controls

This tutorial gives a brief overview of how to use the ASP.NET Input
Validation Controls.
http://www.devhood.com/Tutorials/tutorial_details.aspx?tutorial_id=46

With ASP.NET, there are six(6) controls included. They are:

• The RequiredFieldValidation Control
  
• The CompareValidator Control
  
• The RangeValidator Control
  
• The RegularExpressionValidator Control
  
• The CustomValidator Control
  

Validator Control Basics

All of the validation controls inherit from the base class BaseValidator so they
all have a series of properties and methods that are common to all validation controls.
They are:

• ControlToValidate - This value is which control the validator is applied
  to.
  
• ErrorMessage - This is the error message that will be displayed in the validation
  summary.
  
• IsValid - Boolean value for whether or not the control is valid.
  
• Validate - Method to validate the input control and update the IsValid property.
  
• Display - This controls how the error message is shown. Here are the possible
  options:
  
  
  
  • None (The validation message is never displayed.)
    
  • Static (Space for the validation message is allocated in the page layout.)
    
  • Dynamic (Space for the validation message is dynamically added to the page if validation fails.)

The RequiredFieldValidation Control

The first control we have is the RequiredFieldValidation Control. As it's obvious,
it make sure that a user inputs a value. Here is how it's used:

Required field: <asp:textbox id="textbox1" runat="server"/> <asp:RequiredFieldValidator id="valRequired" runat="server" ControlToValidate="textbox1" ErrorMessage="* You must enter a value into textbox1" Display="dynamic">* </asp:RequiredFieldValidator>

In this example, we have a textbox which will not be valid until the user types something in.
Inside the validator tag, we have a single *.
The text in the innerhtml will be shown in the controltovalidate if the control is not valid.
It should be noted that the ErrorMessage attribute is not what is shown.
The ErrorMessage tag is shown in the Validation Summary (see below).

The CompareValidator Control
Next we look at the CompareValidator Control. Usage of this CompareValidator is for confirming new passwords, checking if a departure date is before the arrival date, etc. We'll start of with a sample:

Textbox 1: <asp:textbox id="textbox1" runat="server"/><br /> Textbox 2: <asp:textbox id="textbox2" runat="server"/><br /> <asp:CompareValidator id="valCompare" runat="server" ControlToValidate="textbox1" ControlToCompare="textbox2" Operator="Equals" ErrorMessage="* You must enter the same values into textbox 1 and textbox 2" Display="dynamic">* </asp:CompareValidator>

Here we have a sample where the two textboxes must be equal. The tags that are unique to this control is the ControlToCompare attribute which is the control that will be compared. The two controls are compared with the type of comparison specified in the Operator attribute. The Operator attribute can contain Equal, GreterThan, LessThanOrEqual, etc.
Another usage of the ComapareValidator is to have a control compare to a value. For example:

Field: <asp:textbox id="textbox1" runat="server"/>
<asp:CompareValidator id="valRequired" runat="server" ControlToValidate="textbox1"
  ValueToCompare="50"
  Type="Integer"
  Operator="GreaterThan"
  ErrorMessage="* You must enter the a number greater than 50" Display="dynamic">*
</asp:CompareValidator>
The data type can be one of: Currency, Double, Date, Integer or String. String being the default data type.

The RangeValidator Control
Range validator control is another validator control which checks to see if a control value is within a valid range. The attributes that are necessary to this control are: MaximumValue, MinimumValue, and Type.
Sample:

Enter a date from 1998:
<asp:textbox 
  id="textbox1" runat="server"/>
<asp:RangeValidator id="valRange" runat="server"
  ControlToValidate="textbox1"
  MaximumValue="12/31/1998"
  MinimumValue="1/1/1998"
  Type="Date"
  ErrorMessage="* The date must be between 1/1/1998 and 12/13/1998" Display="static">*</asp:RangeValidator>

The RegularExpressionValidator Control

The regular expression validator is one of the more powerful features of ASP.NET.
Everyone loves regular expressions. Especially when you write those really big nasty
ones... and then a few days later, look at it and say to yourself. What does this
do?

Again, the simple usage is:

E-mail: <asp:textbox id="textbox1" runat="server"/> <asp:RegularExpressionValidator id="valRegEx" runat="server" ControlToValidate="textbox1" ValidationExpression=".*@.*\..*" ErrorMessage="* Your entry is not a valid e-mail address." display="dynamic">* </asp:RegularExpressionValidator>

Here is a webpage to check regular expressions.

The CustomValidator Control
The final control we have included in ASP.NET is one that adds great flexibility to our validation abilities. We have a custom validator where we get to write out own functions and pass the control value to this function.

Field: <asp:textbox id="textbox1" runat="server">
<asp:CustomValidator id="valCustom" runat="server"
  ControlToValidate="textbox1"
  ClientValidationFunction="ClientValidate"
  OnServerValidate="ServerValidate"
  ErrorMessage="*This box is not valid" dispaly="dynamic">*
</asp:CustomValidator>

notice that there are two new attributes ClientValidationFunction and
OnServerValidate. These are the tell the validation control which functions
to pass the controltovalidate value to. ClientValidationFunction is usually a javascript
funtion included in the html to the user. OnServerValidate is the function that
is server-side to check for validation if client does not support client-side validation.

Client Validation function:

&ltscript language="Javascript">
<!--
  /* ... Code goes here ... */
-->
</script>

Server Validation function:

Sub ServerValidate (objSource As Object, objArgs As ServerValidateEventsArgs)
  ' Code goes here
End Sub

Validation Summary

ASP.NET has provided an additional control that complements the validator controls.
This is the validation summary control which is used like:

<asp:ValidationSummary id="valSummary" runat="server"
  HeaderText="Errors:"
  ShowSummary="true" DisplayMode="List" />

The validation summary control will collect all the error messages of all the non-valid
controls and put them in a tidy list. The list can be either shown on the web page
(as shown in the example above) or with a popup box (by specifying ShowMessageBox="True")

Now you know how to use the Validator Controls in ASP.NET! Have fun!

I will also upload a sample of all the validator controls to the code sample section.

Acknoledgment: Professional ASP.NET (published by Wrox) was used a reference.
It's a good book!

Tips to remember

• If you are doing server-side validation, make sure the button onclick method has
  a Page.IsValid if statement or it will look like your validators aren't doing anything
  
• Don't forget to wrap everything in the <form runat=server> tag.
  

ListBox max value received from database field.

asp.net function : receives value from one of the database field that acts as an max value for a listbox. useful when ordering quantity needs to be restricted to available quantity.

----ASP.NET ----

<asp:TemplateField HeaderText="Select">

<ItemTemplate>

<asp:ListBox ID="ListBox1" SelectionMode="Single" Rows="1" runat="server" DataSource='<%# ddlItems((string)(Eval("QtyLeft").ToString())) %>'>asp:ListBox>

ItemTemplate>

<ItemStyle HorizontalAlign="Center" />

asp:TemplateField>

--- C# ---

protected ListItemCollection ddlItems(String max)

{

ListItem li = null;

ListItemCollection liC = new ListItemCollection();

int intmax = Convert.ToInt32(max);

for (int i = 0; i <= intmax; i++)

{

li = new ListItem();

li.Text = ""+ i;

liC.Add(li);

li = null;

}

return liC;

}