DataAdapter and Datatable - ThnX !

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 !
LVL 5
allanau20Asked:
Who is Participating?
 
mmarinovConnect With a Mentor Commented:
have you tried this :
dataAdapter = sqlclass.CreateSqlDataAdapter(sqlString)
?

B..M
0
 
mmarinovCommented:
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
 
allanau20Author Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
ruffoneConnect With a Mentor Commented:
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
 
allanau20Author Commented:
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
 
ruffoneCommented:
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
 
mmarinovCommented:
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
 
allanau20Author Commented:
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
 
allanau20Author Commented:
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
 
allanau20Author Commented:
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
 
ruffoneCommented:
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
 
allanau20Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.