• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 926
  • Last Modified:

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 !
0
allanau20
Asked:
allanau20
  • 6
  • 3
  • 3
2 Solutions
 
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
 
ruffoneCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
mmarinovCommented:
have you tried this :
dataAdapter = sqlclass.CreateSqlDataAdapter(sqlString)
?

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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now