Visual Basic and SQL

[Trashed675]

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.

[2forme]

Originally Posted by Trashed675

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.

[Spartan65]

have you tried
WHERE (getdate() > date1)

[Trashed675]

Originally Posted by 2forme

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

---------------------------------------------------------------

[Trashed675]

Originally Posted by Spartan65

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.

[2forme]

Oh, you're using a tableadapter.

http://www.dreamincode.net/forums/to...adapter-query/

[FrX]

Originally Posted by 2forme

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.

[2forme]

Originally Posted by FrX

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.

[Trashed675]

Originally Posted by FrX

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.

[Want.FR-S]

@FrX

Originally Posted by FrX

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.

Originally Posted by Trashed675

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.

[Trashed675]

Originally Posted by Want.FR-S

@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.