|
I need to compare 2 tables to find (List) the difference. How would I do this?
modified 18-Aug-14 13:46pm.
|
|
|
|
|
Sql tables? DataTables? Difference in columns? Difference in data? Need a lot more information.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
try
class Program
{
static void CompareList<T>(List<T> a, List<T> b, ref List<T> diff)
{
foreach(T item in a)
{
if(!b.Contains(item))
{
if(diff.Count == 0)
{
diff.Add(item);
}
else if(!diff.Contains(item))
{
diff.Add(item);
}
}
}
}
static void ListDifference<T>(List<T> a, List<T> b, ref List<T> diff)
{
CompareList(a,b,ref diff);
CompareList(b,a,ref diff);
diff.Sort();
}
static void Main(string[] args)
{
List<int> a = new List<int>{1,2,3,4,5,6,7};
List<int> b = new List<int>{4,6,7,8,9,10};
List<int> diff = new List<int>();
List<string> c = new List<string>{"a", "b", "c", "d","e", "f", "t", "U"};
List<string> d = new List<string>{"b","e","f","g","h"};
List<string> diff2 = new List<string>();
ListDifference(a,b, ref diff);
ListDifference(c, d, ref diff2);
Console.WriteLine("-- Result of CompareLists for Int");
foreach(int i in diff)
{
Console.WriteLine(i.ToString());
}
Console.WriteLine();
Console.WriteLine("Results for CompareLists for strings");
foreach(string s in diff2)
{
Console.WriteLine(s);
}
Console.Read();
}
}
I don't speak Idiot - please talk slowly and clearly
"I have sexdaily. I mean dyslexia. Fcuk!"
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi,
I want to download multiple pdf files in a single button click. I am using an open source dll, Ionic.Zip.Reduced.dll.
All code runs with out showing any errors or exceptions. By using Response.End(), I am getting a response in firefox's firebug window but when I use it in my application it throws an exception and not showing any download dialogue box. So, I had changed it to HttpContext.Current.ApplicationInstance.CompleteRequest();. Now the exception is gone, but I am not getting any response and dialogue window to download. When I debugged, I found something like this
:ClientDisconnectedToken = 'context.Response.ClientDisconnectedToken' threw an exception of type 'System.PlatformNotSupportedException'
:base {System.NotSupportedException} = {"This operation requires IIS version 7.5 or higher running in integrated pipeline mode."}
What is the real problem here, I could download files in zip by using the same code for a new project.
Please help me to sort this out. Here is my code:
System.Web.HttpContext context = System.Web.HttpContext.Current;
using (ZipFile zip = new ZipFile())
{
zip.AlternateEncodingUsage = ZipOption.AsNecessary;
zip.AddDirectoryByName("PDFs");
foreach (string item in FilePaths)
{
zip.AddFile(item.ToString(), "/PDFs/");
}
context.Response.Clear();
context.Response.BufferOutput = false;
//context.Response.Buffer = true;
context.Response.ClearHeaders();
context.Response.ClearContent();
string zipName = String.Format("Zip_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
context.Response.ContentType = "Application/zip";
context.Response.AppendHeader("content-disposition", "attachment; filename=" + zipName);
zip.Save(Response.OutputStream);
//Response.Redirect("www.google.com", true);
context.Response.Flush();
context.Response.SuppressContent = true;
context.ApplicationInstance.CompleteRequest();
//Response.End();
}
Thanks in advance
Sarath Raj
|
|
|
|
|
Isn't this a programming related question?
Favourite line: Throw me to them wolves and close the gate up. I am afraid of what will happen to them wolves - Eminem
~! Firewall !~
|
|
|
|
|
|
When I commented the ajax settings from page source, the download dialogue box seems to be appear. But after downloading it, when I try to open/extract an error shows like the specified zip is invalid. Any solution?
|
|
|
|
|
I want to scrape a table from web page but the content of table doesn't exist in the source of page
the table created on the fly by java script
this is the source of page
<HTML>
<HEAD>
<TITLE>Cisco Unified Communications Manager Express GUI</TITLE>
<SCRIPT Language="JavaScript">
GUIVersion = "7.0.0.1 (Compatible with IOS 12.4(20)T)"
var MWinC
var newestDN = 0
var retryTimes = 0
var changeBit = false
var FeedString = new Array()
FeedString[0] = "Router config has been successfully changed."
FeedString[1] = "Configuration has been successfully changed."
FeedString[2] = "Configuration change failed."
FeedString[3] = "Other telnet session active. Write delayed."
var FailReason = new Array()
FailReason[0] = "Please report the following error: "
FailReason[1] = " Ambiguous Command."
FailReason[2] = " No Matched Command."
FailReason[3] = " No Matched Help Command."
FailReason[4] = " No Matched Alias Command."
FailReason[5] = " Config Locked Down Too Long."
FailReason[6] = " Configuration Change is not allowed."
FailReason[7] = " Wrong Tag Value."
FailReason[8] = " System is writing config to memory. Your change is aborted!"
FailReason[9] = " Parser return code failed."
FailReason[10] = " Invalid ITS command option."
var GenFeed = new Array()
GenFeed[0] = "Add the new extension to a new phone?"
function ksBanner() {
MWinC = "<html><head><style>BODY{\n margin-left: 0pt; \n margin-top: 0pt;\n}"
+ "</style><title>Cisco Unified Communications Manager Express</title></head>"
+ "<body><IMG src='logohome.gif' alt='Cisco Logo'>"
+ "</body></html>"
return MWinC
}
function Greet1() {
MWinC = "<html><head><title> View Window </title></head><style>BODY{BACKGROUND-COLOR: #cccccc} H2{PADDING-RIGHT: 0.25em; PADDING-LEFT: 0.25em; FONT-WEIGHT: bold; FONT-SIZE: 0.9em; PADDING-BOTTOM: 0.25em; MARGIN: 0.5em; COLOR: #666699; PADDING-TOP: 0.25em; FONT-FAMILY: Arial, Verdana, Lucida, sans-serif; BACKGROUND-COLOR: #cccccc }</style><body>\n"
+ "<H2>View Window</H2></body></html>\n"
return MWinC
}
function Greet2() {
MWinC = "<html><head><title> Change Window </title></head><body>\n"
+ "<h2>Change Window </h2></body></html>\n"
return MWinC
}
</SCRIPT>
</HEAD>
<FRAMESET rows="0,*" frameborder="0">
<FRAME name="banner" src="ITSMain" scrolling="NO" frameborder="0" marginwidth="0" marginheight="0" noresize>
<FRAME name="dispframe" src="javascript:parent.Greet1()">
</FRAMESET>
</HTML>
however i can get the content of the table from browser developer tools
any suggestions
modified 16-Aug-14 8:41am.
|
|
|
|
|
<pre><code>Imports System.Data
Imports System.Data.OleDb
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As OleDbConnection
Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\db.mdb")
con.Open()
Dim insert As String = "insert into tbl1(names) values(@names)"
cmd = New OleDbCommand(insert, con)
cmd.Parameters.AddWithValue("@names", "Jacob")
cmd.ExecuteReader()
cmd.Dispose()
con.Dispose()
con.Close()
End Sub
End Class
</pre></code>
I'm trying to insert record into microsoft access database using the above code in asp.net. But the error specified above occurred and my connection string is ok. When i used same code in windows application, it works fine. Please help.
|
|
|
|
|
Don't you need spaces in your sql statement? ie
insert into tbl1(names) values(@names)
should be
insert into tbl1 (names) values (@names)
|
|
|
|
|
Thank you very much. It worked perfectly
|
|
|
|
|
If this is an MS Access database then the word names is a reserved keyword. You need to use square brackets around it to use in a query.
"insert into tbl1([names]) values(@names)"
http://support2.microsoft.com/kb/321266[^]
Please up vote if it helped you
Thanks And Regards
Sibeesh
|
|
|
|
|
|
That depends where the data is being stored at.
The only thing to do is, to first check for any match in the data. Forexample, if it is a database related data then alteast first check for any row that has the same data as
"SELECT * FROM table_name WHERE column_name = value";
If there is a result, then don't save it. If there is no data for this query (no result) then save it.
There are other methods for saving data too, like JSON and XML files. Same algorithm works, you first check whether the data is present or not, and then do what's required, save it or not! General logic would be
if(data == "present") {
Console.WriteLine("Data present! Thanks");
} else {
Console.WriteLine("Data saved! Thanks");
}
Favourite line: Throw me to them wolves and close the gate up. I am afraid of what will happen to them wolves - Eminem
~! Firewall !~
|
|
|
|
|
Hi,
You can use bellow mentioned SQL Statement in your Stored Procedure / Query.
IF NOT EXISTS (SELECT <COLUMN NAME> FROM <TABLE NAME>)
BEGIN
"YOUR INSERT INTO SQL QUERY"
END
ELSE
BEGIN
"YOUR UPDATE SQL QUERY"
END
By this way you will handle duplicate insertion.
Hope this will help you.
Thanks,
Nishant Raval.
modified 28-Aug-14 5:48am.
|
|
|
|
|
You can check it in your c#, javascript, SQl codes. Where exactly you need that validation ?
Please up vote if it helped you
Thanks And Regards
Sibeesh
|
|
|
|
|
I have following problems disc. below
In the Iframe i call this page 'WorkingPanel.aspx'
on this working panel page i have many control .
i want to set all control enable false. when this page call from iframe.
not call from other link....
please give me suggestion for this topic.........
kp 777
|
|
|
|
|
You can put a JavaScript function in the WorkingPanel.aspx page that you can then call from the main page.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
required field validator is not working in this code
<%@ Page Language="VB" AutoEventWireup="false" codefile="~/Test.aspx.vb" Inherits="Test" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<link rel="stylesheet" href="beml.css" />
<title>Welcome to BEML Limited</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="scriptmanger1" runat="server">
</asp:ScriptManager>
<div>
<table id="tblResume" runat="server" style="width: 325px;" cellpadding="0" cellspacing="0">
<tr>
<td style="background-image: url('images/title_bar.png'); padding-left: 10px; vertical-align: middle;
height: 40px;" class="subhead" align="left">
SUBMIT RESUME
</td>
</tr>
<tr>
<td style="border: 1px solid #F2F1F1; width: 325px;" align="center">
<table style="width: 96%;">
<tr>
<td class="mandatory" style="text-align: right; padding-right: 10px;" colspan="2">
All fields are mandatory
</td>
</tr>
<tr>
<td align="left" class="content">
Name*:
</td>
</tr>
<tr>
<td align="left" class="content">
<asp:TextBox ID="txtName" Width="225px" runat="server"
ValidationGroup="valEnquiry" CausesValidation="True" AutoPostBack ="True"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFName" runat="server" ControlToValidate="txtName" ErrorMessage="Please Enter Your Name" ValidationGroup="valEnquiry"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="center" style="padding-right: 10px;">
<asp:Button ID="btnRSend" CausesValidation="False" runat ="server" Text="Send" ValidationGroup="valEnquiry" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
I need help.
Thanks in advance
Have A Nice Day!
Murali.M
Blog
modified 27-Aug-14 16:14pm.
|
|
|
|
|
Your button, btnRSend has CausesValidation set to False which means when it is clicked the validators will not fire.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Hello Experts,
We are building an app that allows users to make online reservations of our facility.
The code below is designed to allow users to perform a search by either specific date or by date range.
Users can also search by selecting a search option from the dropdownlist.
The way the code works currently, if user's search turns up nothing, then we get the following error message:
Object reference not set to an instance of an object.
Line 259: Dim lblcurrentpage As Label = DirectCast(gvrow.Cells(0).FindControl("CurrentPage"), Label)
Rather than the error message, w would like the code to work differently.
If user's search is successful, the results of his/her search is displayed.
If however, no data is found based on his or her search parameter, we would like to display one month's worth of results.
Is this possible?
Below is the current code we are using.
Private Sub showGrid()
' Set the value of the SearchString so it gets
SearchString = txtSpecificDate.Text
ViewSelect = Request("ViewBy")
If ViewSelect = "" Or ViewSelect = "Range" Then
ViewSelect = "Range"
Else
ViewSelect = "Specific"
End If
'Response.Write(ViewSelect)
'Response.End()
Dim strQuery As String
Dim strSearch As String
Dim receiveddate As String = txtSpecificDate.Text
Dim fromdate As String = txtFromDate.Text
Dim enddate As String = txtToDate.Text
' Dim dts As DateTime = DateTime.ParseExact(DateString, "MM/dd/yyyy", CultureInfo.InvariantCulture)
'Protect against SQL Injection
strSearch = Replace(receiveddate, "'", "''", 1, -1, 1)
fromdate = Replace(fromdate, "'", "''", 1, -1, 1)
enddate = Replace(enddate, "'", "''", 1, -1, 1)
receiveddate = DateTime.Today.ToString("MM/dd/yyyy")
fromdate = DateTime.Today.ToString("MM/dd/yyyy")
enddate = "12/31/2050"
' first: Do we use AND or OR between clauses in the WHERE?
Dim andors As String = ANDOR.SelectedValue
'Response.Write(andors)
'Response.End()
' and now build up the WHERE.
'First, initialize where just to make sure it has nothing prior to our search
Dim where As String
where = ""
Dim facilityT As DropDownList
facilityT = FindControl("DropDownList1")
If facilityT.SelectedValue <> "" Then
If where <> "" Then where = where & andors
where = " i.instructorName = '" & Replace(facilityT.SelectedValue, "'", "''") & "'"
End If
If ViewSelect = "Range" Then
If where <> "" Then where = where & andors
where = where & " CONVERT(VARCHAR(10), d.trainingDates, 101) >= '" & fromdate & "' AND CONVERT(VARCHAR(10), d.trainingDates, 101) < '" & (enddate) & "'"
End If
If ViewSelect = "Specific" Then
If where <> "" Then where = where & andors
where = where & " CONVERT(VARCHAR(10), d.trainingDates, 101) = '" & strSearch & "' "
End If
'Response.Write(where)
'Response.End()
strQuery = "SELECT L.LocationId," & _
"c.courseId, " & _
"c.coursename, " & _
"CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
" ELSE 'Available' END AS 'AvailableSeats'," & _
"d.dateid, " & _
" d.trainingDates, " & _
"d.trainingtime, " & _
" c.CourseDescription, " & _
" i.instructorName, " & _
"l.location " & _
" FROM tblLocations L " & _
" Inner Join tblCourses c on l.locationId = c.locationId " & _
" Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
" Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
" Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
" OUTER APPLY (Select Count(*) TakenSeats " & _
" FROM tblTrainings T " & _
" where(L.LocationId = T.LocationId) " & _
" AND c.courseId = t.courseId) TS where " & where
'Response.Write("DEBUG: " & strQuery & "<HR>")
'Response.End()
Dim cmd As New SqlCommand(strQuery)
Dim dt As DataTable = GetData(cmd)
If dt.Rows.Count = 0 Then
message.Text = "No Records Found"
End If
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
Thanks a lot in advance for your assistance.
|
|
|
|
|
samflex wrote: 'Protect against SQL Injection
strSearch = Replace(receiveddate, "'", "''", 1, -1, 1)
fromdate = Replace(fromdate, "'", "''", 1, -1, 1)
enddate = Replace(enddate, "'", "''", 1, -1, 1)
Don't do that!
The only way to truly protect against SQL injection is to use a parameterized query. It's not even difficult:
Dim cmd As New SqlCommand("-placeholder-", conn)
Dim sb As New StringBuilder( *** BASE QUERY HERE, up to but not including the "where" keyword *** )
Dim andors As String = IF(ANDOR.SelectedValue = "OR", " OR ", " AND ")
Dim startedWhere As Boolean = False
If facilityT.SelectedValue <> "" Then
sb.Append(If(startedWhere, andors, " WHERE "))
sb.Append("i.instructorName = @InstructorName")
cmd.Parameters.AddWithValue("@InstructorName", facilityT.SelectedValue)
startedWhere = True
End If
If ViewSelect = "Range" Then
sb.Append(If(startedWhere, andors, " WHERE "))
sb.Append("d.trainingDates Between @FromDate And @EndDate")
cmd.Parameters.AddWithValue("@FromDate", Date.Parse(fromdate))
cmd.Parameters.AddWithValue("@EndDate", Date.Parse(enddate))
startedWhere = True
ElseIf ViewSelect = "Specific" Then
sb.Append(If(startedWhere, andors, " WHERE "))
sb.Append("d.trainingDates = @SpecificDate")
cmd.Parameters.AddWithValue("@SpecificDate", Date.Parse(strSearch))
startedWhere = True
End If
cmd.CommandText = sb.ToString()
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi Richard
Ever since I started posting here, you have save me a lot - Thank you very much.
Does your solution include the core question that I have about presenting one month's data if user's search turns up nothing?
Also, I am not sure I understand the difference between
Dim cmd As New SqlCommand("-placeholder-", conn)
and
Dim sb As New StringBuilder( *** BASE QUERY HERE, up to but not including the "where" keyword *** )
I know the sqlcommand(..) is this:
SELECT L.LocationId," & _
"c.courseId, " & _
"c.coursename, " & _
"CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
" ELSE 'Available' END AS 'AvailableSeats'," & _
"d.dateid, " & _
" d.trainingDates, " & _
"d.trainingtime, " & _
" c.CourseDescription, " & _
" i.instructorName, " & _
"l.location " & _
" FROM tblLocations L " & _
" Inner Join tblCourses c on l.locationId = c.locationId " & _
" Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
" Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
" Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
" OUTER APPLY (Select Count(*) TakenSeats " & _
" FROM tblTrainings T " & _
" where(L.LocationId = T.LocationId) " & _
" AND c.courseId = t.courseId) TS where "
but I don't understand what goes into the stringbuilder(...)
modified 14-Aug-14 13:14pm.
|
|
|
|
|
The "-placeholder-" string in the SqlCommand constructor doesn't need to be changed. It's just there so that the command has some initial text which is obviously not a query. That text will be replaced later, once the full query has been built. (cmd.CommandText = sb.ToString() )
The *** BASE QUERY HERE ... *** part needs to be the base query, up-to but not including the final "where" statement:
Dim sb As New StringBuilder("SELECT L.LocationId," & _
"c.courseId, " & _
"c.coursename, " & _
"CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
" ELSE 'Available' END AS 'AvailableSeats'," & _
"d.dateid, " & _
" d.trainingDates, " & _
"d.trainingtime, " & _
" c.CourseDescription, " & _
" i.instructorName, " & _
"l.location " & _
" FROM tblLocations L " & _
" Inner Join tblCourses c on l.locationId = c.locationId " & _
" Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
" Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
" Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
" OUTER APPLY (Select Count(*) TakenSeats " & _
" FROM tblTrainings T " & _
" where(L.LocationId = T.LocationId) " & _
" AND c.courseId = t.courseId) TS")
The code then appends the relevant filters to the StringBuilder instance to produce the final query.
As for returning one month's data if the search doesn't find anything, you'll need to modify the If dt.Rows.Count = 0 Then ... End If block to issue a new query to return the default data. Something like:
If dt.Rows.Count = 0 Then
message.Text = "No matching records found."
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
cmd.Parameters.AddWithValue("@EndDate", Date.Today)
cmd.CommandText = "SELECT L.LocationId," & _
"c.courseId, " & _
"c.coursename, " & _
"CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
" ELSE 'Available' END AS 'AvailableSeats'," & _
"d.dateid, " & _
" d.trainingDates, " & _
"d.trainingtime, " & _
" c.CourseDescription, " & _
" i.instructorName, " & _
"l.location " & _
" FROM tblLocations L " & _
" Inner Join tblCourses c on l.locationId = c.locationId " & _
" Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
" Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
" Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
" OUTER APPLY (Select Count(*) TakenSeats " & _
" FROM tblTrainings T " & _
" where(L.LocationId = T.LocationId) " & _
" AND c.courseId = t.courseId) TS " & _
" where d.trainingDates Between @FromDate And @EndDate"
dt = GetData(cmd)
End If
You'll still need to modify the code which tries to find the "CurrentPage" label, in case the second query doesn't return any data either. There might not be a row, the row might not have any cells, or the cell might not contain a Label control called "CurrentPage"; any one of these things would cause your code to throw a NullReferenceException .
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi Richard,
Thank you very much sir.
Your code is so elegant. I hope to get close to where you are one day.
I have pretty much put all the pieces together but I think I have things a bit mixed up because first, I commented out some lines that are throwing errors.
Second, when the page loads, I start paging, it starts showing some weird lines.
Can you see what I am doing wrong?
Private Sub showGrid()
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
' Dim strQuery As String
Dim cmd As New SqlCommand("-placeholder-", conn)
Dim sb As New StringBuilder("SELECT L.LocationId," & _
"c.courseId, " & _
"c.coursename, " & _
"CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
" ELSE 'Available' END AS 'AvailableSeats'," & _
"d.dateid, " & _
" d.trainingDates, " & _
"d.trainingtime, " & _
" c.CourseDescription, " & _
" i.instructorName, " & _
"l.location " & _
" FROM tblLocations L " & _
" Inner Join tblCourses c on l.locationId = c.locationId " & _
" Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
" Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
" Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
" OUTER APPLY (Select Count(*) TakenSeats " & _
" FROM tblTrainings T " & _
" where(L.LocationId = T.LocationId) " & _
" AND c.courseId = t.courseId) TS")
' Hard-code the acceptable values here:
Dim andors As String = If(ANDOR.SelectedValue = "OR", " OR ", " AND ")
Dim startedWhere As Boolean = False
If DropDownList1.SelectedValue <> "" Then
sb.Append(If(startedWhere, andors, " WHERE "))
sb.Append("i.instructorName = @InstructorName")
cmd.Parameters.AddWithValue("@InstructorName", DropDownList1.SelectedValue)
startedWhere = True
End If
If ViewSelect = "Range" Then
sb.Append(If(startedWhere, andors, " WHERE "))
sb.Append("d.trainingDates Between @FromDate And @EndDate")
cmd.Parameters.AddWithValue("@FromDate", Date.Parse(txtFromDate.Text))
cmd.Parameters.AddWithValue("@EndDate", Date.Parse(txtToDate.Text))
startedWhere = True
ElseIf ViewSelect = "Specific" Then
sb.Append(If(startedWhere, andors, " WHERE "))
sb.Append("d.trainingDates = @SpecificDate")
cmd.Parameters.AddWithValue("@SpecificDate", Date.Parse(txtSpecificDate.Text))
startedWhere = True
End If
cmd.CommandText = sb.ToString()
' Dim cmd As New SqlCommand(strQuery) 'This says cmd has already been declared
Dim dt As DataTable = GetData(cmd)
If dt.Rows.Count = 0 Then
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
cmd.Parameters.AddWithValue("@EndDate", Date.Today)
cmd.CommandText = "SELECT L.LocationId," & _
"c.courseId, " & _
"c.coursename, " & _
"CASE WHEN L.Seating_Capacity - COALESCE(TS.TakenSeats,0) = 0 THEN 'Not Available' " & _
" ELSE 'Available' END AS 'AvailableSeats'," & _
"d.dateid, " & _
" d.trainingDates, " & _
"d.trainingtime, " & _
" c.CourseDescription, " & _
" i.instructorName, " & _
"l.location " & _
" FROM tblLocations L " & _
" Inner Join tblCourses c on l.locationId = c.locationId " & _
" Inner Join tblTrainingDates d on c.dateid=d.dateid " & _
" Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " & _
" Inner Join tblInstructors i on ic.instructorId = i.instructorId " & _
" OUTER APPLY (Select Count(*) TakenSeats " & _
" FROM tblTrainings T " & _
" where(L.LocationId = T.LocationId) " & _
" AND c.courseId = t.courseId) TS " & _
" where d.trainingDates Between @FromDate And @EndDate"
dt = GetData(cmd)
End If
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
|
|
|
|
|