?
Solved

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

Posted on 2010-09-13
9
Medium Priority
?
725 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 750 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 750 total points
ID: 33672468
can you post more of the code, I would need to see how you are referencing the web service.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 20

Assisted Solution

by:ChristoferDutz
ChristoferDutz earned 250 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 750 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

770 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