Reader parameters

[netnatter]

I have used the following to get data from my SQL database and populate text
& label boxes.

I understand how to use parameters with gridview and data view but not with
the reader.

How can I change this code to use parameters and so give some protection
from insertion issues.



Dim con As New
SqlConnection(C onfigurationMan ager.Connection Strings("mydata ConnectionStrin g").ToString )
Dim str As String
Dim sel As SqlCommand
Dim myReader As SqlDataReader
con.Open()
str = "Select id, title, keywords, speaker, date_text, duration
from webcasts where id=" & Val(Request.Que ryString("id")) and speaker=" &
"'" & textbox_speaker _requested.text &"'"
sel = New SqlCommand(str, con) : myReader = sel.ExecuteRead er()
If myReader.Read() Then
If (myReader.IsDBN ull(0)) = False Then Label_id.Text =
myReader.GetInt 32(0)
If (myReader.IsDBN ull(1)) = False Then TextBox_title.T ext =
myReader.GetStr ing(1).Trim
If (myReader.IsDBN ull(2)) = False Then TextBox_keyword s.Text
= myReader.GetStr ing(2).Trim
If (myReader.IsDBN ull(3)) = False Then TextBox_speaker s.Text
= myReader.GetStr ing(3).Trim
If (myReader.IsDBN ull(4)) = False Then
TextBox_daterec orded.Text = myReader.GetStr ing(4).Trim
If (myReader.IsDBN ull(5)) = False Then TextBox_duratio n.Text
= myReader.GetInt 32(5)
End If
myReader.Close( ) : con.Close()


netnatter

[=?Utf-8?B?ZG90TmV0RGF2ZQ==?=]

RE: Reader parameters

In most cases you should not use a DataReader. The reason is that until you
call close you are keeping a connection open. It's much better to use a
DataSet since the time connected to the database is usually much shorter. I
tend to use typed DataSets since they are much easier to use or you can use
the new Entity Framework.

If you still want to use DataReader, PLEASE use Exception handeling and
close the reader and connection in the Finally block.

To actually answer your question you can put parameters in your SQL string
and then use parameter objects to set them. There are tons of examples if you
Google it.

David

=============== =============== ========
David McCarter [Microsoft MVP]

https://www.dotNetTips.com

David McCarter''''s .NET Coding Standards available at:
http://www.cafepress.com/geekmusicart.1654787045


"netnatter" wrote:

>
>
I have used the following to get data from my SQL database and populate text
& label boxes.
>
I understand how to use parameters with gridview and data view but not with
the reader.
>
How can I change this code to use parameters and so give some protection
from insertion issues.
>
>
>
Dim con As New
SqlConnection(C onfigurationMan ager.Connection Strings("mydata ConnectionStrin g").ToString )
Dim str As String
Dim sel As SqlCommand
Dim myReader As SqlDataReader
con.Open()
str = "Select id, title, keywords, speaker, date_text, duration
from webcasts where id=" & Val(Request.Que ryString("id")) and speaker=" &
"'" & textbox_speaker _requested.text &"'"
sel = New SqlCommand(str, con) : myReader = sel.ExecuteRead er()
If myReader.Read() Then
If (myReader.IsDBN ull(0)) = False Then Label_id.Text =
myReader.GetInt 32(0)
If (myReader.IsDBN ull(1)) = False Then TextBox_title.T ext =
myReader.GetStr ing(1).Trim
If (myReader.IsDBN ull(2)) = False Then TextBox_keyword s.Text
= myReader.GetStr ing(2).Trim
If (myReader.IsDBN ull(3)) = False Then TextBox_speaker s.Text
= myReader.GetStr ing(3).Trim
If (myReader.IsDBN ull(4)) = False Then
TextBox_daterec orded.Text = myReader.GetStr ing(4).Trim
If (myReader.IsDBN ull(5)) = False Then TextBox_duratio n.Text
= myReader.GetInt 32(5)
End If
myReader.Close( ) : con.Close()
>
>
netnatter
>
>

[Mark Rae [MVP]]

Re: Reader parameters

"dotNetDave " <dotNetDave@dis cussions.micros oft.comwrote in message
news:873DA553-D2AB-4F41-9ADD-100F7C77C300@mi crosoft.com...

If you still want to use DataReader, PLEASE use Exception handling and
close the reader and connection in the Finally block.

Or, even better, don't... Instead, use a using {} block:

http://www.aspfree.com/c/a/ASP.NET/Using-the-SqlDataReader-Class/2/

--
Mark Rae
ASP.NET MVP

http://www.markrae.net