How to Applied SQL in VB6 ?

Ask about SQL Date Operation in VB6

Page 1 of 1

8 Replies - 3818 Views - Last Post: 26 November 2010 - 09:12 AM Rate Topic: -----

#1 vbmania  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 23-November 10

How to Applied SQL in VB6 ?

Posted 23 November 2010 - 09:39 AM


How to get the interval number of days leaped between the two date using SQL Statement in VB6 ??

currently i used ADODC Data Control in my project..

i had tried several SQL Date Built-In Function found in w3schools.com but still useless

such not functioning code is ..

Dim dTglNow As Date
Dim commSQL As String

 dTglNow = CDate( Format(Now,"dd-mmm-yy") )

commSQL = "SELECT ID_BUKU, JUDUL_BUKU, TGL_PINJAM, DATEDIFF(dd," & CStr(dTglNow) _  &",TGL_PINJAM) AS [SELANG HARI] FROM PINJAM"

AdocData1.RecordSource = commSQL
AdocData1.Refresh



Finally its viewed the ERROR MESSAGE As : wrong number of arguments used with function in query expression 'DATEDIFF(23-11-10,TGL_PINJAM)'

<please .. urgently requiring the answer :nottalkingtoyou: >




Is This A Good Question/Topic? 0
  • +

Replies To: How to Applied SQL in VB6 ?

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,755
  • Joined: 26-March 09

Re: How to Applied SQL in VB6 ?

Posted 23 November 2010 - 03:41 PM

Have you tried the SQL Query direct in SQL Server or the Database you're using, just to verify that the query itself is OK?

Also try to ensure that the dates in DATEDIFF are wrapped in single quotes (ie.: '23-11-2010')
Was This Post Helpful? 1
  • +
  • -

#3 vbmania  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 23-November 10

Re: How to Applied SQL in VB6 ?

Posted 25 November 2010 - 09:04 AM


no, i rather used "Microsoft.Jet.OLEDB.4.0" as a SQL Emulator, does it originally has a limitation in performing SQL command or should i install MSDE ( Microsoft SQL Desktop Edition ) ?? or other third party SQL Server Management System ?? thanks ..


Was This Post Helpful? 0
  • +
  • -

#4 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,755
  • Joined: 26-March 09

Re: How to Applied SQL in VB6 ?

Posted 25 November 2010 - 09:25 AM

I only asked the question as you haven't said what DB your connecting to, and it's an easy way to test your sql query.

Did you try modifying the Datediff function as I suggested?
Was This Post Helpful? 0
  • +
  • -

#5 vbmania  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 23-November 10

Re: How to Applied SQL in VB6 ?

Posted 25 November 2010 - 10:14 AM


i'm connect to MS. Access 2003 DB, i have tried your suggestion but still not work.. :nottalkingtoyou:



Was This Post Helpful? 0
  • +
  • -

#6 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,755
  • Joined: 26-March 09

Re: How to Applied SQL in VB6 ?

Posted 25 November 2010 - 11:07 AM

Are you still getting the same error??

Put a breakpoint on the line
commSQL = 
and then print this to the immediate window and post it here.

Please try to give as much information as you can when you post back...don't just say it still doesn't work, it doesn't really help at all.
Was This Post Helpful? 0
  • +
  • -

#7 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: How to Applied SQL in VB6 ?

Posted 25 November 2010 - 02:12 PM

check if the two dates have same format
Was This Post Helpful? 0
  • +
  • -

#8 vbmania  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 23-November 10

Re: How to Applied SQL in VB6 ?

Posted 26 November 2010 - 08:58 AM


first of all thanks..
there are two parts of SQL i had used to filter the data that matches the desired criteria,
1. Display the interval number of days that leaped between the two date and had succeeded.
Private Sub Command1_Click()
Dim commSQL As String
Dim dTgl As Date

dTgl = CDate(Format(Now, "dd-mmm-yy"))

commSQL = "SELECT ID_BUKU, JUDUL_BUKU, TGL_PINJAM," & "(#" & CStr(dTgl) & "#" & " - TGL_PINJAM) AS [SELANG HARI] FROM PINJAM"

Adodc1.RecordSource = commSQL

Adodc1.Refresh

Query_Interval CInt(txtAwal), CInt(txtAkhir)

End Sub



2. To display the data that matches the criteria for [SELANG HARI] BETWEEN <n1> AND <n2>
For this still have a problem, and i had done what you've said to place a breakpoint on commSQL =

Sub Query_Interval(ByVal nStart As Integer, ByVal nTo As Integer)
Dim commSQL As String
Dim sDay1 As String, sDay2 As String

sDay1 = Trim(CStr(nStart))

sDay2 = Trim(CStr(nTo))

commSQL = "SELECT ID_BUKU, JUDUL_BUKU, ID_PEL, TGL_PINJAM FROM PINJAM"
commSQL = commSQL & " WHERE [SELANG HARI] BETWEEN " & sDay1 & " AND " & sDay2

Adodc1.RecordSource = commSQL

Adodc1.Refresh

End Sub



Breakpoint on two "commSQL = " has passed successfully, but it stop on the Adodc1.Refresh
it says that " No value given for one or more required parameters ."
and " Method Refresh of IAdodc Failed.



Was This Post Helpful? 0
  • +
  • -

#9 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 299
  • View blog
  • Posts: 1,755
  • Joined: 26-March 09

Re: How to Applied SQL in VB6 ?

Posted 26 November 2010 - 09:12 AM

The reason I asked you to put the breakpoint on the commSQL = line, is so we can see the value of commSQL.

If you break on that line then step over it, go to the immediate window and type "? commSQL" and press enter it will print the value of commSQL to the immediate window. Copy and past that value and post it here please, then we can validate your SQL is correct.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1