SQL Command - DataTextField for Drop Down List in Gridview

@theID int
AS SELECT
A.D_ID,
CONVERT(CHAR(5), A.D_Time, 114) as D_Time,
A.D_Cardiac_Rhythm,
b.CARD_Name,
A.D_Joules,
A.D_Drug,
D.L_Description,
A.D_Dose,
c.DOSE_NAME,
A.D_Notes
FROM DETAILS a
INNER JOIN CARDIAC_RHYTHM b
ON a.D_Cardiac_Rhythm = b.CARD_ID JOIN DOSE c
ON a.D_Dose = c.DOSE_ID JOIN LOOKUP_TEXT d
ON a.D_Drug = d.L_ID
WHERE
a.D_E_ID = @theID

ASP.NET Displaying XML in a XSLT

Quite simple this one, don't bother about using either c# or vb.net

Just use this line in the aspx code between the form tags....

asp:xml id="ID" runat="server" documentsource="tool.xml" transformsource="tool.xsl"/

IF statement in SQL Stored Procedure

A nifty little SQL code here, that checks if a value exists in a SQL table, and if it finds the value doesn't exist will insert the value....

First declare and set the variable
DECLARE @patientID VARCHAR(30)
SET @patientID = 'xxxxxxx'

next write the if statement to check if the value already exists in the table
if exists(Select * From episodes
Where patient_id=@patientID)

If it exists return and end the function
begin
return
end

Else insert the value into the table
else
begin
INSERT INTO episodes(patient_id)
values(@patientID)

End the statement

end
Go

And thats it, of course if you wish you can write this as a stored procedure. Good Luck.

Show Footer on Gridview binded from SQLDataSource

Today, is a little simple but very helpful piece of code in both c#and vb.net. This is a demonstration of how to show the footer and bind a blank row to a DataGrid that has been binded form the SQLDataSource.

Firstly, in the page load, enter the following code at the end of all other code.

In c#

int rowcount = GridView1.Rows.Count;
if (rowcount == 0)
{bindCount();
}

and in vb.net

Dim rowcount As Integer = GridView1.Rows.Count
If rowcount = 0 Then
bindCount()
End If

Then create the following void to create a false row and show the footer.


void bindCount()
{
GridView1.DataSourceID = null;
GridView1.DataSource = null;
DataTable dt = new DataTable();
string FirstC = "episode_id";
DataColumn dCol1 = new DataColumn(FirstC, typeof(System.String));
string SecondC = "code";
DataColumn dCol2 = new DataColumn(SecondC, typeof(System.String));
string ThirdC = "value";
DataColumn dCol3 = new DataColumn(ThirdC, typeof(System.String));
string FourthC = "description";
DataColumn dCol4 = new DataColumn(FourthC, typeof(System.String));
string FifthC = "theTimestamp";
DataColumn dCol5 = new DataColumn(FifthC, typeof(System.String));

dt.Columns.Add(dCol1);
dt.Columns.Add(dCol2);
dt.Columns.Add(dCol3);
dt.Columns.Add(dCol4);
dt.Columns.Add(dCol5);
for (int i = 0; i <= 0; i++) {
DataRow row1 = dt.NewRow();
row1[FirstC] = "";
row1[SecondC] = "";
row1[ThirdC] = "";
row1[FourthC] = "";
row1[FifthC] = "";
dt.Rows.Add(row1);
}
foreach (DataColumn col in dt.Columns) {
BoundField bField = new BoundField();
bField.DataField = col.ColumnName;
bField.HeaderText = col.ColumnName;
GridView1.Columns.Add(bField);
}
GridView1.DataSource = dt;
GridView1.Columns[6].Visible = false;
GridView1.Columns[7].Visible = false;
GridView1.Columns[8].Visible = false;
GridView1.Columns[9].Visible = false;
GridView1.Columns[10].Visible = false;
GridView1.DataBind();
}

and in vb.net

Private Sub bindCount()
GridView1.DataSourceID = Nothing
GridView1.DataSource = Nothing
Dim dt As New DataTable()

Dim FirstC As String = "episode_id"
Dim dCol1 As New DataColumn(FirstC, GetType(System.String))

Dim SecondC As String = "code"
Dim dCol2 As New DataColumn(SecondC, GetType(System.String))

Dim ThirdC As String = "value"
Dim dCol3 As New DataColumn(ThirdC, GetType(System.String))

Dim FourthC As String = "description"
Dim dCol4 As New DataColumn(FourthC, GetType(System.String))

Dim FifthC As String = "theTimestamp"
Dim dCol5 As New DataColumn(FifthC, GetType(System.String))

dt.Columns.Add(dCol1)
dt.Columns.Add(dCol2)
dt.Columns.Add(dCol3)
dt.Columns.Add(dCol4)
dt.Columns.Add(dCol5)

For i As Integer = 0 To 0
Dim row1 As DataRow = dt.NewRow()
row1(FirstC) = ""
row1(SecondC) = ""
row1(ThirdC) = ""
row1(FourthC) = ""
row1(FifthC) = ""
dt.Rows.Add(row1)
Next

For Each col As DataColumn In dt.Columns
Dim bField As New BoundField()
bField.DataField = col.ColumnName
bField.HeaderText = col.ColumnName
GridView1.Columns.Add(bField)
Next

GridView1.DataSource = dt
GridView1.Columns(6).Visible = False
GridView1.Columns(7).Visible = False
GridView1.Columns(8).Visible = False
GridView1.Columns(9).Visible = False
GridView1.Columns(10).Visible = False
GridView1.DataBind()

End Sub

This example has been produced on a datagrid that has 5 columns. Depending how many columns your datagrid has, increase or decrease the number of columns accordingly. This code need to take in to consideration any invisible columns you may have.

Checkbox Checked in Gridview

Just a little bit of code, which can be used in GridView to find out which row/id has been checked by a checkbox in a Gridview, this one might need some hashing about.


Sub chkStatus_OnCheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
' MsgBox("CHECKED!")
Dim checkbox As CheckBox = DirectCast(sender, CheckBox)
Dim row As GridViewRow = DirectCast(checkbox.NamingContainer, GridViewRow)
Dim UserId As Integer = CInt(GridView1.DataKeys(row.DataItemIndex).Value)
Dim UserInfo As String = ""
Dim Status As Boolean = checkbox.Checked
Dim strStatus As String = ""
UserInfo = " ("
UserInfo += GridView1.Rows(row.DataItemIndex).Cells(1).Text + " "
UserInfo += GridView1.Rows(row.DataItemIndex).Cells(4).Text
UserInfo += ")"
If Status Then
strStatus = "Approved"
Else
strStatus = "NOT Approved"
End If
' MsgBox("CheckBox1_CheckedChanged Fired for User Id = " + UserId.ToString + UserInfo + " and new status is " + strStatus + "

")
heldCodes.Text = heldCodes.Text + "," & UserId.ToString
Dim t1 As TextBox = DirectCast(sender, TextBox)
Dim t2 As String = GridView1.Rows(row.DataItemIndex).Cells(2).Text
Dim t3 As String = GridView1.Rows(row.DataItemIndex).Cells(3).Text
Dim t4 As String = GridView1.Rows(row.DataItemIndex).Cells(4).Text
'Line below works
'Dim t As TextBox = DirectCast(Me.GridView1.Rows(0).Cells(1).FindControl("lbl_quantity"), TextBox)
Dim c As CheckBox = DirectCast(Me.GridView1.Rows(0).Cells(1).FindControl("lbl_checkbox"), CheckBox)
'MsgBox("This is the TextBox text in the GridView: " & t.Text)

End Sub

XML - Reading from an XML Document.

Finally how to read from a XML Document, with this little script for a page.

use the following for c#

protected void Page_Load(object sender, EventArgs e)
{
XmlDocument xmlDoc = new XmlDocument();
DataSet mycountries = new DataSet();
mycountries.ReadXml(MapPath("text_xml.xml"));

rb.DataSource = mycountries;
rb.DataValueField = "value";
rb.DataTextField = "design";
rb.DataBind();

xmlDoc.Load(MapPath("text_xml.xml"));

XmlNodeList xmlnodelist = xmlDoc.DocumentElement.ChildNodes;
XmlNode xmlnode = xmlnodelist.Item(0);
int xmlcount = xmlnodelist.Count;

TextBox1.Text = xmlnode["design"].InnerText;
TextBox2.Text = xmlnode["value"].InnerText;

Body.Style.Add("background-color", xmlnode["backcolor"].InnerText);
XmlNode xmlnode2 = xmlnodelist.Item(1);
TextBox2.Text = xmlnode2["value"].InnerText;

}
protected void Button1_Click(object sender, EventArgs e)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(MapPath("text_xml.xml"));
XmlNodeList xmlnodelist2 = xmlDoc.DocumentElement.ChildNodes;
XmlNode xmlnode2 = xmlnodelist2.Item(1);
TextBox1.Text = xmlnode2["design"].InnerText;
Body.Style.Add("background-color", xmlnode2["backcolor"].InnerText);
Body.Style.Add("color", "white");

}


or in vb.net use:

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


Dim mycountries = New DataSet
mycountries.ReadXml(MapPath("text_xml.xml"))

rb.DataSource = mycountries
rb.DataValueField = "value"
rb.DataTextField = "design"
rb.DataBind()

xmlDoc.Load(MapPath("text_xml.xml"))

Dim xmlnodelist As XmlNodeList = xmldoc.DocumentElement.ChildNodes
Dim xmlnode As XmlNode = xmlnodelist.Item(0)
TextBox1.Text = xmlnode("design").InnerText
TextBox2.Text = xmlnode("value").InnerText

Body.Style.Add("background-color", xmlnode("backcolor").InnerText)
Dim xmlnode2 As XmlNode = xmlnodelist.Item(1)
TextBox2.Text = xmlnode2("value").InnerText

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim xmlDoc As New XmlDocument
xmlDoc.Load(MapPath("text_xml.xml"))
Dim xmlnodelist2 As XmlNodeList = xmlDoc.DocumentElement.ChildNodes
Dim xmlnode2 As XmlNode = XmlNodeList2.Item(1)
TextBox1.Text = xmlnode2("design").InnerText
Body.Style.Add("background-color", xmlnode2("backcolor").InnerText)
Body.Style.Add("color", "white")
End Sub

XML - Delete All Elements in XML Document

The penultamite one today in my tips is how to delete all elements in a XML Document

in c# use:

string filename = "categories.xml";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath(filename));
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList nodes = xmlDoc.SelectNodes("//Category");
foreach (XmlNode node in nodes)
{
node.ParentNode.RemoveChild(node);
}
xmlDoc.Save(Server.MapPath(filename));

and in vb.net use:

Dim filename As String = "categories.xml"
Dim xmlDoc As XmlDocument = New XmlDocument()
xmlDoc.Load(Server.MapPath(filename))

Dim root As XmlNode = xmlDoc.DocumentElement
Dim nodes As XmlNodeList = xmlDoc.SelectNodes("//Category")

For Each node As XmlNode In nodes
node.ParentNode.RemoveChild(node)
Next
xmlDoc.Save(Server.MapPath(filename))

XML - Delete an element by the ID

Next step is to delete an element by its ID:

in c# use

string filename = "categories.xml";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath(filename));
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList nodes = xmlDoc.SelectNodes("/CategoryList/Category[@ID='03']");
foreach (XmlNode node in nodes)
{
node.ParentNode.RemoveChild(node);
}
xmlDoc.Save(Server.MapPath(filename));

and in vb.net use:

Dim filename As String = "categories.xml"
Dim xmlDoc As XmlDocument = New XmlDocument()
xmlDoc.Load(Server.MapPath(filename))

Dim root As XmlNode = xmlDoc.DocumentElement
Dim nodes As XmlNodeList = xmlDoc.SelectNodes("/CategoryList/Category[@ID='03']")
For Each node As XmlNode In nodes
node.ParentNode.RemoveChild(node)
Next
xmlDoc.Save(Server.MapPath(filename))

XML - Finding a XML Element by the attribute ID

Straight as it sounds, its now time to demonstrate how to find an element by the element's attribute ID.

in c#

string filename = "categories.xml";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath(filename));

XmlNodeList xnList = xmlDoc.SelectNodes("/CategoryList/Category[@ID='02']");
foreach (XmlNode xn in xnList)
{
Console.WriteLine(xn.InnerText);
}

and in vb.net

Dim filename As String = "categories.xml"
Dim xmlDoc As XmlDocument = New XmlDocument()
xmlDoc.Load(Server.MapPath(filename))

Dim xnList As XmlNodeList = xmlDoc.SelectNodes("/CategoryList/Category[@ID='01']")


For Each xn As XmlNode In xnList
MsgBox(xn.InnerText)
Next

XML - Amending an Element in a XML Document

Now we can create and write to a XML Document, we can move onto demonstating how to amend an element in a document.

Again, first in c#

string filename = "categories.xml";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath(filename));

XmlNode node = xmlDoc.SelectSingleNode("/CategoryList/Category[@ID='03']/MainCategory");
//node.Attributes[0].Value = "03";

node.Name.ToString();
if (node.Name == "MainCategory")
{
node.InnerXml = "3";
}

xmlDoc.Save(Server.MapPath(filename));

and in vb.net

Dim filename As String = "categories.xml"
Dim xmlDoc As XmlDocument = New XmlDocument()
xmlDoc.Load(Server.MapPath(filename))

Dim node As XmlNode = xmlDoc.SelectSingleNode("/CategoryList/Category[@ID='03']/MainCategory")

node.Name.ToString()
If node.Name = "MainCategory" Then
node.InnerXml = "3"
End If

xmlDoc.Save(Server.MapPath(filename))

XML - Write to an XML Documnent

Next up is a quick bit of coding to show how to dynamically insert a new element to a XML Document.

In c# use:

string filename = "categories.xml";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath(filename));

XmlNode root = xmlDoc.DocumentElement;
XmlElement ListNode = xmlDoc.CreateElement("CategoryList");

XmlElement childNode = xmlDoc.CreateElement("MainCategory");

XmlElement parentNode = xmlDoc.CreateElement("Category");
parentNode.SetAttribute("ID", "04");
xmlDoc.DocumentElement.PrependChild(parentNode);
// Set attribute name and value!

XmlElement mainNode = xmlDoc.CreateElement("MainCategory");
XmlElement descNode = xmlDoc.CreateElement("Description");
XmlElement activeNode = xmlDoc.CreateElement("Active");

// retrieve the text
XmlText categoryText = xmlDoc.CreateTextNode(txt_maincategory.Text);
XmlText descText = xmlDoc.CreateTextNode(txt_description.Text);
XmlText activeText = xmlDoc.CreateTextNode(txt_active.Text);

// append the nodes to the parentNode without the value
parentNode.AppendChild(mainNode);
parentNode.AppendChild(descNode);
parentNode.AppendChild(activeNode);

// save the value of the fields into the nodes
mainNode.AppendChild(categoryText);
descNode.AppendChild(descText);
activeNode.AppendChild(activeText);

xmlDoc.Save(Server.MapPath("categories.xml"));

and in vb.net

Dim filename As String = "categories.xml"
Dim xmlDoc As XmlDocument = New XmlDocument()
xmlDoc.Load(Server.MapPath(filename))

Dim root As XmlNode = xmlDoc.DocumentElement
Dim ListNode As XmlElement = xmlDoc.CreateElement("CategoryList")

