Solved

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

Posted on 2010-09-13
9
723 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
Technology Partners: 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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