Getting a stored procedures return value -- URGENT !

We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>

GETDATE() to insert into a datetime field

Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:

> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:

> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql

Get user syntax

I am building a view for a report and one of the requirements is to grab the
user who is running the report. What would this syntax look like? I thought
it was getuser(), but no luck there.
Thanks,
Ryan
Ryan,
Depending on whether you want a User or a Login, you can try one of the
following: CURRENT_USER, SYSTEM_USER, USER_NAME, SUSER_SNAME. Check them
out in the Books Online, or just run them and choose what you prefer to use.
RLF
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:266EBEEE-3FB5-448D-A58B-0EF6D8F006F1@.microsoft.com...
>I am building a view for a report and one of the requirements is to grab
>the
> user who is running the report. What would this syntax look like? I
> thought
> it was getuser(), but no luck there.
> Thanks,
> Ryan

Get Saturday's date...

I am building a report that has two parameters that will always be Sunday to
Sunday but I want a text box to show the Saturdays date for the first
parameter which would be the first sunday. Example:
Parameter 1 - 4/15/2007
Parameter 2 - 4/22/2007
I need the text box to read 4/21/2007 which is the saturday of the week that
begins on 4/15.
Any help will be greatly appreciated, Thanks.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+Hi,
You should create a new dataset to retreive your parameter default. Put this
code in a stored procedure and the result should be the last Saturday:
set datefirst 7
declare @.date datetime
set @.date = getdate()
while datepart(dw,@.date) <> 7
begin
set @.date= dateadd(day,-1,@.date)
end
select @.date
If you are using the datefirst with the default settingd, you should not put
it in the code. Also, you should format your date with CONVERT or CAST
function, if required.
Another way is to write a custom code in SSRS, but I was lazy :)
I hope, it helps for you.
Regards,
Janos
"CipherTeKST" <CipherTeKST@.discussions.microsoft.com> wrote in message
news:3F271DBD-A5C5-4550-90FB-95B862A2C682@.microsoft.com...
>I am building a report that has two parameters that will always be Sunday
>to
> Sunday but I want a text box to show the Saturdays date for the first
> parameter which would be the first sunday. Example:
> Parameter 1 - 4/15/2007
> Parameter 2 - 4/22/2007
> I need the text box to read 4/21/2007 which is the saturday of the week
> that
> begins on 4/15.
> Any help will be greatly appreciated, Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+|||Yes, I can do this in SQL with,
SELECT
CONVERT(VARCHAR(10),DATEADD(wk, DATEDIFF(wk, 5, getdate()), 5),101) as
SATURDAY
I was looking for an expression to use in SSRS.
Thanks for your help though!
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"BERKE Janos" wrote:
> Hi,
> You should create a new dataset to retreive your parameter default. Put this
> code in a stored procedure and the result should be the last Saturday:
> set datefirst 7
> declare @.date datetime
> set @.date = getdate()
> while datepart(dw,@.date) <> 7
> begin
> set @.date= dateadd(day,-1,@.date)
> end
> select @.date
> If you are using the datefirst with the default settingd, you should not put
> it in the code. Also, you should format your date with CONVERT or CAST
> function, if required.
> Another way is to write a custom code in SSRS, but I was lazy :)
> I hope, it helps for you.
> Regards,
> Janos
> "CipherTeKST" <CipherTeKST@.discussions.microsoft.com> wrote in message
> news:3F271DBD-A5C5-4550-90FB-95B862A2C682@.microsoft.com...
> >I am building a report that has two parameters that will always be Sunday
> >to
> > Sunday but I want a text box to show the Saturdays date for the first
> > parameter which would be the first sunday. Example:
> > Parameter 1 - 4/15/2007
> > Parameter 2 - 4/22/2007
> >
> > I need the text box to read 4/21/2007 which is the saturday of the week
> > that
> > begins on 4/15.
> >
> > Any help will be greatly appreciated, Thanks.
> > --
> > CipherTeKST
> > MCSE: Security 2003, CCNA, Security+
>