Dim childNode As XmlElement = xmlDoc.CreateElement("MainCategory")
Dim parentNode As XmlElement = xmlDoc.CreateElement("Category")
parentNode.SetAttribute("ID", "04")
xmlDoc.DocumentElement.PrependChild(parentNode)
' Set attribute name and value!

Dim mainNode As XmlElement = xmlDoc.CreateElement("MainCategory")
Dim descNode As XmlElement = xmlDoc.CreateElement("Description")
Dim activeNode As XmlElement = xmlDoc.CreateElement("Active")

' retrieve the text
Dim categoryText As XmlText = xmlDoc.CreateTextNode(txt_maincategory.Text)
Dim descText As XmlText = xmlDoc.CreateTextNode(txt_description.Text)
Dim activeText As XmlText = xmlDoc.CreateTextNode(txt_active.Text)

' append the nodes to the parentNode without the value
parentNode.AppendChild(mainNode)
parentNode.AppendChild(descNode)
parentNode.AppendChild(activeNode)

' save the value of the fields into the nodes
mainNode.AppendChild(categoryText)
descNode.AppendChild(descText)
activeNode.AppendChild(activeText)

xmlDoc.Save(Server.MapPath("categories.xml"))

XML - Create New XML Document

The time of year at looking how XML interacts with c#/vb.net is upon us and its time to start to look at the coding.

Now firstly remember to copy these Namespaces at the top of the page where you will write your code.

For c# paste in:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Xml;
using System.Xml.Schema;
using System.Data;


and vb.net paste in:

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Xml
Imports System.Data


Now for the code for creating that new XML document from c#/vb.net code:

For c# use the following:

XmlDocument xmlDoc = new XmlDocument();
XmlDeclaration xmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);
// Create the root element
XmlElement rootNode = xmlDoc.CreateElement("CategoryList");
xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement);
xmlDoc.AppendChild(rootNode);

// Create a new element and add it to the root node
XmlElement parentNode = xmlDoc.CreateElement("Category");

// Set attribute name and value!
parentNode.SetAttribute("ID", "01");

xmlDoc.DocumentElement.PrependChild(parentNode);

// Create the required nodes
XmlElement mainNode = xmlDoc.CreateElement("MainCategory");
XmlElement descNode = xmlDoc.CreateElement("Description");
XmlElement activeNode = xmlDoc.CreateElement("Active");

// retrieve the text
XmlText categoryText = xmlDoc.CreateTextNode("XML");
XmlText descText = xmlDoc.CreateTextNode("This is a list my XML articles.");
XmlText activeText = xmlDoc.CreateTextNode("true");

// append the nodes to the parentNode without the value
parentNode.AppendChild(mainNode);
parentNode.AppendChild(descNode);
parentNode.AppendChild(activeNode);

// save the value of the fields into the nodes
mainNode.AppendChild(categoryText);
descNode.AppendChild(descText);
activeNode.AppendChild(activeText);

xmlDoc.Save(Server.MapPath("categories.xml"));


and in vb.net use:

Sub create_and_save()

Dim xmlDoc As XmlDocument = New XmlDocument
Dim xmlDeclaration As XmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", "")
Dim rootNode As XmlElement = xmlDoc.CreateElement("CategoryList")
xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement)
xmlDoc.AppendChild(rootNode)

'Create a new element and add it to the root node
Dim parentNode As XmlElement = xmlDoc.CreateElement("Category")

'Set attribute name and value!
parentNode.SetAttribute("ID", "01")
xmlDoc.DocumentElement.PrependChild(parentNode)

'Create the required nodes
Dim mainNode As XmlElement = xmlDoc.CreateElement("MainCategory")
Dim descNode As XmlElement = xmlDoc.CreateElement("Description")
Dim activeNode As XmlElement = xmlDoc.CreateElement("Active")


'retrieve the text
Dim categoryText As XmlText = xmlDoc.CreateTextNode("XML")
Dim descText As XmlText = xmlDoc.CreateTextNode("This is a list my XML articles.")
Dim activeText As XmlText = xmlDoc.CreateTextNode("true")

