4 Replies - 22975 Views - Last Post: 24 November 2007 - 01:01 PM Rate Topic: -----

#1 juniorm  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 17-August 07

Using an SQL statement in VB6

Post icon  Posted 12 November 2007 - 03:23 AM

Im trying to manipulate an Access database using an SQL statement but its giving me errors. Ive used this with data inputed and it works but if i try to import text inputted by a user from a textbox it refuses. I dont know whe im missing it. Im using the statement below, please correct me and if possible assist me with better statements, Im using the following code

Set helpdeskRS = employeeDB.OpenRecordset("SELECT * FROM HelpDesk WHERE Start_Time BETWEEN " & txtStartDate & " AND " & txtEndDate & "")

Textbox txtStartDate and txtEndDate are on the form. The filed name is Start_Time and is in the database.

Pliz help

This post has been edited by PsychoCoder: 12 November 2007 - 05:36 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Using an SQL statement in VB6

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Using an SQL statement in VB6

Posted 12 November 2007 - 05:35 AM

First, you have to execute the OpenRecordset command with your query, then set your variable to a field in the returned Recordset, but I need to see more code than that to determine what your error is, but I do know you cannot set a variable to an OpenRecordSet call.

But to be sure this is your ownly error, post more than a single line of code please :)
Was This Post Helpful? 0
  • +
  • -

#3 juniorm  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 17-August 07

Re: Using an SQL statement in VB6

Posted 12 November 2007 - 06:50 AM

Ok. I understand but how do i query a recorset? I'l give u the whole module i coded. Its using a flexgrid, flexViewPeriod. Basically a user has to specify a range of dates in dd/mm/yyyy format then it fills the flex grid with the records lying in that range of date. Here is the rest of the code;

Private Sub cmdView_Click()
Dim helpdeskRS As Recordset
Dim employeeDB As Database
Set employeeDB = OpenDatabase("C:\Documents and Settings\mmoyo\Desktop\vbHelpdesk\employee.mdb")
Set helpdeskRS = employeeDB.OpenRecordset("SELECT * FROM HelpDesk WHERE  Start_Time  BETWEEN " & txtStartDate & " AND " & txtEndDate & "")

Dim rowCount As Integer

flexViewPeriod.Row = 0
flexViewPeriod.ColWidth(0) = 550
flexViewPeriod.ColWidth(1) = 1700
flexViewPeriod.ColWidth(2) = 1000
flexViewPeriod.ColWidth(3) = 1700
flexViewPeriod.ColWidth(4) = 700
flexViewPeriod.ColWidth(5) = 800
flexViewPeriod.ColWidth(6) = 2000
flexViewPeriod.ColWidth(7) = 980
flexViewPeriod.ColWidth(8) = 700
flexViewPeriod.ColWidth(9) = 1000
flexViewPeriod.ColWidth(10) = 1000
flexViewPeriod.ColWidth(11) = 1000

flexViewPeriod.Col = 0
flexViewPeriod.Text = "Job ID"
flexViewPeriod.Col = 1
flexViewPeriod.Text = "Caller"
flexViewPeriod.Col = 2
flexViewPeriod.Text = "Contact"
flexViewPeriod.Col = 3
flexViewPeriod.Text = "Location"
flexViewPeriod.Col = 4
flexViewPeriod.Text = "Priority"
flexViewPeriod.Col = 5
flexViewPeriod.Text = "Category"
flexViewPeriod.Col = 6
flexViewPeriod.Text = "Description"
flexViewPeriod.Col = 7
flexViewPeriod.Text = "Assigned To"
flexViewPeriod.Col = 8
flexViewPeriod.Text = "Status"
flexViewPeriod.Col = 9
flexViewPeriod.Text = "Start Time"
flexViewPeriod.Col = 10
flexViewPeriod.Text = "Completion Time"
flexViewPeriod.Col = 11
flexViewPeriod.Text = "Turn Around Time"

