.NET Mixer: November 2009

Wednesday, November 25, 2009

Bind GridView with MS Access Database table in Asp.net

Please visit my new Web Site https://coderstechzone.com

In many forums i found that developers struggle "To connect with MS Access Database from Asp.net". Thats why in this post i will show you how one can access MS Access database from Asp.net ASPX page to bind GridView. To do that first open any MS Access Database & then create below table:

Fig: Entity

And ofcourse copy your MS Access mdb Database into your application root folder.

Ok, Now our MS Access Database Article table is ready. So add an aspx page in your asp.net project & then drag & drop a GridView within the page. Named it by gvArticle. Full HTML Markup Language is given below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MSACCESSGV.aspx.cs" Inherits="MSACCESSGV" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Test MS ACCESS DATABASE CONNECTION</title>
</head>

<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="gvArticle" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Id" />
<asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Title" />
<asp:BoundField DataField="Published" HeaderText="Published" SortExpression="Visit" />
<asp:BoundField DataField="Modified" HeaderText="Modified" SortExpression="Modified" />
</Columns>
</asp:GridView>

</div>
</form>

</body>
</html>

Now you need to write code in server side to bind GridView gvArticle. First make connection string for your MS Access Database. And then read data from MS Access database. Complete server side code is given below:

using System;
using System.Data;
using System.Web.UI;
using System.Data.OleDb;

public partial class MSACCESSGV : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{
string sFilePath = Server.MapPath("MSACCESS.mdb");
DataTable dt;
OleDbConnection Conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";");

using (Conn)
{
Conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM ARTICLE", Conn);
OleDbDataAdapter oDA = new OleDbDataAdapter(cmd);
dt = new DataTable();
oDA.Fill(dt);
}

gvArticle.DataSource = dt;
gvArticle.DataBind();

}
}
}

Now run your page. And you will get an output like below:

Fig: Output

Hope now you can make connection with MS-Access Database & also can retrieve data from MS-Access from your asp.net application.

Tuesday, November 24, 2009

SQL Server 2008 Error: Saving changes is not permitted

Please visit my new Web Site https://coderstechzone.com

Few days ago I have migrated one of my Database from SQL Server 2005 to SQL Server 2008. Actually what I did during migration? I have created a Database first in SQL Server 2008. Then I have imported all of my SQL Server 2005 tables into the SQL Server 2008 Database. I thought that Everything is perfect but when I live my application for testing then I found that System can not insert data into few tables. Then I debug my application & found that my imported tables has missed identity constraint. So what can I do? According to SQL Server 2005 my understanding was I can apply identity constraint on numeric columns if the column has a sequence of number. So I open my table (which has lost identity constraint) in design mode then click on ID column then from ColumnProperties select identity = yes & then save. But at that time I found the below error:

Saving changes is not permitted. The changes you have made require the
following tables to be dropped and re-created. You have either made changes to

a table that can’t be re-created or enabled the option Prevent saving changes

that require the table to be re-created.

The snapshot is given below:

Fig: Error

The error is self explanatory & helpful enough to find a solution clearly.

Then after few minutes i found the below solution:
GO TO Tools >> Options >> Designers

Then you will found the option "Prevent saving changes that require table re-creation" is checked. Just uncheck the option will resolve your problem.

Please find the step by step screenshots from below:
GO TO Tools >> Options

Fig: Step 1

Now just uncheck the option will resolve your problem:

Fig: Step 2

Hope it will help you.

Monday, November 23, 2009

How to pass SP parameters into Dynamic SQL

Please visit my new Web Site https://coderstechzone.com

In my first article I wrote An Introduction on creating Dynamic SQL in SQL Server Stored Procedure (SP). In this article I will try to write a bit advance Dynamic SQL. Here i will show you How one can transfer or pass parameter into Dynamic SQL query & get scalar value as output parameter or can Store output data into a temporary table.

CLICK HERE To read "How can invoke/read SQL Server SP from ASP.net ASPX page".

Focus Area:
1. How one can pass an Input Parameter to Dynamic SQL
2. How one can pass OUTPUT Parameter to Dynamic SQL
3. How one can store Dynamic SQL data into a Temporary table

To do that first create a table Article like below:

ID - bigint - Unchecked
CategoryID - bigint - Unchecked
Title - varchar(500) - Unchecked
Published - datetime - Unchecked
ModifedDate - datetime - Checked
Active - bit - Unchecked
TotalView - bigint - Checked

Then insert below rows into Article table:

