VB-Tips

Report Viewer control and dataset

Saturday, July 17, 2010

1.- I created a Sql Server 2005 database in the App_Code of my Web App. In it, I build two tables named ‘Encabs´ (‘Headers’ in

English I suppose) and ‘ Detalle’

The ‘Encabs’ table has colums that will be used in text boxes of the Header and the Bottom of the Report, like :

- Folio, date, user, total, sending warehouse, receiving warehouse, Total amount expressed in alphabetical letters etc..

The ‘Detalle’ table has colums that will be included in a table control (of the Report Items Toolbar) that will show the details of the

Report, like Article Code, Description, Unit Cost, Series numbers, quantity and (I don´t know how to say in english :

Pedimento Aduanal)

. These will be used just to create a xsd schema. Really they won´t be used directly in the program. It´s just that when you create

the xsd file, it´s mandatory (I think) to use them for configuration purposes. In fact, the program will use a couple of tables

created at runtime individualized with the 3 first characters of the User code that connected to the Web App. In this way I solved the

problema that could occur if two or more users use the Report at the same time. (Remember ?). For example : ‘Encabs_ken’ or

‘Encabs_mig’ and “Detalle_ken” or “Detalle_usr”

2.- Then I created the xsd file ( I named it ‘Traspasos.xsd’. My Web App is about traspassing merchandise between warehouses

In the company. Some of them very far from Mexico City, like Los Cabos B. Calif.). I´m pretty sure you know how to do it.

3- You create the Report Definition Fle (.rdlc) . I´m using a Report Viewer in Local Mode. We don´t need a huge level of impression.

4.- Using code, I fill the two mentioned tables with the data I need. (At Runtime)

5.- Using Code also, in the Web page that contains the Report Viewer Control, I managed to replace the data sources for it.

I saw the code in a Web Portal but sadly, I don´t remember the URL, so I send you my own code, which I developed upon what

I saw in that Portal. (In fact, the code was in C#)

Imports Microsoft.Reporting.WebForms

Imports System.Data

Imports System.Data.SqlClient

Partial Class Reporte

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim strXsd As String, strXsDet As String

strXsd = CType(Session("strxsd"), String)

strXsDet = CType(Session("strxsdet"), String)

RV1.ProcessingMode = ProcessingMode.Local

Dim LocalReport = RV1.LocalReport

LocalReport.reportPath = "C:\WebSite_Traspasos\Report.rdlc"

Dim Encabs_Set As New DataSet

Dim Detalle_Set As New DataSet

Dim Cn As New SqlConnection

Cn.ConnectionString = "Data Source=MALTAMIRANO\SQLEXPRESS;Initial Catalog=C:\WEBSITE_TRASPASOS\APP_DATA\TRASP_WORK.MDF;Integrated Security=True"

Cn.Open()

Dim Comando As New SqlCommand("SELECT * FROM " + strXsd, Cn)

Dim Adapter As New SqlDataAdapter

Adapter.SelectCommand = Comando

Adapter.Fill(Encabs_Set, strXsd)

Dim ds As New ReportDataSource

ds.Name = "Traspasos_Encabs"

ds.Value = Encabs_Set.Tables(strXsd)

LocalReport.datasources.add(ds)

Dim Comanda As New SqlCommand("SELECT * FROM " + strXsDet, Cn)

Dim Adaptador As New SqlDataAdapter

Adaptador.SelectCommand = Comanda

Adaptador.Fill(Detalle_Set, strXsDet)

Dim det As New ReportDataSource

det.Name = "Traspasos_Detalle"

det.Value = Detalle_Set.Tables(strXsDet)

LocalReport.datasources.add(det)

Cn.Close()

End Sub

Explanations :

StrXsd is the variable which holds the name of the table with the information for the ‘Encabs’ table

StrXdet has the infomation for the ‘Detalle’ table.

RV1 is the Instance of the Report Viewer Control. In the next lines of code, the program creates a New couple of Data Sets and

Somehow manages to set the replace the original datasources of the Report for the newly created. I´m very aware I don´t need to

Explain anything to you. Besides, I did almost identcally to what I saw. Someday I´ll analize it deeper.

I think the code :

Dim det As New ReportDataSource

det.Name = "Traspasos_Detalle" ‘ You can see these names in the xsd schema

det.Value = Detalle_Set.Tables(strXsDet)

LocalReport.datasources.add(det)

Is what really makes the miracle.

Submitted by Miguel Altamirano

Load CSV into DataTable

Saturday, July 17, 2010

If you write a file called a Schema.ini in the same folder as the CSV file you can load it straight into a datatable with the datatypes and headers you want. Here is the documentation on Schema.ini is here http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Here is some sample code to do it:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strCSVFile = "c:\temp\data.csv"
Dim dtReturnData As New DataTable
If CreateSchema(strCSVFile) Then
            CSV2DataTable(strCSVFile, dtReturnData, "SELECT *", "", "", True)
End If
End Sub
Public Function CSV2DataTable(ByVal strFilename As String, ByRef dtCSVData As DataTable, ByVal strSelectList As String, _
ByVal strWhere As String, ByVal strGroupBy As String, ByVal blnHeader As Boolean) As Boolean
If Not System.IO.File.Exists(strFilename) Then
Return False
End If
Dim strFullPath As String = System.IO.Path.GetFullPath(strFilename)
Dim strFile As String = System.IO.Path.GetFileName(strFilename)
Dim strDir As String = System.IO.Path.GetDirectoryName(strFilename)
Dim strConnection As String
If blnHeader = True Then
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDir & "\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""
Else
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDir & "\;" & _
"Extended Properties=""text;HDR=No;FMT=Delimited(,)"""
End If
Dim conCSV As OleDb.OleDbConnection
        conCSV = New OleDb.OleDbConnection(strConnection)
        conCSV.Open()
Dim strSQL As String
        strSQL = strSelectList & vbCrLf & "FROM [" & strFile & "]"
If strWhere.Length > 0 Then
            strSQL = strSQL & vbCrLf & strWhere
End If
If strGroupBy.Length > 0 Then
            strSQL = strSQL & vbCrLf & strGroupBy
End If
Dim cmdCSV As New OleDb.OleDbCommand(strSQL, conCSV)
Try
Dim daCSV As New OleDb.OleDbDataAdapter(cmdCSV)
            daCSV.Fill(dtCSVData)
Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Importing Data", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
            conCSV.Close()
            conCSV.Dispose()
End Try
Return dtCSVData.Rows.Count > 0
End Function

Private Function CreateSchema(ByVal strFileName As String) As Boolean
Dim ascii As System.Text.Encoding = System.Text.Encoding.ASCII
Dim swSchema As System.IO.StreamWriter = Nothing
Dim blnReturn As Boolean
Dim strSchemaPath As String = System.IO.Path.GetFileName(strFileName)
Try
            strSchemaPath = System.IO.Path.GetDirectoryName(strFileName) & "\Schema.ini"
            swSchema = My.Computer.FileSystem.OpenTextFileWriter(strSchemaPath, False, ascii)
Dim strFile As String = System.IO.Path.GetFileName(strFileName)
            swSchema.WriteLine("[" & strFileName & "]")
            swSchema.WriteLine("ColNameHeader=False")
            swSchema.WriteLine("Format=Delimited(,)")
            swSchema.WriteLine("Col1=Value1 Text")
            swSchema.WriteLine("Col2=Value2 Text")
            swSchema.WriteLine("Col3=SomeDate Date")
            swSchema.WriteLine("Col4=SomePoint1 Integer")
            swSchema.WriteLine("Col5=SomePoint2 Integer")
'Continue for all fields
            blnReturn = True
Catch ex As Exception
            blnReturn = False
Finally
If swSchema IsNot Nothing Then
                swSchema.Close()
End If
End Try
Return blnReturn
End Function

Submitted by Lee D. Johnson

test

Sunday, January 17, 2010

test

Making a REST service with VB and WCF

Sunday, January 17, 2010

REST which stands for Representational State Transfer is a way of sending data over the Internet without an additional message layer. Standard web services use soap for there message header. In this example we will create a service that uses the northwind database to send a list of the products for a category.

Lets start by create a new VB web application. In that web application add a Ado.Net Entities data model. In that class add the northwind database's product class.

Now add a service named Service1 to the web application. Lets start by modify the web.config for the service to support rest. First remove the ServiceBehavior section and add a endpointBehaviors section for webHttp. In the endpoint we have to change the binding to webHttpBinding and the behaviorConfiguration to the webBehavior we created.

    <system.serviceModel>
        <behaviors>
      <endpointBehaviors>
        <behavior name="webBehavior">
          <webHttp/>
        </behavior>
      </endpointBehaviors>
        </behaviors>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="True"></serviceHostingEnvironment>
        <services>
            <service name="RestTest.Service1">
                <endpoint address="" behaviorConfiguration="webBehavior" binding="webHttpBinding" bindingConfiguration="" contract="RestTest.IService1">
                </endpoint>
            </service>
        </services>
    </system.serviceModel>

When we added the service we got 2 items the wcf service and an interface for the service. Before we go any further we need to add a reference to system.servicebehavior.web

In the Interface we need to define the how the categoryId is passed to the service. In this example it expects product/categoryID in the url for the service

Imports System.ServiceModel
Imports System.ServiceModel.Web

<ServiceContract()> _
Public Interface IService1

    <OperationContract()> _
    <WebGet(UriTemplate:="Product/{categoryID}", ResponseFormat:=WebMessageFormat.Xml, BodyStyle:=WebMessageBodyStyle.Bare)> _
    Function GetProducts(ByVal categoryId As String) As List(Of Products)

End Interface

In the service we need to set the AspNetCompatibilityMode and write some code to return the products

Imports System.ServiceModel.Activation

<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class Service1
Implements IService1

    Public Function GetProducts(ByVal categoryId As String) As System.Collections.Generic.List(Of Products) Implements IService1.GetProducts
        Dim dc As New NorthwindEntities
        Dim q = From p In dc.Products Select p Where p.CategoryID = CInt(categoryId)
        Return q.ToList
    End Function
End Class

To call the service you would use a url like http://localhost:2050/Service1.svc/Product/7

you will get an xml file like this returned

Silverlight 3 Could Not download the silverlight Application

Wednesday, July 15, 2009

I created a simple Silverlight 3 app.

When I run it I get this error

Line: 56
Error: Unhandled Error in Silverlight Application
Code: 2104
Category: InitializeError
Message: Could not download the Silverlight application. Check web server settings

Well if you look in the ClientBin folder you will see it is empty so the xap file is not available to be used

To fix this right click on the web application and select Build Order. On the Dependencies tab make sure the Checkbox next to the Silverlight app is checked.