|
First, stop using string concatentation to build your SQL query. Second, Google for "vb.net parameterized sql query" for examples on how to do this using parameter objects instead of building a string query.
|
|
|
|
|
Thank you for answering so quick.
Can you please tell me what you mean with string concatentation and can you show me a small example to do it better or just tell me what i have to look for. Or is it related to parameterized sql query?
Thank's again!
|
|
|
|
|
String concatentation is appending smaller string together to build a bigger one:
Dim x As String = "Part 1" & " Part 2"
This is as far as I'm going to go to give you examples: Click Me[^]
It's up to you to do the work to teach yourself something new.
|
|
|
|
|
Kurdy86 wrote: Its working but like i said, my results are not the one that i want.
You have a special concept of "working" then.
Here are some comments:
1.
you didn't provide some essential information, such as what database you are using, what the field types are, etc.
2.
if (I can't tell) you were to store dates as strings, that would not be smart as it makes things overly complex; a date in string format depends on culture. The general advice is to use the most specialized field type that matches your purpose.
3.
Your SQL statement is using whatever the user types in some TextBoxes; that is very dangerous. Read up on SQL Injection attacks, then start doing it the proper way:
- get the user data from the TextBoxes into properly typed variables (using SomeType.TryParse), which forces you to validate the input;
- construct an SQLCommand and use SQLParameter instances to provide variable data, such as the begin and end date.
It takes some more code than you have right now, however it is safe and won't suffer from regional settings problems which you may have right now.
[ADDED]
4.
As others have pointed out, you probably can't just have an OR operator in a complex WHERE clause without some parentheses; when in doubt, or just to make things absolutely clear, add parentheses and format your SQL statement so it reads more easily.
[/ADDED]
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
modified on Tuesday, April 26, 2011 11:11 AM
|
|
|
|
|
Thank you,
I have a access database. My startdate & enddate are date/time field types.
I will try to find more information about to use my sql query different.
Thank's
|
|
|
|
|
Thank you,
Can you please check my last post?
I changed my code.
Thank you..
|
|
|
|
|
Wrong order perhaps?
SELECT *
FROM [Posts]
WHERE [firstname] LIKE '%john%'
AND [address] LIKE '%street%'
AND (startdate BETWEEN #1-1-2007# AND #1-1-2008# OR enddate BETWEEN #1-1-2007# AND #1-1-2008#) And yes, the OleDbParameter would make it more readable and a bit safer
I are Troll
|
|
|
|
|
I suspect you are getting TOO much data because of your 'OR' statement.
This is a SQL question, not a VB question, but, here is some help.
SELECT * FROM Posts
WHERE FirstName LIKE '...'
AND Address LIKE '...'
AND ( StartDate between '...' AND '...'
or EndDate between '...' and '...')
Hope that helps...
Tim
|
|
|
|
|
Hi Guys,
I changed my code and its now like this, but i still get the wrong results.
Dim Sqlstring As String = "SELECT startdate,enddate FROM Posts WHERE ((startdate between ? and ?) and (enddate between ? and ?))"
DataGrid.DataSource = Nothing
Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
OleDbConn.Open()
Dim MyDataSet As DataSet = New DataSet()
Dim FillAdapter As OleDbDataAdapter = New OleDbDataAdapter()
Dim oCmd As New OleDbCommand(Sqlstring, OleDbConn)
FillAdapter.SelectCommand = oCmd
oCmd.Parameters.Add("startdate", startdate.Text)
oCmd.Parameters.Add("startdate", enddate.Text)
oCmd.Parameters.Add("enddate", startdate.Text)
oCmd.Parameters.Add("enddate", enddate.Text)
FillAdapter.Fill(MyDataSet)
DataGrid.DataSource = MyDataSet.Tables(0)
FillAdapter.Dispose()
MyDataSet.Dispose()
OleDbConn.Close()
OleDbConn.Dispose()
When i search between
26-4-2011 and 30-4-2011
i get 2 result with:
startdate - enddate
26-4-2011 27-4-2011
26-4-2011 28-4-2011
when i search between
26-4-2011 and 1-5-2011
i get nothing...
when i search between
26-4-2011 and 18-5-2011
i get
26-4-2011 27-4-2011
26-4-2011 28-4-2011
26-4-2011 1-5-2011
15-5-2011 18-5-2011
|
|
|
|
|
Your code has improved already, however IMO OleDbCommand.Parameters.Add isn't strongly typed, all it does is add strings to some collection.
Not absolutely sure what exactly goes wrong, however my best guess is this:
"26-4-2011' is recognized as "dd-MM-yyyy" and works fine (it does not fit "MM-dd-yyyy"), however "1-5-2011" is recognized as "MM-dd-yyyy", which takes precedence over "dd-MM-yyyy". Regional settings are fooling you.
My suggestion was and is:
- first capture the user data, and turn it into variables of appropriate type; that implies validation;
- then use those variables when adding parameters.
Could look like:
bool allOK=true;
DateTime startDate=DateTime.MinValue;
if (!DateTime.TryParse(tbStartdate.Text, "dd-MM-yyyy', null, out startDate)) {
allOK=false;
log("startDate is not valid, should be dd-MM-yyyy format");
}
...
if (allOK) {
// construct and execute SQL query
}
Note: by explicitly specifying the format, you get independent of the system settings (if that is what you want, your users might want to be in charge).
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
modified on Wednesday, April 27, 2011 9:23 AM
|
|
|
|
|
Thank you,
I will check it and try to make it work.
Thanks again!
|
|
|
|
|
Hi;
I am trying to use MS Word as a reporting tool thru VB6. I have a problem
with getting the 'superscripts and subscripts'. What I have so far is not
good.
For example , 'y=3X^2+Xv+2X, with '^' being a 'superscript' and 'v' and
being a 'subscript'
send it to MS Word from vb6.
I would be grateful if anyone has a 'Micro to do this' and can post it or
send it to me -or can direct me to a website to download something that
will help me.
I would also be interested in third party tools at a reasonable price if it
can be used
from the vb6 not like MS Equation editor that I have to cut and paste the
image.
Regards,
M. Kol
mkol
|
|
|
|
|
|
thatraja
--------------------------------------------------------------------------------
Thank you for reading and answering my posting.
I hope you can come up with something.
Regards,
M.Kol
|
|
|
|
|
Hi,
I need help to draw a line graph in VB6 by using picture box. I have one senzor for light intesity and from this senzor i get Y value for my graph (in lux). X value for my diagram is a time. I need to draw 24-hour diagram of light intesity. Someting like on the link below.
http://img856.imageshack.us/i/dijagramosvjetljenja.jpg/
I dont konow how to draw x and y axes in pisture box, and how to my values (x-time, y-light intesity) plot in picture box.
Thanks
|
|
|
|
|
|
in my project, i need a lot of different formulas
so there is no standard formula and parameter
the parameter will write on textbox, the formula in richtextbox
example:
formula 1
Parameter = test1, test2 (so there are 2 parameters)
formula = test1 * test2
formula 2
Parameter = test1, test2, test3
formula = (test1 * test2) / test3
when i execute the formula
i write
strExec = "Dim formula" & vbCrLf
strExec = strExec & "Sub Main (" & parameter & ")" & vbCrLf
strExec = strExec & formula & vbCrLf
strExec = strExec & "End Sub" & vbCrLf
i use VBScript as the language
the problem is, when the run method
msScript.Run("Main", xxx)
the parameter doesn't have a standard value, i may 2 or 3 parameter
how to write in the xxx part?
i have try using array, but it return error
thanks
|
|
|
|
|
aphei wrote: how to write in the xxx part?
i have try using array, but it return error
Like this;
ref myArrayName
I are Troll
|
|
|
|
|
umm.. so, i just write msScript.Run("Main", ref strParam)?
it returns error
name ref is not declare
comma, ')', or a valid expression continuation expected.
do i need to imports anything?
thanks
|
|
|
|
|
Hi,
aphei wrote: msScript.Run("Main", ref strParam)
My apologies, accidentally posted c# syntax. Guess that would be "ByRef" in VB.NET, but I can't try it at the moment.
aphei wrote: do i need to imports anything?
Only the reference to the scriptcontrol, and I think you already have that
I are Troll
|
|
|
|
|
Our company is attempting to roll out Windows 7 (SP1) to new client boxes. There is one very large app that is causing the UAC box to pop up asking for administrative rights. Is there a utility or way to determine what section of code is triggering the UAC to popup? The application is very large and it would be nice to narrow down what area is requiring elevated rights.
Thanks for any pointers or guidance ahead of time.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
There's nothing that would cover all possiblities. Even Microsoft ACT[^] can't do it reliably. But, just in case, you'd best give it a try.
|
|
|
|
|
Hey Dave,
Saw your post Friday but forgot to reply. Yeah, I had tried ACT Friday, but didn't have much luck with it for this particular app. Thanks for the response though.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
No problem. We ran into this issue 2 year ago when we were considering moving from XP to Vista and had 1,000 apps to test. We quickly found out that there's nothing out there that can look at every app and tell you, definitively, which apps were going to work and which were going to fail, let alone how they were going to fail.
There's a few thing to look at. The first is if you're going to enable UAC and at which level.
The next is how your applications are going to react in a more restricted environment and how those problems are going to be mitigated. The results of some of this testing may affect the outcome of turning on UAC!
The third thing to look at is the installers for your apps. Depending on your deployment environment, your apps may not even install correctly.
We found a 40-50% failure rate in our installers alone. Most of those problems were easily resolved by either retiring the app if it was no longer needed, making only simple modifications to the installers, upgrading the app to a newer version, or using App-V to run the app.
|
|
|
|
|
I am creating an application were it will check installed applications(in win xp) compatibility for windows 7.
My application will run on machine running win xp and it will inform user that which installed application will have problems in windows 7 environment.
thank you.
|
|
|
|