Passing parameter to SQL statement ASP.NET VB.NET
Hello, I need your help to resolve a problem with passing a parameter to an SQL statement. Line 18: Dim dt As DataTable = GetData(cmd) appears to be wrong but I can't figure out how to fix it. It does not like the (cmd) in there. In short, I am trying to use the login email of the user logging in to filter some data that will be used to produce map (rptMarkers).
Imports System.Data.SqlClient
Public Class MapMyTerritories
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim eMail As String = Context.User.Identity.Name
If Not Me.IsPostBack Then
Dim strQuery As String = "select * from VW_MyTerritories WHERE eMail = @eMail"
Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.AddWithValue("@eMail", eMail)
Dim dt As DataTable = GetData(cmd)
rptMarkers.DataSource = dt
rptMarkers.DataBind()
End If
End Sub
Private Function GetData(query As String) As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim cmd As New SqlCommand(query)
Using con As New SqlConnection(conString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
End ClassASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Russ, that did the trick. At what line # should I add cmd.Dispose() ?
Anytime after line 18 will work. It looks like you're done with the object after that.
ASKER
For anyone benefit, this is my final working code thanks to Russ.
Imports System.Data.SqlClient
Public Class MapMyTerritories
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim eMail As String = Context.User.Identity.Name
If Not Me.IsPostBack Then
Dim strQuery As String = "select * from VW_MyTerritories WHERE eMail = @eMail"
Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.AddWithValue("@eMail", eMail)
Dim dt As DataTable = GetData(cmd)
rptMarkers.DataSource = dt
rptMarkers.DataBind()
End If
End Sub
Private Function GetData(query As String) As DataTable
Using cmd As New SqlCommand(query)
Return GetData(cmd)
End Using
End Function
Private Function GetData(cmd As SqlCommand) As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Using con As New SqlConnection(conString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
cmd.Dispose()
End Function
End Class
Open in new window