insert update delete using store procedure and three tier architecture

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i want to display some records in the gridview. i am using three tier architecture for display the records
This is store procedure and table name apmc2.

SQL

create procedure proapmc3
as
select * from apmc2

in the BEL class .i have declared properties which is already in my table.

C#

public class BEL
{

    public string ID { get; set; }
    public string Name { get; set;}
    public decimal Salary { get; set; }
    public DateTime Date_In { get; set; }
}

in the BLL class i have declared

C#

public object displygird(BEL oob)
{
    DLL ob = new DLL();
    return ob.bindgrid(oob);
}

in the DLL class

C#

public object bindgrid(BEL obj)
{
    SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");
    con.Open();
    SqlCommand cmd = new SqlCommand("proapmc3", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 22));
    cmd.Parameters["@id"].Value = obj.ID;

    cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 44));
    cmd.Parameters["@Name"].Value = obj.Name;

    cmd.Parameters.Add(new SqlParameter("@Salary", SqlDbType.Decimal, 7));
    cmd.Parameters["@Salary"].Value = obj.Salary;

    cmd.Parameters.Add(new SqlParameter("@Date_in", SqlDbType.DateTime, 27));
    cmd.Parameters["@Date_in"].Value = obj.Date_In;

    DataSet ds = new DataSet();
    BLL ob = new BLL();

    da.Fill(ds);
    return ds;
}

and in the last in my home.aspx
method is blew

C#

public void displygrid()
{
    BEL ojbelb = new BEL();
    BLL ob = new BLL();
    GridView1.DataSource = ob.displygird(ojbelb);
    GridView1.DataBind();
}

please help me where i am wrong .

I am getting one exception error in da.fill(ds) line
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Posted 24-Dec-14 0:18am

faizyab 2009

Updated 28-Dec-14 20:33pm

Tejas Vaishnav

v4

Add a Solution

Comments

Kornfeld Eliyahu Peter 24-Dec-14 6:24am

And the question is?

faizyab 2009 24-Dec-14 6:29am

i am not able to get the records into the gridview

Kornfeld Eliyahu Peter 24-Dec-14 6:32am

'not able' - in what way? Do you have errors? Did your run it with debugger to see if it does what you have expected?

Tejas Vaishnav 24-Dec-14 6:36am

Have you try to debug your BEL and DLL class methods, they are filling your dataset or not? and if you got any exception then please mention that too.

faizyab 2009 24-Dec-14 6:45am

i am getting one exception error in da.fill(ds) line
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
"
@tejas sir i want to run this code properly. please tell me where i am missing

Tomas Takac 24-Dec-14 6:50am

Well, why didn't you say so in the beginning? I guess you don't assing any value to BEL.Date_in so it has the default value of DateTime.MinValue which cannot be handled by SQL Server. Consider saving it as NULL.

faizyab 2009 24-Dec-14 7:11am

i have defined properties in BEL class. if i am missing something in BEL so please tell me how to correct

faizyab 2009 24-Dec-14 16:06pm

Thank you all of you friends. now i have change the codes i used dataset and returned ds value now my code is working

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KaushalJB · Answer 1 · 2014-12-24T01:24:00

Solution 1

Refer this : Passing null as SQLParameter DateTime value

Posted 24-Dec-14 1:24am

KaushalJB

Tejas Vaishnav · Answer 2 · 2014-12-24T01:58:00

If you are using store procedure like you mention in your question...

SQL

create procedure proapmc3
as
select * from apmc2

And it will not taking any parameter to return data, so why are you sending parameter to your bindgrid method?

and as per your code, you are using BEL class object to define your property and you are just creating object of it and pass it in your displygird method without initializing any property values like ID, Name or Date_In etc.

Please remove all parameter binding in your DLL method and also remove parameter of BLE from your BLL method too. And try to debug all this methods.

Like this...

C#

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("proapmc3", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

also please take care your your open connection object and please properly open/close your sql server connection