Solved

How to get the data from datatable and put on a sqldasource ??

Posted on 2010-09-13
9
724 Views
Last Modified: 2012-05-10
Hi i have this Problem here, i have this object:

<asp:DropDownList ID="DDLEscolaridade" runat="server"
                                                     SkinID="ddlnormal" DataSourceID="ObjectDataSource1"
                                                     DataTextField="Escolaridade" DataValueField="ID">
                                                 </asp:DropDownList>

and i have this function that i use on a webservice. so i will be calling this function from the webservice asmx.

    <WebMethod()> _
    Public Function GlobalTable_Get_Escolaridade() As DataTable
        Dim Table As New DataTable()
        Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("GlobalNucleoConnectionString").ConnectionString)
            Using command As New SqlCommand("SP_Global_Get_Escolaridade", connection)
                command.CommandType = CommandType.StoredProcedure
                connection.Open()
                Dim adapt As New SqlDataAdapter(command)
                adapt.Fill(Table)
            End Using
        End Using
        Return Table
    End Function


how can i load this data inside this object. the datatable format is:

id     escolaridade
1      sldjflsdf
2       lsdkjfljf
3      sdklfskjdfl
0
Comment
Question by:rafaelrgl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 7

Accepted Solution

by:
whityum earned 375 total points
ID: 33667749
you could load it directly in code
<script runat=server>
DDLEscolaridade.DataSource = [WebServiceInstance].GlobalTable_Get_Escolaridade();
DDLEscolaridade.DataBind();
</script>
remove the semicolons for VB.  To use a datasource, you should use an objectdatasource.

http://explore.wordpress.com/2006/12/11/objectdatasource-gridview-and-net-web-services/
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 33668467
i got this error when i tried like you show above:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: Cannot serialize the DataTable. DataTable name is not set.
  at System.Data.DataTable.WriteXmlSchema(XmlWriter writer, Boolean writeHierarchy)
  at System.Data.DataTable.System.Xml.Serialization.IXmlSerializable.WriteXml(XmlWriter writer)
  at System.Xml.Serialization.XmlSerializationWriter.WriteSerializable(IXmlSerializable serializable, String name, String ns, Boolean isNullable, Boolean wrapped)
  at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write57_Item(Object[] p)
  at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer113.Serialize(Object objectToSerialize, XmlSerializationWriter writer)
  at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
  --- End of inner exception stack trace ---
  at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
  at System.Web.Services.Protocols.SoapServerProtocol.WriteReturns(Object[] returnValues, Stream outputStream)
  at System.Web.Services.Protocols.WebServiceHandler.WriteReturns(Object[] returnValues)
  at System.Web.Services.Protocols.WebServiceHandler.Invoke()
  --- End of inner exception stack trace ---
0
 
LVL 7

Assisted Solution

by:whityum
whityum earned 375 total points
ID: 33672468
can you post more of the code, I would need to see how you are referencing the web service.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Assisted Solution

by:ChristoferDutz
ChristoferDutz earned 125 total points
ID: 33672577
Seems that this problem is related to your DataTable not being assigned a name:
http://bytes.com/topic/net/answers/428472-serializing-datatable
0
 
LVL 7

Assisted Solution

by:whityum
whityum earned 375 total points
ID: 33672609
that would be a problem in the web service then wouldn't it?
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 33675440
component:

       <asp:DropDownList ID="DDLEscolaridade" runat="server" SkinID="ddlnormal" DataSourceID="ObjectDataSource1"  DataTextField="Escolaridade" DataValueField="ID">
       </asp:DropDownList>



the code page:

Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Imports System.Data

Partial Class _Default
    Inherits SecurityPage
    Dim GlobalNucleo As New GlobalNucleo_WebService.GlobalNucleo_WebService

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        DDLEscolaridade.DataSource = GlobalNucleo.GlobalTable_Get_Escolaridade()
        DDLEscolaridade.DataBind()
    End Sub
End Class


Here is on the webservice:

    <WebMethod()> _
    Public Function GlobalTable_Get_Escolaridade() As DataTable
        Dim Table As New DataTable()
        Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("GlobalNucleoConnectionString").ConnectionString)
            Using command As New SqlCommand("SP_Global_Get_Escolaridade", connection)
                command.CommandType = CommandType.StoredProcedure
                connection.Open()
                Dim adapt As New SqlDataAdapter(command)
                adapt.Fill(Table)
            End Using
        End Using
        Return Table
    End Function


here is the storeprocedure:

use [databasename]
GO
/****** Object:  StoredProcedure [dbo].[SP_Global_Get_Escolaridade]    Script Date: 09/14/2010 11:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Global_Get_Escolaridade]
AS
         SELECT * from TB_GLOBAL_ESCOLARIDADE
RETURN


here is the table structure:

id   escolaridade
1    highschool
2    lskjdf lsdjfls
3   sldjflskjdfljsldf

0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 33675455
btw, i add the webservice to the App_WebReferences
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 33675500
found the solution:

here is how:

added this line to the webservice function:

                Table.TableName = "Escolaridade"
0
 
LVL 1

Author Closing Comment

by:rafaelrgl
ID: 33675509
Thanks guys,
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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