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(ConfigurationManager.ConnectionStrings("mydataConnectionString").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.QueryString("id")) and speaker=" &
"'" & textbox_speaker_requested.text &"'"
sel = New SqlCommand(str, con) : myReader = sel.ExecuteReader()
If myReader.Read() Then
If (myReader.IsDBNull(0)) = False Then Label_id.Text =
myReader.GetInt32(0)
If (myReader.IsDBNull(1)) = False Then TextBox_title.Text =
myReader.GetString(1).Trim
If (myReader.IsDBNull(2)) = False Then TextBox_keywords.Text
= myReader.GetString(2).Trim
If (myReader.IsDBNull(3)) = False Then TextBox_speakers.Text
= myReader.GetString(3).Trim
If (myReader.IsDBNull(4)) = False Then
TextBox_daterecorded.Text = myReader.GetString(4).Trim
If (myReader.IsDBNull(5)) = False Then TextBox_duration.Text
= myReader.GetInt32(5)
End If
myReader.Close() : con.Close()


netnatter

 

[dotNetDave]

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]
www.dotNetTips.com
David McCarter''''s .NET Coding Standards available at:
http://www.cafepress.com/geekmusicart.1654787045