Solved

error converting data typa nvarchar to int

Posted on 2013-02-05
3
452 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
Comment Utility
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
Comment Utility
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
Comment Utility
wow, that simple. It was the order. thank you
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now