Hi there
I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K.
Wish to provide some knowledge gained back to community - hopefully
help others.
1.Question how do you test stored procedure from SQL Server vs MS
Access point of view ?
2.How do you insert many records at same time ?
Questions may seem straight forward to some but I found very
frustrating.
Here goes :-
N.B.
1.Strings e.g. ' or "
See below my sproc.
1.************************************************ *********
A.SQL Server
i.
Query Analyzer - debug procedure - value
'HARLEY.I','03004','A000-AA00',NULL,NULL,getdate(),2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ,getdate(),1,getdate(),null,NULL,0,null,0,null;'HA RLEY.I','03004','A000-AA00',NULL,NULL,getdate(),2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ,getdate(),1,getdate(),null,null,0,null,0,null;
ii.
Query Analyzer
DECLARE @RetCode int
DECLARE @RetMsg varchar(100)
EXEC procTimesheetInsert
@TimesheetDetails
='''HARLEY.I'',''03004'',''A000-AA00'',NULL,NULL,getdate(),2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,''Notes'',''General'',1 ,2,3,getdate(),1,getdate(),null,NULL,0,null,0,null ;''HARLEY.I'',''03004'',''A000-AA00'',NULL,NULL,getdate(),2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,''Notes'',''General'',1 ,2,3,getdate(),1,getdate(),null,null,0,null,0,null ;',
@RetCode = @RetCode OUTPUT,
@RetMsg = @RetMsg OUTPUT,
@TimesheetID = NULL
B.Access Side
i.ADO conection
..Source = "SELECT '''' & [re_code] & '''' AS RE_Code1, Test.PR_Code,
'''' & [AC_Code] & '''' AS AC_Code1, Test.CO_Code, Test.CH_Code,
'getdate()' AS TS_LastEdit1, Test.WE_Date, Test.SAT, Test.SUN,
Test.MON, Test.TUE, Test.WED, Test.THU, Test.FRI, '''' & [NOTES] &
'''' AS NOTES1, '''' & [GENERAL] & '''' AS GENERAL1, Test.PO_Number,
Test.WWL_Number, Test.CN_Number, 'getdate()' AS Last_Editby1, '1' AS
User_Confirm1, 'getdate()' AS User_Confirm_Date1, Test.Appd_By,
Test.Appd_Date, '0' AS Approved1, '' AS a, Test.ConcurrencyID, '' AS b
FROM Test;"
2.************************************************ *********
To separate data use ";" - see data
'HARLEY.I','03004','A000-AA00',NULL,NULL,getdate(),2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ,getdate(),1,getdate(),null,NULL,0,null,0,null;'HA RLEY.I','03004','A000-AA00',NULL,NULL,getdate(),2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ,getdate(),1,getdate(),null,null,0,null,0,null;
************************************************** *********
CREATE PROCEDURE [dbo].[procTimesheetInsert](
@TimesheetDetails varchar(5000) = NULL,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT,
@TimesheetID int = NULL)
WITH RECOMPILE
AS
SET NOCOUNT ON
DECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)
DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)
DECLARE @TimesheetCount int, @TimesheetCount1 int
DECLARE @TS_LastEdit smalldatetime
DECLARE @Last_Editby smalldatetime
DECLARE @User_Confirm bit
DECLARE @User_Confirm_Date smalldatetime
DECLARE @DetailCount int
DECLARE @Error int
/* Validate input parameters. Assume success. */
SELECT @RetCode = 1, @RetMsg = ''
IF @TimesheetDetails IS NULL
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)
/* Create a temp table parse out each Timesheet detail from input
parameter string,
count number of detail records and create SQL statement to
insert detail records into the temp table. */
CREATE TABLE #tmpTimesheetDetails
/*
(Counter int IDENTITY (1,1),
SELECT @SQLBase ='INSERT INTO
#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number,User_Confirm,User_Confir m_Date,Approved,ConcurrencyID)
*/
(
RE_Code varchar(50),
PR_Code varchar(50),
AC_Code varchar(50),
CO_Code varchar(50),
CH_Code varchar(50),
TS_LastEdit smalldatetime,
WE_Date smalldatetime,
SAT REAL DEFAULT 0,
SUN REAL DEFAULT 0,
MON REAL DEFAULT 0,
TUE REAL DEFAULT 0,
WED REAL DEFAULT 0,
THU REAL DEFAULT 0,
FRI REAL DEFAULT 0,
Notes varchar(255),
General varchar(50),
PO_Number REAL,
WWL_Number REAL,
CN_Number REAL,
Last_Editby varchar(50),
User_Confirm bit,
User_Confirm_Date smalldatetime,
Appd_By varchar(50),
Appd_Date smalldatetime,
Approved bit,
POSTED_TO_TES smalldatetime,
ConcurrencyID int,
[Timestamp] varchar(50))
SELECT @SQLBase ='INSERT INTO
#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,CO_Co de,
CH_Code,TS_LastEdit,WE_Date,SAT,SUN,MON,TUE,WED,TH U,FRI,Notes,General,PO_Number,WWL_Number,CN_Number ,Last_Editby,
User_Confirm,User_Confirm_Date,Appd_By
,Appd_Date,Approved,POSTED_TO_TES,ConcurrencyID,[Timestamp])
VALUES ( '
SELECT @TimesheetCount=0
WHILE LEN( @TimesheetDetails) > 1
BEGIN
SELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,
Charindex(';', @TimesheetDetails) -1) + ')'
EXEC(@SQLComplete)
SELECT @TimesheetCount = @TimesheetCount + 1
SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(
@TimesheetDetails)-Charindex(';', @TimesheetDetails))
END
IF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCount
SELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Details
couldn''t be saved.' + CHAR(13) + CHAR(10)
-- If validation failed, exit proc
IF @RetCode = 0
RETURN
-- If validation ok, continue
SELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +
CHAR(10)
/* RETURN*/
-- Start transaction by inserting into Timesheet table
BEGIN TRAN
INSERT INTO timesheet.dbo.WWL_Timesheets
select RE_Code,PR_Code,AC_Code,CO_Code,
CH_Code,TS_LastEdit,WE_Date,SAT,SUN,MON,TUE,WED,TH U,FRI,Notes,General,PO_Number,WWL_Number,CN_Number ,Last_Editby,
User_Confirm,User_Confirm_Date,Appd_By
,Appd_Date,Approved,POSTED_TO_TES,ConcurrencyID,[Timestamp]
FROM #tmpTimesheetDetails
/*
SELECT RE_Code, PR_Code,AC_Code, WE_Date, SAT, SUN, MON, TUE, WED,
THU, FRI, Notes, General, PO_No, WWL_No, CN_No
*/
/*
SELECT @SQLBase1 ='INSERT INTO WWL_Timesheets (RE_Code,
PR_Code,AC_Code, WE_Date, SAT, SUN, MON, TUE, WED, THU, FRI, Notes,
General, PO_No, WWL_No, CN_No)
VALUES ( '
SELECT @TimesheetCount1=0
WHILE LEN( @TimesheetDetails) > 1
BEGIN
SELECT @SQLComplete1 = @SQLBase1 + LEFT( @TimesheetDetails,
Charindex(';', @TimesheetDetails) -1) + ')'
EXEC(@SQLComplete1)
SELECT @TimesheetCount1 = @TimesheetCount1 + 1
SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(
@TimesheetDetails)-Charindex(';', @TimesheetDetails))
END
RETURN
*/
-- Check if insert succeeded.
IF @@ROWCOUNT = @TimesheetCount AND @@ERROR = 0
BEGIN
commit tran
SELECT @RetCode = 1,
@RetMsg = 'Timesheet added successfully.'
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @RetCode = 0,
@RetMsg = 'Insertion of new Timesheet failed. Timesheet Details
couldn''t be saved.'
END
RETURN
GO
************************************************** *********
Public Function Test_1()
Dim boolState As Boolean
Dim rstDetails As ADODB.Recordset
Dim strDetails As String
Dim cmd As ADODB.Command
Dim fOK As Boolean
Dim strMsg As String
Dim strOK As String
If gcnn Is Nothing Then
Set gcnn = New ADODB.Connection
End If
If gcnn.State = adStateOpen Then
boolState = True
'do nothing
Else
'If Not IsFormOpen("Frm_Login") Then
'DoCmd.OpenForm "Frm_Login", WindowMode:=acDialog
'End If
GlobalConnection_A = DLookup("[mstrOLEDBConnect]",
"Refresh_FrontEnd_Access", "Not [mstrOLEDBConnect] is null")
If GlobalConnection_A <> "" Or IsNull(GlobalConnection_A) Then
gcnn.ConnectionString = GlobalConnection_A
gcnn.Open
If gcnn.State = adStateOpen Then
boolState = True
Else
boolState = False
End If
'do nothing
End If
End If
'***AIM-Build string from Order Details.
'***AIM-Using ADO Recordset.GetString.
Set rstDetails = New ADODB.Recordset
With rstDetails
.ActiveConnection = CurrentProject.Connection
'***AIM-Sort by Access autonumber field, to preserve entry
Timesheet of details.
'***AIM-Open a default read-only, forward-only recordset
.Source = "SELECT '''' & [re_code] & '''' AS RE_Code1,'''' &
[PR_Code]& '''' AS PR_Code1, '''' & [AC_Code] & '''' AS AC_Code1,
Test.CO_Code, Test.CH_Code, 'getdate()' AS TS_LastEdit1, '''' &
FORMAT([WE_Date],'YYYY/MM/DD') & '''' AS WE_Date1, Test.SAT, Test.SUN,
Test.MON, Test.TUE, Test.WED, Test.THU, Test.FRI, '''' & [NOTES] &
'''' AS NOTES1, '''' & [GENERAL] & '''' AS GENERAL1, Test.PO_Number,
Test.WWL_Number, Test.CN_Number, 'getdate()' AS Last_Editby1, '1' AS
User_Confirm1, 'getdate()' AS User_Confirm_Date1, Test.Appd_By,
Test.Appd_Date, '0' AS Approved1, '' AS a, Test.ConcurrencyID, '' AS b
FROM Test WHERE (((Test.User_Confirm)=False));"
'***AIM-Open a default read-only, forward-only recordset
.Open
'***AIM-Build a string to marshall Timesheet details to the
stored procedure
strDetails = .GetString( _
StringFormat:=adClipString, _
ColumnDelimeter:=",", _
RowDelimeter:=";", _
NullExpr:="NULL")
.Close
MsgBox strDetails
End With
Set rstDetails = Nothing
'Exit Function
'***AIM-Add Timesheet
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = gcnn
.CommandText = "procTimesheetInsert"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@TimesheetDetails",
adVarChar, adParamInput, 5000, strDetails)
.Parameters.Append .CreateParameter("@RetCode", adInteger,
adParamOutput)
.Parameters.Append .CreateParameter("@RetMsg", adVarChar,
adParamOutput, 100)
.Parameters.Append .CreateParameter("@TimesheetID",
adInteger)
.Execute
fOK = .Parameters("@RetCode")
strMsg = .Parameters("@RetMsg")
If fOK Then
Else
strMsg = "Timesheet was not added." & vbCrLf & strMsg
End If
End With
Set cmd = Nothing
End Function
