Stored Procedure (1 Viewer)

gezrod · May 28, 2017

I have created a stored procedure "UpdateValues" to update 2 columns in a record which matches a specific ID

The stored procedure executes fine in sql manager.

I am trying to execute the stored procedure within access VBA by providing the ID initially and the 2 other parameters which the record will be updated with.

the parameters within the SP are
@ID
@Value1
@Value2
i.e.

the exec command line expects my parameters, but don't know the syntax to use

sql1 = "exec UpdateValues" PARAMETERS TO PASS HERE ?
Cn1.Execute sql1

pbaldy · May 28, 2017

If they're all numeric:

sql1 = "exec UpdateValues 123,456,789"

Text or date/time values get surrounded by single quotes.

gezrod · May 28, 2017

Thanks Pbaldy,

that works a treat with typed values, can I pass values from an access form ?

i.e.

sql1 = "exec UpdateNettTotalTester [id],[val1],[val2]"

Tried it and get syntax error . .

pbaldy · May 28, 2017

You concatenate them into the string:

sql1 = "exec UpdateNettTotalTester " & [id] & ", " & [val1]...

gezrod · May 28, 2017

Thank you again Pbaldy,

I still get a syntax error

sql1 = "exec UpdateNettTotalTester " & [ID] & "," & [Value1] & "," & [Value2]

gezrod · May 28, 2017

Just had a thought the ID is text, the other two are int, what would the syntax be to surround the ID

sql1 = "exec UpdateNettTotalTester " & [ID] & "," & [Value1] & "," & [Value2]

gezrod · May 28, 2017

Sorted it, thank you pbaldy,

sql1 = "exec UpdateNettTotalTester " & "'" & [ID] & "'" & "," & [Value1] & "," & [Value2]

thank you again

pbaldy · May 28, 2017

Add an apostrophe on either side of the value, inside the quotes.

pbaldy · May 28, 2017

Ah, glad you got it sorted.

Stored Procedure (1 Viewer)

gezrod

New member

pbaldy

Wino Moderator

gezrod

New member

pbaldy

Wino Moderator

gezrod

New member

gezrod

New member

gezrod

New member

pbaldy

Wino Moderator

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread