Passing multiple variables to stored procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Passing multiple variables to stored procedure

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 05:55:59

Hi there,

I am using an SQL Server 2000 database with an ASP front end and I need to pass 6 variables to a stored procedure. The variable names are as follows:

SiteCode
Title
Add1
Add2
Add3
PostCode

These are all string values to be inserted into varchar fields in the database, Including the primary key (Don't ask....I didn't design this part....haha)

I'm having trouble with the syntax for the statement to execute the stored procedure (spEditProperty). Here is the statement:

connection.execute "spEditProperty " & SiteCode & "," & Title & "," & Add1 & "," & Add2 & "," & Add3 & "," & PostCode ....

I am particularly unsure of the syntax to close the statement.....have been getting a number of errors such as:

Object required: "

and

"Unterminated string constant"

Any ideas?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-10 : 06:01:20

enclose the string in single quote

connection.execute "spEditProperty '" & SiteCode & "','" ...

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 06:05:14

quote:
Originally posted by khtan

enclose the string in single quote

connection.execute "spEditProperty '" & SiteCode & "','" ...

KH

Ok, have done as follows:


connection.execute "spEditProperty '" & SiteCode & "','" & Title & "','" & Add1 & "','" & Add2 & "','" & Add3 & "','" & PostCode & "'"

it says Object required: "

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 06:08:12

Is either of the parameters empty or NULL?

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 06:13:03

quote:
Originally posted by Peso

Is either of the parameters empty or NULL?

Peter Larsson
Helsingborg, Sweden

umm....well I was actually using the 'live data view' in dreamweaver to test the syntax there.....but it doesn't work when trying to pass the data to the SP either.....even when none of the fields are empty or NULL

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 06:43:24

I'm bordering on giving up on this one and just executing an UPDATE statement in the ASP page.....only problem is this will mean having to create another DSN as the database I need to update is not the one I am already working with =S

Is this totally unadvisable?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 06:48:08

Don't use dynamic SQL. This is a proper way to do these things.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 07:13:14

quote:
Originally posted by Peso

Don't use dynamic SQL. This is a proper way to do these things.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

...but wasn't he doing it client side instead of using stored procedures?

(I have to admit....this project, perhaps unwisely, uses mainly client side procedures so far anyway.....so I'd probably be better off continuing that way for its remainder.....?!) Perhaps something to consider for next time....assuming I can actually get my head round it =S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 07:30:01

No, look at my reply on 08/21/2006 : 10:27:08.

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 07:37:34

quote:
Originally posted by Peso

No, look at my reply on 08/21/2006 : 10:27:08.

Peter Larsson
Helsingborg, Sweden

Ok....I'll try and follow it through.....I think I get it.....but I'm not going to hold my breath.....because it may potentially take some time and I might suffocate......haha

Thanks for your help though :)

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 08:02:11

Could u possibly just explain the following few lines to me?


Dim cn As ADODB.Connection, cmd As ADODB.Command

   On Error Resume Next
   Set cn = modDatabase.Connection(adModeRead)

   Set cmd = modDatabase.Command(cn, "uspDimensionSave")

1) What is modDatabase?
2) I'm using ASP and it doesn't like the "AS ADODB.connection" or "As ADODB.Command" parts.....can I get round that?
3)Is there any code I need to add in the ASP page before all of this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 08:05:00

This is taken from a VB application, the cn variable is an ordinary ADODB connection. Create one for yourself. Also cmd variable is an ordinary ADODB command.
I just pointed out HOW to use parameters.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-10 : 09:34:37

>>These are all string values to be inserted into varchar fields in the database, Including the primary key (Don't ask....I didn't design this part....haha)

If possible, change it

http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 11:28:13

quote:
Originally posted by madhivanan

>>These are all string values to be inserted into varchar fields in the database, Including the primary key (Don't ask....I didn't design this part....haha)

If possible, change it

http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Yeah tell me about it.....I may be able to do it when I transfer records over from the old database.....hopefully.....

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 11:29:28

quote:
Originally posted by Peso

This is taken from a VB application, the cn variable is an ordinary ADODB connection. Create one for yourself. Also cmd variable is an ordinary ADODB command.
I just pointed out HOW to use parameters.

Peter Larsson
Helsingborg, Sweden

Ok, here's what I've done so far.....it doesn't produce any errors, however it doesn't appear to be updating the database either.....can u see the problem?



<%

SiteCode=request.form("SiteCode")
Title=request.form("Title")
Add1=request.form("Add1")
Add2=request.form("Add2")
Add3=request.form("Add3")
PostCode=request.form("PostCode")

Dim cn
Dim CmdStoredProc
Set cn = Server.CreateObject("ADODB.Connection")
Dim strConnect
strConnect="DSN=Engineering;UID=***;PWD=***"
cn.open strConnect

Set CmdStoredProc = Server.CreateObject("ADODB.Command")
CmdStoredProc.ActiveConnection = cn

CmdStoredProc.CommandText = "spEditProperty"
CmdStoredProc.CommandType = adCmdStoredProc

CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@SiteCode", adVarChar, adParamInput, 255, SiteCode)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@Title", adVarChar, adParamInput, 255, Title)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@Add1", adVarChar, adParamInput, 255, Add1)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@Add2", adVarChar, adParamInput, 255, Add2)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@Add3", adVarChar, adParamInput, 255, Add3)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@PostCode", adVarChar, adParamInput, 255, PostCode)

CmdStoredProc.Execute , , adExecuteNoRecords

Set CmdStoredProc = Nothing

%>

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-10 : 11:30:03

...and here's my stored procedure....

CREATE PROCEDURE spEditProperty

@Title varchar(255),
@SiteCode varchar(255),
@Add1 varchar(255),
@Add2 varchar(255),
@Add3 varchar(255),
@PostCode varchar(255)

AS

UPDATE Property.dbo.tProperties SET PropertyName = @Title WHERE PropertyNo = @SiteCode
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 14:18:00

What is Add1, Add2, Add3 and PostCode used for?

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-11 : 04:35:57

quote:
Originally posted by Peso

What is Add1, Add2, Add3 and PostCode used for?

Peter Larsson
Helsingborg, Sweden

Sorry, I just realised I posted an incomplete version of my stored procedure =S Those fields are for a property address like this:

Add1: 1 The Street
Add2: New York
Add3: USA
PostCode: (PostCode/ZipCode here)

Something like that......they are simply the variables that I need to pass to the stored procedure to insert into the database....I just haven't added the code to the SP to do it yet....

I didn't design this database though.....and I can't really change it now.....so I take no responsibility if it sucks :P haha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-11 : 04:58:34

After

CmdStoredProc.Execute , , adExecuteNoRecords

What is the collection CmdStoredProc.ActiveConnection.Errors? What is the Count?

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-11 : 05:15:27

quote:
Originally posted by Peso

After

CmdStoredProc.Execute , , adExecuteNoRecords

What is the collection CmdStoredProc.ActiveConnection.Errors? What is the Count?

Peter Larsson
Helsingborg, Sweden

Neither CmdStoredProc.Errors.count or CmdStoredProc.ActiveConnection.Errors appear to return a value

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-11 : 05:24:30

...yes.....response.write(CmdStoredProc.ActiveConnection.Errors.Count) returns 0

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources