ASP.NET

27-Jun-07 4:39

For some reason the code below to fill a datagridview works very intermittently, I was wondering if anyone could spot any coding horrors my inexperience may have allowed in there...

dateFrom = "CONVERT(DateTime, '01/" & MonthDD.SelectedIndex + 1 & "/" & Trim(YearDD.SelectedValue) & "', 103)"

If MonthDD.SelectedValue <> "December" Then
dateTo = "CONVERT(DateTime, '01/" & MonthDD.SelectedIndex + 2 & "/" & Trim(YearDD.SelectedValue) & "', 103)"
Else
dateTo = "CONVERT(DateTime, '01/01/" & Trim(YearDD.SelectedValue + 1) & "', 103)"
End If

SQLString1 = "SELECT [InvoiceNo], [ClientCode], [Date], [PnrCode], [Initials], [Amount], [AccyNC4000], [DisbsNC4090], [DKpgmgmtNC4050], [CorpTaxNC4021], [PersTaxNC4030], [PayrollNC4070], [ExectrustNC4031], [CoySecNC4010], [OtherNC4080], [AccyNC4000]+[DisbsNC4090]+[DKpgmgmtNC4050]+[CorpTaxNC4021]+[PersTaxNC4030]+[PayrollNC4070]+[ExectrustNC4031]+[CoySecNC4010]+[OtherNC4080] as [Total] FROM [transaction] WHERE ("

If PartnerDD.SelectedItem.Value <> 0 Then
SQLString1 = SQLString1 & "([PnrCode] = @PnrCode) AND "
End If

If ClientCodeDD.SelectedItem.Text <> "Select a Client Code" Then
SQLString1 = SQLString1 & "([ClientCode] = @ClientCode) AND "
End If

SQLString1 = SQLString1 & "([Date] >= " & dateFrom & ") AND ([Date] < " & dateTo & "))"

SqlDataSource1.SelectCommand = SQLString1

Colin Angus Mackay27-Jun-07 5:12

Colin Angus Mackay

27-Jun-07 5:12

TenmanS14 wrote:
I was wondering if anyone could spot any coding horrors my inexperience may have allowed in there...

You should use paramertised queries rather than injecting values in to the SQL. See SQL Injection Attacks and tips on how to prevent them[^]... Actually later on you are using parameters! OMG | :OMG:

Why use two different ways of doing things?

Other than that I can't really see what you are trying to do. The code is quite difficult to read.

Perhaps you need to isolate this SQL and see how it runs on its own with various inputs to see if you can determine where exactly it is failing.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Ready to Give up - Your help will be much appreciated.

My website

TenmanS1427-Jun-07 5:26

27-Jun-07 5:26

because the dates come from a dropdown that contains a list of months and a dropdown that contains a list of years, these have then to be concatenated into Date Format with variables that I'm using in the SQL statement. This is going to be running on a company intranet so I'm not that worried about SQL Injection attacks

Code to populate the drop downs runs in not page.ispostback

thisMonth = Month(Now())
thisYear = Year(Now())

For j = thisYear - 5 To thisYear + 5
YearDD.Items.Add(Str(j))
Next

MonthDD.Items.Add("January")
MonthDD.Items.Add("February")
MonthDD.Items.Add("March")
MonthDD.Items.Add("April")
MonthDD.Items.Add("May")
MonthDD.Items.Add("June")
MonthDD.Items.Add("July")
MonthDD.Items.Add("August")
MonthDD.Items.Add("September")
MonthDD.Items.Add("October")
MonthDD.Items.Add("November")
MonthDD.Items.Add("December")

MonthDD.SelectedIndex = thisMonth - 1
YearDD.SelectedIndex = 5

Colin Angus Mackay27-Jun-07 6:09

Colin Angus Mackay

27-Jun-07 6:09

TenmanS14 wrote:
This is going to be running on a company intranet so I'm not that worried about SQL Injection attacks

Statistiacally most attacks are insider jobs.

TenmanS14 wrote:
because the dates come from a dropdown that contains a list of months and a dropdown that contains a list of years, these have then to be concatenated into Date Format with variables that I'm using in the SQL statement

