Solved

error converting data typa nvarchar to int

Posted on 2013-02-05
3
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

742 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