How to access Cmd.exe in Windows 7?

There are some changes compared with what get to the command prompt in XP. I read a summary listed 5 ways to access the Cmd.exe in Windows 7 from book  Windows 7 Inside Out Appedex B: (The command prompt works identically in all editions of Windows 7)
http://www.amazon.com/Windows-Inside-Out-Ed-Bott/dp/0735626650
 
 

To get to the command prompt, run Cmd.exe, which you can do in any of the following ways:

  • Type cmd in the Start menu search box, and click the Cmd shortcut when it appears, under Programs, at the top of the menu.

  • Press Windows logo key+R, and type cmd in the Open box.

  • Choose Start, All Programs, Accessories, Command Prompt.

  • Double-click the Cmd icon in your %SystemRoot%\System32 folder.

  • Double-click any shortcut for Cmd.exe.


Problem with Using SQL Server Express user instancing under IIS

 
There is a security setting issue needs to be addressed if you want to run your web application which is created under your default document structure. For example, quoted from Microsoft: "c:\Users\[YourUserAccountName]\Documents\Visual Studio 20XX\Projects\[YourSolutionName]\[YourProjectnName]\App_Data\aspnetdb.mdf failed".
Just a quick note to the detailed document from Microsoft for resolving issues related to work with SQL Server Express instance for websites hosted under IIS6/IIS7/IIS7.5.
 
http://support.microsoft.com/?kbid=2002980
 
The document provided detailed instructions to resolve the issue.

Bulkcopy Excel 2007 data to SQL Server: Issue— Could not find installable ISAM

If you have installed Office 2007 SP2(or SP1) for the machine, the error is likely casused by the connection string.
You can find all kind of connection string from connectionstring.com. But you need to use them with a right syntax in your code.
Here is sample with the right syntax in C#:

string OLEDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\datatest\ImportUserFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""";

 
 
I also include a working ASP.NET sample code for how to:

protected void myButton_Click(object sender, EventArgs e)

{

string OLEDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|ImportUserFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""";

// string OLEDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\datatest\ImportUserFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""";

using (System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(OLEDBConnectionString))

{

OleDbCommand command = new OleDbCommand("SELECT UserID, Password, FirstName, LastName, Email, Address, PostalCode, MobilePhone, HomeNumber, DD, MM, YYYY, Role FROM [ImportUserFile$]", myConnection);

myConnection.Open();

using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader())

{

string sqlConnectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ToString();

// Bulk Copy to SQL Server

using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString))

{

bulkCopy.DestinationTableName =

"dbo.Account";

//Define ColumnMappings: source(Excel) –destination(DB Table column)

bulkCopy.ColumnMappings.Add(

"UserID", "UserID");

bulkCopy.ColumnMappings.Add(

"Password", "Password");

bulkCopy.ColumnMappings.Add(

"FirstName", "FirstName");

bulkCopy.ColumnMappings.Add(

"LastName", "LastName");

bulkCopy.ColumnMappings.Add(

"Email", "Email");

bulkCopy.ColumnMappings.Add(

"Address", "Address");

bulkCopy.ColumnMappings.Add(

"PostalCode", "PostalCode");

bulkCopy.ColumnMappings.Add(

"MobilePhone", "MobilePhone");

bulkCopy.ColumnMappings.Add(

"HomeNumber", "HomeNumber");

bulkCopy.ColumnMappings.Add(

"DD", "DD");

bulkCopy.ColumnMappings.Add(

"MM", "MM");

bulkCopy.ColumnMappings.Add(

"YYYY", "YYYY");

bulkCopy.ColumnMappings.Add(

"Role", "Role");

bulkCopy.WriteToServer(dr);

}

}

}

}


Use a Pipe delemited file in ASP.NET

It is very straightforward to use CSV delimted with comma or tab, since they are standard default setting in registry under this entry:HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
 
You may need to set to other values to use other delemiters delimited files. When you import a pipe delemited file, you can change the delemiter to pipe. But you cannot work with other type deleimeted files. Another way is to use Schema.ini file to control a specific text file for importing.

One way to define the delimiter: you can use a Schema.ini file to define your text file name and delimiter.

Here is a sample Schema.ini file and it will sit in the same folder as your text file.: 

[myPipeFileName.txt]
Format=Delimited(|)
ColNameHeader=False
MaxScanRows=0

 More information about Schema.ini: http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

 
Here is an example with a button clik to show a piple delemited on an ASP.NEt page:

protected void Button1_Click(object sender, EventArgs e)

