Knight.Liao's Tale

Error Description:

you may write a stored prcedure which return bigint value, the code can be :

SELECT CAST(SCOPE_IDENTITY() AS BIGINT)

The above statement returns the affected rows of tables and the return value is Big int.  In the .cs file, you may write code to get the return value of this stored procedure:

int id = (int)sqlCommand.ExecuteScalar();

I'm very sorry that the very strange error happen. We'll get the error: System.InvalidCastException: Specified cast is not valid. at....

Solution:

I don't know why this error happen. We can write code as follows to fix the erorr:

int id = (int)(long)sqlCommand.ExecuteScalar();

Why?

the sql command return an object, we firstly cast it into "long" type, and then cast it to "int" type, however, why can we cast it into "int" type directly? I'm so confused, Who can tell me the reasons?

Thank you very much in advanced.

Error Description:

Procedure or function 'XX' expects parameter 'YY', which was not supplied.

Dicussion:

this error may be caused by some very fool mistakes. For example, if you use sql procedure to get data and you forget to write codes as follows:

sqlCommand.CommandType = CommandType.StoredProcedure;

You may get this error which say that your first parametr's value was not supplied. for example, if you write codes: (I supposed that the stored procedure name is "InsertQuery")

parameters = new SqlParameter[]{
                new SqlParameter("@BatchId", record.BatchId),
                new SqlParameter("@QueryString", record.QueryString),
                new SqlParameter("@BingURL", "")
                };

and you'll get error:  Procedure or function InsertQuery expects parameter @BatchId, which was not supplied.

Good Luck~

There are some SQL programming mistaken ideas about "order" clause. You may meet a error which is :

SQL SERVER – Fix : ERROR : Msg 1033, Level 15, State 1 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

This is mainly because that you are using your order clause in views or derived tables.
I don't recommend you to use "TOP 100 percent" method to avoid this error, Howerver, I strongly advise you to write the codes like :

CREATE VIEW vTestContactID
AS
SELECT FirstName, LastName
FROM Person.Contact
GO

SELECT *
FROM vTestContactID
ORDER BY FirstName DESC

These codes are orginally created by pinaldave[1].

Reference:

[1]. http://blog.sqlauthority.com/2007/08/12/sql-server-fix-error-msg-1033-level-15-state-1-the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqueries-and-common-table-expressions-unless-top-or-for-xml-is-als/

When you are using SQL SERVER Programming, if you want to get 'select column' data from 'select row' data, you have two choices: use 'case' statement or use 'Pivot' method.  'case' statement is very easy, it can be used in previous SQLSERVER version. Howerver, you should only use 'Pivot' if you want to get dynamic column data.

For example, you want to get serveral columns which the number of column you are not confirmed. In this way, you can use 'hard-code' method in SQL programming because you don't know how many columns you'll use. In this circumstance, you should use 'Pivot'.

There's very good tutorial about this new function. You can understand the usage of it if you have read this article.

The link of this article is : http://www.cnblogs.com/icebutterfly/archive/2009/08/07/1541322.html

Good Luck!

Today I meet a horrible problem: I can log in my local SQLSERVER!!!  There is a important project (ASPNET) which need this db!

This is how it come out:

Everyone konws that if we want to move ***.mdf and ***.ldf file to another place, we have to detach the db from sqlserver. Ok, now I detach the specified db, then when I want to connect my local db, An error came out.

Cannot Open User Default Database (error 4064)

This is because that the default db of my login account is the db which has just be detatched. 
 What is more serious is that, I fogot my "SA" password and all account for my SQLSERVER. So, I can't login any more!!!
Howerver, I found the solution at last. The solution is :

继续阅读

来微软有一定时间了，差不多有25天了吧(1.25-2.10, 2.23-3.2)。这些天里，日子过得还是比较紧张的，因为我对微软这套解决方案还是不怎么熟悉，花了挺多时间在代码学习上。还好，自己的学习能力还是不错的，现在的我差不多已经融合到微软的体系中了。之前在学校里都是在用visual studio工具写C++程序，图形API都是用DirectX，现在又会了C#，SQL Server，windows serivce, ASP.NET技术，走了都是微软的开发路线。那开源的社区是离我越来越远了。

回想刚入职的前夕，刚在从C++阵营转入C#阵营的纠结中，对于ASP程序的恐慌，对于SP是啥玩意非常心虚，而现在的我对于写C#，存储过程，数据库设计，ASP程序，windows service程序创建都可以很好的完成了。至于在昨天超前的完成公司的项目，自己也感到很有成就感。

在微软待了多久，就在北京住了多久。帝都虽奢华，但始始终是他人的帝都。这里不得不提下一下她，little pig pig.  因为有了她，灰色的天空竟然会变得那么的蓝，一切都变得是那么的有意义了:)

Quickly development with C# and SQL server, ASP.NET~!