December 4, 2003 at 8:54 am
Big Trouble in Little China (For me anyway)
My forehead is getting sore from pounding it on the monitor.
I am trying to use a stored procedure to insert data to a table.
Here is the SP
CREATE PROCEDURE dbo.procAddProtocol(
@User int,
@ShortName nvarchar(50),
@ProtocolName ntext,
@CRRIProtID nvarchar(10),
@SponsorProtID nvarchar(20),
@TestArticleFK int,
@SponsorFK int,
@ProtocolDate smalldatetime)
AS INSERT INTO dbo.tblProtocol
(strShortName, memProtocolName, strCRRIProtID, strSponsorProtID, tblTestArticleFK, tblSponsorFK, dtmProtocol, intUser)
VALUES (@ShortName, @ProtocolName, @CRRIProtID, @SponsorProtID, @TestArticleFK, @SponsorFK, @ProtocolDate, @User)
GO
And here is the VBA Procedure that I call to add the record:
Private Sub SaveNew()
Dim cmdP As ADODB.Command
Dim prInsert(7) As Variant
On Error GoTo Save_Err
fOK = QuickConnect()'this makes the ADO Connection
If fOK = False Then
MsgBox "Unable to Connect", , _
"Can't connect to the database."
End If
Set cmdP = New ADODB.Command
With cmdP
.ActiveConnection = ADOCon
.CommandType = adCmdStoredProc
.CommandText = "procAddProtocol"
prInsert(0) = Me.strShortName
prInsert(1) = Me.memProtocolName
prInsert(2) = Me.strCRRIProtID
prInsert(3) = Me.strSponsorProtID
prInsert(4) = CInt(Me.tblTestArticleFK)
prInsert(5) = CInt(Me.tblSponsorFK)
prInsert(6) = Me.dtmProtocol
prInsert(7) = Me.CInt(intUser)
.Execute , prInsert
End With
Set cmdP = Nothing
ErrorTrap:
Blah, blah, blah
End Sub
I am getting two different errors. The first one is “Error Converting Data type char to int”. So to get rid of this error I went ahead and removed the FK’s to try to get it to add a record.
Then I get this error message – “Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT Function to run this query.” So I use the CONVERT function to convert these items to nvarchar, to no avail.
What am I missing? I know there is something that I’m overlooking, but I just can’t find it.
If you can help I would greatly appreciate it.
December 4, 2003 at 5:09 pm
I think its because you're missing the implicit returnvalue param. There is a VB add-in at this link (http://www.betav.com/Files/Content/code/SampleCode.htm), easy enough to run stand alone, will generate all the code you need to call a proc.
Andy
December 9, 2003 at 7:52 am
Thanks for the reply. My next question, a silly one at that is why do I need a ' implicit returnvalue param'? I couldn't find it in the books I have (probably looking in hte wrong place). If you could just post a refence that would be great.
Thanks!
Dennis
December 9, 2003 at 4:13 pm
Hi,
If you don't want to return a parameter back from the SP (such as @@ERROR or @@IDENTITY) you can simplify your calling code by just executing the stored proc against your ado connection object. eg sp_name param1 param2 etc... Then you don't need to bother about populating the parameters collection of a command object. This is tricky to get right manually and causes loads of difficult to trace errors. If you particularly want to use the parameters collection I would use an automated tool to build the paramerers for you(like Andy suggested). You could also use the refresh method of the command object if you are prepared to take a performance hit.
HTH
Chris
Edited by - cwedgwood50 on 12/09/2003 4:15:46 PM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply