How to access Cmd.exe in Windows 7?
Posted: January 31, 2010 Filed under: My IT tools Leave a commentThere 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)
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
Posted: January 31, 2010 Filed under: SQL Server 2005 Express Leave a commentThere 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.
The document provided detailed instructions to resolve the issue.
Bulkcopy Excel 2007 data to SQL Server: Issue— Could not find installable ISAM
Posted: January 31, 2010 Filed under: Excel 2007 1 CommentIf 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
Posted: January 30, 2010 Filed under: ASP.NET 2 Leave a commentIt 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?
Posted: January 25, 2010 Filed under: SQL Server 2008 Leave a commentI 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?
Posted: January 24, 2010 Filed under: ASP.NET 4 Leave a commentBy 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
Posted: January 12, 2010 Filed under: SQL Server 2005 Leave a commentTo 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)
(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 (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 (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)
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
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
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
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
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
DELETE Locations
FROM Locations L INNER JOIN DirectReports D ON L.LocationID=D.LocationID
SELECT * FROM Locations
drop table Locations
jQuery Samples from Scott Mitchell at 4Guysfromrolla
Posted: January 11, 2010 Filed under: My IT tools Leave a commentHere again Scott gave a good write up about how to use jQuery to creat e a filtering user interface in a webform:
It seems he skips the C# code on purpose.
I converted it to C# and put it somewhere in my Space. You may find it here if you give the full credit to Scott:
Add line break in Javascript from HTML page
Posted: January 11, 2010 Filed under: jQuery/Javascript Leave a commentHere is the syntax for how to get the line break \n from an HTML element to show in an Alert box:
<script type="text/javascript">
function fnGetLineBreak() {
alert(document.getElementById(
"teksts").innerHTML.toString().replace(‘\\n’, "\n"));}
</
script>…
<
input type="button" value="Show First DIV Content" onclick="fnGetLineBreak()" ><a
id="teksts">line 8\nline 9</a> <a id="teksts">line 8\nline 9</a>
Jingyang Li 