SqlDataSource Control and Hidded Field

[runmd]

I am using a SqlDataSource control to pull data for a GridView control. I would like to use a value that is stored in a Hidden Field as part of the where clause. I get an Oracle invalid number error.

The value of the Hidden Field is set on Page_Load and looks something like this:

2335 OR B.COUNTY_ID = 2336)

In the code below, it is placed in the :COUNTY_ID property which is linked to the hidden field.

<asp:HiddenField ID="CountySQL" runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>"
        SelectCommand="SELECT A.TRIBAL_ID, A.COUNTY_ID, A.STATE_ABBR, B.COUNTY_NAME, B.STATE_NAME
                       FROM TRIBE_COUNTY A, STATE_COUNTY B
                       WHERE A.COUNTY_ID = B.COUNTY_ID AND
                             (B.COUNTY_ID = :COUNTY_ID
                       ORDER BY B.COUNTY_NAME">
        <SelectParameters>
            <asp:ControlParameter ControlID="CountySQL" Name="COUNTY_ID" 
                PropertyName="Value" />
        </SelectParameters>
</asp:SqlDataSource>

So, is it possible to tie the hidden field value to the SelectCommand parameter of a SqlDataSource so that it pulls the data? I also thought it might be best to build custom SQL and populate the GridView. I have very little experience with the custom route since I am still new to ASP.NET.

 

[jbenson001]

First, a SqlDataSource control is meant to be used with SQL Servers, not Oracle.

Second, and most importantly, don't use the datasource controls at all. On the surface they look good, but you will learn quickly that they are not good once you need to do something more complicated, and they are not debuggable. Write the data access code yourself. There are many examples out there. You can post here if you need help

 

[runmd]

I'm using SqlDataSource for a drop down and a list box on a couple of my pages and it does connect to oracle just fine. I'm using a control value to pull in part of the where clause.

I definitely would like to write custom queries since it would give me better control and also make it debuggable. I did find this and tailored it a bit for my needs but I think it's written in C#. I am also using the VS 2008 help menu for SqlDataAdaptor help but it only addresses SQLServer and not Oracle in it's text. Quite annoying for Oracle users.

Am I getting anywhere with this code?

String selectCommand = "SELECT A.TRIBAL_ID, A.COUNTY_ID, A.STATE_ABBR, B.COUNTY_NAME, B.STATE_NAME FROM TRIBE_COUNTY A, STATE_COUNTY B WHERE A.COUNTY_ID = B.COUNTY_ID AND (B.COUNTY_ID = " + CountySQL.Value
SqlDataAdapter ad = New SqlDataAdapter(selectCommand, SqlDataSource1.ConnectionString)
DataTable dt = New DataTable()
ad.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()

As you can see, I need to set CountySQL.Value to fulfill the remaining part of the query. I am setting it prior to the SelectCommand as part of the Page_Load sub. The value looks like this:

934 OR B.COUNTY_ID = 935)

 

[runmd]

I also have the <connectionStrings> stored in the web.config file. I should be able to use this in my code instead of creating one.

<connectionStrings>
  <add name="OracleConnectionString" connectionString="Data Source=D77ADEV;User ID=TDAT;Password=TDAT;Unicode=True"
   providerName="System.Data.OracleClient" />
 </connectionStrings>

 

[runmd]

I've written this so far.

 Dim strConnection As String = "Data Source=D77ADEV;User ID=TDAT;Password=TDAT"
        Dim objConnection As New SqlConnection(strConnection)

        Dim strSQL As String = "SELECT * " & _
                               "FROM STATE;"
        Dim objCommand As New SqlCommand(strSQL, objConnection)

        objConnection.Open()

I'm getting this error. This error references SQLServer but I am using an Oracle database.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

So, how would I use my connection string that is stored in the web.config file?

 

[jbenson001]

Google is your friend. I found the answer to your question in about 10 seconds:
SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("YOUR CONNECTION STRING NAME HERE...").ConnectionString

 

[runmd]

I use Google a ton but in this case I am not sure what to even look for. So I have this now and it gives me an error, 'Keyword not supported: unicode.' on the third line. It has something to do with the ConnectionString value. The connection string in the web.config file is where the value Unicode=True is stored. I take this out and I get the SQL Server error that I mentioned before. Seems to me that I have to go about this a different way since this seems all SQL Server speak instead of Oraclease.

Dim ConnectionString As String = GetConnectionString()
        Dim selectCommand As String = "SELECT * " & _
                                      "FROM STATE;"
        Dim adaptor As SqlDataAdapter = New SqlDataAdapter(selectCommand, ConnectionString)
        Dim dataTable As DataTable = New DataTable()
        'adaptor.Fill(dataTable)
        'GridView1.DataSource = dataTable
        'GridView1.DataBind()

 Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file, using the
        ' System.Configuration.ConfigurationManager.ConnectionStrings property
        Return System.Configuration.ConfigurationManager.ConnectionStrings("OracleConnectionString").ConnectionString

    End Function

 

[jbenson001]

Like I said before, the SQL DataSource controls are ment to be used with SQL Servers, not Oracle. You would need to use the Object datasource. But like I said, don't use those controls at all.

 

[runmd]

Ive installed the ODAC tools for Visual Studio and added the System.Data.OracleClient to my IDE. I started to work on a query to add records to a gridview control. I think the query is working but what is bound to the gridview is a row of attributes describing the parameters of one of the properties or methods in my code.

Dim strConnString As String = GetConnectionString()
        Dim objConn As New System.Data.OracleClient.OracleConnection(strConnString)

        Dim strSQL As String = "SELECT * FROM TRIBE_COUNTY_VW " & _
                               " WHERE (COUNTY_ID = " & strParam

        objConn.Open()
        Dim adapter As System.Data.OracleClient.OracleDataAdapter
        adapter = New System.Data.OracleClient.OracleDataAdapter(strSQL, objConn)
        Dim dataSet As New DataSet
        adapter.Fill(dataSet)
        GridView1.DataSource = dataSet.Tables
        GridView1.DataBind()
        objConn.Close()

The strParam could be set to, for example, "97)". It finishes the end of the where clause. So, does something stand out as an issue.

 

[runmd]

I've fixed the code in the previous post to fill the GridView with data from this query. I've put it in its own procedure passing it the connection string and part of the query parameter and call it from the Page_Load procedure. It works like a charm.

Dim queryString As String = "SELECT * FROM TRIBE_COUNTY_VW " & _
                                    " WHERE (COUNTY_ID = " & qryParam & _
                                    " ORDER BY COUNTY_NAME"

        Using connection As New OracleConnection(connectionString)
            Dim command As New OracleCommand(queryString, connection)
            connection.Open()
            Dim adapter As System.Data.OracleClient.OracleDataAdapter
            adapter = New System.Data.OracleClient.OracleDataAdapter(command)
            Dim dataSet As New DataSet
            adapter.Fill(dataSet)
            GridView1.DataSource = dataSet
            GridView1.DataBind()
            dataSet.Dispose()
            adapter.Dispose()
        End Using

I'm working on sorting and paging the GridView control which cannot be configured the same way as using a SQLDataSource since it uses two way data binding.