Retrive all vaues from sql using C#

Question

1.00/5 (1 vote)

See more:

Hi

I create a database and procedure now i want to retrieve all value using asp.net using data adaptor How i do that

Please help me

Thanks

Gurpreet Arora

Posted 2-Nov-14 21:17pm

Arora1992

Add a Solution

Comments

syed shanu 3-Nov-14 3:21am

Use Google,Lots of sample available there.if you really cont find any solution of your question using google then replay here will give you sample.

Arora1992 3-Nov-14 3:25am

I write this code but not working

nt Id = Convert.ToInt32(TextBox1.Text);

DataTable dt = new DataTable();

SqlConnection abc = new SqlConnection(@"connection string");
abc.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = abc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "contact_retrieve_all_procedure";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
int a = dt.Rows.Count;

for (int i = 0; i < a; i++)
{
Response.Write(dt.Rows[i]["Id"] + " " + dt.Rows[i]["title"] + "" + dt.Rows[i]["Firstname"] + " " + dt.Rows[i]["Lastname"] + "" + dt.Rows[i]["address"] + "" + dt.Rows[i]["company"] + "" + dt.Rows[i]["lastsenddate"] + "");
}

syed shanu 3-Nov-14 3:26am

what error did you get

Arora1992 3-Nov-14 3:27am

Procedure contact_retrieve_all_procedure has no parameters and arguments were supplied.

syed shanu 3-Nov-14 3:27am

can you paste your procedure here.

Arora1992 3-Nov-14 3:28am

create procedure contact_retrieve_all_procedure as
select * from contact

syed shanu 3-Nov-14 3:32am

here you have the mistake
In procedure you dont have any parameter but in your code you use parameter as ID
I think your proecedure should be like this.
create procedure contact_retrieve_all_procedure (
@Id int = 0
)

AS
BEGIN
select * from contact where id=@Id

END

Arora1992 3-Nov-14 3:46am

Sorry my procedure is this
I am Still getting the error

create procedure contact_retrieve_procedure(@id int)as
select * from contact where Id=@Id

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)

OriginalGriff · Accepted Answer · 2014-11-02T21:22:00

Try:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlDataAdapter da = new SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable", con))
        {
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
        }
    }

Praveen_P · Accepted Answer · 2014-11-02T21:57:00

Solution 2

Using data adapter you can retrieve data like

SqlConnection con = new SqlConnection();
con.ConnectionString="your_connection_string";
String str="your select query";
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);

Posted 2-Nov-14 21:57pm

Praveen_P

Comments

Arora1992 3-Nov-14 4:12am

int Id = Convert.ToInt32(TextBox1.Text);

DataTable dt = new DataTable();

SqlConnection abc = new SqlConnection(@"connection string");
abc.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = abc;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "contact_retrieve_procedure";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
int a = dt.Rows.Count;

for (int i = 0; i < a; i++)
{
Response.Write(dt.Rows[i]["Id"] + " " + dt.Rows[i]["title"] + "" + dt.Rows[i]["Firstname"] + " " + dt.Rows[i]["Lastname"] + "" + dt.Rows[i]["address"] + "" + dt.Rows[i]["company"] + "" + dt.Rows[i]["lastsenddate"] + "");
}

and Procedure is

create procedure contact_retrieve_procedure(@id int)as
select * from contact where Id=@Id

Praveen_P 3-Nov-14 4:20am

can u change your procedure like this

ALTER PROCEDURE contact_retrieve_all_procedure
@Id int = 0

AS
BEGIN
select * from contact where id=@Id

END

Arora1992 3-Nov-14 4:25am

I dont want to change the procedure I only want to retrieve data

Praveen_P 3-Nov-14 4:37am

i didn't see any error in your code , thats why i ask to update your procedure

Arora1992 3-Nov-14 4:55am

Its working Now Thank You