Cosine_Consultants
asked on
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.
Thanks,
Vangelis
Cosine Consultants Ltd.
Never heard that one before, Do you by any change use a typed dataset
'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=T est;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")
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=T
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")
ASKER
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.
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
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
ASKER
Here is my code. The SqlDataAdapterCompanyDetai ls is an object that created using the sqldataadapter wizard. The SqlDataAdapterCompanyDetai ls 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
SqlDataAdapterCompanyDetai ls.SelectC ommand = createCommand("CompanySele ct", pars)
' Create a new dataset
Dim ds As DataSet
ds = New DataSet
SqlDataAdapterCompanyDetai ls.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
<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("
' Assign the SQL command to the data adapter
SqlDataAdapterCompanyDetai
' Create a new dataset
Dim ds As DataSet
ds = New DataSet
SqlDataAdapterCompanyDetai
' 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.