Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DataAdapter and Datatable - ThnX !

Posted on 2004-04-21
12
Medium Priority
?
923 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 600 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 1400 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

963 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