July 6, 2004 at 10:02 am
I have some vb code that worked a view months ago. But now does not work. The problem is on the connecting. I am not what is causing. I am hoping that some one can look at the code and give me some help.
Dim adoConn As New adodb.Connection
Const DSNConnTelemagic = "uid=sa;pwd=;driver={SQL SERVER};server=pmehta;database=telemagic"
Private Sub CmdCash_Click()
Dim adors As New adodb.Recordset
Dim sSql As String
Dim sUpdSql As String
Dim sLoanDate As String
Dim sProjectId As String
Dim sStartDate As String
Dim sEndDate As String
Dim ssqlStartDate As String
Dim ssqlEndDate As String
Dim dCashamt As Double
Dim iNumPmts As Integer
Dim dCash As Double
Dim sIntRate As String
Dim iNOPmts As Integer
Dim sPmtPer As String
Dim spaytype As String
Dim sCompdper As String
Dim sAmount As String
Dim iIncr As Integer
Dim iRnum As Long
Dim dPercentSkip As Double
Dim dSpecialLevel As Double
Dim sFlag As String
On Error GoTo err_hand
If txtRate <> "" Then
If txtDate <> "" Then
Else
MsgBox "Enter Loan Date"
txtDate.SetFocus
Exit Sub
End If
Else
MsgBox "Enter Rate"
txtRate.SetFocus
Exit Sub
End If
Screen.MousePointer = vbHourglass
sIntRate = txtRate
sLoanDate = Format(txtDate, "mm/dd/yyyy")
sCompdper = "1"
sFlag = "C"
Set adoConn = Nothing
adoConn.Open DSNConnTelemagic
sSql = "Select * from futurecash Order by Rnum"
adors.Open sSql, adoConn, adOpenKeyset
Do While Not adors.EOF
dPercentSkip = 0
dSpecialLevel = 0
iRnum = adors("Rnum")
sProjectId = adors("ProjectId") 'Project ID
sStartDate = adors("Startdate") 'Start Date
sEndDate = adors("EndDate") 'End Date
spaytype = Trim(adors("Payperiod"))
dCash = adors("Cash")
iNOPmts = adors("Noofpmts")
dPercentSkip = adors("Ipercent")
dSpecialLevel = adors("Paylvl")
If CDate(sLoanDate) < CDate(sEndDate) Then
If dPercentSkip > 0 And CDate(sStartDate) < CDate(sLoanDate) Then
Select Case Trim$(UCase(spaytype))
Case "MONTHLY"
iNumPmts = (DateDiff("m", sStartDate, sLoanDate)) + 1
spaytype = "1"
Case "ANNUAL"
iNumPmts = (DateDiff("yyyy", sStartDate, sLoanDate))
spaytype = "12"
Case "SEMIANNUAL"
iNumPmts = (DateDiff("yyyy", sStartDate, sLoanDate) * 2) + 1
spaytype = "6"
Case "QUARTERLY"
iNumPmts = (DateDiff("q", sStartDate, sLoanDate)) + 1
spaytype = "3"
End Select
dCash = Format(ReCalculateCash(sCompdper, sStartDate, iNumPmts, dCash, sIntRate, sStartDate, spaytype, sProjectId, dPercentSkip, dSpecialLevel, sLoanDate, sFlag), "##.00")
spaytype = Trim(adors("Payperiod"))
sStartDate = adors("Startdate") 'Start Date
sEndDate = adors("EndDate") 'End Date
End If
Do While CDate(sStartDate) <= CDate(sLoanDate)
If CDate(sStartDate) = CDate(sLoanDate) Then
sStartDate = sLoanDate
Exit Do
End If
Select Case Trim$(UCase(spaytype))
Case "MONTHLY"
sStartDate = DateAdd("m", 1, sStartDate)
Case "ANNUAL"
sStartDate = DateAdd("yyyy", 1, sStartDate)
Case "SEMIANNUAL"
sStartDate = DateAdd("q", 2, sStartDate)
Case "QUARTERLY"
sStartDate = DateAdd("q", 1, sStartDate)
End Select
Loop
Select Case Trim$(UCase(spaytype))
Case "MONTHLY"
iNumPmts = (DateDiff("m", sStartDate, sEndDate)) + 1
spaytype = "1"
Case "ANNUAL"
iNumPmts = (DateDiff("yyyy", sStartDate, sEndDate)) + 1
spaytype = "12"
Case "SEMIANNUAL"
iNumPmts = (DateDiff("yyyy", sStartDate, sEndDate) * 2) + 1
spaytype = "6"
Case "QUARTERLY"
iNumPmts = (DateDiff("q", sStartDate, sEndDate)) + 1
spaytype = "3"
End Select
iRnum = adors("Rnum")
dPercentSkip = adors("Ipercent")
dSpecialLevel = adors("Paylvl")
sAmount = Format(CalculateUnknownAmount(sCompdper, sStartDate, iNumPmts, dCash, sIntRate, sLoanDate, spaytype, sProjectId, dPercentSkip, dSpecialLevel, sFlag), "##.00")
sUpdSql = "UPDATE futurecash Set Purrate =" & txtRate & " ,Loandate = '" & sLoanDate & "',IPayno = " & iNumPmts & ",Amount =" & sAmount & " Where Rnum =" & iRnum
adoConn.Execute sUpdSql
End If
adors.MoveNext
Loop
Screen.MousePointer = vbNormal
MsgBox "Successfully Completed"
err_hand:
Call Writetolog("Error While Updating " & sProjectId & " In futurecash " & Err.Description)
Resume Next
End Sub
July 7, 2004 at 2:31 am
My first guess is that someone added a password for sa. It's one of the vulnerabilities that was exploited by one of them dang worms, I can't remember if it was slammer or whatever.
The way I get my connection strings is to create a file with a .UDL extension. Then I open the properties and set up my connection. You can then open the file with Notepad(or other text editor) and copy out the connection string. If you can set up a UDL that works when testing, you've got other problems, then you can look at your code.
July 7, 2004 at 7:40 am
Thanks but I find found out were it stops it is the line
sSql = "Select * from futurecash Order by Rnum"
The problem seems to be the Order By Rnum. I can run it with out the order by. Do you know what the issue is with Order By?
Thanks
Paul
July 7, 2004 at 10:55 am
Try to incorporate your logic in the stored proc instead of running select/update statement on the front end.
It is also good for the speed and security reason.
July 7, 2004 at 1:16 pm
What kind of field is Rnum and does it actually exist in the table? ntext, text, or image columns cannot be used in an ORDER BY clause.
July 7, 2004 at 1:42 pm
The Rnum field is an INT.
July 7, 2004 at 2:11 pm
Put [] around the RNum. You need to list the columns instead of having * also. This is bad for performance and lazy coding. Also, as someone mentioned earlier, you need to be writing stored procedures for this and calling those instead of using inline code.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
July 7, 2004 at 2:17 pm
Just a WAG on my part here but have you tried using the TOP keyword in your SELECT statement to see if that works?
|
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply