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
)
ASBEGIN TRANSACTION
DECLARE @.balanceAS money
select @.balance = balance
from bank.dbo.bill
where no_bill = @.no_billselect @.totalcost = totalcost
from games.dbo.totalcost
where no_order = @.no_orderif (@.balance > @.totalcost)
begin
set @.balance = @.balance - @.totalcostUPDATE 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'
endCOMMIT 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"
)
ASBEGIN TRANSACTION
DECLARE @.balanceAS money
select @.balance = balance
from bank.dbo.bill
where no_bill = @.no_billselect @.totalcost = totalcost
from games.dbo.totalcost
where no_order = @.no_orderif (@.balance > @.totalcost)
begin
set @.balance = @.balance - @.totalcostUPDATE 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'
endCOMMIT 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.
