SqlDataSource and SelectParameters

[kebele]

Hi all,
you have been a lot of help for me and i really appreciate the help i get form you all.
My question for today is as follows: I am using sqlDataSource and i created my own sql like this SqlDataSource1.SelectCommand = Sql

I am not sure what I did belwo here is legitimate, if so can someone give a suggestion how this can be done.
If (Trim(txtVendorName.Text) <> "") Then
Sql = Sql & " AND (Upper(V.VNAMEL) LIKE '%' || Trim(UpperVendorName))|| '%') "
SqlDataSource1.SelectParameters.Add("VendorName", txtVendorName.Text)
End If

as always, i am very grateful for the help i get from here

 

[CassidyHunt]

I am trying to duplicate what I think your doing. Here is my best guess:

behind code on page load:

If Me.IsPostBack Then
            If Not String.IsNullOrEmpty(TextBox1.Text) Then
                SqlDataSource1.SelectCommand &= " where upper(name)like '%' || upper(:vendorname) || '%'"
                SqlDataSource1.SelectParameters.Add(":vendorname", TextBox1.Text)
            End If
        End If

ASPX

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="select * from vendor">
            </asp:SqlDataSource>

Keep in mind this is only the sqlDataSource and not the textbox or gridview.

Let me know if this answers you question.

Cassidy

 

[kebele]

thank you Cassidy for your response i followed your suggestion and i am still getting an error msg.Here is my code behing ans aspx code for you to look at it. as you can see i am using sqlDataSource but i just write my own sql statement for selectCommand and selectparamter.
when i enter a vlue to the textbox and delete the value and make a selection from the other two dropdwons then it throws at me an excetions. please help me..I have been struggling with this for a while

thanks again



Sub VendorSearch()
Dim Sql As String = ""
gvSearch.Visible = True
Dim MinPDate As String = drPur.SelectedItem.ToString
Dim MaxMDate As String = drPurM.SelectedItem.ToString

Dim liThisOne As ListItem
Dim strState As String = ""
For Each liThisOne In lstState.Items
If liThisOne.Selected Then
strState = strState & "'" & liThisOne.Value & "'" & ","
End If
Next
Sql = " SELECT V.VENDOR ""Vendor Id"",R.ADDRNUM,V.VNAMEL ""Vendor Name"",R.AADDR1,R.ACITY,R.VASST1, "
Sql = Sql & "R.ASTATE State,R.AZIPCODE, to_char(Max(P.DATEPUR),'YYYY/DD/MM') ""Plan Purchased Date"" , "
Sql = Sql & "TRIM (r.aaddr1 || decode(trim(r.aaddr2),null,'',' - ') || r.aaddr2) Address,"
Sql = Sql & " substr(decode(trim(r.vasst1),null, 'N/A','000/000-0000?','N/A','000/000-0000','N/A', r.vasst1),1,12) Fax, "
Sql = Sql & " substr(decode(trim(r.aphone),null, 'N/A','000/000-0000?','N/A', r.aphone),1,12)Phone "
Sql = Sql & "FROM VENDOR V,VENDADDR R, PLANHOLD P "
Sql = Sql & "WHERE V.VENDOR = R.VENDOR AND P.VENDOR = R.VENDOR "
Sql = Sql & " AND (P.DATEPUR >= TO_DATE('1999-01-01','YYYY-MM-DD')) "
Sql = Sql & "AND P.DATEPUR In ( select Max(P.DATEPUR) from PLANHOLD P where P.vendor = R.VENDOR) "
Sql = Sql & " AND V.VOBSOLET = 'N' "

If Not (Trim(txtVendorName.Text.Length) = 0) Then
Sql = Sql & " AND (Upper(V.VNAMEL) LIKE '%' || Trim(UpperVendorName))|| '%') "
SqlDataSource1.SelectParameters.Add(":VendorName", txtVendorName.Text)
End If
If (strState.Length > 0 And lstState.SelectedIndex <> 0) Then
strState = Left(strState, strState.Length - 1)
strState = "(" & strState & ")"
Sql = Sql & "AND R.ASTATE IN " & strState
End If
If (drPur.SelectedIndex <> 0 And drPurM.SelectedIndex <> 0) Then
Sql = Sql & " AND to_number(to_char(p.datepur, 'YYYY')) between " & "'" & MinPDate & "'" & " AND " & "'" & MaxMDate & "'"
End If

