VB6 Open RecordSet Help!

[crazystitch626]

Hi po, hingi po sana ako ng advice. Medyo mahaba po ito sana po matulungan niyo po ako.

I'm using VB6 and my database is MS SQL Server.

I'm using VB6 for months na po, nag ccreate ako ng exe na ang goal ng exe ay dapat makakapag create siya ng csv file at ang laman ng csv file ay manggagaling po sa database.

Ngayon po sa pc ko may database ako, i'll call it "DB1".
May exe file na din ako for DB1.
Nakakapag create na ako ng csv file na at ang laman ng csv file ay galing kay DB1.

Pinatest ko po sa friend#1 ko yung DB1 pati yung exe file for DB1 sa PC niya.
Ok din po yung testing, same results po sa pc ko.

Para sure na sure, pinatest ko pa sa isa kong friend#2 gamit ang pc niya.
Same database, same exe.
Habang nag tetest siya sa pc niya, naka encounter siya ng error.

"Run time error 3705: Operation is not allowed when the object is open"

To make sure, bumalik ako sa pc ko. Ini restore ko yung database kung saan may lumabas na error.
This time, sa mismong code ako nag run hindi ko muna ginamit yung exe. Wala naman akong binabago sa code, nakapag create naman ako ng csv file ulit sa pc ko.

Pina restore ko din kay friend#1 yung database na galing kay friend#2, same exe file pa din ang gamit niya. Nakakapag create pa din naman ng csv file.

Nag try po ulit kami kay friend#2, same error pa din po na encounter namin.


From code, nag connect po ako sa database ni friend#2. Ni-run ko po yung code ko po and naka encounter nga po ako ng error.

Private Sub Media()

Dim rs9 As New ADODB.Recordset
Dim generatemedia As String
Dim TSL_CSH1 As String
Dim TSL_TC_CASH1 As String
Dim TSL_VISA1 As String
Dim TSL_TC_VIS1 As String
Dim TSL_MCRD1 As String
Dim TSL_TC_MCD1 As String

TSL_CSH1 = vbCrLf & "Select Sum((ItemAmount+Gratuity)* case when ((FunctionID)=1) then 1 else 0 end * case when ((TransMode)='REG') then 1 else -1 end " _
& vbCrLf & "* case when((TransStatus=' ') OR (TransStatus='S')) then 1 else 0 end " _
& vbCrLf & "* case when ((sdate + stime) >= '" & Format(cdatestartdate, "MM/dd/yyyy ") & Format(cdatestarttime, "hh:mm:ss") & "') " _
& vbCrLf & "and ((sdate + stime) <= '" & Format(cdateenddate, "MM/dd/yyyy ") & Format(cdateendtime, "hh:mm:ss") & "') then 1 else 0 end) AS TSL_CSH,"

TSL_TC_CASH1 = vbCrLf & "Sum(([Quantity])*case when (FunctionID='1') then 1 else 0 end * case when (TransMode='REG') then 1 else -1 end " _
& vbCrLf & "* case when((TransStatus=' ') OR (TransStatus='S')) then 1 else 0 end " _
& vbCrLf & "* case when ((sdate + stime) >= '" & Format(cdatestartdate, "MM/dd/yyyy ") & Format(cdatestarttime, "hh:mm:ss") & "') " _
& vbCrLf & "and ((sdate + stime) <= '" & Format(cdateenddate, "MM/dd/yyyy ") & Format(cdateendtime, "hh:mm:ss") & "') then 1 else 0 end ) AS TSL_TC_CASH,"

TSL_VISA1 = vbCrLf & "Sum((ItemAmount+Gratuity)*case when (FunctionID=2) then 1 else 0 end *case when ((UPPER([ItemName])) like '%VISA%') then 1 else 0 end" _
& vbCrLf & "* case when (TransMode='REG') then 1 else -1 end * case when((TransStatus=' ') OR (TransStatus='S')) then 1 else 0 end " _
& vbCrLf & "* case when ((sdate + stime) >= '" & Format(cdatestartdate, "MM/dd/yyyy ") & Format(cdatestarttime, "hh:mm:ss") & "') " _
& vbCrLf & "and ((sdate + stime) <= '" & Format(cdateenddate, "MM/dd/yyyy ") & Format(cdateendtime, "hh:mm:ss") & "') then 1 else 0 end ) AS TSL_VISA,"

TSL_TC_VIS1 = vbCrLf & "Sum((Quantity)*case when (FunctionID=2) then 1 else 0 end * case when ((UPPER([ItemName])) like '%VISA%') then 1 else 0 end " _
& vbCrLf & "* case when (TransMode='REG') then 1 else -1 end * case when((TransStatus=' ') OR (TransStatus='S')) then 1 else 0 end " _
& vbCrLf & "* case when ((sdate + stime) >= '" & Format(cdatestartdate, "MM/dd/yyyy ") & Format(cdatestarttime, "hh:mm:ss") & "') " _
& vbCrLf & "and ((sdate + stime) <= '" & Format(cdateenddate, "MM/dd/yyyy ") & Format(cdateendtime, "hh:mm:ss") & "') then 1 else 0 end ) AS TSL_TC_VIS,"

TSL_MCRD1 = vbCrLf & "Sum((ItemAmount+Gratuity)*case when (FunctionID=2) then 1 else 0 end *case when ((UPPER([ItemName])) like '%MASTER%') then 1 else 0 end " _
& vbCrLf & "* case when (TransMode='REG') then 1 else -1 end * case when((TransStatus=' ') OR (TransStatus='S')) then 1 else 0 end " _
& vbCrLf & "* case when ((sdate + stime) >= '" & Format(cdatestartdate, "MM/dd/yyyy ") & Format(cdatestarttime, "hh:mm:ss") & "') " _
& vbCrLf & "and ((sdate + stime) <= '" & Format(cdateenddate, "MM/dd/yyyy ") & Format(cdateendtime, "hh:mm:ss") & "') then 1 else 0 end ) AS TSL_MCRD,"

TSL_TC_MCD1 = vbCrLf & "Sum((Quantity)*case when (FunctionID=2) then 1 else 0 end * case when ((UPPER([ItemName])) like '%MASTER%') then 1 else 0 end" _
& vbCrLf & "* case when (TransMode='REG') then 1 else -1 end * case when((TransStatus=' ') OR (TransStatus='S')) then 1 else 0 end " _
& vbCrLf & "* case when ((sdate + stime) >= '" & Format(cdatestartdate, "MM/dd/yyyy ") & Format(cdatestarttime, "hh:mm:ss") & "') " _
& vbCrLf & "and ((sdate + stime) <= '" & Format(cdateenddate, "MM/dd/yyyy ") & Format(cdateendtime, "hh:mm:ss") & "') then 1 else 0 end ) AS TSL_TC_MCD from view_view where idnum = '" & idnum & "' group by idnum"

generatemedia = TSL_CSH1 + TSL_TC_CASH1 + TSL_VISA1 + TSL_TC_VIS1 + TSL_MCRD1 + TSL_TC_MCD1
'Debug.Print generatemedia

cm1.CommandType = adCmdText
cm1.CommandTimeout = 0
cm1.ActiveConnection = cn
cm1.CommandText = generatemedia

With rs9
.ActiveConnection = cn
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Source = cm1.CommandText
MsgBox (" Opening RS ")
.Open
End With
MsgBox (" RS OPEN")

If rs9.BOF And rs9.EOF = True Then
TSL_CSH = "0.00"
.....
Else
TSL_CSH = Format(rs9.Fields("tsl_csh"), "0.00", 2)
.....
End If

rs9.Close
End Sub
​

So ayan po yung part kung saan siya nag error. To be specific po, after po ng MsgBox ("Opening RS")
Hindi po nag oopen yung rs9 na recordset ko. Eh siya lang din po ang pinag gamitan ko ng rs9.

Kaya ayun po ang problema ko.
Bakit sa pc ko and sa pc ng friend#1 ko, okay po yung code ko, same database, same exe
Bakit kapag naka connect na ko sa pc ng friend#2 ko, nag eerror na po ako?

Sana po matulungan niyo po ako
Salamat po in advance po

 

[pterpol]

^
TS, baka may mali sa set-up ng mysql sa friend#2? O kaya naman ay may kulang/maling version ng DLL kung meron man sa set-up?

 

[flimsyme]

^
TS, baka may mali sa set-up ng mysql sa friend#2? O kaya naman ay may kulang/maling version ng DLL kung meron man sa set-up?

Bakit kasi VB6 gamit mo? 2019 na po. Madaming mga dependency something sh*t yang vb6, nirerecommend ko sayo, mag c# ka, wala pang 20 lines of code tapos yang project mo. heheheh

 

[crazystitch626]

^
TS, baka may mali sa set-up ng mysql sa friend#2? O kaya naman ay may kulang/maling version ng DLL kung meron man sa set-up?

Hi po,

Same lang din po ng set up and dll eh. Sinubukan din po namin baka nga sa dll kaso hindi din po nag work. Ok na po siya ngayon. Hinati ko po yung query ko then tumuloy na po. Parang nag timeout po sa command eh.

Thanks po

- - - Updated - - -

Bakit kasi VB6 gamit mo? 2019 na po. Madaming mga dependency something sh*t yang vb6, nirerecommend ko sayo, mag c# ka, wala pang 20 lines of code tapos yang project mo. heheheh

Oo nga po eh, 2019 na. dami din pong experience sa vb6 na mapapa sh*t nga talaga lalo na pagdating sa pagbasa niya ng mga date format. Plano nga po sana na i-migrate ko from vb6 to java po. Anyway, maraming salamat po sa advice.

 

[arvinpastoraquio]

I dont know if it's a local db or not. if local, wala akong nakikitang mali sa code mo except for something for an optional thing. kung client server naman, baka it has something trouble sa closing ng connection mo kasi hindi yata pwede ang disconnected sa server ADO Recordset unless you set CursorLocation property of the ADO Recordset into adUseClient.

put this code before opening the recordset

con.CursorLocation = adUseClient
Or
Set rs9.ActiveConnection = Nothing

Or katulad ng sabi ko na there is something missing optional code

something like this bago mo open ang transaction mo sa DB:

If Not rs9.State = adStateClosed Then
'Closing statement here..
End If

Im not a VB6 Programmer but the logic is somewhat generic in every code when making DB transactions. it's a good practice to check your connection state then close it if it is open.

let me know what you've got after testing it.

- - - Updated - - -

Advice ko lang, gumamit ka ng stored procedures sa pag query mo sa databases mo. hindi good practice ang gumamit ng sql query string, prone yan sa SQL injection and other vulnerabilities. at pangalawa, muka naman may foundation ka na sa VB at general programming, try mo na sa higher at modern na language like VB.Net using VS2013 up. promise! you will be amazed by how far the technology have evolved. sa industry, bihira na ang hiring sa mga vb6 and other old languages maliban lang sa cobol at c++ kasi highly demanding parin ang mga yan lalo na sa mga machine embedded scripts. try mo lang sir.


Good luck!