Solved

DataAdapter and Datatable - ThnX !

Posted on 2004-04-21
12
914 Views
Last Modified: 2012-06-21
I'm not sure how to do this, and at lost trying to figure it out.

Plz help.

I want to be able to do this:

    objConn = CreateConnStr() 'returns sqlconnection

    dataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSql, objConn)
    dataSet = New DataSet

    If Page.IsPostBack() Then
      dataAdapter.Fill(dataSet)
      lblRecordCount.Text = CStr(dataSet.Tables(0).Rows.Count)
      dataSet = Nothing
      dataSet = New DataSet
    End If
      dataAdapter.Fill(dataSet, 1, 10, "TABLE01")
      datalist01.DataSource = dataSet.Tables("TABLE01").DefaultView

    datalist01.DataBind()

However, I have a sqlclass that returns a datatable.

When I instaniate dataadapter, I pass the connectionstring and sqlconnection.

(I don't want to re-create the sqlconnection).

Is there a way I can take advantage of my sqlclass that returns the datatable?

Again, thank you for all posts !
0
Comment
Question by:allanau20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 10883605
you mean that you have a method that return the same datatable as the tables(0) in the dataset filled from the dataadapter, correct ?
B..M
0
 
LVL 5

Author Comment

by:allanau20
ID: 10883761
That's correct, mmarinov.

I'm just thinking, is there another way around instatiating like this:
 
  dataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSql, objConn)

Since I have the datatable.

Any ideas mmarinov?



0
 
LVL 4

Assisted Solution

by:ruffone
ruffone earned 150 total points
ID: 10884737
If u want to reuse a class then instantiate the class from inside your event and then just use it's properties or methods. This is really kool to because on your aspx/ascx pages u don't have to be connecting to DB and deal with Adaptors and command objects and connections. At that point u only deal with your data.

    Public Class MyClass1

        Dim objConn = CreateConnStr() 'returns sqlconnection

        Public Function MyFunction() As DataTable
           
            Dim dataAdapter = New System.Data.SqlClient.SqlDataAdapter(strSql, objConn)
            Dim dataSet = New DataSet
            Dim ret As DataTable


            dataAdapter.Fill(dataSet)
            lblRecordCount.Text = CStr(dataSet.Tables(0).Rows.Count)
            dataSet = Nothing
            dataSet = New DataSet

            dataAdapter.Fill(dataSet, 1, 10, "TABLE01")
            ret = dataSet.Tables("TABLE01") '.DefaultView

            Return ret

        End Function

    End Class

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Page.IsPostBack() Then
            Dim mClass As New MyClass1
            datalist01.DataSource = mClass.MyFunction
            datalist01.DataBind
        End If

    End Sub


Protected Sub BindData()
        Dim dp As New MyClass
        Dim dr As SqlDataReader

        dr = dpAlbs.GetDrDetails()

       While dr.Read()
           Textbox1.Text = dr.GetInt32(0)
       End While
End Sub

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 5

Author Comment

by:allanau20
ID: 10884790
Thanks ruffone for your comments.

But right now, I cannot change our sqlclass b/c it's being used by other developers.

I just need to find away around it, then maybe suggest a change to the group.

Our sqlclass doesn't have a method that returns a dataAdapter.

I wish it did.

I need the dataAdapter is b/c of the Overloads for the Fill method.

Utimately, I need it to create a paging datalist.

Any ideas?


0
 
LVL 4

Expert Comment

by:ruffone
ID: 10884915
rewrite your classes with methods and properties and then migrate to the new class. This way u have something that works to present to the group. The rest of the group could migrate to it at a later date
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 10886088
you can use

        Dim dtSQLClass As DataTable = sqlclass.Copy()
        Dim dtRezult As DataTable = dtSQLClass.Clone()

        If Page.IsPostBack() Then
            lblRecordCount.Text = dtSQLClass.Rows.Count      
        End If
        Dim rowIndex As Integer
        For rowIndex = 0 To 9
            dtRezult.Rows.Add(dtSQLClass.Rows(rowIndex).ItemArray)
        Next
      datalist01.DataSource = dtRezult

    datalist01.DataBind()
you can send parameters so you can paging - to do this change the start and end value for the cicle

B..M
0
 
LVL 5

Author Comment

by:allanau20
ID: 10890903
Thanks ruffone, I'm thinking about creating another method to return a dataAdapter.
But that's sometime later.

Thanks mmarinov, the code I have above works for paging nicely.
How it works is that it uses the Overload Fill method from the DataAdapter.

I need to find away from a datatable/dataview to the DataAdapter:

   dataAdapter.Fill(dataSet, 1, 10, "TABLE01")
   ret = dataSet.Tables("TABLE01") '.DefaultView

Maybe, what I'm asking is not possible?

any ideas?  Thanks again.
0
 
LVL 5

Author Comment

by:allanau20
ID: 10893464
Hey Experts,

If I have a method like this in my sqlclass:

    Public Function CreateSqlDataAdapter(sqlString As String) as SqlClient.SqlDataAdapter
        Return New SqlClient.SqlDataAdapter(sqlString,sqlConnection)
    End Function

In my code-behind, how do I point to the DataAdapter being returned?

I figured it would be something like this:

    dataAdapter = New sqlclass.CreateSqlDataAdapter(sqlString)

But the above statement is wrong.

Does anyone know how to reference it?

Thanks again !

0
 
LVL 28

Accepted Solution

by:
mmarinov earned 350 total points
ID: 10893539
have you tried this :
dataAdapter = sqlclass.CreateSqlDataAdapter(sqlString)
?

B..M
0
 
LVL 5

Author Comment

by:allanau20
ID: 10893764
AHH mmarinov.

Funny thing is that VS .NET intelli-sense has the syntax underlined in blue.
The tooltip says that: "Type 'sqlclass.CreateSqlDataAdapter' is not defined"

I'll try it.
0
 
LVL 4

Expert Comment

by:ruffone
ID: 10894632
Are u doing all this inside of the same Class. "sqlcalss". What does this give you, if u are in sqlclass

dataAdapter = CreateSqlDataAdapter(sqlString)

 
0
 
LVL 5

Author Comment

by:allanau20
ID: 10895078
I'm not doing all this inside of the same class.
I'm using my sql class from my aspx page (code-behind).

You're right mmarinov. Taking out the 'New' works.

I think I figured out why it didn't work.

This goes back to my understanding of C++ (w/pointers).

The 'New' returns the address of the data type.

What I needed was just for it to point to a datatype, that's why I don't need the 'New'.

If I wanted to use the 'New' then I would do this:

 dataAdapater = New system.Data.SqlClient.SqlDataAdapter(sqlclass.CreateSqlDataAdapter(sqlString))

I hope I'm correct about this.

Anyways, plz let me know if I'm wrong.

Thanks ruffone and mmarinov for your help !

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question