'append the nodes to the parentNode without the value
parentNode.AppendChild(mainNode)
parentNode.AppendChild(descNode)
parentNode.AppendChild(activeNode)

'save the value of the fields into the nodes
mainNode.AppendChild(categoryText)
descNode.AppendChild(descText)
activeNode.AppendChild(activeText)

xmlDoc.Save(Server.MapPath("categories.xml"))

End Sub

Selected Values for Multiple ListBox

Simple little one in both vb.net and C# When you have a Multiple Selection Listbox whether it is static or dynamic and you wish to have previous entered data for a view page to be selected it is imperative that you enter the code for each selection as follows:

in vb.net

lb_cause.Items(1).Selected = True

where 1 is the value for the list item in the listbox

and c#

lb_cause.Items[1].Selected = True;

Never, ever use lb_cause.

SelectedValue = 1

Thats all for now.

Mircosoft ReportViewer with Sub Reports

Creating a report for report viewer and including a sub-report.

1. Create a new .ASPX page.
2. Go to the Toolbox and double click on Microsoft ReportViewer.
3. Right click on the project name in the Solution Explorer and right click.
4. Select Add New Item
5. Select Data Set and rename the Data Set.
6. Click Add
7. Click Yes to save the new Data Set in the App_Code folder.

In this instance I will be using a Stored Procedure already created.

8. Click on Server Explorer in the design view of the Data Set.
9. In the Server Explorer Window expand the folders for the database and then the Store Procedure folder to find the stored procedure you wish to use.
10.Hold the left button of your mouse down on the stored procedure you wish to use and drag the stored procedure onto the design view of the Data Set.
11. Save and close the data set.
12. Right click on the project name in the Solution Explorer and right click.
13. Select Add New Item
14. Select Report and rename the Report.
15. Click Add
16. Ensure your new report is open.
17. Expand Web Site Data Sources
18. Expand the Data Set that you created from step 8.
19. Drag and drop the field to be displayed in your report on to your report document in design mode.
20. Save your report document.
21. Go to your .ASPX page created in step 1.
22. Click on the arrow to the top right of the ReportViewer.
23. Under the drop drown list for choose report, select the report that you have created from step 14.
24. Then click on Choose Data Sources from below the drop down list called “Choose Report”
25. The Data Set you created in step 8 should automatically be chosen as Report Data Source for the Data Source Instance.
26. On the Object Data Source created below the ReportViewer click on the right arrow and select Configure Data Source.
27. On the page titled “Choose a Business Object”, the business object should be automatically created. Click Next
28. The next page titled “Define Data Methods” you will need to select the method from the drop down list.
29. Select the appropriate method and click Next.
30. On the page titled “Define Parameters”, you would only need to define a parameter if the SQL statement requires one.
31. Input your parameter method if needed.
32. ClickFinish
33.You can now test this report.
34. Now to create a sub report.
35. Again you will need a data set for your sub report so repeat steps 3 to 11.
36. Now you need to create the report form for your sub-report. For this you need to repeat steps 12 to 19 Only.
37. Next right click on the square on the top left axis of where both the axis meets.
38. From the context menu select Report Parameters.
39. In the Report Parameters wizard. Click add and enter a name in both Name and Prompt in the properties area. I tend to keep these names the same. Make a note of these names for future reference.
40. Click OK on the Report Parameters wizard.
41. Save and close your new sub report.
42. Find and open your original report created between steps 12 to 20.
43. Open the toolbox and drag and drop subreport on to your report. Resize the subreport so its shape will be displayed correctly in your report.
44. Right click on the grey area of the subreport and click properties in the context menu.
45. Under the General tab, find the drop down list subreport and find the name of the report you have created for you subreport and then click on it.
46. Then click on the parameters tab.
47. Click to the right of the star under the heading Parameter Name and enter the name of the parameter your created in step 39.
48. Click then under the heading Parameter Value and select the value you wish to use as a parameter to connect the report to your subreport, for which the subreport will need for its stored procedure.
49. Click OK.
50. Save and close the report.
51. Goto the code page of the ASPX Page you created in step 1. It will end in aspx.vb or aspx.cs dependent on which language you are using. In this case we are using VB.NET so it will be aspx.vb.
52. Above the Parital Class of the .aspx.vb document enter the following import commands

Imports Microsoft.Reporting.WebForms
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlCommand

53. In the Page Load sub of the .aspx.vb page enter:

AddHandler Me.ReportViewer1.LocalReport.SubreportProcessing, AddressOf ReportViewer1_SubreportProcessing

Note. ReportViewer1 is the name(ID) of your reportviewer in the HTML code. ReportViewer1_Subreportprocessing is the name of the subroutine in step 54.

54. Create the following subroutine.

Public Sub ReportViewer1_SubreportProcessing(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

Dim strParameter1 As String = e.Parameters("").Values(0).ToString()
Dim rdS As New ReportDataSource("", (strParameter1))
e.DataSources.Add(rdS)

End Sub

Note: is the name of the parameter you created in step 39.
is created from the name of your dataset and the name of your stored proceedure in this fomrat “dataset_storedproceedure”.
is the name of the subroutine in step 55 used to call the data for the subreport.

55. Finally enter the following code for subroutine to open the database to and collect the data for the subreport.

Public Function (ByVal strParameter1 As String)
Dim dta As New SqlDataAdapter()
Dim con As New SqlConnection("")
Dim ds As New DataSet()
con.Open()
Dim cmd As New SqlCommand("", con)
dta.SelectCommand = cmd
dta.SelectCommand.Connection = con
dta.Fill(ds, "subrep_table")
con.Close()
Return ds.Tables("subrep_table ")
End Function

56. Save all document.
57. If you now run your report the report will display all data from both the report and the subreport.

Using the IIF command in Microsoft SQL

A little nitty problem of today I want to document. Basically came up with this command in an old Access Database

IIf([Length]=True,"Twelve Months"

Now, there is no IIf command in Microsoft SQL Statments, so this command needs to be converted to :

CASE
WHEN [Length]=1 THEN 'Twelve Months'
END

Simple!

Combine two Columns into one in a Microsoft SQL Statement

First post of the day, and just a quickie here for a Microsoft SQL Statement. This one is basically for use, if say, you have two fields for example one is first name and the other is last name. This short command in the 'SELECT' section of the SQL statement will condense two fields/columns into one.

(RTRIM(forname) + ' ' + RTRIM(lastname)) AS fullname

There go, short and simple! :)

Calculations and Formulas with Conditions in Crystal Report

A short post this time continuing with the Crystal Reports issues. This time I'm going to focus on doing calculations within the formula workshop, in particular with conditional uses.

1. From your crystal report, expand Unbound Fields in the Field Explorer.

2. Select Number from Unbound Fields and drag it to the position you require on your Crystal Report.

3. When the Unbound Number has been placed on your report, right click and select 'Edit Formula'.

4. Then follow the code below to create a percentage calculation based on two boolean true/false conditions:

Shared numberVar MyCount;

If

{Field 1 Selected from Report Field Window} = true

and

{Field 2 Selected from Report Field Window} = false

then

MyCount := MyCount + 1

Else

MyCount := MyCount;

MyCount/count({Field 3 Selected from Report Field Window})*100 & '%'

You can alter this code to your needs but hopefully it will give a broad understanding of a calculation done within Crystal Reports on data already called to the report.

Checkboxes in Crystal Report XI

This little problem niggled me for about an hour or so before rendering this soultion below :

1. Left click on the boolean field you wish to change to a checkbox in your Crystal Report.
2. Change the font to Wingdings. (This will change your boolean field to a lot of wierd and wonderful characters, but don't worry.
3. Right click on the boolean field and select "Format Object".
4. Select the common tab.
5. Click on the "X+2" button relevant to Display String.
6. In the formula box enter the following code :

If = true thenCHR(254) else CHR(111)

* I find it easier to double click on the boolean to be converted in the Report Field explorer window.

7. Save and close your new formula.
8. Click 'OK' on the Format Editor.
9. When you run your report, you'll find the boolean true/false will be displayed as a checkbox.

How to create a connection from a SQL Database to Crystal Reports with parameters.


1. Create a new Crystal Report
2. Set the new Crystal Report as a blank report
3. Right click on Database Fields
4. Select Database Expert
5. Expand Create New Connection
6. Double click on OLE DB (ADO)
7. Select OLE DB Provider for SQL Server
8. Click Next >
9. Input name of server
10. Input name of database
11. Check Integrated Security Checkbox
12. Click Next>
13. On the next page click Finish
14. Under Database Expert click on Current Connections
15. Click on database name
16. Click on dbo or specified user
17. Select Table or Stored Procedure
18. Table or Stored Procedure will now be available under Database Fields in the report.
19. Expand Database Fields
20. Expand Table or Stored Procedure
21. Select fields from Table or Stored Procedure and drag on to report.
22. Save the Crystal Report
23. On the web page from toolbox double click on Crystal Report Viewer
24. When the grey Crystal Report Viewer box appear click on the right arrow
25. Under Choose Report Source select
26. In the Create Report Source dialog box, under Specify a Crystal Report, select the Crystal Report you wish to display.
27. Click OK
28. If you do not require parameters debug here otherwise see below for parameters.
29. Enter the following code either on Page Load or with a sub/function and include the Import reference at the top of your document.

In VB.NET

Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Reporting
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Dim repDoc As ReportDocument = New ReportDocument
Dim repFilePath = Server.MapPath("~\Reports\CrystalReport.rpt")
repDoc.Load(repFilePath)
repDoc.SetParameterValue(0, )
repDoc.SetParameterValue(1, ))

CrystalReportViewer1.ReportSource = repDoc
CrystalReportViewer1.Visible = True

Or in C#

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Reporting;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

ReportDocument repDoc = new ReportDocument();
dynamic repFilePath = Server.MapPath("~\\Reports\\CrystalReport.rpt");
repDoc.Load(repFilePath);
repDoc.SetParameterValue(0, );
repDoc.SetParameterValue(1, );
CrystalReportViewer1.ReportSource = repDoc;
CrystalReportViewer1.Visible = true;

30. The report is now ready for display, and can be tested using the debugger.

That's how to basically use parameters on a Crystal Report using Visual Studio 2008 with ASP.NET 3.5.

Next will be how to use subGroups within a Crystal Report

Crystal Reports - The Basic

Hello, today I'm going to display how to create a basic Crystal Report by directly accessing a table or a stored procedure from a database.

1. Create a new Crystal Report
2. Set the new Crystal Report as a blank report
3. Right click on Database Fields
4. Select Database Expert
5. Expand Create New Connection
6. Double click on OLE DB (ADO)
7. Select OLE DB Provider for SQL Server
8. Click Next >
9. Input name of server
10. Input name of database
11. Check Integrated Security Checkbox
12. Click Next>
13. On the next page click Finish
14. Under Database Expert click on Current Connections
15. Click on database name
16. Click on dbo or specified user
17. Select Table or Stored Procedure
18. Table or Stored Procedure will now be available under Database Fields in the report.
19. Expand Database Fields
20. Expand Table or Stored Procedure
21. Select fields from Table or Stored Procedure and drag on to report.
22. Save the Crystal Report
23. On the web page from toolbox double click on Crystal Report Viewer
24. When the grey Crystal Report Viewer box appear click on the right arrow
25. Under Choose Report Source select
26. In the Create Report Source dialog box, under Specify a Crystal Report, select the Crystal Report you wish to display.
27. Click OK
28. The report is now ready for display, and can be tested using the debugger.

Later I will show how to use integer and sub groups within a crystal report.