SQL Query works intermittently...
-
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
-
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
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: 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
-
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: 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
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
-
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
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.
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
-
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.
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
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
-
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
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