Solved

error converting data typa nvarchar to int

Posted on 2013-02-05
3
470 Views
Last Modified: 2013-02-05
I have code behiind a form which triggers a sp in sql server. It should return a GroupID (integer) but I am getting the above error.

Any ideas where my code is wrong?

Dim myConn As ADODB.Connection
Dim objCom As ADODB.Command
Dim rsTemp As ADODB.Recordset

'Initialize variables
Set myConn = New ADODB.Connection
Set objCom = New ADODB.Command
    
'Open the connection, using your connection string
myConn.Open GetConnectString

With objCom
     .CommandText = "sp_Access_AddTranslationContract"     'Name of the stored procedure
     .CommandType = adCmdStoredProc  'Type : stored procedure
     .ActiveConnection = GetConnectString
    .Parameters.Append .CreateParameter("@societeID", adInteger, adParamInput, , Forms![frmtrad_commandes2]![societeID])
    .Parameters.Append .CreateParameter("@GroupName", adVarWChar, adParamInput, 150, Me.tmpGroupName)
    .Parameters.Append .CreateParameter("@ClientCode", adVarWChar, adParamInput, 50, Forms![frmtrad_commandes2]![ClientCode])
    .Parameters.Append .CreateParameter("@ClientReference", adVarWChar, adParamInput, 150, Me.ClientReference)
End With
 
'Store the result in a recordset
Set rsTemp = objCom.Execute
   
Dim intGroupID As String

With rsTemp
intGroupID = rsTemp("GroupID")
.Close
End With

Open in new window


sp
ALTER PROCEDURE [dbo].[sp_Access_AddTranslationContract]
	-- Add the parameters for the stored procedure here
@GroupName nvarchar(150),
@ClientCode nvarchar (50),
@societeID int,
@ClientReference nvarchar(150)
As
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare @tmpRP as table(GroupID int)
Insert tblTranslationContractGroup
(DateStart, GroupName, Active, ClientCode, PrivilegeContract, societeID, ClientReference)
Output inserted.GroupID into @tmpRP
VALUES(GETDATE(), @GroupName, 1, @ClientCode, 1, @societeID, @ClientReference) 

SET NOCOUNT OFF -- for final select

Select * from @tmpRP

END

Open in new window

0
Comment
Question by:Shawn
  • 2
3 Comments
 
LVL 1

Author Comment

by:Shawn
ID: 38857798
I have narrowed it down to this line
    .Parameters.Append .CreateParameter("@societeID", adInteger, adParamInput, 4, Forms![frmtrad_commandes2]![societeID])

still not sure what the fix is though. In sql server this is an initeger field
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 38857799
Code looks fine. Did you try executing the procedure directly from sqlserver management studio? not sure if the order of parameters is causing the error.
try changing

With objCom
     .CommandText = "sp_Access_AddTranslationContract"     'Name of the stored procedure
     .CommandType = adCmdStoredProc  'Type : stored procedure
     .ActiveConnection = GetConnectString
    .Parameters.Append .CreateParameter("@societeID", adInteger, adParamInput, , Forms![frmtrad_commandes2]![societeID])
    .Parameters.Append .CreateParameter("@GroupName", adVarWChar, adParamInput, 150, Me.tmpGroupName)
    .Parameters.Append .CreateParameter("@ClientCode", adVarWChar, adParamInput, 50, Forms![frmtrad_commandes2]![ClientCode])
    .Parameters.Append .CreateParameter("@ClientReference", adVarWChar, adParamInput, 150, Me.ClientReference)
End With

to


With objCom
     .CommandText = "sp_Access_AddTranslationContract"     'Name of the stored procedure
     .CommandType = adCmdStoredProc  'Type : stored procedure
     .ActiveConnection = GetConnectString
    .Parameters.Append .CreateParameter("@GroupName", adVarWChar, adParamInput, 150, Me.tmpGroupName)
    .Parameters.Append .CreateParameter("@ClientCode", adVarWChar, adParamInput, 50, Forms![frmtrad_commandes2]![ClientCode])
    .Parameters.Append .CreateParameter("@societeID", adInteger, adParamInput, , Forms![frmtrad_commandes2]![societeID])
    .Parameters.Append .CreateParameter("@ClientReference", adVarWChar, adParamInput, 150, Me.ClientReference)
End With

0
 
LVL 1

Author Comment

by:Shawn
ID: 38857817
wow, that simple. It was the order. thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question