|
Greetings again,
I am still having some issues with this project.
This time, the issue is with pulling records by passing date values.
Everything else seems to work well.
We have two date parameters, date_start and date_end
A user can enter just value for start date only, end date only or date range between start date and end date.
The issue is that if a user enters start date, and clicks search, we get No data found message.
If user enters either value for end date or date range between start date and end date, the system hangs.
On the DB, using SSMS, if I enter a value for start date, I get results.
Similarly, if I enter value for either end date or date range between start date and end date, I get results very quickly.
When I put a debugger on my asp.net code, it returns the exact same value as the value I get when I run it from SSMS.
For instance,
Here is a value for start and end dates:
start date: 2014-05-05 10:34:03
end date: 2014-05-05 10:35:52
These values are the same when I query from the DB and as well as when I put debugger on my code.
The data type for these dates is DateTime2(0).
Any ideas why this is not working from my app?
Here are some code snips:
'//stored proc WHERE clause
WHERE 1 = 1
AND ((@uuid IS NULL OR qp.uuid Like '%' + @uuid + '%')
AND (@callerlist IS NULL OR qp.call_list_id Like '%' + @callerlist + '%')
AND (@phone IS NULL OR cl.phone_number Like '%' + @phone + '%')
AND (@start IS NULL OR startttime >= @start)
AND (@Endd IS NULL OR endtime <= @Endd)
AND (@calltype IS NULL OR c.id Like '%' + @calltype + '%')
AND cl.phone_number IS NOT NULL)
'/
<asp:TextBox id="date_start" style="width:150px;" class="form-control alignleft" runat="server" />
<asp:TextBox id="date_end" style="width:150px;" class="form-control alignleft" runat="server" />
'/VB:
Private Sub SearchCustomers()
Dim constr As String = ConfigurationManager.ConnectionStrings("ppmtest").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand()
Dim sql As String = "spGetLogs"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@uuid", suuid.Text)
cmd.Parameters.AddWithValue("@callerlist", caller_list_id.Text)
cmd.Parameters.AddWithValue("@phone", phonenumber.Text)
cmd.Parameters.AddWithValue("@start", date_start.Text)
cmd.Parameters.AddWithValue("@Endd", date_end.Text)
cmd.Parameters.AddWithValue("@calltype", call_type.SelectedValue)
'Response.Write(sql)
'Response.End()
cmd.CommandText = sql
cmd.Connection = con
Using sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Using
End Using
End Sub
As usual, any help is greatly appreciated
|
|
|
|
|
Why are you using text strings for dates instead of DateTime types, and textboxes instead of Date pickers?
|
|
|
|
|
Hi Richard,
Thanks for your response however, I am not sure I understand your question.
Where did you see me use text strings?
|
|
|
|
|
In your command parameters e.g:
cmd.Parameters.AddWithValue("@start", date_start.Text)
, and the textboxes in your form.
|
|
|
|
|
Hmm, still confused.
The textboxes in my form are populated by selecting date from datepicker.
How would have coded the form?
|
|
|
|
|
Try something like:
Dim startDate As DateTime
If DateTime.TryParse(date_start.Text, startDate) Then
cmd.Parameters.AddWithValue("@start", startDate)
Else
cmd.Parameters.AddWithValue("@start", DBNull.Value)
End If
DateTime.TryParse Method (System) | Microsoft Docs[^]
If the date picker's format doesn't match the server's regional settings, then you may need to use DateTime.TryParseExact Method[^] instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you so much sir.
That worked.
It is spitting out way too many records.
Is it possible to do something like this on the stored proc:
Year(startttime) >= Year(@start) on the stored proc?
For instance, I queried with this range:
14-05-05 10:34:03 for start date and
2014-05-30 12:16:21 for end date
and it produced almost 700,000 records. First time, it timed out.
|
|
|
|
|
I suspect the problem is with the start date, which only has a two-digit year. DateTime.TryParse doesn't know which part is the year, the month, and the day, so it has to guess based on your computer's regional settings.
On my computer, parsing 14-05-05 10:34:03 returns 2005-05-14 10:34:03, which is obviously not what you wanted.
Try changing your date picker so that it always formats the date with four-digit years. If you can't do that, then you'll need to use DateTime.TryParseExact[^] with a list of possible formats.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Did you say that start_date has two digit year because of the data type which is
DateTime2(0). ?
If so, I can remove the 2 so we get 4 digit year.
Thanks for your help sir.
|
|
|
|
|
No, I said that start date has a two-digit year because that's what you showed: 14-05-05 10:34:03. 
(datetime2(0) is the correct SQL type to use; the "2" has nothing to do with the number of digits in the year.)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That's not what I showed sir.
I showed four digit year:
start date: 2014-05-05 10:34:03
end date: 2014-05-05 10:35:52
The year from my example is 2014
|
|
|
|
|
No it's not:
For instance, I queried with this range:
14-05-05 10:34:03 for start date and
2014-05-30 12:16:21 for end date
That start date only has a two-digit year.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
If you're actually using four-digit years and you're still getting 700,000 records, then either you have 700,000 records in that date range, or DateTime.TryParse can't understand your input.
Try TryParseExact[^] instead:
Dim startDate As DateTime
If DateTime.TryParseExact(date_start.Text, "yyyy-MM-dd HH:mm:ss", Nothing, System.Globalization.DateTimeStyles.None, startDate) Then
cmd.Parameters.AddWithValue("@start", startDate)
Else
cmd.Parameters.AddWithValue("@start", DBNull.Value)
End If
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have actually switched to DateTime.TryParse using this:
m startDate As DateTime
Dim EndDate As DateTime
If DateTime.TryParseExact(date_start.Text, "yyyy-MM-dd hh:mm:ss", New CultureInfo("en-US"), DateTimeStyles.None, startDate) Then
cmd.Parameters.AddWithValue("@start", startDate)
Else
cmd.Parameters.AddWithValue("@start", DBNull.Value)
End If
If DateTime.TryParseExact(date_end.Text, "yyyy-MM-dd hh:mm:ss", New CultureInfo("en-US"), DateTimeStyles.None, EndDate) Then
cmd.Parameters.AddWithValue("@Endd", EndDate)
Else
cmd.Parameters.AddWithValue("@Endd", DBNull.Value)
End If
I am going to your solution instead.
You might be right about having that many records in the DB but this is the part that confuses me.
I run this in SSMS:
select startttime, endtime from cti.qpcdr where startttime >= '2014-05-05 10:34:03' and endtime <= '2014-05-05 10:35:52'
It returns one record.
I enter one value to start date and the other value to end date textboxes and it returns No record found.
If a record had been returned, it would have given me the confidence I need to conclude that it works.
|
|
|
|
|
You need to match the query in SSMS to the query you're using in your code. You're missing a join to at least one other table, and the cl.phone_number IS NOT NULL condition, both of which could filter out the one record you're seeing in SSMS.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Run Sql profiler and look at the t-sql that is being sent to the sql server. You should then be able to find the issue quickly.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
hi,
I need to open a word file which saved in server in client side(without open/save dialog and not download in client system) and let users to edit it. then save back this file in server again.
I try
Word.ApplicationClass for this, but it open word file in server side.
do you know any component or code in asp.net for doing this??
please answer and help me asap.
best regards.
|
|
|
|
|
You cannot open applications on the client side, especially if the client does not have it installed.
|
|
|
|
|
if office was installed in client system, Can I do this??
|
|
|
|
|
No, you cannot take control of a user's system from a browser; it is a security issue.
|
|
|
|
|
I'm using .Net core and when I first read of user secrets I thought it was brilliant. That is until I met the powershell command:
dotnet user-secrets list
I'm looking at storing them on the database again, and encrypting them using something like bcrypt to encrypt them. The weakness of this argument is that the key is in the code...
Thoughts anyone on secrecy?
Ger
|
|
|
|
|
Who are you trying to keep the secrets secret from?
dotnet user-secrets list will only list the secrets stored for the current user. And it's intended to keep them out of your source control, not to prevent you from seeing them.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm trying to keep them secret from the next person to use the machine. I missed the detail about that list command only applying to the current user. I am much relived.
Ger
|
|
|
|
|
First thing to mention here is, why are you even trying to store the secrets—I am assuming, connection strings, API keys, etc. etc.—in your own machine, unless your web server runs in the same machine. In testing or development environment, you should consider using testing or development credentials, that when exposed can be cleared, rotated and wiped without any panic.
I am not sure why you didn't read the documentation for this tool, Microsoft had already made it pretty much clear that this tool is not for "securely storing your credentials", rather "storing your secure credentials". There is a huge difference,
The Secret Manager tool doesn't encrypt the stored secrets and shouldn't be treated as a trusted store. It's for development purposes only. The keys and values are stored in a JSON configuration file in the user profile directory. So, that was pretty much clear from the documentation that this tool doesn't do anything on its own side and as Richard said, it merely takes the secure information out of your code, to prevent it from being version controlled.
If you are using an external hosting service, use their secure vaults (or something similar in technical terms). For example, it is a bad idea of store the security details or credentials in environment variables, or even in the databases that you hold or own. Because as you said,
Quote: I'm looking at storing them on the database again, and encrypting them using something like bcrypt to encrypt them.
They are merely encrypted, anyone who has access to your machine—since this data is in your machine—has access to that database, which is clearly visible as these values are needed by your apps to function. Thus, anyone can access the keys.
The good practice to use nowadays is to use secure vaults, you can check with your hosting providers to check if they do provide any. For example, on Microsoft Azure you should use Azure KeyVault, Key Vault | Microsoft Azure, which secures your credentials and resists tampering against it.
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
Afzaal Ahmad Zeeshan wrote: I am not sure why you didn't read the documentation for this tool, Microsoft had already made it pretty much clear that this tool is not for "securely storing your credentials", rather "storing your secure credentials". There is a huge difference,
Chances are I missed it when reading it, although I'm not sure whoose documentation I read on the subject so it might not even have been mentioned. Either way it was lost on me.
Afzaal Ahmad Zeeshan wrote: They are merely encrypted, anyone who has access to your machine—since this data is in your machine—has access to that database,
This is why I move away from database storage in the first place.
Afzaal Ahmad Zeeshan wrote: For example, on Microsoft Azure you should use Azure KeyVault
And this is the real value in your response. 
Ger
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
