ASP.NET - parametrize query for SqlDataSource in ASP.NET code behing- VB.NET / Visual Studio 2010

Question

I am creating a query for a SqlDataSource in ASP.NET from code behind.

My code is as following:

Dim SqlDataSource1 As New SqlDataSource()
Dim SQL As String

' If Not IsPostBack Then
        SqlDataSource1.ID = "sqlexpsearch"
        Me.Page.Controls.Add(SqlDataSource1)
        Dim connectionString As String
        Dim connection As SqlConnection
        connectionString = ConfigurationManager.ConnectionStrings("exam2ndconnection").ToString
        connection = New SqlConnection(connectionString)

        SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("exam2ndconnection").ConnectionString

        If opname.Checked = True Then
            SQL = "select SRNO,tr_date as  Date ,MailMethod,SpeedId,Content,ExYear,Exroll as No,NAME,Address from dispatch where name = '%'+ @srname +'%' OR ADDRESS ='%'+ @srname +'%' order by TR_DAte DESC,NAME  "
            SqlDataSource1.SelectParameters.Add("@srname", UCase(txtitem.Text))

            SqlDataSource1.SelectCommand = SQL
        End If
        If opchno.Checked Then
            SqlDataSource1.SelectCommand = "select SRNO,tr_date as Date ,MailMethod,SpeedId,Content,ExYear,Exroll as No,NAME,Address from dispatch  where rtrim(exroll) ='" & txtitem.Text & "' order by tr_Date,exroll desc"
        End If

        GridView1.DataSource = SqlDataSource1
        GridView1.DataBind()

I get this error:

Must declare the scalar variable "@srname".

on the line of code:

Gridview1.DataBind()

Please help to resolve this problem.

Why are you parametrising in one statement but injecting in the other? Also the value of SqlDataSource1.SelectCommand is never going to be the value of SQL, as your first If has If opname.Checked = True and then the one immediately afterwards has If opchno.Checked, which is implicitly the same. — Thom A
– Thom A ♦, Commented Jan 17, 2022 at 10:50
also, no point of using wildcards if you're using = instead of like. — Zohar Peled
– Zohar Peled, Commented Jan 17, 2022 at 10:53
Yes Sir, = instead of like. is error it is by mistake with many tries. Now even after removing I am having same problem — neeraj bhushan
– neeraj bhushan, Commented Jan 17, 2022 at 11:09

Albert D. Kallal · Accepted Answer · 2022-01-18 04:43:07Z

I would avoid trying to inject (add) a sql datasource into a page. (they don't persist anyway - you would have to re-inject each time).

However, DO keep in mind that any data aware control ALWAYS will automatic persist for you anyway.

The above information is thus great, since then you don't even have to bother with the sql data source. This is especially so in your example - you want to fill out a dropdown list, a grid view - whatever. In those cases? Just shove into that control a datatable - and your off to the races.

As noted, a lot of us use + prefer using code in place of a data source on the page - I find them rather messy and a pain to work with anyway.

In fact, what I will often do is say drop in a listbox, or even a grid view. I then use the wizard to create new data source - lay out the grid real nice and fast. I then go into the markup, remove the data source conrol that appears in the page.

Also, don't forget to remove the DataSourceID = from the Gridview markup (or whatever control you using).

This lets me still use the wizards to create the gridview, listview etc. but then I delete that extra junk, and wind up with a nice clean page without all that extra stuff in the page - (which is a pain to control from code anyway).

So, say for your example?

Try coding it this way:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then

        LoadGrid()

    End If

End Sub

Sub LoadGrid()

    Using conn As New SqlConnection(ConnectionStrings("exam2ndconnection").ConnectionString)

        Dim strSQL As String = ""
        strSQL = "SELECT SRNO, tr_date as Date, MailMethod, SpeedId, Content, ExYear, Exroll as No, " &
                 "[Name], Address FROM dispatch "

        Dim strWhere As String = ""

        Using cmdSQL As New SqlCommand(strSQL, conn)

            If opName.Checked Then
                strWhere = "([Name] Like '%'+ @srname +'%' OR ADDRESS = '%'+ @srname +'%') "
                cmdSQL.Parameters.Add("@srname", SqlDbType.NVarChar).Value = txtitem.Text
            End If

            If opchno.Checked Then
                If strWhere <> "" Then strWhere &= " AND "
                strWhere &= "(Rtrim(exroll) = @exroll)"
                cmdSQL.Parameters.Add("@exroll", SqlDbType.NVarChar).Value = txtitem.Text
            End If

            If strWhere <> "" Then
                cmdSQL.CommandText &= " WHERE " & strWhere
            End If

            ' add our sorting

            cmdSQL.CommandText &= " ORDER BY tr_Date, exroll DESC"

            conn.Open()
            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)

            GridView1.DataSource = rstData
            GridView1.DataBind()

        End Using
    End Using

End Sub

A few things:

YES DO NOT forget to put the load of such data inside of the PostBack = false.

Load the results into a datatable (there are several reasons for this, but one BIG reason is that the row data bound event will have full use of that data row - including the WHOLE data row - even columns that are NOT part of the gridview.

note careful in above.

If you don't check "opname", then the criteria is not added.

if you don't check "opchno", then the criteria is not added.

if you check either one - the criteria for that option is added

so, if you check none, no criteria

if you check both, then you get both filters. So this allows you add even more options, and they are ALL optional - and we "clumative" can build up selection criteria this way.

Collectives™ on Stack Overflow

ASP.NET - parametrize query for SqlDataSource in ASP.NET code behing- VB.NET / Visual Studio 2010

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related