Tuesday, 2 August 2011

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

Thursday, 21 July 2011

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"/

Tuesday, 12 July 2011

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.

Monday, 11 July 2011

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.

Wednesday, 6 July 2011

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

Thursday, 9 June 2011

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))