Sql = Sql & "GROUP BY V.VENDOR, R.ADDRNUM,V.VNAMEL,R.AADDR1,R.AADDR2,R.ACITY,R.ASTATE, R.AZIPCODE, R.APHONE, R.VASST1, P.DATEPUR "

SqlDataSource1.SelectCommand = Sql
Response.Write("Sql " & "<br/>" & Sql & "<hr/>")
Response.Write("value of txtVendorName is = " & "<br/>" & txtVendorName.Text & "<br/>")


End Sub


------aspx.page
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" >
</asp:SqlDataSource>

 

[CassidyHunt]

First what is the exception? Second your going through a lot of trouble to make this query and have a mixture of methods to put the variables in it. I do this to help me with my SQL Queries and it might help you:

create a file called VendorQuery.sql and save it in the same directory as your application with this text in it.

SELECT V.VENDOR "Vendor Id",
       R.ADDRNUM,
       V.VNAMEL "Vendor Name",
       R.AADDR1,
       R.ACITY,
       R.VASST1,
       R.ASTATE State,
       R.AZIPCODE,
       to_char(Max(P.DATEPUR), 'YYYY/DD/MM') "Plan Purchased Date",
       TRIM(r.aaddr1 || decode(trim(r.aaddr2), null, '', ' - ') || r.aaddr2) Address,
       substr(decode(trim(r.vasst1),
                     null,
                     'N/A',
                     '000/000-0000?',
                     'N/A',
                     '000/000-0000',
                     'N/A',
                     r.vasst1),
              1,
              12) Fax,
       substr(decode(trim(r.aphone),
                     null,
                     'N/A',
                     '000/000-0000?',
                     'N/A',
                     r.aphone),
              1,
              12) Phone
  FROM VENDOR V, VENDADDR R, PLANHOLD P
 WHERE V.VENDOR = R.VENDOR
   AND P.VENDOR = R.VENDOR
   AND (P.DATEPUR >= TO_DATE('1999-01-01', 'YYYY-MM-DD'))
   AND P.DATEPUR In
       (select Max(P.DATEPUR) from PLANHOLD P where P.vendor = R.VENDOR)
   AND V.VOBSOLET = 'N'
   AND (Upper(V.VNAMEL) LIKE '%' || Trim(Upper(:VendorName)) || '%')
   AND R.ASTATE IN (:state)
   AND to_number(to_char(p.datepur, 'YYYY')) between to_date(:mindate,'MM/DD/YYYY') AND
       to_date(:maxdate,'MM/DD/YYYY')
 GROUP BY V.VENDOR,
          R.ADDRNUM,
          V.VNAMEL,
          R.AADDR1,
          R.AADDR2,
          R.ACITY,
          R.ASTATE,
          R.AZIPCODE,
          R.APHONE,
          R.VASST1,
          P.DATEPUR

Then in your code behind set a reference to System.Data.OracleClient and put this code in your code behind:

