display a message in store procedure

 helo all..., this is my store procedure. but it can not display message. my friend said it must use output code.

can someone add output code to my store procedure, so it can display message?

ALTER PROCEDURE [bank].[dbo].[pay]
(
 @.no_billINT,
 @.no_orderint,
 @.totalcostmoney,
 @.messagevarchar(100)-- make it output parameter in your stored procedure
)
AS
BEGIN TRANSACTION
DECLARE @.balanceAS money
select @.balance = balance
from bank.dbo.bill
where no_bill = @.no_bill
select @.totalcost = totalcost
from games.dbo.totalcost
where no_order = @.no_order
if (@.balance > @.totalcost)
begin
set @.balance = @.balance - @.totalcost
UPDATE bank.dbo.bill
SET
 [balance] = @.balance
WHERE [no_bill] = @.no_bill
-- set @.message = 'your have enough balance'
end
else
begin
set @.message ='sorry, your balance not enough'
end
COMMIT TRANSACTION
set nocount off

pls.., thx 

ALTER PROCEDURE [bank].[dbo].[pay]( @.no_billINT, @.no_orderint, @.totalcostmoney, @.messagevarchar(100)OUTPUT-- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @.balanceAS moneyselect @.balance = balancefrom bank.dbo.billwhere no_bill = @.no_billselect @.totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @.no_orderif (@.balance > @.totalcost)beginset @.balance = @.balance - @.totalcostUPDATE bank.dbo.billSET [balance] = @.balanceWHERE [no_bill] = @.no_billset @.message ='your have enough balance'endelsebeginset @.message ='sorry, your balance not enough'end

|||

yes, thx. but when i execute that procedure, it told to insert no_order, no_bill,totalcost, message. i don't want to insert message, i want to insert no_order,no_bill,totalcost, and click ok, so then message automatic display it..

can u add any code ?

thx...

|||

Using the code posted below, I dont see how it could be wanting to "INSERT" or "UPDATE" message. We are setting the parameter @.message after the update statement is completed.
If you are still getting the error then post the current version of the code you are using.

ALTER PROCEDURE [bank].[dbo].[pay]( @.no_billINT, @.no_orderint, @.totalcostmoney, @.messagevarchar(100)OUTPUT-- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @.balanceAS money select @.balance = balancefrom bank.dbo.billwhere no_bill = @.no_billselect @.totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @.no_orderif (@.balance > @.totalcost)begin set @.balance = @.balance - @.totalcostUPDATE bank.dbo.billSET [balance] = @.balanceWHERE [no_bill] = @.no_billset @.message ='your have enough balance'end else begin set @.message ='sorry, your balance not enough'endCOMMIT TRANSACTION

|||

yes, your code are right, but when i execute your code, it display:

type direction name value

int in no_bill we insert to this,example:110

int in no_order we insert to this,ex:2

money in totalcost we insert to this, ex 100$

char in/out message ??? if i not insert to this, myerror :Procedure or Function 'pay' expects parameter '@.message', whichwas not supplied.

so i must insert it something, then it can work... but i want to only insert no_bill,no_order,totalcost,so message is display automatic after i insert no_bill,no_order,totalcost.. is there can show automatic message without insert data to message?

thx...

|||

When you define an output parameter in a Stored procedure, you still need to pass a parameter in to the stored procedure. In your case you would pass in an empty string param.

Its not inserting into your DB message it just needs to know what to pass your output param back to.

If you need help with calling the stored procedure, then post that code where you are calling it and we can help you call it properly.

|||

thx .., now i want to call that message from pay.aspx. this is my pay.aspx.vb like:

pay.aspx.vb

Protected Sub Button1_Command(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles Button1.Command

Dim sp As String = "pay"
Dim connect As String = ConfigurationManager.AppSettings("ConnectionString")
Dim conn As New SqlConnection(connect)
Dim cm As New SqlCommand(sp, conn)
Dim totalcost As Label = Me.FormView1.FindControl("totallabel")
Dim no As Label = Me.FormView1.FindControl("no_orderlabel")
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.AddWithValue("@.totalcost ", totalcost.Text)
cm.Parameters.AddWithValue("@.no_order ", no.Text)
cm.Parameters.AddWithValue("@.no_bill ", no_bill.Text)
cm.Parameters.AddWithValue("@.message", Label1.Text )

conn.Open()
cm.ExecuteNonQuery() ' do not forget to close Connection
conn.Close()

end sub

that code can work..., but i don't know about to display that message.

if totalcost > balance, show message"sorry, your balance is not enough"

if totalcost<balance, not show message..

pls...

thx...

|||

First change

cm.Parameters.AddWithValue("@.message", Label1.Text )

to

cm.Parameters.Add(New SqlParameter("@.message", SqlDbType.VarChar, 250, ParameterDirection.Output))

Then right after

cm.ExecuteNonQuery() ' do not forget to close Connection

add

Dim message As String - cm.Parameters("@.message").Value

|||

thx, it work.. but not display a message.

so i put label1.text in this, but it still no display a message. this is my code:

Protected Sub Button1_Command(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.CommandEventArgs) Handles Button1.Command

Dim sp As String = "pay"
Dim connect As String = ConfigurationManager.AppSettings("ConnectionString")
Dim conn As New SqlConnection(connect)
Dim cm As New SqlCommand(sp, conn)
Dim totalcost As Label = Me.FormView1.FindControl("totallabel")
Dim no As Label = Me.FormView1.FindControl("no_orderlabel")
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.AddWithValue("@.totalcost ", totalcost.Text)
cm.Parameters.AddWithValue("@.no_order ", no.Text)
cm.Parameters.AddWithValue("@.no_bill ", no_bill.Text)
cm.Parameters.Add(New SqlParameter("@.message", SqlDbType.VarChar, 250, ParameterDirection.Output))

conn.Open()
cm.ExecuteNonQuery() ' do not forget to close Connection

Dim message As String - cm.Parameters("@.message").Value
conn.Close()
label1.text= message

end sub

but the label no show message.. how should i do?

thx...

|||

have you tried debugging and stepping through the code?

|||

yes, i have many try it, but not show a message, maybe i put label1.text in wrong location. can u show that label put in true location?

pls..,thx..

|||

other than changing it to

label1.text = message.tostring()

your code looks correct. When you step through it set a breakpoing on "Dim message as string" so when you step on to conn.close() you can see if message has a value.

|||

i have use breakpoint at row label1.text, it display value text="". it mean not take message from @.message,so empty..

or maybe my store procedure is wrong?

this is my store procedure

ALTER PROCEDURE [bank].[dbo].[paid]
(
 @.no_billINT,
 @.no_orderint,
 @.totalcostmoney,
 @.messagevarchar(100) output="a"
)
AS
BEGIN TRANSACTION
DECLARE @.balanceAS money
select @.balance = balance
from bank.dbo.bill
where no_bill = @.no_bill
select @.totalcost = totalcost
from games.dbo.totalcost
where no_order = @.no_order
if (@.balance > @.totalcost)
begin
set @.balance = @.balance - @.totalcost
UPDATE bank.dbo.bill
SET
 [balance] = @.balance
WHERE [no_bill] = @.no_bill
-- set @.message = 'your have enough balance'
end
else
begin
set @.message ='sorry, your balance not enough'
end
COMMIT TRANSACTION

but i execute in store procedure, it can display message...

so,which is wrong?

thx 

|||

Here is an article for you on another way to define an output parameter.

http://www.sqlservercentral.com/columnists/kKellenberger/usingparameterswithstoredprocedures.asp

Also you have in your stored procedure one line commented out that sets @.message, and your also trying to set a default value using double quotes.

If you want to set a default value for the parameter then set it inside of your begin transaction statement, and also use single quotes in setting it.