I've spent all day on this and still feel I'm no nearer solving the problem (whatever it is). I've checked and re-checked the parameter and table mapping settings in the data adapter. plus dataset fields, and code parameters etc etc. The procedure is intended to fill a table that has fields "StatusID" and "StatusType" in dataset "dsStatus". Is it the declare parameters? does visual studio think i should be supplying values for the declare parameters too? Thanks to anyone who has suggestions or a solution!
the form code...
DsStatus1.Clear()
DsStatus1 = kernel.getStatus(intDepartmentID, intJobID, "Job")
the data adapter code...
Public Function getStatus(ByVal intDepartmentID As Integer, ByVal intJobID As Integer, ByVal strStatusCategory As String) As dsStatus
Dim dsTemp As New dsStatus
SqlDataAdapter1.SelectCommand.Parameters("@DepartmentID").Value = intDepartmentID
SqlDataAdapter1.SelectCommand.Parameters("@JobID").Value = intJobID
SqlDataAdapter1.SelectCommand.Parameters("@StatusCategory").Value = strStatusCategory
Me.SqlDataAdapter1.Fill(dsTemp)
Return dsTemp
End Function
the stored procedure...
USE [microgenDB]
GO
/****** Object: StoredProcedure [dbo].[get_job_status] Script Date: 08/16/2013 15:20:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* -------------------- */
ALTER PROCEDURE [dbo].[get_job_status]
@JobID INT,
@DepartmentID INT,
@StatusCategory VARCHAR(50)
AS
BEGIN
DECLARE @MaxJobTaskStartDate DATETIME
DECLARE @MaxJobTaskEndDate DATETIME
--Get the most recent task...
--Get MaxStartDate where EndDate is null
SELECT @MaxJobTaskStartDate = MAX(dbo.tbl_JobTask.JobTaskStartDate)
FROM dbo.tbl_JobTask INNER JOIN
dbo.tbl_JobDepartmentTask ON dbo.tbl_JobTask.JobDepartmentTaskID = dbo.tbl_JobDepartmentTask.JobDepartmentTaskID INNER JOIN
dbo.tbl_Task ON dbo.tbl_JobDepartmentTask.TaskID = dbo.tbl_Task.TaskID INNER JOIN
dbo.tbl_Job ON dbo.tbl_JobTask.JobID = dbo.tbl_Job.JobID INNER JOIN
dbo.tbl_Status ON dbo.tbl_JobDepartmentTask.StatusID = dbo.tbl_Status.StatusID
WHERE (dbo.tbl_JobTask.JobID = @JobID) AND (dbo.tbl_JobTask.JobTaskEndDate IS NULL) AND (dbo.tbl_Status.StatusCategory = @StatusCategory)
GROUP BY dbo.tbl_JobTask.JobID
--Get MaxEndDate (for jobs where no task has a null end date)
SELECT @MaxJobTaskEndDate = MAX(dbo.tbl_JobTask.JobTaskEndDate)
FROM dbo.tbl_JobTask INNER JOIN
dbo.tbl_JobDepartmentTask ON dbo.tbl_JobTask.JobDepartmentTaskID = dbo.tbl_JobDepartmentTask.JobDepartmentTaskID INNER JOIN
dbo.tbl_Task ON dbo.tbl_JobDepartmentTask.TaskID = dbo.tbl_Task.TaskID INNER JOIN
dbo.tbl_Job ON dbo.tbl_JobTask.JobID = dbo.tbl_Job.JobID INNER JOIN
dbo.tbl_Status ON dbo.tbl_JobDepartmentTask.StatusID = dbo.tbl_Status.StatusID
WHERE (dbo.tbl_JobTask.JobID = @JobID) AND (dbo.tbl_Status.StatusCategory = @StatusCategory)
GROUP BY dbo.tbl_JobTask.JobID
--get the max end date coz there's no open tasks
IF (@MaxJobTaskStartDate IS NULL)
BEGIN
--select the highest order task with the max end date
SELECT dbo.tbl_Status.StatusID, dbo.tbl_Status.StatusType
FROM dbo.tbl_Status INNER JOIN
dbo.tbl_JobDepartmentTask ON dbo.tbl_Status.StatusID = dbo.tbl_JobDepartmentTask.StatusID
WHERE (dbo.tbl_JobDepartmentTask.TaskLookupOrder IN
(SELECT MAX(tbl_JobDepartmentTask_1.TaskLookupOrder) AS MaxTaskLookupOrder
FROM dbo.tbl_JobDepartmentTask AS tbl_JobDepartmentTask_1 INNER JOIN
dbo.tbl_JobTask ON tbl_JobDepartmentTask_1.JobDepartmentTaskID = dbo.tbl_JobTask.JobDepartmentTaskID INNER JOIN
dbo.tbl_Status ON tbl_JobDepartmentTask_1.StatusID = dbo.tbl_Status.StatusID
WHERE (dbo.tbl_JobTask.JobID = @JobID) AND (dbo.tbl_JobTask.JobTaskEndDate = @MaxJobTaskEndDate) AND (dbo.tbl_Status.StatusCategory = @StatusCategory))) AND
(dbo.tbl_JobDepartmentTask.DepartmentID = @DepartmentID)
END
--there is an open task so see if it's start date is later than the closed tasks
ELSE
IF @MaxJobTaskStartDate < @MaxJobTaskEndDate
BEGIN
--use the maxenddate as the task status
SELECT dbo.tbl_Status.StatusID, dbo.tbl_Status.StatusType
FROM dbo.tbl_Status INNER JOIN
dbo.tbl_JobDepartmentTask ON dbo.tbl_Status.StatusID = dbo.tbl_JobDepartmentTask.StatusID
WHERE (dbo.tbl_JobDepartmentTask.TaskLookupOrder IN
(SELECT MAX(tbl_JobDepartmentTask_1.TaskLookupOrder) AS MaxTaskLookupOrder
FROM dbo.tbl_JobDepartmentTask AS tbl_JobDepartmentTask_1 INNER JOIN
dbo.tbl_JobTask ON tbl_JobDepartmentTask_1.JobDepartmentTaskID = dbo.tbl_JobTask.JobDepartmentTaskID INNER JOIN
dbo.tbl_Status ON tbl_JobDepartmentTask_1.StatusID = dbo.tbl_Status.StatusID
WHERE (dbo.tbl_JobTask.JobID = @JobID) AND (dbo.tbl_JobTask.JobTaskEndDate = @MaxJobTaskEndDate) AND (dbo.tbl_Status.StatusCategory = @StatusCategory)))AND
(dbo.tbl_JobDepartmentTask.DepartmentID = @DepartmentID)
END
-- use the maxstartdate as the task status
ELSE
BEGIN
SELECT dbo.tbl_Status.StatusID, dbo.tbl_Status.StatusType
FROM dbo.tbl_Status INNER JOIN
dbo.tbl_JobDepartmentTask ON dbo.tbl_Status.StatusID = dbo.tbl_JobDepartmentTask.StatusID
WHERE (dbo.tbl_JobDepartmentTask.TaskLookupOrder IN
(SELECT MAX(tbl_JobDepartmentTask_1.TaskLookupOrder) AS MaxTaskLookupOrder
FROM dbo.tbl_JobDepartmentTask AS tbl_JobDepartmentTask_1 INNER JOIN
dbo.tbl_JobTask ON tbl_JobDepartmentTask_1.JobDepartmentTaskID = dbo.tbl_JobTask.JobDepartmentTaskID INNER JOIN
dbo.tbl_Status ON tbl_JobDepartmentTask_1.StatusID = dbo.tbl_Status.StatusID
WHERE (dbo.tbl_JobTask.JobID = @JobID) AND (dbo.tbl_JobTask.JobTaskStartDate = @MaxJobTaskStartDate) AND (dbo.tbl_Status.StatusCategory = @StatusCategory)))AND
(dbo.tbl_JobDepartmentTask.DepartmentID = @DepartmentID)
END
END
the code that defines SqlDataAdapter1 (copied from designer window in Visual Studio) it's displaying three tables from the stored procedure in the data mapping window. I tried mapping all three tables from source sp to the one table in the dataset dsStatus, and also creating three separate tables in the dataset and mapping each to corresponding table from stored procedure. Error message remains!
'SqlSelectCommand4
'
Me.SqlSelectCommand4.CommandText = "get_job_status"
Me.SqlSelectCommand4.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand4.Connection = Me.SqlConnection1
Me.SqlSelectCommand4.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing), New System.Data.SqlClient.SqlParameter("@JobID", System.Data.SqlDbType.Int, 4), New System.Data.SqlClient.SqlParameter("@DepartmentID", System.Data.SqlDbType.Int, 4), New System.Data.SqlClient.SqlParameter("@StatusCategory", System.Data.SqlDbType.VarChar, 50)})
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand4
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table1", "get_job_status", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("StatusID", "StatusID"), New System.Data.Common.DataColumnMapping("StatusType", "StatusType")}), New System.Data.Common.DataTableMapping("Table2", "get_job_status", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("StatusID", "StatusID"), New System.Data.Common.DataColumnMapping("StatusType", "StatusType")}), New System.Data.Common.DataTableMapping("Table", "get_job_status", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("StatusID", "StatusID"), New System.Data.Common.DataColumnMapping("StatusType", "StatusType")})})
ok, so it was because a separate xml file resides on the local machine where the user application runs. Even though the connection string to the server/database was correct in the application, the xml file connection string was pointing to a different server. 2 days + 2 hours because of this. I think you can guess how I feel. Hope this helps someone else!