Private Sub VendorSearch()
        Dim cn As New OracleConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)
        Dim sql As New System.IO.StreamReader(Server.MapPath("vendorquery.sql"))
        Dim cm As New OracleCommand(sql.ReadToEnd.ToString, cn)
        Dim da As New OracleDataAdapter(cm)
        Dim dt As New DataTable
        Try
            If String.IsNullOrEmpty(drPur.SelectedValue.ToString) = False And String.IsNullOrEmpty(drPurM.SelectedValue.ToString) = False Then
                'Use Selected Values
                cm.Parameters.Add(New OracleParameter(":mindate", drPur.SelectedValue))
                cm.Parameters.Add(New OracleParameter(":maxdate", drPurM.SelectedValue))
            Else
                'Use Default values to avoid error
                cm.Parameters.Add(New OracleParameter(":mindate", "1/1/2006"))
                cm.Parameters.Add(New OracleParameter(":maxdate", Date.Now.ToString))
            End If

            Dim strState As New StringBuilder

            For Each liOne As ListItem In lstState.Items
                'Check for selection made
                If liOne.Selected Then
                    strState.Append(liOne.Value)
                    strState.Append(",")
                End If
            Next

            If String.IsNullOrEmpty(strState.ToString) Then
                'No selection fill default
                For Each liOne As ListItem In lstState.Items
                    strState.Append(liOne.Value)
                    strState.Append(",")
                Next
            End If

            If Not String.IsNullOrEmpty(strState.ToString) Then
                'Redundant check for empty string in case listbox had no items
                cm.Parameters.Add(New OracleParameter(":state", strState.ToString.Substring(0, strState.ToString.Length - 1)))
            Else
                'Just in case put a last ditch effort to not through error
                cm.Parameters.Add(New OracleParameter(":state", "WY"))
            End If

            If Not String.IsNullOrEmpty(txtVendorName.Text.ToString) Then
                'Make sure Oracle Wild cards in place
                If Not txtVendorName.Text.ToString.StartsWith("%") Then
                    txtVendorName.Text = "%" & txtVendorName.Text
                End If
                If Not txtVendorName.Text.ToString.EndsWith("%") Then
                    txtVendorName.Text &= "%"
                End If

                cm.Parameters.Add(New OracleParameter(":vendorname", txtVendorName.Text.ToString))
            Else
                'If nothing is available then use default
                cm.Parameters.Add(New OracleParameter(":vendorname", "%"))
            End If

            da.Fill(dt)

            gvSearch.DataSource = dt
            gvSearch.DataBind()
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            sql.Close()
            sql.Dispose()
            cn.Close()
            cn.Dispose()
            cm.Dispose()
            da.Dispose()
            dt.Dispose()
        End Try
    End Sub
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        VendorSearch()
    End Sub

Then in your ASPX page I had mine looking like this (not pretty):

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        
        &nbsp;
        <div>
            &nbsp;&nbsp;
            <asp:GridView ID="gvSearch" runat="server">
            </asp:GridView>
            <asp:DropDownList ID="drPur" runat="server">
                <asp:ListItem Selected="True">1/1/2005</asp:ListItem>
                <asp:ListItem>10/31/2006</asp:ListItem>
    </asp:DropDownList>
    <asp:DropDownList ID="drPurM" runat="server">
        <asp:ListItem Selected="True">10/31/2006</asp:ListItem>
        <asp:ListItem>11/30/2006</asp:ListItem>
    </asp:DropDownList>
    <asp:TextBox ID="txtVendorName" runat="server"></asp:TextBox>
    <asp:ListBox ID="lstState" runat="server">
        <asp:ListItem Value="WY">Wyoming</asp:ListItem>
        <asp:ListItem Value="CO">Colorado</asp:ListItem>
    </asp:ListBox>&nbsp;
    <asp:Button ID="Button1" runat="server" Text="Button" />
        </div>
    </form>    
</body>
</html>

Only other thing you need to check is in your web.config file that you have the proper connection string set and I believe you do by your previous code.

Let me know if this makes more sense to you.

Cassidy

 

[kebele]

Thank you Cassidy for taking your time to help me out.I know this can be done differently but I wanted to use the SqlDataSource property, paging and sorting that is why i choose do it a little differently. I just add a few extra lines of code and it seems to work ok and now whenever there is no vendorname enterd in the text box, the default value will be '%' that seems to take care of my problem. I also looked at your code and I found it very helpful in my future project. thank for the help and your time.

If Not String.IsNullOrEmpty(txtVendorName.Text) Then
'SqlDataSource1.SelectCommand &= " AND Upper(V.VNAMEL) LIKE :VendorName "
SqlDataSource1.SelectCommand &= " AND (Upper(V.VNAMEL) LIKE '%' || Trim(UpperVendorName))|| '%') "
SqlDataSource1.SelectParameters.Add(":VendorName", txtVendorName.Text)
Else
SqlDataSource1.SelectCommand &= " AND (Upper(V.VNAMEL) LIKE :VendorName) "
SqlDataSource1.SelectParameters.Add(":VendorName", "%")
End If