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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2588
  • Last Modified:

SQLDataAdapter fill DataSet; include columns with null values

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
Cosine_Consultants
Asked:
Cosine_Consultants
  • 3
  • 3
1 Solution
 
RonaldBiemansCommented:
Never heard that one before, Do you by any change use a typed dataset
0
 
ahmadfuwadCommented:
'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
 
Cosine_ConsultantsAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RonaldBiemansCommented:
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
 
Cosine_ConsultantsAuthor Commented:
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
 
RonaldBiemansCommented:
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
 
Cosine_ConsultantsAuthor Commented:
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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