Or you could do that to create a DateTime object then pass that as a parameters.

TenmanS1427-Jun-07 8:47

Re: SQL Query works intermittently... [modified]

27-Jun-07 8:47

OK, had a go at getting that variable as a parameter as well, works OK, but I still have the same problem, if I change the year or the month drop down and repost the page, it doesn't seem to run the query, as soon as I change the ClientCode or Partner dropdown and repost it, it runs the query with the correct Dates selected as well... This is going to be something really simple I feel and I'm going to look like a right tool...

SQLString1 = "SELECT [InvoiceNo], [ClientCode], [Date], [PnrCode], [Initials], [Amount], [AccyNC4000], [DisbsNC4090], [DKpgmgmtNC4050], [CorpTaxNC4021], [PersTaxNC4030], [PayrollNC4070], [ExectrustNC4031], [CoySecNC4010], [OtherNC4080], [AccyNC4000]+[DisbsNC4090]+[DKpgmgmtNC4050]+[CorpTaxNC4021]+[PersTaxNC4030]+[PayrollNC4070]+[ExectrustNC4031]+[CoySecNC4010]+[OtherNC4080] as [Total] FROM [transaction] WHERE "

If PartnerDD.SelectedItem.Value <> 0 Then
SQLString1 = SQLString1 & "([PnrCode] = @PnrCode) AND "
End If

If ClientCodeDD.SelectedItem.Text <> "Select a Client Code" Then
SQLString1 = SQLString1 & "([ClientCode] = @ClientCode) AND "
End If

SqlDataSource1.SelectParameters.Item("dateFrom").DefaultValue = dateParam
SqlDataSource1.SelectParameters.Item("dateTo1").DefaultValue = dateParam1

SQLString1 = SQLString1 & "([Date] >= @DateFrom) AND ([Date] < @DateTo1)"
MsgBox(SQLString1)
SqlDataSource1.SelectCommand = SQLString1

TenmanS1427-Jun-07 9:20

urgent need about stored procedure...,

27-Jun-07 9:20

Fixed it.... had to make an invisible asp:label so I could pass the value from a controlparameter and bind the labels text property as the value...

-- modified at 15:57 Wednesday 27th June, 2007 cos I'm a prat and wrote commandparameter instead of controlparameter :p

Member 387988127-Jun-07 3:21

Member 3879881

27-Jun-07 3:21

This is the actual result from my procedure

18-Mar-03 AUD -7.4505805969238281E-9
07-Apr-03 AUD -7.9162418842315674E-9
09-Apr-03 AUD 5.2386894822120667E-10
17-Apr-03 AUD -7.9162418842315674E-9

but My expected result is

18-Mar-03 AUD -7.45058
07-Apr-03 AUD -7.91624
09-Apr-03 AUD 5.23868
17-Apr-03 AUD -7.91624

Please help the do this process how to perform this?

Regards,
Magi

Re: urgent need about stored procedure...,

Colin Angus Mackay27-Jun-07 3:37

Colin Angus Mackay

27-Jun-07 3:37

What does this have to do with ASP.NET (a framework designed for writing web applications)?

This should go in the SQL Forum.

Re: urgent need about stored procedure...,

dekart_roo27-Jun-07 6:46

dekart_roo

27-Jun-07 6:46

It's sounding to me like you might be needing to do some string formats. Check out this link... I think it's the answer to your question.

http://blog.stevex.net/index.php/string-formatting-in-csharp/

-Roo

Printing

Blue_Boy27-Jun-07 3:10

Blue_Boy

27-Jun-07 3:10

How can I print window directly to printer without displaying print-dialogbox?

Regards

I Love SQL

Re: Printing

Vasudevan Deepak Kumar27-Jun-07 3:13

27-Jun-07 3:13

MeadCo ScriptX would help you do that.

I have a brief review on it here: http://lavanyadeepak.blogspot.com/2007/03/review-meadco-scriptx.html[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Re: Printing

Blue_Boy27-Jun-07 5:27

Blue_Boy

27-Jun-07 5:27

Thanks for your answer, I will check it.

I Love SQL

Export Crystal reports

itzmevishu27-Jun-07 2:51

27-Jun-07 2:51

