What's wrong with this code? Trying to grab a DataReader

A

Alan Silver

hello,

I am writing my first real ASP.NET application, but am getting a bit
confused with ADO.NEt and the best way to use it.

My old method (with Classic ASP) was to have a function that you called
like ...

Set rsSomething = GrabRS("select * from mytable")

The function would open a connection to the database, pull out the data
and return a disconnected recordset. This could then be used in the ASP.

I want to do something similar in ADO.NET and ASP.NET, but am unsure of
the best way to do it. I have got as far as ...

SqlDataReader GrabDS(String strSQL) {
SqlConnection conConn = New
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
conConn.Open();
SqlCommand comComm = new SqlCommand(strSQL, conConn);
SqlDataReader drdData = comComm.ExecuteReader();
return drdData;
}

but keep getting an error on the first line of the block telling me
there is a missing colon. I have tried all sorts of variations of this,
but can't work out what's wrong.

Any advice? TIA for any help.
 
A

Alan Silver

Before anyone wastes their time, and to help any future readers, i got
this working just after I posted (ain't that always the way?). The
following code did what I wanted ...

SqlDataReader GrabDR(String strSQL) {
SqlConnection conConnection;
SqlCommand cmdCommand;
SqlDataReader dtrData;

conConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
conConnection.Open();
cmdCommand = new SqlCommand(strSQL, conConnection);
dtrData = cmdCommand.ExecuteReader();
return dtrData;
}
I'm not saying this is the most efficient way to do it, and if anyone
has any suggestions for improvement, I would be very grateful to hear
them, but this does work.
hello,

I am writing my first real ASP.NET application, but am getting a bit
confused with ADO.NEt and the best way to use it.

My old method (with Classic ASP) was to have a function that you called
like ...

Set rsSomething = GrabRS("select * from mytable")

The function would open a connection to the database, pull out the data
and return a disconnected recordset. This could then be used in the ASP.

I want to do something similar in ADO.NET and ASP.NET, but am unsure of
the best way to do it. I have got as far as ...

SqlDataReader GrabDS(String strSQL) {
SqlConnection conConn = New
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
conConn.Open();
SqlCommand comComm = new SqlCommand(strSQL, conConn);
SqlDataReader drdData = comComm.ExecuteReader();
return drdData;
}

but keep getting an error on the first line of the block telling me
there is a missing colon. I have tried all sorts of variations of this,
but can't work out what's wrong.

Any advice? TIA for any help.
 
I

IPGrunt

Before anyone wastes their time, and to help any future readers, i got
this working just after I posted (ain't that always the way?). The
following code did what I wanted ...

SqlDataReader GrabDR(String strSQL) {
SqlConnection conConnection;
SqlCommand cmdCommand;
SqlDataReader dtrData;

conConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
conConnection.Open();
cmdCommand = new SqlCommand(strSQL, conConnection);
dtrData = cmdCommand.ExecuteReader();
return dtrData;
}
I'm not saying this is the most efficient way to do it, and if anyone
has any suggestions for improvement, I would be very grateful to hear
them, but this does work.
hello,

I am writing my first real ASP.NET application, but am getting a bit
confused with ADO.NEt and the best way to use it.

My old method (with Classic ASP) was to have a function that you called
like ...

Set rsSomething = GrabRS("select * from mytable")

The function would open a connection to the database, pull out the data
and return a disconnected recordset. This could then be used in the ASP.

I want to do something similar in ADO.NET and ASP.NET, but am unsure of
the best way to do it. I have got as far as ...

SqlDataReader GrabDS(String strSQL) {
SqlConnection conConn = New
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
conConn.Open();
SqlCommand comComm = new SqlCommand(strSQL, conConn);
SqlDataReader drdData = comComm.ExecuteReader();
return drdData;
}

but keep getting an error on the first line of the block telling me
there is a missing colon. I have tried all sorts of variations of this,
but can't work out what's wrong.

Any advice? TIA for any help.

There's nothing wrong, per se, but you would be safer to use

1. stored procedures if possible, and if not, at least a
parameterized query

2. a try / catch block around your call to the server. This way you
can handle your error for the user and it also gives you an
opportunity to return a (SqlDataReader) null to the caller if no data
is returned.


-- ipgrunt
 
A

Alan Silver

There's nothing wrong, per se, but you would be safer to use
1. stored procedures if possible, and if not, at least a parameterized
query

Good point, however this code was supposed to be generic and so would be
harder with a sp. I could of course pass a call to an sp in the
parameter, it doesn't have to be plain SQL.
2. a try / catch block around your call to the server. This way you can
handle your error for the user and it also gives you an opportunity to
return a (SqlDataReader) null to the caller if no data is returned.

Ah, very good point. This was as far as I got when I got it working, so
I posted it before adding any checking. It did occur to me to add some,
but I must confess I haven't added it yet.

Thanks very much for the comments.
 
I

IPGrunt

Good point, however this code was supposed to be generic and so would be
harder with a sp. I could of course pass a call to an sp in the
parameter, it doesn't have to be plain SQL.


Ah, very good point. This was as far as I got when I got it working, so
I posted it before adding any checking. It did occur to me to add some,
but I must confess I haven't added it yet.

Thanks very much for the comments.

A Pleasure.

Sounds like you have it under control.

You might consider implementing your generic routine as a data access
class, where you can pass variable number and types of parameters in
as a constructor parameter.

Have fun.

-- ipgrunt
 
A

Alan Silver

A Pleasure.

;-)
Sounds like you have it under control.

That might be going too far, but I do have some idea of what I'm doing
now!!
You might consider implementing your generic routine as a data access
class, where you can pass variable number and types of parameters in
as a constructor parameter.

Which leads me to the next question I was going to ask ...

Once I have a useful routine like this (and its variant friends who will
soon appear), how do I go about making them globally available to any
web site on the machine without having to add them to each site? Common
routines like this should (presumably) be housed in one central location
where they can be accessed by any page on any site on the machine.

I'm quite new at this, so please explain clearly otherwise you'll lose
me!!
Have fun.

I am actually. I am a 100% convert from Classic ASP. I'm being more and
more amazed every time I find something else you can do in ASP.NET. I
wrote a script yesterday that would have taken me far longer in Classic
ASP, even with the library routines I have built up. I'm sure I could
have done it even faster had it not been the first time I had done a lot
of the things I was doing. I guess it will be a lot easier next time.

Thanks for the reply.
 
I

IPGrunt

That might be going too far, but I do have some idea of what I'm doing

Which leads me to the next question I was going to ask ...

Once I have a useful routine like this (and its variant friends who will
soon appear), how do I go about making them globally available to any
web site on the machine without having to add them to each site? Common
routines like this should (presumably) be housed in one central location
where they can be accessed by any page on any site on the machine.

I'm quite new at this, so please explain clearly otherwise you'll lose

I am actually. I am a 100% convert from Classic ASP. I'm being more and
more amazed every time I find something else you can do in ASP.NET. I
wrote a script yesterday that would have taken me far longer in Classic
ASP, even with the library routines I have built up. I'm sure I could
have done it even faster had it not been the first time I had done a lot
of the things I was doing. I guess it will be a lot easier next time.

Thanks for the reply.



OK.... you need to learn about static classes.


I declare the class like this:

public class Util
{

...

and then your methods get declared as static...


public static HttpContext getUserContext ()
{
return System.Web.HttpContext.Current;
}



Now this method is available to all in the namespace as:

HttpContext curr = Util.getUserContext();


Very simple, right?


By the way, I too was an ASPer and was resistent to learning *yet
another* set of programming conventions. But I'm glad I did. I abhor
writing for ASP anymore.

What I did was take the survey course that covered VisualStudio.net,
ADO.NET, ASP.NET, the Framework, etc., which really kickstarted my
learning. (I recommend that you try to get your employer/clients to
pay for it!)


-- ipgrunt
 
A

Alan Silver

Very simple, right?

Yup, once you know it!!

As soon as I saw it, I realised what I was doing wrong. I did some Java
a few years ago, and it was enough to kick my brain into gear when I saw
the answer posted. It's just a case of getting your head into a new
frame of thinking.
By the way, I too was an ASPer and was resistent to learning *yet
another* set of programming conventions. But I'm glad I did. I abhor
writing for ASP anymore.

Well, I've only been doing this for a few weeks, and it's only the last
couple of days that I've written anything that could be called Real
World(tm) stuff, so I'm still quite slow at it, but I can see already
that it knocks the spots of Classic ASP. This stuff is brilliant,
there's just so much here that can save wads of time.
What I did was take the survey course that covered VisualStudio.net,
ADO.NET, ASP.NET, the Framework, etc., which really kickstarted my
learning. (I recommend that you try to get your employer/clients to pay
for it!)

I *am* my employer <g>

Where do I find out about this course? I don't know if I can afford it,
but it's worth looking.

I've been working my way through ASP.NET Unleashed, which is very good.
Between that and some great help in this group, I'm getting the hang of
it all.

Thanks for the reply.
 
I

IPGrunt

Yup, once you know it!!

As soon as I saw it, I realised what I was doing wrong. I did some Java
a few years ago, and it was enough to kick my brain into gear when I saw
the answer posted. It's just a case of getting your head into a new
frame of thinking.


Well, I've only been doing this for a few weeks, and it's only the last
couple of days that I've written anything that could be called Real
World(tm) stuff, so I'm still quite slow at it, but I can see already
that it knocks the spots of Classic ASP. This stuff is brilliant,
there's just so much here that can save wads of time.


I *am* my employer <g>

Where do I find out about this course? I don't know if I can afford it,
but it's worth looking.

I've been working my way through ASP.NET Unleashed, which is very good.
Between that and some great help in this group, I'm getting the hang of
it all.

Thanks for the reply.

I am my employer (unemployer?), too. That's why I suggested getting a
client to pay.

These are Microsoft courses and taught by various shops around the
country. The one I took was in Bethesda, MD and given by a group from
VA somewhere, but that's cause my client was there and I got to sit
in with a group of their developers.

First we took a two-day course:

http://www.microsoft.com/learning/syllabi/en-us/2717Bfinal.mspx

This was the key to getting us kickstarted and if you can, get to
this course.

The next week we built our own learning (this teacher was good and
taught it all) out of bits and pieces of what seemed like these two
courses, (can't find the specifics as this was two or three years
ago.)


http://www.microsoft.com/learning/syllabi/en-us/2349bfinal.mspx

http://www.microsoft.com/learning/syllabi/en-us/2310Bfinal.mspx

The course did not have the detail that these seem to offer in some
areas, and provided much more detail in others... (which was
convenient, as these courses go to slowly for two days while students
get in gear and too fast for two days where it really counts as the
instructor is trying to catch up.)

We were focused on porting a large webapp from Java to .NET and
needed specifics about ADO.NET with Oracle, assemblies vs. libraries,
scriptlet issues, use of delagates for call-backs, and a dozen other
issues. I was hired because the client's programmers had zero
Microsft background and I was a bigtime ASP developer (so I told
them). Turns out, my knowledge was almost useless...the things I
learned from my experience with ASP didn't apply anymore. (like don't
put COM objects in Session vars, consolidate globals and utility
functions in include files (sound familiar), etc.)

A word of advice, use your Java skills, NOT your ASP skills when
architecting your application.

There are probably online learning course you could take that would
a) save dollars and b) save time. You need to be disciplined to do
this, and maybe your book gives you all you need?

But, there is something about an instructor led course, the
interaction with other students, the nights back at the hotel bar
telling lies, that really gets things moving in the brain to learn a
new technology. Of course, you have to keep applying your knowledge
once you get back home or you lose it quickly.

One more tip... get to know the Patterns and Practices site. This is
the best thing since sliced bread (and class factories) on msdn for
experienced developers.

http://www.microsoft.com/resources/practices/default.mspx

Now, I gotta get to work! If you need to you can email me at the name
below via yahoo mail.

-- ipgrunt
 
A

Alan Silver

<snip>

Thanks for the comments. Some back for you ...
A word of advice, use your Java skills, NOT your ASP skills when
architecting your application.

Spotted that one!! Trouble is, it's a few years since I did any Java, so
I'm basically remembering not to do it the ASP way!! Thinking in Java is
as hard as thinking in C# at the moment, so I'm just concentrating on
the latter.
There are probably online learning course you could take that would
a) save dollars and b) save time. You need to be disciplined to do
this, and maybe your book gives you all you need?

So far the book and this newsgroup have been excellent. The book gives
me the ideas and basic code, the group gives me feedback and ideas on
how to do more complex stuff.
One more tip... get to know the Patterns and Practices site. This is
the best thing since sliced bread (and class factories) on msdn for
experienced developers.

http://www.microsoft.com/resources/practices/default.mspx

I had a look at this, but at the time it was beyond me. I may go back
and look again though now I have more idea what I'm doing.

Thanks again,

Alan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top