follow ft86club on our blog, twitter or facebook.
FT86CLUB
Ft86Club
Speed By Design
Register Garage Members List Calendar Search Today's Posts Mark Forums Read
Old 06-04-2013, 12:10 PM   #1
Trashed675
Senior Member
 
Trashed675's Avatar
 
Join Date: Oct 2012
Drives: 2013 Ultramarine FRS
Location: Grants Pass, OR, USA
Posts: 654
Thanks: 140
Thanked 180 Times in 110 Posts
Mentioned: 7 Post(s)
Tagged: 0 Thread(s)
Garage
Visual Basic and SQL

Does anybody know how to pass a variable from a VB form (datetimepicker) into an SQL query?

Heres what i've got and it doesn't quite work:
SELECT FirstName, LastName, Phone, DateJoined
FROM Members
WHERE (DateJoined < date1)

Bascially i just want to get all the records before the date that user selects from the date time picker that is stored in date1.
__________________
Trashed675 is offline   Reply With Quote
Old 06-04-2013, 12:41 PM   #2
2forme
Senior Member
 
Join Date: Jun 2012
Drives: 2013 Subaru BRZ Limited
Location: MA
Posts: 2,974
Thanks: 972
Thanked 1,552 Times in 843 Posts
Mentioned: 164 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by Trashed675 View Post
Does anybody know how to pass a variable from a VB form (datetimepicker) into an SQL query?

Heres what i've got and it doesn't quite work:
SELECT FirstName, LastName, Phone, DateJoined
FROM Members
WHERE (DateJoined < date1)

Bascially i just want to get all the records before the date that user selects from the date time picker that is stored in date1.
There are a few ways. The "easiest" is to assemble the SQL in a string and execute it with an ADODB command object.

Dim strSQL as string = "SELECT blah FROM blah WHERE blah = " & variable

Alternatively, you could use a stored procedure with a parameter, but will still need an object to interface with it. In most of my applications, I use some form of abstraction to handle all my database functions/needs. Of course choosing your solution should be based on the requirements of the project, scalability, etc. So it's solely your discretion.

Also SQL is a pretty arbitrary description of what database you're trying to run against. Some of the less popular databases (I'm looking at you, Sybase), need the date formatted in a certain way before it will be able to analyze it in a WHERE clause. The VB version also makes a difference.
2forme is offline   Reply With Quote
Old 06-04-2013, 01:32 PM   #3
Spartan65
USMC Veteran Semper Fi
 
Spartan65's Avatar
 
Join Date: Aug 2012
Drives: 2010 WR Blue STi
Location: NM it's part of the US
Posts: 955
Thanks: 801
Thanked 378 Times in 243 Posts
Mentioned: 8 Post(s)
Tagged: 1 Thread(s)
Garage
have you tried
WHERE (getdate() > date1)
__________________
Spartan65 is offline   Reply With Quote
Old 06-04-2013, 01:35 PM   #4
Trashed675
Senior Member
 
Trashed675's Avatar
 
Join Date: Oct 2012
Drives: 2013 Ultramarine FRS
Location: Grants Pass, OR, USA
Posts: 654
Thanks: 140
Thanked 180 Times in 110 Posts
Mentioned: 7 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by 2forme View Post
There are a few ways. The "easiest" is to assemble the SQL in a string and execute it with an ADODB command object.

Dim strSQL as string = "SELECT blah FROM blah WHERE blah = " & variable

Alternatively, you could use a stored procedure with a parameter, but will still need an object to interface with it. In most of my applications, I use some form of abstraction to handle all my database functions/needs. Of course choosing your solution should be based on the requirements of the project, scalability, etc. So it's solely your discretion.

Also SQL is a pretty arbitrary description of what database you're trying to run against. Some of the less popular databases (I'm looking at you, Sybase), need the date formatted in a certain way before it will be able to analyze it in a WHERE clause. The VB version also makes a difference.
Forgive me, but a little hand holding would be greatly appreciated. This is my first term working with SQL(we are only skimming the surface in this course) and i've never used ADODB anything.

Google hasn't given me anything that that really makes sense to me as far as setting up the ADODB object.

I'm using VS 2012.
Code from the form that i'm working on.

Public Class MembersDates
Dim retVal As New Date

Private Sub gtnGo_Click(sender As Object, e As EventArgs) Handles gtnGo.Click
' Me.MembersTableAdapter.Fill(Me.FindMemberDataSet.M embers, txtLastName.Text)
dtpDate.Value = Now()
'Takes the value of whatever is in the datetimepicker
Dim date1 As Date = dtpDate.Value
'2forme's suggestion, not sure how to implement further
Dim strSQL As String = "SELECT FirstName, LastName, Phone, DateJoined FROM Members WHERE " & date1 & " > DateJoined"

'just to see what the output looks like as a string, in a textbox on the form.
txtDate.Text = dtpDate.Value.ToString()

Me.MembersTableAdapter.Fill(Me.DateJoinedDataSet.M embers)
End Sub

Public Function getDate()
'Earlier attempt, obviously didn't work, no longer calling
txtDate.Text = dtpDate.Value.ToString()
retVal = txtDate.Text
Return retVal
End Function

End Class

---------------------------------------------------------------
Attached Images
  
__________________
Trashed675 is offline   Reply With Quote
Old 06-04-2013, 01:37 PM   #5
Trashed675
Senior Member
 
Trashed675's Avatar
 
Join Date: Oct 2012
Drives: 2013 Ultramarine FRS
Location: Grants Pass, OR, USA
Posts: 654
Thanks: 140
Thanked 180 Times in 110 Posts
Mentioned: 7 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by Spartan65 View Post
have you tried
WHERE (getdate() > date1)
Yup, it doesn't seem to understand the date1 variable. If i plug in a static number in the sql, it works fine, but not with a variable.
__________________
Trashed675 is offline   Reply With Quote
Old 06-04-2013, 01:50 PM   #6
2forme
Senior Member
 
Join Date: Jun 2012
Drives: 2013 Subaru BRZ Limited
Location: MA
Posts: 2,974
Thanks: 972
Thanked 1,552 Times in 843 Posts
Mentioned: 164 Post(s)
Tagged: 0 Thread(s)
Garage
Oh, you're using a tableadapter.

http://www.dreamincode.net/forums/to...adapter-query/
2forme is offline   Reply With Quote
The Following User Says Thank You to 2forme For This Useful Post:
Spartan65 (06-04-2013)
Old 06-04-2013, 06:17 PM   #7
FrX
Senior Member
 
Join Date: May 2012
Drives: 2013 Scion FR-S, 1993 Lexus SC300
Location: Houston, TX
Posts: 411
Thanks: 284
Thanked 175 Times in 102 Posts
Mentioned: 5 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by 2forme View Post
Dim strSQL as string = "SELECT blah FROM blah WHERE blah = " & variable




As someone in the security industry. Please, please, please do not do this. While it will likely not matter for a local application, it is a bad habit to get into. Please use parameters and prepared statements.


Sorry...after investigating about the 100th SQL injection caused by this exact method, you tend to get a bit touchy.
FrX is offline   Reply With Quote
Old 06-04-2013, 07:03 PM   #8
2forme
Senior Member
 
Join Date: Jun 2012
Drives: 2013 Subaru BRZ Limited
Location: MA
Posts: 2,974
Thanks: 972
Thanked 1,552 Times in 843 Posts
Mentioned: 164 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by FrX View Post



As someone in the security industry. Please, please, please do not do this. While it will likely not matter for a local application, it is a bad habit to get into. Please use parameters and prepared statements.


Sorry...after investigating about the 100th SQL injection caused by this exact method, you tend to get a bit touchy.
It's for his course LOL. I never use this, as you would see if you quoted the rest of my post.
2forme is offline   Reply With Quote
Old 06-04-2013, 07:59 PM   #9
Trashed675
Senior Member
 
Trashed675's Avatar
 
Join Date: Oct 2012
Drives: 2013 Ultramarine FRS
Location: Grants Pass, OR, USA
Posts: 654
Thanks: 140
Thanked 180 Times in 110 Posts
Mentioned: 7 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by FrX View Post



As someone in the security industry. Please, please, please do not do this. While it will likely not matter for a local application, it is a bad habit to get into. Please use parameters and prepared statements.


Sorry...after investigating about the 100th SQL injection caused by this exact method, you tend to get a bit touchy.
Don't worry. After searching the grungy corners of the internet for the past few days, i've realized this isn't something that people do often. Its just part of an extra credit assignment that i'd like to get done.
__________________
Trashed675 is offline   Reply With Quote
Old 06-10-2013, 11:11 AM   #10
Want.FR-S
Senior Member
 
Want.FR-S's Avatar
 
Join Date: Aug 2010
Drives: 4 Wheels Auto
Location: Canada
Posts: 1,191
Thanks: 251
Thanked 274 Times in 187 Posts
Mentioned: 4 Post(s)
Tagged: 0 Thread(s)
@FrX
Quote:
Originally Posted by FrX View Post


As someone in the security industry. Please, please, please do not do this. While it will likely not matter for a local application, it is a bad habit to get into. Please use parameters and prepared statements.

Sorry...after investigating about the 100th SQL injection caused by this exact method, you tend to get a bit touchy.
What is wrong if one forms a SQL statement in a string and submit that to the database to query and retrieve information? I thought that is the way to interface with a SQL database.

Quote:
Originally Posted by Trashed675 View Post
Don't worry. After searching the grungy corners of the internet for the past few days, i've realized this isn't something that people do often. Its just part of an extra credit assignment that i'd like to get done.
Just by looking at your code, I noticed something in terms of data types. You pass the object *as is* to the string without converting or getting the correct data type of information. For example, *date1* is an object of Date. I am not sure if the default value of a Date object is a string type, but when you concatenate it into a string, you need to ensure it is the string that is interpreted when you form it. You can check it by printing the string to see if VB can get the date as a string for SQL statement.

Hope this helps.
__________________
Quote:
Originally Posted by Winding Road FR-S review
What is astonishing about the FR-S is that it combines the cruising comportment and function of the 128i with the dynamics of the Cayman, or Boxster, or S2000.
Want.FR-S is offline   Reply With Quote
Old 06-10-2013, 12:18 PM   #11
Trashed675
Senior Member
 
Trashed675's Avatar
 
Join Date: Oct 2012
Drives: 2013 Ultramarine FRS
Location: Grants Pass, OR, USA
Posts: 654
Thanks: 140
Thanked 180 Times in 110 Posts
Mentioned: 7 Post(s)
Tagged: 0 Thread(s)
Garage
Quote:
Originally Posted by Want.FR-S View Post
@FrX

Just by looking at your code, I noticed something in terms of data types. You pass the object *as is* to the string without converting or getting the correct data type of information. For example, *date1* is an object of Date. I am not sure if the default value of a Date object is a string type, but when you concatenate it into a string, you need to ensure it is the string that is interpreted when you form it. You can check it by printing the string to see if VB can get the date as a string for SQL statement.

Hope this helps.
I'm sure that has something to do with it. I have tried converting it to a string, a date(short and long), even and Int, all with no luck. It does make me feel a little better that i asked my (OCD) teacher for help on the problem on Wednesday and she has yet to get back to me with a solution.
__________________
Trashed675 is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visual Garage Shark fin antenna EvoXDD Cosmetic Modification (Interior/Exterior/Lighting) 86 08-02-2015 02:20 PM
GC basic coilovers / Cusco Strut bar Evilbrz151 Brakes, Suspension, Chassis 3 03-29-2013 09:35 PM
Question about basic performance mods bdanisi Engine, Exhaust, Transmission 2 01-25-2013 05:40 PM
visual garage shark fin group buy. Mr.Jay Exterior Parts - The Rest 39 07-02-2012 10:09 PM


All times are GMT -4. The time now is 08:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
User Alert System provided by Advanced User Tagging v3.3.0 (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.

Garage vBulletin Plugins by Drive Thru Online, Inc.