Solved

SQLDataAdapter fill DataSet; include columns with null values

Posted on 2004-09-22
7
2,394 Views
Last Modified: 2008-02-20
I'm using SQLDataAdapter to fill a DataSet. The problem that I have is when I fill the DataSet from the SQLDataAdapter. The DataSet doesn't include the columns with null values. Is there any property of SQLDataAdapter that can solve the problem?

Thanks,
Vangelis
Cosine Consultants Ltd.
0
Comment
Question by:Cosine_Consultants
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120920
Never heard that one before, Do you by any change use a typed dataset
0
 
LVL 2

Expert Comment

by:ahmadfuwad
ID: 12121057
'fill your datagrid by using following code i will solve your problem
   
 Public cn As New SqlConnection
    Public cmd1 As New SqlCommand
    Public oda1 As New SqlDataAdapter
    Public ods As New DataSet

 cn.ConnectionString = "Server=eteam04;Database=Test;user id=sa;password=password"
        cn.Open()

        ods.Clear()
        cmd1.Connection = cn
        cmd1.CommandType = CommandType.Text
        cmd1.CommandText = "select * from Customer"
        oda1.SelectCommand = cmd1
        oda1.Fill(ods, "Customer")
        Me.DataGrid1.DataSource = ods.Tables("Customer")
0
 

Author Comment

by:Cosine_Consultants
ID: 12121087
I don't want to use a typed dataset since I have a lot of columns to add (app. 40) manually. My application has many tables with lots of columns, therefore I will definitely use the SQLDataAdapter which fills automatically the dataset. I'm looking for a property or something else that will allow the dataset to have columns with null values.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12121266
Hi Cosine_Consultants,

I didn't imply that you should use a typed dataset, I just asked if you used one , because a typed dataset sometimes has problems with columns containing Nulls.

As far as properties to set to allow columns that contain nulls there is no such property, the dataadapter doesn't care if they contain nulls it should load them just as any other, If it doesn't then there is something else wrong.
I've just tested it with a table that contained  10 empty columns and it loads them just fine.

could you post some code so we might see what's wrong
0
 

Author Comment

by:Cosine_Consultants
ID: 12121641
Here is my code. The SqlDataAdapterCompanyDetails is an object that created using the sqldataadapter wizard. The SqlDataAdapterCompanyDetails use SQL Server stored procedures for SELECT,INSERT,UPDATE functionality. The createCommand is a custom function that returns SqlCommand. If you need any other info please let me know. Thanks.


<WebMethod()> Public Function GetCompanyDetails(ByVal intCompanyId As Integer) As String

        ' Build the parameter to send to the createCommand function
        Dim pars As New ArrayList
        pars.Add(createParameter("@CompanyId", SqlDbType.Int, intCompanyId))

        ' Assign the SQL command to the data adapter
        SqlDataAdapterCompanyDetails.SelectCommand = createCommand("CompanySelect", pars)

        ' Create a new dataset
        Dim ds As DataSet
        ds = New DataSet

        SqlDataAdapterCompanyDetails.Fill(ds)
       
      ' Create a string writer to write the dataset into in XML format
        Dim sw As System.IO.StringWriter = New System.IO.StringWriter
        ' Write the dataset into the string writer as XML
        ds.WriteXml(sw)

        ' Return the XML string
        Return sw.ToString()

    End Function
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 500 total points
ID: 12121701
so after this

SqlDataAdapterCompanyDetails.Fill(ds)

when you do

msgbox (ds.tables(0).columns.count)

you don't get the amount of columns you expect ?

0
 

Author Comment

by:Cosine_Consultants
ID: 12130617
I have solved the problem, thanks for the help. I was passing the xml format of the Dataset instead of the Dataset object itself. Now I can see the xml schema info of the table so I have everything I need.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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