Solved

DataAdapter and Datatable - ThnX !

Posted on 2004-04-21
12
911 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now