{

string str = System.IO.Path.GetDirectoryName(MapPath(".\\App_Data\\myPipe.txt"));

string strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + str + "; Extended Properties=\"Text;HDR=No;\"";

OleDbConnection oCon = new OleDbConnection(strConString);

oCon.Open();

//F1, F2, F3 …. in the place of column names

string strSql = "SELECT * FROM myPipe.txt";

OleDbDataAdapter daSource = new OleDbDataAdapter(strSql, oCon);

DataSet ds = new DataSet();

daSource.AcceptChangesDuringFill =

false;

daSource.Fill(ds,

"TransferData");

GridView3.DataSource = ds;

GridView3.DataBind();

}

 

How to deploy a SQL Server report with VS 2008 in Windows 7?

I am trying to deploy a sample report generated with VS 2008’s Report Server Project Wizard to run Reporting Service on a Named Instance of SQL Server 2008. I ran into a big problem for this error message: “The permissions granted to user mymachine\myUserName are insufficient for performing this operation.”
I reconfigure the reporting server a few times from Reporting Service Configration Manager without any luck. I even reset the reporting default databases a few times along with all kinds of permission setting for the user in question.  I checked a few forum discussion about this topic and finally I found this link to hit the nail on the head.
 
 I need to run IE to access the report and to set the permission there in this way: Run as Administrator.
When I point IE to the report, in my case it is an named instance: http://myMachineNmae/Reports_myInstanceName.
From the report page, we can access the report properties to assign user roles under New role Assignment tab in security: add new or edit existing at your choice from there.
After adding the user with proper rights, we should be able to view our report deployed in Window 7 now.
The last thing I want to mention is the TargetServerURL from your Project property page in a named instance (I used default 80): http://localhost/ReportServer_myInstanceName
 

Where is the Design View in VS 2010?

By default, VS 2010 disables the HTML designer. You can turn it on by checking a checkbox from Tools > Options > HTML Designer >> Check Enable HTML Designer.
You need to restart your application to see the designer tab under the left lower cornerof your page.
 

Delete records in a tree structure with SQL

To answer a quertion at ASP.NET forum( http://forums.asp.net/t/1513336.aspx ).
Here is my example with CTE:
create table Locations
(LocationID int, Title varchar(50), description varchar(50), ParentLocationID int)
insert into Locations values (1,’aaa1′,’about aaa1′, null)
insert into Locations values (2,’aaa2′,’about aaa2′, 23)
insert into Locations values (3,’aaa3′,’about aaa3′, 24)
insert into Locations values (4,’aaa4′,’about aaa4′, 23)
insert into Locations values (5,’aaa5′,’about aaa5′, 24)
insert into Locations values (12,’aaa12′,’about aaa12′, 24)
insert into Locations values (13,’aaa13′,’about aaa13′, 12)
insert into Locations values (14,’aaa14′,’about aaa41′, 12)
insert into Locations values (15,’aaa15′,’about aaa15′, 12)
insert into Locations values (112,’aaa112′,’about aaa112′, 1)
insert into Locations values (113,’aaa113′,’about aaa113′, 15)
insert into Locations values (114,’aaa114′,’about aaa114′, 113)
insert into Locations values (115,’aaa115′,’about aaa115′, 114)
insert into Locations values (22,’aaa22′,’about aaa22′, 112)
insert into Locations values (23,’aaa23′,’about aaa23′, 1)
insert into Locations values (24,’aaa24′,’about aaa24′, 1)
insert into Locations values (25,’aaa5′,’about aaa25′, 23)
 
declare @LocationID int
set @LocationID =12
 
;WITH DirectReports (LocationID, ParentLocationID, Title, description, Level)
AS
(
— Anchor member definition
    SELECT e.LocationID, e.ParentLocationID,  e.Title, e.description , 0 AS Level
    FROM Locations AS e
        WHERE  e.LocationID=@LocationID
    UNION ALL
— Recursive member definition
    SELECT e.LocationID, e.ParentLocationID,  e.Title, e.description, Level + 1
     FROM Locations AS e INNER JOIN DirectReports AS d
        ON e.ParentLocationID= d.LocationID
)
— Statement that executes the CTE
—-SELECT LocationID, Title, description, ParentLocationID, Level
—-FROM DirectReports
—-order by Level,LocationID
–DELETE
DELETE Locations
FROM Locations L INNER JOIN DirectReports D ON L.LocationID=D.LocationID
SELECT * FROM Locations
drop table Locations