Simple SQL Select Statement

[dataman86]

Hi everyone,

I have a SQL Select statement I am trying to write to pull some records from a table. I pretty much have no trouble until I get to the WHERE clause. I have fields ID, Issue, Resolution, and DateTimeAdded. I want to pull records that are incomplete less than 6 days old. I have the following function that I have written to subtract 6 days from today's date.

I have this coding in my Visual Basic Form:

Me.txtDate.Text = Cstr(DateAdd("d", -6, Today()))


I need to pass the Me.txtDate.txt variable to a Tsequel Statement which I am very green in.

I need to place the passed variable name into a Where Clause somehow. I know this much.


I was trying to write the TSEQUEl statement like this:


Select ID, Issue, Resolution, DateTimeAdded,
FROM CallIssueMain
WHERE User=UserName and DateTimeAdded Between txtDate and
Today.

The function has the date: 08/21/2009 based on 6 days from
todays date 08/27/2009. So the 08/21/2009 is passed to the
TSequel statement to pull records. But I do noy know how to write it correct to pull the records.

I need to pull records between 08/21/2009 and 08/27/2009 from the table. How can I write the Select statement to do this and have the right syntax. I know you have to break out the strings somehow to write it correctly so as to run.

any help is appreciated.

Dataman86

 

[markros]

You need to pass parameters to your query or you can do
in T-SQL

declare @EndDate smalldatetime, @StartDate smalldatetime

set @StartDate = dateadd(dd,-6,getdate())
set @EndDate = getdate()

Select ID, Issue, Resolution, DateTimeAdded,FROM CallIssueMainWHERE User= @UserName and Date between @StartDate and @EndDate

 

[dataman86]

Markros,

I am getting an error message that the Declare statement is not supported when I try to insert this code in the Query builder. Do you place the Declare @Enddate smalldatetime, @Startdate smalldatetime along with the set statements in the query builder or the Visual Basic coding window on my form?

DataMan86

 

[compuveg]

I don't know why you are in query builder unless you just want to test your code. The declare statement should definitely be supported, regardless.

I'd expected you were building a SQL query in VB then executing it. Something like

conn.open "connection string"
set myRS = conn.execute"declare @EndDate smalldatetime, @StartDate smalldatetime
set @StartDate = dateadd(dd,-6,getdate())
set @EndDate = getdate()
Select ID, Issue, Resolution, DateTimeAdded,FROM CallIssueMainWHERE User= @UserName and Date between @StartDate and @EndDate"

if myRS.eof then
'do whatever
else
myVar = myRS("myCount")
end if

 

[dataman86]

CompuVeg,

First of all, I am new to SQl and very green in VB as well. I am about 10% and got 90% to learn. I can do a few things, but not many. I am trying to learn the syntax but have a long way to go.

I have created my database with a MainCallIssue Table which I am trying to use the query analyzer to help me get some records quickly. The table has only 63 records that I have added with dateTimeAdded dates showing up in the field. I need to query these dates and find all records with DateTimeAdded less than 6 days. The coding you have written works, but I just do not know where to put the coding for it to work.

With me being new could you explain yourself a little better. I think you assume I know things that I do not. I am really starting out. I can read your codng and understand what it is doing, but placing the coding in the right place is my problem to get it to work.

I am just trying to use a VB form in Visual Studio 2005 and I want to pull the records in a Gridview when the form runs.
I am trying to configure the query inside the Grid View where I have all of fields bound to the database.

I need to know where to put your coding to do this.
When my form runs in VB I want the grid view to load with the records queried based on your statements.
Dataman86

 

[compuveg]

I'm fairly strong in SQL, but haven't actually coded Visual Basic since V6 when I was in my undergrad program in '99. A little Vb Script in server side web pages, but nothing like what you're doing.

Here's the page that I found detailing using an ADODB data source...

http://visualbasic.ittoolbox.com/documents/executing-sql-statements-from-visual-basic-12121

But that doesn't detail the magic of creating that connection string, which is what's missing there.

Here's a much more in-depth article, from the best reference on the subject, IMHO, Microsoft... They do make the server and the programming language. You definitely can't disregard them because Vista sux.

Creating Connections to SQL Server

http://msdn.microsoft.com/en-us/library/s4yys16a(VS.71).aspx?ppud=4

 

[dataman86]

Markros,

I have placed the TSequel Coding in and getting an error message whenI try to execute.

ERROR PARSING QUERY.[TOKEN LINE NUMBER = 1, TOKEN LINE OFFSET = 1 TOKEN IN ERROR = DECLARE]

ERROR SOURCE: SQL SERVER COMPACT ADO.NET PROVIDER IS ERROR SOURCE.

 

[markros]

Ok, forget about declare statements, use calculations directly

Select ID, Issue, Resolution, DateTimeAdded,FROM CallIssueMainWHERE User= @UserName and Date between dateadd(dd,-6,getdate()) and getdate()