ASP.NET SQL Server

[Scott Newberry]

Please pardon my ignorance on this one, but here's what I'm trying to do:

I have a table in a SQL Server 2000 DB called distributors. I've got an
ASPX which has a text box, a button, and two labels. In the textbox, the
user is to enter a "distributor id", then click the button. The button
should query the distributors table for a matching distributor id, and
return two prices, which is what the labels should reflect. Right now,
though, I'm not even trying to match up the distributor id. The table has
only one record in it at this time.

This is what I'm doing right now:

sqlConnection1.ConnectionString = blah blah blah;
sqlSelectCommand1.CommandText = "SELECT distid, colorprice, blackprice FROM
dbo.distributors";
sqlSelectCommand1.Connection = sqlConnection1;


private void Button1_Click(object sender, System.EventArgs e)
{
sqlConnection1.Open();
sqlDataReader MyReader = sqlSelectCommand1.ExecuteReader();
Label1.Text = MyReader.getSqlMoney(1).ToString();
Label2.Text = MyReader.getSqlMoney(2).ToString();
sqlConnection1.Close;
}

As you can see, right now, I'm not even trying to match up the distributor
id. I'm just trying to get the prices for the one record that's there.
After I get that working I'll want to start matching up the distributor id.

The error I'm getting is: Invalid attempt to read when no data is present.

Any ideas?

Thanks!

Scott

 

[Scott Newberry]

Never mind... Had to issue MyReader.Read() first... Oops.

But now...

How do I make my query "dynamic". I don't want to hard code the search
expression. I want to pull it from the text box on the form. I've tried
assigning it to a variable and inserting the variable name into the command
text, but I'm pretty sure I'm using the wrong syntax...

SELECT distid, colorprice, blackprice FROM dbo.distributors WHERE (distid =
cDistID)

 

[Scott Newberry]

Got that working too by adding a parameter...

Sorry for wasting space on the newsgroup. Turns out I can't figure anything
out for myself until I ask for help. Then I get it.

 

[Kevin Yu [MSFT]]

Hi Scott,

I'd so glad to know that you've got your program working. You can also use
the following code to get the records dynamically. You can put it in
Button1_Click().

sqlSelectCommand1.CommandText = "SELECT distid, colorprice, blackprice FROM
dbo.distributors WHERE distid = " + Button1.Text;

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "Scott Newberry" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: Re: ASP.NET SQL Server
| Date: Tue, 28 Oct 2003 00:23:00 -0500
| Lines: 6
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: ddsl-216-196-240-194.fuse.net 216.196.240.194
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:64684
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Got that working too by adding a parameter...
|
| Sorry for wasting space on the newsgroup. Turns out I can't figure
anything
| out for myself until I ask for help. Then I get it.
|
|
|

 

[David]

Hi Scott,

I'd so glad to know that you've got your program working. You can also use
the following code to get the records dynamically. You can put it in
Button1_Click().

sqlSelectCommand1.CommandText = "SELECT distid, colorprice, blackprice FROM
dbo.distributors WHERE distid = " + Button1.Text;

Which is good advice for getting started, but tends to create huge
security holes when you start leaving things like that in real apps.
IMHO, using parameters, as Scott has now, is better, and using stored
procs is better yet.

 

[Kevin Yu [MSFT]]

Hi Scott,

I'd like to know if this issue has been resolved. Is there anything that I
can help on this? I'm still monitoring on it.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: David <[email protected]>
| Subject: Re: ASP.NET SQL Server
| References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
| Message-ID: <[email protected]>
| User-Agent: slrn/0.9.7.4 (Linux)
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Date: Tue, 28 Oct 2003 12:46:46 -0800
| NNTP-Posting-Host: 129-138.175-24.bham.rr.com 24.175.138.129
| Lines: 1
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa09.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:64767
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| > Hi Scott,
| >
| > I'd so glad to know that you've got your program working. You can also
use
| > the following code to get the records dynamically. You can put it in
| > Button1_Click().
| >
| > sqlSelectCommand1.CommandText = "SELECT distid, colorprice, blackprice
FROM
| > dbo.distributors WHERE distid = " + Button1.Text;
|
| Which is good advice for getting started, but tends to create huge
| security holes when you start leaving things like that in real apps.
| IMHO, using parameters, as Scott has now, is better, and using stored
| procs is better yet.
|
|
|
| --
| David
| dfoster at
| hotpop dot com
|

 

[Scott Newberry]

No thanks, but I think I've got it...

I appreciate your suggestions though... Thanks!

Scott