Delete Filing

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I am testing my Data Access Layer. I have stored procedures developed for
all the CRUD. I am creatng records without a problem. When I go to delete
them, I get an error:

System.Data.SqlClient.SqlException: Procedure 'sp_Document_DeleteCommand'
expects parameter '@Original_DocumentID', which was not supplied.

Here's the code that deletes the records.

For Each row In m_documentDS.Documents.Rows
row.Delete()
Next
System.Console.WriteLine("Deleting Data . . . ")
m_documentDALC.UpdateDocuments(m_documentDS)
 
I'm using the DAAB 3.1 and getting the sp params from the database.

' <summary>
' Updates all document data in the DS
' </summary>
Public Function UpdateDocuments(ByVal pDocumentDS As DocumentDS) As Boolean
If IsNothing(pDocumentDS) Then Throw New
ArgumentNullException(ResourceManager.GetString("RES_InvalidDocumentDS"))
If Not pDocumentDS.HasChanges Then Return False
Try
Dim InsertParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_InsertCommand")
Dim UpdateParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_UpdateCommand")
Dim DeleteParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_DeleteCommand")
Dim DocumentInsertCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_InsertCommand]", CommandType.StoredProcedure, InsertParams)
Dim DocumentUpdateCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_UpdateCommand]", CommandType.StoredProcedure, UpdateParams)
Dim DocumentDeleteCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_DeleteCommand]", CommandType.StoredProcedure, DeleteParams)
helper.UpdateDataset(DocumentInsertCommand, DocumentDeleteCommand,
DocumentUpdateCommand, pDocumentDS, "Documents")
Dim AttachInsertParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_InsertCommand")
Dim AttachUpdateParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_UpdateCommand")
Dim AttachDeleteParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_DeleteCommand")
Dim AttachmentInsertCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_InsertCommand]", CommandType.StoredProcedure,
AttachInsertParams)
Dim AttachmentUpdateCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_UpdateCommand]", CommandType.StoredProcedure,
AttachUpdateParams)
Dim AttachmentDeleteCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_DeleteCommand]", CommandType.StoredProcedure,
AttachDeleteParams)
helper.UpdateDataset(AttachmentInsertCommand, AttachmentDeleteCommand,
AttachmentUpdateCommand, pDocumentDS, "Attachments")
Catch e As Exception
Throw New
ApplicationException(ResourceManager.GetString("RES_ExceptionCantUpdateDocum
ents"), e)
End Try
End Function

helper is the ADOHelper from DAAB. I get it in a base class.

The insert works just fine. I'll look around for column mappings with sp's.
I've thought about wlking the rows and doing it all manually. I just wanted
to see if I could get this work instead.

thnx
rkr
 
I looked at the parameters returned by the GetSpParameterSet function. None
of them have a SourceColumn value. The size of the Int32 types is 0. Could
this have something to do with it?

Keith said:
I'm using the DAAB 3.1 and getting the sp params from the database.

' <summary>
' Updates all document data in the DS
' </summary>
Public Function UpdateDocuments(ByVal pDocumentDS As DocumentDS) As Boolean
If IsNothing(pDocumentDS) Then Throw New
ArgumentNullException(ResourceManager.GetString("RES_InvalidDocumentDS"))
If Not pDocumentDS.HasChanges Then Return False
Try
Dim InsertParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_InsertCommand")
Dim UpdateParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_UpdateCommand")
Dim DeleteParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_DeleteCommand")
Dim DocumentInsertCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_InsertCommand]", CommandType.StoredProcedure, InsertParams)
Dim DocumentUpdateCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_UpdateCommand]", CommandType.StoredProcedure, UpdateParams)
Dim DocumentDeleteCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_DeleteCommand]", CommandType.StoredProcedure, DeleteParams)
helper.UpdateDataset(DocumentInsertCommand, DocumentDeleteCommand,
DocumentUpdateCommand, pDocumentDS, "Documents")
Dim AttachInsertParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_InsertCommand")
Dim AttachUpdateParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_UpdateCommand")
Dim AttachDeleteParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_DeleteCommand")
Dim AttachmentInsertCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_InsertCommand]", CommandType.StoredProcedure,
AttachInsertParams)
Dim AttachmentUpdateCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_UpdateCommand]", CommandType.StoredProcedure,
AttachUpdateParams)
Dim AttachmentDeleteCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_DeleteCommand]", CommandType.StoredProcedure,
AttachDeleteParams)
helper.UpdateDataset(AttachmentInsertCommand, AttachmentDeleteCommand,
AttachmentUpdateCommand, pDocumentDS, "Attachments")
Catch e As Exception
Throw New
ApplicationException(ResourceManager.GetString("RES_ExceptionCantUpdateDocum
ents"), e)
End Try
End Function

helper is the ADOHelper from DAAB. I get it in a base class.

The insert works just fine. I'll look around for column mappings with sp's.
I've thought about wlking the rows and doing it all manually. I just wanted
to see if I could get this work instead.

thnx
rkr

William Ryan eMVP said:
It's hard to tell from the code. It looks like you are missing a
columnmapping but that param value isn't being supplied. Can you post the
whole Deletecommand code?

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
Click to expand...
Click to expand...
 
I just tested the update command and it is doing the same thing. Only the
insert works.

Keith said:
I'm using the DAAB 3.1 and getting the sp params from the database.

' <summary>
' Updates all document data in the DS
' </summary>
Public Function UpdateDocuments(ByVal pDocumentDS As DocumentDS) As Boolean
If IsNothing(pDocumentDS) Then Throw New
ArgumentNullException(ResourceManager.GetString("RES_InvalidDocumentDS"))
If Not pDocumentDS.HasChanges Then Return False
Try
Dim InsertParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_InsertCommand")
Dim UpdateParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_UpdateCommand")
Dim DeleteParams() As IDataParameter = helper.GetSpParameterSet(connection,
"sp_Document_DeleteCommand")
Dim DocumentInsertCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_InsertCommand]", CommandType.StoredProcedure, InsertParams)
Dim DocumentUpdateCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_UpdateCommand]", CommandType.StoredProcedure, UpdateParams)
Dim DocumentDeleteCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Document_DeleteCommand]", CommandType.StoredProcedure, DeleteParams)
helper.UpdateDataset(DocumentInsertCommand, DocumentDeleteCommand,
DocumentUpdateCommand, pDocumentDS, "Documents")
Dim AttachInsertParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_InsertCommand")
Dim AttachUpdateParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_UpdateCommand")
Dim AttachDeleteParams() As IDataParameter =
helper.GetSpParameterSet(connection, "sp_Attachments_DeleteCommand")
Dim AttachmentInsertCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_InsertCommand]", CommandType.StoredProcedure,
AttachInsertParams)
Dim AttachmentUpdateCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_UpdateCommand]", CommandType.StoredProcedure,
AttachUpdateParams)
Dim AttachmentDeleteCommand As IDbCommand = helper.CreateCommand(connection,
"[sp_Attachments_DeleteCommand]", CommandType.StoredProcedure,
AttachDeleteParams)
helper.UpdateDataset(AttachmentInsertCommand, AttachmentDeleteCommand,
AttachmentUpdateCommand, pDocumentDS, "Attachments")
Catch e As Exception
Throw New
ApplicationException(ResourceManager.GetString("RES_ExceptionCantUpdateDocum
ents"), e)
End Try
End Function

helper is the ADOHelper from DAAB. I get it in a base class.

The insert works just fine. I'll look around for column mappings with sp's.
I've thought about wlking the rows and doing it all manually. I just wanted
to see if I could get this work instead.

thnx
rkr

William Ryan eMVP said:
It's hard to tell from the code. It looks like you are missing a
columnmapping but that param value isn't being supplied. Can you post the
whole Deletecommand code?

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
Click to expand...
Click to expand...
 
This fixed the problem:
Private Function GetSpParameterSet(ByVal sp As String) As IDataParameter()

Dim Params As IDataParameter() = helper.GetSpParameterSet(connection, sp)

Dim param As IDataParameter

For Each param In Params

If param.ParameterName.StartsWith("@Original_") Then

param.SourceVersion = DataRowVersion.Original

param.SourceColumn = param.ParameterName.Substring(10)

End If

Next

Return Params

End Function
 
Back
Top