problem with stored procedure in access project
-
Hi wondered if somebody can help me with this.
Trying to get a stored procedure to work in Access 2000 project - the one with a .adp file extension.
The vba code is:-
Private Sub Command231_Click()
'-------------------------------------------------------------
Dim mydb As Connection, myHistoryID As Long, MyUser As String
Dim myFieldsToChange As String, TheFieldsToChange As String
Dim ExceptionFieldsToChange As String, TheExceptionFieldsToChange As String
Dim myWhere As String, myPatternWhere As String, myLandFillWhere As String, myDocWhere As String, ExceptionWhere As String
Dim Location As Long, Collector As Long, Waste As String, Bin As String, myReason As String
Dim BinsOnSite As Integer, NoOfLifts As Integer, NoOfVisits As Integer
Dim myErr As Byte, go As Boolean
Dim i As Date, iWeekDay As Integer, StartDate As String, EndDate As String
Dim IncludeException As Boolean
Dim Density As String, DensityID As Long
Location = Me!cmbLocation
Collector = Me!cmbCollector
Waste = Me!cmbWasteType
Bin = Me!cmbBinSize
StartDate = Me!txtStartDate
EndDate = Me!txtEndDate
myReason = Me!txtMiscNote
myErr = 0
Dim adoRS As ADODB.Recordset
Dim steve As Integer
Dim test As String
Dim test1 As Date
test1 = Format(StartDate, "Long Date")
steve = 3
If steve = 3 Then
Dim cmdx As New ADODB.Command
cmdx.ActiveConnection = "Provider=SQLOLEDB.1;Data Source =emcsql;Initial Catalog=vantage;UID=UID;PWD=PWD;"
cmdx.CommandType = adCmdText
cmdx.CommandText = "dbo.usp_CheckMasschangeDatesAgainstBase " & Location & "," & Waste & "," & Bin & "," & Collector & "," & "" & StartDate & "" & "," & "" & EndDate & ""
test = cmdx.CommandText
Set adoRS = cmdx.Execute
MsgBox "steve test", vbExclamation, "steve test"
cmdx.ActiveConnection.Close
End If
end sub
---------
I have tried it without the ',' and still it dont work. Think the datatypes should be fine. Can anyone offer any advice i.e. does the code look basically correct? I seem to have a problem with the text fields and the dates i think
Cheers
Steve
-
Have you tried cmdx.CommandType = adCmdStoredProc ?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hi Thanks for the reply...
did do this, i am starting to think the problem is with data format, and not the code. Are text and date a funny format soemtimes?
steve = 0
If steve = 1 Then
MsgBox ("steve =1")
'Dim adoRS As ADODB.Recordset
Dim adoCmd As New ADODB.Command
Dim adoParam As ADODB.Parameter
adoCmd.CommandText = "dbo.usp_CheckMasschangeDatesAgainstBase"
adoCmd.CommandType = adCmdStoredProc
adoCmd.ActiveConnection = adoCN
'& Location & Waste & Bin & Collector & StartDate & EndDate
'@location as int,
'@wastetype as varchar(20),
'@binsize varchar(20),
'@colldepot as int,
'@startdate as datetime,
'@enddate as datetime
Set adoParam = New ADODB.Parameter
adoParam.Name = "@location"
adoParam.Type = 3
adoParam.Value = Location
adoCmd.Parameters.Append adoParam
Set adoParam = New ADODB.Parameter
adoParam.Name = "@wastetype"
adoParam.Type = 200
adoParam.Size = 20
adoParam.Value = Waste
adoCmd.Parameters.Append adoParam
Set adoParam = New ADODB.Parameter
adoParam.Name = "@binsize"
adoParam.Type = 200
adoParam.Size = 20
adoParam.Value = Bin
adoCmd.Parameters.Append adoParam
Set adoParam = New ADODB.Parameter
adoParam.Name = "@colldepot"
adoParam.Type = 3
adoParam.Value = Collector
adoCmd.Parameters.Append adoParam
Set adoParam = New ADODB.Parameter
adoParam.Name = "@startdate"
adoParam.Type = 135
'adoParam.Size =
adoParam.Value = StartDate
adoCmd.Parameters.Append adoParam
Set adoParam = New ADODB.Parameter
adoParam.Name = "@enddate"
adoParam.Type = 135
'adoParam.Size =
adoParam.Value = EndDate
adoCmd.Parameters.Append adoParam
'Set adoRS =
adoCmd.Execute
If Not (adoRS.EOF And adoRS.BOF) Then
'adoRS.MoveNext
MsgBox ("steve got there")
End If
End If
-
Can you post the code of the stored proc? I have a code generator for vba code that can generatea more optimized version of the code.
-
Hi Remi,
stored proc -
CREATE procedure usp_CheckMasschangeDatesAgainstBase
--- created by smr 08/06/2005
--- this query is used so that when a mass change done just to make sure no invoice beakdown already
(
@location as int,
@wastetype as varchar(20),
@binsize varchar(20),
@colldepot as int,
@startdate as datetime,
@enddate as datetime
)
as
SELECT COUNT(*) AS noofrecs
FROM t_Base INNER JOIN
t_InvoiceBreakdown ON t_Base.BaseID = t_InvoiceBreakdown.fkBase
WHERE (t_Base.fkLocation = @location) AND (t_Base.fkWasteType = @wastetype) and (t_base.fkBinSize=@binsize) AND (t_Base.fkCollectorDepot = @colldepot)
AND (t_Base.CollectionDate BETWEEN @startdate AND @enddate)
GO
The datatypes are the same as the table. I think my trouble ones are the varchars and date ones.
Cheers
Steve
-
Sorry about the loss of formating...
This is not the shortest way of writting the code but it works well and gives a remote procedure call on the server instead of a tsql batch (which makes it slightly faster, but mainly protects you against sql injection attacks)
MyCn is a global connection.
Private Function exec_usp_CheckMasschangeDatesAgainstBase(ByVal location As Integer, ByVal wastetype As String, ByVal binsize As String, ByVal colldepot As Integer, ByVal startdate As Date, ByVal enddate As Date, Optional ByRef ReturnValue As Integer) As ADODB.Recordset
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.usp_CheckMasschangeDatesAgainstBase"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@location"
MyParam.Value = location
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@wastetype"
MyParam.Value = wastetype
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@binsize"
MyParam.Value = binsize
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@colldepot"
MyParam.Value = colldepot
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@startdate"
MyParam.Value = startdate
MyParam.Size = 8
MyParam.Direction = adParamInput
MyParam.Type = adDate
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@enddate"
MyParam.Value = enddate
MyParam.Size = 8
MyParam.Direction = adParamInput
MyParam.Type = adDate
MyCmd.Parameters.Append MyParam
Dim MyRs As ADODB.Recordset
Set MyRs = New ADODB.Recordset
MyRs.CursorLocation = adUseClient
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyRs.Open MyCmd, , adOpenKeyset, adLockOptimistic
If MyRs.State = 1 Then
Set exec_usp_CheckMasschangeDatesAgainstBase = MyRs.Clone
exec_usp_CheckMasschangeDatesAgainstBase.ActiveConnection = Nothing
Else
Set exec_usp_CheckMasschangeDatesAgainstBase = Nothing
End If
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
DisposeRS MyRs
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
ErrHandler ModuleName, Me.Name, "exec_usp_CheckMasschangeDatesAgainstBase", Err
MsgBox Err.Description & " : " & Err.Number
End Function
-
Hi Many thanks for that. Ill give that a try in a bit and let you know how i get on.
Cheers
Steve
-
Good luck.
-
Please try putting any strings and dates inside single quotes when you call the SP.
HTH,
Rich
-
No need to do that if he's using a command object and the command type adCmdStoredProc.
-
Hi Remi,
You may be correct. But actually I rarely use the bug-prone and verbose ADO syntax anymore. I find it much easier to use:
rs.open "EXEC mySPname " & param1 & ", " & param2 ....
Works every time, in one line of code. Much less bug-prone, and trivail to learn and use. This is how its done in T-SQL, so you may as well use the same method in ADO. Of course, if you need output parameters, you have to revert to the ADO syntax mess.
Rich
-
This syntaxe is prone to sql injection attacks. That's why I created a code generator that does the dirty work for me
. -
No, not the way I do it.
I check all parameters for datatype, and for a list of characters (e.g. ";" "," " ' ") and keywords (e.g. EXEC, DEL, etc ) before running them, using a simple function. This should be standard procedure for all parameters and all SQL everywhere.
Furthermore, most of my parameters are numeric keys (derived from combo boxes, etc.) and loaded into numeric variables or dates loaded into date variables. The parameters use Cstr(param) into the EXEC line. Thus there is essentially no chance of injection.
-
the safest way would be to go like this (in case you forgot something in the validation or if you or someone else forgets to validate) : "exec dbo.sp [" & param1 & ], ["...
This will block every possible wrong input left.
-
Remi,
That's good! I like it! That makes it even easier.
Thanks,
Rich
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply