Printable View
Hello Everyone,
I am having a problem with the following line of code...
vb.net Code:
if I use...
vb.net Code:
is works.
can anybody suggest what I am doing wrong.
Thanks for reading.
Without seeing anything else, I would suspect the date is in the wrong format. Before the first statements executes, look at the value and check the format. Using parameters would solve that issue quite handily I do believe.
Yes kebo,Quote:
Originally Posted by kebo
you are quite correct, the format for the date was wrong. the DatetimePicker was outputing the date as 16/12/2013, and it should have been 20131216.
SQL kick up a stink...hehehe
did a custom date format on the DateTimePicker and everything is working.
Thanks for your reply.
That is a bad solution. The format used to display the dates in the controls should be irrelevant. As kebo said, you should be using parameters. It would solve this problem and avoid many others. I suggest that you follow the Blog link in my signature below and check out my post on Parameters In ADO.NET to learn why and how to insert values into SQL code the proper way.Quote:
Originally Posted by WestSideRailways
Hi there Jim,
so you are saying that I should do something like this....
vb.net Code:
If I understand you correctly, I can delete the format off the DateTimePicker and SQL will not "Spit the dummy" :-)
depends - what is M12S and M12F? If they are date time pickers, then you should be using M12S.Value and M12F.Value ... because that's what you're really after and that will return the value typed correctly as a Date... I think what had been causing the issue was that by default the DTP will return the .ToString which is just that, a string, which isn't what you really want. You want to keep it as a Date.
This concerns me though...
or did you mean 2013-12-16?Quote:
and it should have been 20131216.
-tg
Yes, that's the idea. It's a superior aproach for a number of reasons but the most important two are that it means you don't have to worry about formatting (which is the issue you hit) and it protects you from injection attacks (which you didn't hit but really wouldn't want to).
If you didn't do so already it would still be well worth reading JM's blog. It will take you through some of the issues that surround the use of parms and give you a depper understanding (it's not just about the syntax).
edit>crossed over with TG. Depending on the DB settings 20131216 might have been accepted as a known date format. Hard to know for sure but I'd have expected the problems to be alot more obvious if it wasn't. Good catch on the use of .Value - I missed that:blush:. Are you sure .Value isn't the default propetry, though? I'm not in a position to check but I thought it was. (Regardless .Value is more explicit so is a better aproach)
To be honest, I'm only fairly certain as the .ToString is the default for most object - think about if you bind a LisT(Of DataRows) to a listbox, with no other info, your listbox get filled with "System.Data.DataRow object" because that's the default text for the .ToString of a DataRow...
but then again, I strictly adhere to Option Explicit and Strict both being on and try to not rely any of the wishy-washiness of VB, so I may not be entirely right.
I do know that there is a difference between .Text and .Value of the DTP... .Text will give you the date in string using the currently set format of the DTP - maybe that's what the default property is, .Text and not .ToString - while the .Value will give you an actual DateTime typed value. Also, considering that the OP simply stuck in the DTP by name only, and all he did was change the format and "it works" suggests that it does in fact return a string type and not a date type.
"Depending on the DB settings 20131216 might have been accepted as a known date format." -- I considered that but in the first post, the use of the DTP failed, while the second succeeded, which was in yyyy-mm-dd format...
-tg
HI guys,
thanks for your reply's...
vb.net Code:
this is part of the button 1.click
I use M12S & M12F as start of the month and finish of the month.
the user selects the month and the year, then the start of the month get put into a DTP and the end of the month get put into another DTP and the user is asked to select a week, as long as M12S is < M12F the program goes and asks the SQl-DB for any data within that week and displays the data or says that there is no data.
Hope this makes thing clearer.
and before you ask, the textboxes are there to make sure the correct values are in the correct areas. I like to make sure of that, once I know this, I delete them. :-)
when I have time I will delete the format for the DTP's and see if the program still works. !
So what I'm reading is "the dates in the database aren't really dates but strings" ... that's the only way that code makes any kind of sense.
-tg
in ssms the data types are as follows :-
Weekday = varchar(50)
Fueldate = date
Fueltime = time(7)
Location = varchar(50)
Pumpnum = int
Total_Liters = float
Price_a_Liter = float
Total_Dollars = float
This was the best combination that I could find within the limitations of
2015-06-10 17:17:00.34 Server Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)
Jul 11 2014 16:11:50
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
I think that DateTime is stored like this:Quote:
Originally Posted by techgnome
So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when I didn't supply the date in my first example. SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.
SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes.
More here:
http://sqlmag.com/sql-server/solving-datetime-mystery
Mostly correct... but to pass "201507010" as a date is dubious, that's the part that concerns me. because the only time I see THAT work is when the field is a string... personally I'm not sure what the problem is... it's a date - the OP has confirmed that - so then as long as it is treated as a date and you don't give a fart about the format, it should work just like this:
It should look familiar... because it's the code from post #5 ... which was almost there.... only two lines needed to be changed.Code:im QueryString As String = "select * from petrol_table" &
" where fueldate >= @sdate" &
" and fueldate < @fdate"
Dim ConString As String
Dim Conn As SqlConnection
Dim sqlCmd As SqlCommand
Dim DAP As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
ConString = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=Petrol_Costs;Integrated Security=True;"
Conn = New SqlConnection(ConString)
sqlCmd = New SqlCommand(QueryString, Conn)
sqlCmd.Parameters.AddWithValue("@sdate", DateTimePicker1.Value)
sqlCmd.Parameters.AddWithValue("@fdate", DateTimePicker2.Value)
Conn.Open()
DAP.SelectCommand = sqlCmd
DAP.Fill(ds, "PetrolCosts")
' dt.Rows.Clear()
dt = ds.Tables("PetrolCosts")
DAP.Dispose()
sqlCmd.Dispose()
Conn.Close()
And then some how by post 9 we start moving the date from the DTP (which as it turns out is DateTimePicker1 & 2, not DTP1 & 2) into strings (DTP1 & DTP2) to textboxes to strings... to check the format? Urgh... See, that's where it goes to ship... the second that you start thinking about the format in cases like this (there are times when it is important, I will grant you that, but this is not one of them) that should be a red flag. Actually it should be a red flag with large flashing neon signs and huge fireworks. It means you're about to stop treating the date as a date.
I don't know why people get so hung up on the format when it doesn't matter... it's the VALUE that counts. If it's a date, treat it like one and don't do any conversions on it. Dates do not have a format. Formats are for display. Display means the user sees it... This is why the DTP has TWO properties... .Text and .Value ... one returns a string (.Text) and the other returns a DateTime object (.Value) ... one is the formatted string representation of the date, the other is the value of the date.
-tg
[QUOTE=It should look familiar... because it's the code from post #5 ... which was almost there.... only two lines needed to be changed.
And then some how by post 9 we start moving the date from the DTP (which as it turns out is DateTimePicker1 & 2, not DTP1 & 2) into strings (DTP1 & DTP2) to textboxes to strings... to check the format? Urgh... See, that's where it goes to ship... the second that you start thinking about the format in cases like this (there are times when it is important, I will grant you that, but this is not one of them) that should be a red flag. Actually it should be a red flag with large flashing neon signs and huge fireworks. It means you're about to stop treating the date as a date.
I don't know why people get so hung up on the format when it doesn't matter... it's the VALUE that counts. If it's a date, treat it like one and don't do any conversions on it. Dates do not have a format. Formats are for display. Display means the user sees it... This is why the DTP has TWO properties... .Text and .Value ... one returns a string (.Text) and the other returns a DateTime object (.Value) ... one is the formatted string representation of the date, the other is the value of the date.
-tg[/QUOTE]
Thanks very much for the correcting me on my coding skills. :-)
This properly why I have been having so much problems with the date as I have been thinking of it as a "string" !!
will now go through my code and change it.
have now ,as you can see by the following code, have changed my code....
vb.net Code:
and it works like a charm..hehehehe
Interesting how that works, huh?
One more thing... this:
should be this:Code:TextBox1.Text = DateTimePicker1.Text
TextBox2.Text = DateTimePicker2.Text
If TextBox1.Text > TextBox2.Text Then
again, you want to treat the dates as dates, not strings. String comparison for greater than and less than is far different from dates.Code:If DatePicker1.Value > DateTimePicker2.Value Then
-tg
Thanks for the tip.
how would i go about using[pun intended :-) ] "parameters" in the following code.
vb.net Code:
You've already been provided with plenty of information so my advice would be to put it to use. Don't wait for us to write your code for you. Think about it and make an attempt. If it doesn't work, THAT would be the time to ask for help. You would post what you tried and then we can then help you fix that, pointing out where YOU have gone wrong in what YOU have done.Quote:
Originally Posted by WestSideRailways
Well, there's also a lot of problems with that code... first is using an entire datatable for one solitary single value... that's a huge waste. If all you're getting back is a singe value, you should be using .ExecuteScalar and get just that one value. However, that rolls into the second point: it's the same SQL over and over and over and over, the only thing different is your aggregation. Bleh. That's also pointless. Just write ONE SINGLE SQL statement with all of the aggregations in it... THEN it's worth returning into ONE DATATABLE.
so even before parameters, I would be rewriting it into one sql statement and one datatable.
-tg
Hi techgnome,
First off, I do not expect other people like your self to write my code for me.
What I would like is recommendations, as to how to either make my code better or a hint as to what direction to go too.
Thank You for your last reply, I am not just getting back a single value. I am getting back 3 values for each Column(ie... AVG,MIN,MAX) which are going into a Table of their own and then being displayed.
Anyway I will think about what you have suggested and see what I can come up with. :-)
But you've already been given recommendations on how to use parameters so why do you need to ask again? Use what you've already been provided with and then, if it doesn't work, ask what you've done wrong.Quote:
Originally Posted by WestSideRailways
hmmm.
it seems we are talking/thinking differently, I ask about sqlcommand parameters before, and this time about how to add parameters to sqldataadapter .
as far as I am concerned they are too different things. If I am wrong please tell me and direct me how to do want I want or tell that it can not be done....etc....
at the moment I am finding out how to do what techgnome suggested in post #18.
A sql adapter is based on a sql select command. That sql statement you're passing in when you create the adapter is being used to create the command behind the scenes. So you've basically got a couple of choices:-Quote:
I ask about sqlcommand parameters before, and this time about how to add parameters to sqldataadapter
1. You can create the adapter as you are but pass in a select statement containg @parm rather than string concatenation. Then you can ad parameter to the underlying commance by using Adapter.SelectCommand.Parameters.AddWithValue (or similar)
2. You can create the adapter without passing in a select statement, create the sql command explicitely complete with parameters (as you've done previously in this thread) and then set it as the SelectCommand of the adapter.
Basically, your adapter has a command, that command has parameters. Does that clarify it?
THANK YOU VERY MUCH FunkyDexter :-)Quote:
Originally Posted by FunkyDexter
I did not know that you could do that. (Adapter.SelectCommand.Parameters.AddWithValue(...)
SELECT * from "table name"
This syntax about select all data from table.
message erased , I found out what the problem was....:blush:
Well that's good because I don't think I offered to do so. I simply stated that I would rewrite it differently. To be fair I would have written it differently from the start. But I wasn't offering to do so.Quote:
Originally Posted by WestSideRailways
Right, and I thought that's what I was doing - offering recommendations on how to write it better. Part of that would have included parameters, but even before you get there, I felt there was some optimization and coding that could be cleaned up. I could have just simply answered the question directly and left it at that. But I chose to go a step further.Quote:
Originally Posted by WestSideRailways
Actually, no you weren't...Quote:
Originally Posted by WestSideRailways
That's a single value... the MAX in this case... that's what annoyed me. You were making THREE trips to the database getting just one single value each time. There was no need for that. You can and should do it all in one shot.Code:Using AD As SqlDataAdapter = New SqlDataAdapter("select MAX(total_liters) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
AD.Fill(ds.Tables("Table8"))
w/o knowing the data, we'd be just guessing... Something that might help though is to give your columns some names. That way when you then look at the data you can see what you have:Quote:
Originally Posted by WestSideRailways
vb.net Code:
You can also get your dollar aggregates at the same time
vb.net Code:
Since you're not grouping by anything, and working across all of your data (within the where clause) this should work.
-tg
hehehhe
You answer the post that I have erased .....
and if you look at my post #18, you will see that I am getting 3 values from each columns ie... AVG,MIN,MAX
which I have combined in this line of code to try it out.
vb.net Code:
I was not quick enough to erase my post.:eek:
One thing you might want to remember, is that NOT everyone does coding for a living.
this is a hobby for me, so with each question I ask, I am learning, some days I learn better then others. :p
Don't delete posts... it disrupts the conversation and has a habit of making the rest of us look like the south end of a north-bound horse... We had (have? not sure if he's still around or not) one poster who will ask a bunch of questions, have a little conversation, once he gets his answers though, he goes and edits ALL of his posts in ALL of his threads leaving just "Deleted" ... it's like listening to someone's conversation on the phone where you only hear the one side. - it's annoying to those of us that took the time to participate (I also feel like it's a sign of disprespect) and worst of all, in 3 months when someone who has the same problem stumbles on the thread... editing is fine, removing a piece for clarifgication or to clean something up is fine... but try to not delete the whole post...Quote:
Originally Posted by WestSideRailways
At any rate, the code you posted looks the same as the one you deleted.... OK, so you combined the three into a single select - YAY! Now you're not getting just a single value. But you still need to alias your columns... and you can also still add in the other three values.
IF you're still having issues though, we need more info... not necessarily more code... just more info. In my signature, there's a link "The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *" - it contains some serious tips on how to get help around here.
And yes, I know not everyone here does this professionally... at the same time some of us do, and some of us take it very seriously. most days. some days. Ok, so sometimes it goes hour by hour. At the same time recognize that some of us do do (hehehe, I said do-do --- see? there's times when things aren't so serious. Shuddup Beavis.) this for a living, so some of the advice we give comes from experience. You want to be a better coder? We want you to be a better coder. So that's where some of this comes from. One of the interesting things I heard was - Code like your life depends on it. Code like you're programming your own pacemaker. - I'm now trying to get myself into shape. I've seen some of hte coding around here. :eep: I don't want a pacemaker. I've seen some of the code around here. There's some scary shnip running loose out there. :eep:
At least you're showing progress... :) ... I've seen people even after 50+ posts they still don't quite get it. No matter how slowly you type to demonstrate the concept.
Although, I'll admit, it's been a while since I've felt the need to trout-slap someone... but I did break out the threat of a thousand lashing from a wet noodle just last week.
-tg
ok consider myself (trout-slaped) :blush:
I seem to remember TG once trout-slapped me for not using parameters. Or it may have been JM, I'm not sure. Either way, I was pretty grateful once I'd had a chance to wash the fish slime off.
That's something that's worth rememberng on a professional forum. We'll often offer up advice you weren't expecting and maybe aren't ready to take on board yet but that doesn't mean it's bad advice. Even when you get the answer to your immediate problem, it's worth taking a bit of time to go back over the advice people have given to see if there's anything extra you can learn.
I try to do that most of the time, I re-read what answers have been given and then go surfing to find out more......
Evening fellow Coders...:wave:
I now have this streamlined bit of code to show you...
[hightlight=vb.net] Using con As New SqlConnection
con.ConnectionString = constr1
con.Open()
'========================
'Get Average Price@Liter
'=========================
Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(Price_a_liter),MIN(Price_a_Liter),MAX(Price_a_Liter) from petrol_table where fueldate >= @SPLdate and fueldate < @FPLdate", con)
AD.SelectCommand.Parameters.AddWithValue("@SPLdate", M12S)
AD.SelectCommand.Parameters.AddWithValue("@FPLdate", M12F)
AD.Fill(ds.Tables("Table1"))
End Using
'==========================
'Get Average Total_Liters
'==========================
Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(total_liters),MIN(total_liters),MAX(total_liters) from petrol_table where fueldate >= @STLdate and fueldate < @FTLdate", con)
AD.SelectCommand.Parameters.AddWithValue("@STLdate", M12S)
AD.SelectCommand.Parameters.AddWithValue("@FTLdate", M12F)
AD.Fill(ds.Tables("Table2"))
End Using
'==========================
'Get Average Total_Dollars
'==========================
Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(total_dollars), MIN(total_dollars),MAX(total_dollars) from petrol_table where fueldate >= @STDdate and fueldate < @FTDdate", con)
AD.SelectCommand.Parameters.AddWithValue("@STDdate", M12S)
AD.SelectCommand.Parameters.AddWithValue("@FTDdate", M12F)
AD.Fill(ds.Tables("Table3"))
End Using
con.Close()
End Using[/highlight]
9 sets of code reduced to 3 sets..:-)
does that get me a slap on the back........?
One line, one adaptor, one table.vb.net Code:
Personally I wouldn't even bother with the dataset... I'd just create a datatable object and fill it
vb.net Code:
-tg
Hi techgnome,
excellent reply, but a little cluttered . looks like a page in a book without paragraphs. :-)
like the idea of just a datatable object.