rowCount = 1
If helpdeskRS.EOF Then
	 MsgBox "No Calls are available in database", vbCritical, "Error"
End If
	helpdeskRS.MoveFirst
	While Not helpdeskRS.EOF
		flexViewPeriod.Row = rowCount
		
		flexViewPeriod.Col = 0
		flexViewPeriod.Text = helpdeskRS!Job_id
		flexViewPeriod.Col = 1
		flexViewPeriod.Text = helpdeskRS!Caller
		flexViewPeriod.Col = 2
		flexViewPeriod.Text = helpdeskRS!Contact
		flexViewPeriod.Col = 3
		flexViewPeriod.Text = helpdeskRS!Location
		flexViewPeriod.Col = 4
		flexViewPeriod.Text = helpdeskRS!Priority
		flexViewPeriod.Col = 5
		flexViewPeriod.Text = helpdeskRS!Category
		flexViewPeriod.Col = 6
		flexViewPeriod.Text = helpdeskRS!Description
		flexViewPeriod.Col = 7
		flexViewPeriod.Text = helpdeskRS!assigned_To
		flexViewPeriod.Col = 8
		flexViewPeriod.Text = helpdeskRS!Status
		flexViewPeriod.Col = 9
		flexViewPeriod.Text = helpdeskRS!Start_Time
		flexViewPeriod.Col = 10
		flexViewPeriod.Text = helpdeskRS!Completion_Time
		flexViewPeriod.Col = 11
		flexViewPeriod.Text = helpdeskRS!Turn_Around_Time
		
		rowCount = rowCount + 1
		flexViewPeriod.Rows = flexViewPeriod.Rows + 1
		helpdeskRS.MoveNext
	Wend

End Sub

MOD EDIT : Please use code tags like this: :code:
Was This Post Helpful? 0
  • +
  • -

#4 nealgabriel45  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 46
  • Joined: 22-October 07

Re: Using an SQL statement in VB6

Posted 20 November 2007 - 05:37 AM

Dim rsTemp as New ADODB.Recordset
Dim strQuery as String
strQuery = "Select * from Temp where something = '" & something.text & "'"
rsTemp.Open strQuery,g_objdbConn, adOpenStatic, adLockOptimistic

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

Where g_objdbconn is the connection object

Just Initialize the connection Anywhere in the Project as a Public object. .

Do search for simple Programs and read it carefully before a post
Was This Post Helpful? 0
  • +
  • -

#5 kwikone  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 25-October 07

Re: Using an SQL statement in VB6

Posted 24 November 2007 - 01:01 PM

First off where you have

View Postjuniorm, on 12 Nov, 2007 - 08:50 AM, said:

Set helpdeskRS = employeeDB.OpenRecordset("SELECT * FROM HelpDesk WHERE Start_Time BETWEEN " & txtStartDate & " AND " & txtEndDate & "")


You don't indicate the following...
Definition of Start_Time: is this a timestamp, date, time field in the database? or what? :?:
Do txtStartDate and txtEndDate have quotes in them (because they do need to be quoted in the sql select).
For example: if Start_time is a date/time field and txtStartDate is 1-1-2007 and txtEndDate is 10-10-2007 then the following would occur.
A) the sql would not execute and would get an SQL syntax error since there were no quotes around the dates.
But, if the start and end dates were like "1-1-2007" (with the quotes) then it still would not work since it would be trying to do a between comparing a date/time field value with a string. The resolution is (for MS for either access or ado to sql server or access; this does not apply for other databases such as mySQL) to enclose (quote) dates within #'s (ie. #1-1-2007#).

Just a comment, you really should put your database object as global within the application so that you do not keep opening and closing it all the time, plus the recordsets used for the forms controls should be global to the form and not within a sub since as soon as the sub exits the recordset will disappear also (unless you have it declared as static)

This post has been edited by kwikone: 24 November 2007 - 01:04 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1