hi this is my procedure to export my .rpt to pdf, xls, doc or rtf. it's working good but when it comes to last line crReportDocument.Export(); ...plz once go through this code and kindly please help me out ......

In page load
-----------------------------

protected void btnGenRep_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection("_constr");
con.Open();
SqlCommand cmd = new SqlCommand("GetLocations", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds, "tbLocations");

cmd.ExecuteNonQuery();
con.Close();

ReportDocument myReportDocument;
myReportDocument = new ReportDocument();
myReportDocument.Load(Server.MapPath("CrystalReport.rpt"));

myReportDocument.SetDataSource(ds);
CrystalReportViewer1.ReportSource = myReportDocument;
string rptFormat = ddlformat.SelectedItem.Value.ToString();
string format = GetReportFormat(rptFormat);
string repname = "CrystalReport." + format;
pal pall = new pal();
string ExportFile=pall.LoadReport(Request.PhysicalApplicationPath, rptFormat, myReportDocument, repname);

Response.Write("window.open(Exported/'" + ExportFile + "');");
}
catch (Exception ex)
{
Response.Write(ex.Message);

}
finally { }
}

public string GetReportFormat(string type)
{
switch (type)
{
case "DOC":
return "DOC";
case "XLS":
return "XLS";
case "RTF":
return "RTF";
case "PDF":
default:
return "PDF";
}
}

In class file

public string LoadReport(string path, string exportfile, ReportDocument crReportDocument, string filename)
{
ExportOptions crExportOptions ;
DiskFileDestinationOptions crDiskFileDestinationOptions;
string ExportPath;
ExportPath = path + "Exported\\";

if (Directory.Exists(ExportPath) == false)
{
Directory.CreateDirectory(path + "Exported\\");
}

crDiskFileDestinationOptions = new DiskFileDestinationOptions();

crExportOptions = new ExportOptions();

crExportOptions = crReportDocument.ExportOptions;

if (exportfile == "RTF")
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType=ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType=ExportFormatType.RichText;

}
if (exportfile == "PDF")
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;

}
if (exportfile == "XLS")
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.Excel;

}

if (exportfile == "DOC")
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.WordForWindows;

}
try
{
crReportDocument.Export();->>> Error is occurred here
return filename;

}
catch (Exception ex)
{
throw (ex);
}
finally { }

}

Vasudevan Deepak Kumar27-Jun-07 3:08

27-Jun-07 3:08

What is the exception you get?

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

itzmevishu27-Jun-07 3:13

27-Jun-07 3:13

here im getting exception as

Object reference is not set to an instance

thanks...

Vasudevan Deepak Kumar27-Jun-07 3:35

27-Jun-07 3:35

Did you attach your debugger to see where it derails accessing a NULL?

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

itzmevishu27-Jun-07 4:11

27-Jun-07 4:11

while in debug mode its giving

filename as rassdk://D:\Crystal_US\CrystalReport.rpt

filepath= D:\Crystal_US\CrystalReport.rpt

is thr anything wrong with this and its giving no where its assisgned to NULL

itzmevishu27-Jun-07 4:16

Maintaining dynamic controls

27-Jun-07 4:16

deepak sir ....

if u don mind i will end my sample project .. cud u plz go thru it .... plzz

Venkatesh Mookkan27-Jun-07 2:15

Venkatesh Mookkan

27-Jun-07 2:15

Hi friends,
I working in a module which would look like Windows Explorer, a Folder tree on the left and a File and folder list on the right.

Loading the folder is working fine and loading the right pane to the selected tree node is also done. The right pane data are loaded dynamically. Each files and folders are Linkbutton generated on runtime. I am not able to retain that controls when any of the link button is clicked. I have added a Click event for the Linkbutton which would open that folder and loads the inner data. When the page postbacks, this linkbuttons are cleared and the Click event is not called since the Page lost that control.

Anybody have an idea to solve this problem?

Thanks in advance...

Regards,
Venkatesh Mookkan.
Software Engineer, India
My: Website | Yahoo Group | Blog Spot

Re: Maintaining dynamic controls

Vasudevan Deepak Kumar27-Jun-07 3:12