INSERT INTO Article
VALUES(1,1,'How to start AJAX','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(2,2,'How to write Dynamic SQL','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(3,1,'Pass parameters','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(4,1,'Advance SQL Sored Procedure','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',0,1005)

INSERT INTO Article
VALUES(5,1,'Advance JQUERY Articles','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',0,1005)

Now our environment is ready for testing. Let our requirement is list all articles based on Active or not. So need to write a stored procedure which will take one parameter for Active or inactive & then we need to pass dynamic sql result into a temprary table.

So first write a Stored Procedure(SP) in SQL Server like below:

CREATE Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC(@sSQL)

END

The SP was created. Now run the Above SP by invoking below code:

EXEC ParamToDynamicSQL 1

UFFS i found an error. The error is:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@bActive".

Yes you can not use parameters within the Dynamic SQL directly in a SP. That’s why we need to use the power of SP_EXECUTESQL built in method to pass parameters into dynamic sql instead of EXEC or EXECUTE method. Which i have discussed in my first article on "An Introduction on creating Dynamic SQL in SQL Server Stored Procedure(SP)".

How one can pass an Input Parameter to Dynamic SQL:
To do that first declare a NVARCHAR type variable to store all parameters & then pass it through SP_EXECUTESQL method like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'

SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive

END

Now run the above SP by invoking the below command:

EXEC ParamToDynamicSQL 1

Fig: OUTPUT

How one can pass OUTPUT Parameter to Dynamic SQL:
So I think initial work around is done. Now I will try to show you how we can use OUTPUT parameter in Dynamic SQL. Lets now our requirement is to show number of active articles. So we need to modify our previous SP like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit,@TotalCount int OUTPUT)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit,@TotalCount int OUTPUT'

SET @sSQL='SELECT @TotalCount=COUNT(*) FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive,@TotalCount=@TotalCount OUTPUT

END

And you can invoke the Stored procedure in the following way:

Declare @TotalCount int
EXEC ParamToDynamicSQL 1,@TotalCount OUTPUT
print @TotalCount

Fig: OUTPUT

Ok now I hope you can pass parameter value into Dynamic SQL as well as can retrieve OUTPUT parameter value from Dynamic SQL.

How one can store Dynamic SQL data into a Temporary table:
As you know developers life is not so easy. The above techniques may not ease your life. We know that if we want to write a complex SQL then we like to break this SQL in different parts. To do that we use either view or temporary table to break down the complex SQL which will more readable & easy to modify. Here I will show you how we can store Dynamic SQL OUTPUT into temporary table. So that you can use this temporary table with another table to make SQL JOINS like Inner Join, Left Join & Right Join also you can then apply SET operation. One of the examples is given below:

ALTER Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @Article TABLE
(
ID bigint,
CategoryID bigint,
Title varchar(500),
Published datetime,
ModifedDate datetime,
Active bit,
TotalView bigint
)

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'
SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'

INSERT @Article
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive

SELECT * FROM @Article

END

Now you can invoke the above SP like below:

EXEC ParamToDynamicSQL 1

Fig: OUTPUT

This is all about Dynamic SQL. Hope now you can write runtime Dynamic SQL in SQL Server Stored Procedure (SP) to meet the client requirements.

Tuesday, November 17, 2009

Write & Execute Dynamic SQL in SQL Server Stored Procedure

Please visit my new Web Site https://coderstechzone.com

Most often we have to Create Dynamic SQL for different conditions to bring back results in our Asp.net applications. If we make the SQL in page code behind then its simple & easy. But think when one need to reuse the query with another set of values then what he can do? Copy the code block & paste it into the newly created aspx page? No here i will try to give you examples on How To Create a Dynamic SQL on the fly in SQL Server SP & reuse it. So lets start with an example:

A simple requirement:
Let you want to pass a table name into a Stored Procedure then collect all data to bind with a GridView. So our dynamic sql should be:

CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
SELECT TOP @topN * FROM @tblName

Uffs you will get the below error:
Msg 102, Level 15, State 1, Procedure GetAllRows, Line 3
Incorrect syntax near '@topN'.
OR
Msg 1087, Level 15, State 2, Procedure GetAllRowss, Line 3
Must declare the table variable "@tblName".

This is the limitation of Dynamic SQL. Don't worry SQL Server provide us two different ways to built a dynamic SQL Statement. These ways are as follows:
1. EXEC()
2. sp_executesql()

Using EXEC():
EXEC takes only one parameter which will be your Dynamic SQL. Its easy to use. If you want to pass few parameters into stroed procedure & then generate Dynamic SQL then its your easy choice. So we can rewrite the previous SP in the following way:

CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
DECLARE @sSQL nvarchar(MAX)
SET @sSQL='SELECT TOP '+CONVERT(varchar(MAX),@topN)+' * FROM '+@tblName
EXEC(@sSQL)

The below command will invoke your SP:

EXEC GetAllRows 10,'AnyTableName'

Now you will get 10 rows from your provided table name.

Using sp_executesql():
The sp_executesql() is a built in System Stored Procedure, an alternative and most flexible upgradation of EXEC(). By using sp_executesql() you will get some advantages like passing parameters into the dynamic sql which i will write later in my another post. Here i will show you a simple example on using sp_executesql():

CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
BEGIN
DECLARE @sSQL nvarchar(MAX)
SET @sSQL='SELECT TOP '+CONVERT(varchar(MAX),@topN)+' * FROM '+@tblName
execute sp_executesql @sSQL
END

The below command will invoke your SP:

EXEC GetAllRows 10,'AnyTableName'

I will suggest if & only if badly needed then use dynamic sql since it has a security hole as well as performence issues may arises. The other thing is difficult to debug since you are creating a string which will execute after invoke. So becareful before going live.

This post is basically an introductory article on "How to write Dynamic SQL". I will try to give more explanation on next articles. Untill then happy programming.

CLICK HERE TO READ PART II.



Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash

Want To Search More?

Newer Posts Older Posts Home

.NET Mixer

Wednesday, November 25, 2009

Tuesday, November 24, 2009

Monday, November 23, 2009

Tuesday, November 17, 2009

Blog Archive