Problem Passing Parameters to SQL-Please Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Problem Passing Parameters to SQL-Please Help

Author

Topic

btp111
Starting Member

6 Posts

Posted - 2008-05-06 : 10:46:14

Hi,
This is my first posting and I am new to doing any development and have some simple code that I am having issues with. Details below:

1)SQL Stored Procedure with Parameters:

CREATE PROCEDURE InsIA @mjedln float(8), @mjedoc float(8),@mjitm float(8),@mjlocn char(20) AS

INSERT INTO table1 (MJEKCO, MJEDCT,MJEDLN,MJEDOC,MJPACD, MJAN8,MJMCU,MJITM,
MJLITM,MJAITM,MJLOCN,MJTRNO,MJTRUM,MJTRQT,MJMMCU,MJKCO,MJDCT,MJDCTO,MJTREX,MJRCD)
VALUES ('02000','IA',@mjedln,@mjedoc,'QT',2000.000000,' 2000',@mjitm,
'7211A-SCV-A101','7211A-SCV-A101',@mjlocn,1.000000,'EA',-10.000000,' 2000',
'02000','IA','IA','EWAAERWGF','002')

INSERT INTO table2 (M1EKCO,M1EDCT,M1EDST,M1EDOC,M1AN8)
VALUES ('02000','IA','852',10682007.000000,2000.000000)
GO

**As you can see I have a few parameters. The one I am having an issue with is @mjlocn for Table 1. The other parameters work well. The other parameters update fields that are FLOAT fields and the one I am having issues with updates a Char field.

2)Below is my code to pass Parameters to the stored procedure upon clicking a Button in Access:

Private Sub cmdupdate_Click()

Dim LocalConnection As ADODB.Connection

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim line As Variant
Dim docnumber As Variant
Dim prm As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim item As Variant
Dim itm As ADODB.Parameter
'Dim mjlitm As ADODB.Parameter
'Dim longitem As String
'Dim mjaitm As ADODB.Parameter
'Dim longitem2 As String
Dim mjlocn As ADODB.Parameter
Dim location As String

cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "HLAERP1"
cn.Properties("Initial Catalog").Value = "PS_CRP"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

Set cmd.ActiveConnection = cn
cmd.CommandText = "insIA"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15

Set prm = cmd.CreateParameter("InputLineNumber", adInteger, adParamInput)
cmd.Parameters.Append prm
line = Text14.Value
prm.Value = line

Set prm2 = cmd.CreateParameter("InputDocNumber", adInteger, adParamInput)
cmd.Parameters.Append prm2
docnumber = Text16.Value
prm2.Value = docnumber

Set itm = cmd.CreateParameter("InputItemNum", adInteger, adParamInput)
cmd.Parameters.Append itm
item = IBITM.Value
itm.Value = item

Set mjlocn = cmd.CreateParameter("InputLocation", adChar, adParamInput)
cmd.Parameters.Append mjlocn
location = LILOCN.Value
mjlocn.Value = location
Set rs = cmd.Execute

cn.Close

End Sub

All parameters work great except for the one in italics which is mjlocn. I get the following error after adding this one:

Run Time Error: 3708
Parameter object is improperly defined. Inconsisten or incomplete info provided.

However, I don't have any idea why because I am using the same logic as I did on the float fields (except for parameter type I chose adInteger on those) except I chose adChar for the type of the parameter but I thought that would be correct since they are CHAR format in the table I am inserting to.
Any ideas would be greatly appreciated.

Thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-06 : 13:09:52

You need to specify the size for CHAR and VARCHAR parameters.

- Jeff
http://weblogs.sqlteam.com/JeffS

btp111
Starting Member

6 Posts

Posted - 2008-05-06 : 13:44:38

That worked great. Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources