×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SqlDataSource Control and Hidded Field

SqlDataSource Control and Hidded Field

SqlDataSource Control and Hidded Field

(OP)
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:

CODE

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.

CODE

<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.

RE: SqlDataSource Control and Hidded Field

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

RE: SqlDataSource Control and Hidded Field

(OP)
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?

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:

CODE

934 OR B.COUNTY_ID = 935)

RE: SqlDataSource Control and Hidded Field

(OP)
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.

CODE

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

RE: SqlDataSource Control and Hidded Field

(OP)
I've written this so far.

CODE

 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.

CODE

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?
 

RE: SqlDataSource Control and Hidded Field

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

 

RE: SqlDataSource Control and Hidded Field

(OP)
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.  smile

CODE

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

RE: SqlDataSource Control and Hidded Field

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.

RE: SqlDataSource Control and Hidded Field

(OP)
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.

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.

RE: SqlDataSource Control and Hidded Field

(OP)
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.

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close