Programming Help

Web Development



What's Here?

  • Members: 149,549
  • Replies: 506,687
  • Topics: 79,842
  • Snippets: 2,666
  • Tutorials: 706
  • Total Online: 1,570
  • Members: 81
  • Guests: 1,489

Welcome to Dream.In.Code
Become a VB Expert!

Join 149,549 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 1,570 people online right now. Registration is fast and FREE... Join Now!




Using an SQL statement in VB6

 
 Reply to this topicStart new topic

Using an SQL statement in VB6
juniorm
12 Nov, 2007 - 02:23 AM
Post #1

New D.I.C Head
*

Joined: 17 Aug, 2007
Posts: 15


My Contributions
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 Nov, 2007 - 04:36 AM
User is offlineProfile CardPM
+Quote Post

PsychoCoder
RE: Using An SQL Statement In VB6
12 Nov, 2007 - 04:35 AM
Post #2

using DIC.Core;
Group Icon

Joined: 26 Jul, 2007
Posts: 9,482



Thanked: 161 times
Dream Kudos: 9050
Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net

My Contributions
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 smile.gif
User is online!Profile CardPM
+Quote Post

juniorm
RE: Using An SQL Statement In VB6
12 Nov, 2007 - 05:50 AM
Post #3

New D.I.C Head
*

Joined: 17 Aug, 2007
Posts: 15


My Contributions
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;

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.gif
User is offlineProfile CardPM
+Quote Post

nealgabriel45
RE: Using An SQL Statement In VB6
20 Nov, 2007 - 04:37 AM
Post #4

New D.I.C Head
Group Icon

Joined: 22 Oct, 2007
Posts: 46


Dream Kudos: 50
My Contributions
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
User is offlineProfile CardPM
+Quote Post

kwikone
RE: Using An SQL Statement In VB6
24 Nov, 2007 - 12:01 PM
Post #5

New D.I.C Head
Group Icon

Joined: 25 Oct, 2007
Posts: 10


Dream Kudos: 25
My Contributions
First off where you have
QUOTE(juniorm @ 12 Nov, 2007 - 08:50 AM) *

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? ques.gif
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 Nov, 2007 - 12:04 PM
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 1/7/09 09:42PM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter

Live VB Help!

VB Tutorials

Reference Sheets

VB Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month