Solved

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

Posted on 2010-09-13
9
718 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now