raise message from Update Trigger from External App

Hello,
I can raise this message from an Update Trigger in Query Analyzer when I
update the recordID field of my table:
RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
Is it possible to raise this message from an external app? How is this
achieved? Actually, I am sure this is possible because I remember doing it.
I just can't remember what I did because I did not document it.
Thanks,
RichWhat is this "external app"? An application that you write yourself? A TSQL
error (which is what you
raise using RAISERROR) is returned to the client application. The client app
lications is connected
to the database using an API, like ADO.NET. And, sure, you can have your dat
abase application
connect to SQL Server and issue a RAISERROR command, and have that error mes
sage be returned to the
same app, but that sounds a bit ... meaningless. If you give us more informa
tion, we can probably
give some suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
> Hello,
> I can raise this message from an Update Trigger in Query Analyzer when I
> update the recordID field of my table:
> RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
> Is it possible to raise this message from an external app? How is this
> achieved? Actually, I am sure this is possible because I remember doing i
t.
> I just can't remember what I did because I did not document it.
> Thanks,
> Rich|||The external app in this case is an Access ADP. I had to modify a trigger a
few months ago, and I added a raiseerror message at the end to see my result
s
in QA - not error results - just checking what parameter was being used. I
accidentally left the raiseerror message in the trigger, and then I got a
call from an End User stating that this message was coming up all of a sudde
n
when she made updates to the table.
I found the table and reactivated the raiseerror message and I get it when I
updaet a field. The only thing I noticed is that the field I update in this
table (the master table) is not a key field. In the Detail table when I
update the RecordID field this action does not raise the message like in the
Master table. I guess my question is if this is something fundamental that
I
am missing or is it something that I need to dig around to see what is going
on?
"Tibor Karaszi" wrote:

> What is this "external app"? An application that you write yourself? A TSQ
L error (which is what you
> raise using RAISERROR) is returned to the client application. The client a
pplications is connected
> to the database using an API, like ADO.NET. And, sure, you can have your d
atabase application
> connect to SQL Server and issue a RAISERROR command, and have that error m
essage be returned to the
> same app, but that sounds a bit ... meaningless. If you give us more infor
mation, we can probably
> give some suggestion.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
>|||Well, I was able to raise that message if I physically update the RecordID -
meaning I go to the live table in the Access ADP which is the same thing tha
t
was going on with the Master table - where the End user was physically
writing to the table through a form. But if I update the table
programmatically from the ADP, then the message does not come up.
While I am at it, I want to alter/replace my raiseerror message. I used the
sp_addmessage sp. Since my message already exists as 50001, I don't want to
add another message. I want to alter this one. But I get an error message
in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
don't know the syntax for this. I have tried variations such as:
USE master
EXEC sp_addmessage 50001, 16,
select replace('This is a test custome message', 'custome', 'custom')
and placing REplace in other locations with no success. Any suggestions how
to do this correctly would be greatly appreciated.
"Rich" wrote:
> The external app in this case is an Access ADP. I had to modify a trigger
a
> few months ago, and I added a raiseerror message at the end to see my resu
lts
> in QA - not error results - just checking what parameter was being used.
I
> accidentally left the raiseerror message in the trigger, and then I got a
> call from an End User stating that this message was coming up all of a sud
den
> when she made updates to the table.
> I found the table and reactivated the raiseerror message and I get it when
I
> updaet a field. The only thing I noticed is that the field I update in th
is
> table (the master table) is not a key field. In the Detail table when I
> update the RecordID field this action does not raise the message like in t
he
> Master table. I guess my question is if this is something fundamental tha
t I
> am missing or is it something that I need to dig around to see what is goi
ng
> on?
> "Tibor Karaszi" wrote:
>|||You more or less lost me on the logic part, but from a technical viewpoint:
If you see the error when executing a statement which will result in the tri
gger being called using
TSQL but not when using ADP, then probably ADP is masking that error and you
should check with an
Access group to see how you can rectify this behavior in Access. Use Profile
r to catch the TSQL
command being submitted from Access just to make sure of what is happening o
n the server level.
Here is how you replace a message text:
EXEC sp_addmessage 50001, 16,
N'Old message text'
GO
EXEC sp_addmessage 50001, 16,
N'NEW message text', @.replace = 'replace'
GO
RAISERROR(50001, -1, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
> Well, I was able to raise that message if I physically update the RecordID
-
> meaning I go to the live table in the Access ADP which is the same thing t
hat
> was going on with the Master table - where the End user was physically
> writing to the table through a form. But if I update the table
> programmatically from the ADP, then the message does not come up.
> While I am at it, I want to alter/replace my raiseerror message. I used t
he
> sp_addmessage sp. Since my message already exists as 50001, I don't want
to
> add another message. I want to alter this one. But I get an error messag
e
> in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
> don't know the syntax for this. I have tried variations such as:
> USE master
> EXEC sp_addmessage 50001, 16,
> select replace('This is a test custome message', 'custome', 'custom')
> and placing REplace in other locations with no success. Any suggestions h
ow
> to do this correctly would be greatly appreciated.
>
> "Rich" wrote:
>|||Thank you for explaining how to replace a custome message.
"Tibor Karaszi" wrote:

> You more or less lost me on the logic part, but from a technical viewpoint
:
> If you see the error when executing a statement which will result in the t
rigger being called using
> TSQL but not when using ADP, then probably ADP is masking that error and y
ou should check with an
> Access group to see how you can rectify this behavior in Access. Use Profi
ler to catch the TSQL
> command being submitted from Access just to make sure of what is happening
on the server level.
> Here is how you replace a message text:
> EXEC sp_addmessage 50001, 16,
> N'Old message text'
> GO
> EXEC sp_addmessage 50001, 16,
> N'NEW message text', @.replace = 'replace'
> GO
> RAISERROR(50001, -1, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
>

radio button data binding-how?

hi,

i have a DB that contains some tables.so i have a radio button group of tow radio button to display a field.

now how can i bind this radio button group to this field for updating and insert a new record?

for more explain i brought a little of code below:

in .aspx page i have:

<asp:RadioButton ID="admin" runat="server" Checked="True" GroupName="membertype" Text="admin" OnCheckedChanged="admin_CheckedChanged" /><br /> <asp:RadioButton ID="member" runat="server" GroupName="membertype" Text="member" />

-----------------------

<asp:ControlParameter ControlID="membertype" Name="isadmin" Type="string" PropertyName="text" />

-----------------------

UpdateCommand="UPDATE UserManagement SET UserName = @.UserName, Password = @.Password, FullName = @.FullName, Description = @.Description, UserID = @.UserID ,isadmin=@.isadmin,usercitycode=@.location WHERE (UserID = @.Original_UserID)"

note that the type of the "isadmin" field is "nvarchar(50)"

thanks,

M.H.H

Have you considered using a RadioButtonList control instead? With this, you can bind the SelectedValue property to your Parameter.

|||

thanks for your reply,

no i dont use radiobuttonlist because it doesnt have any groupname property.

so i want to join two radio buttons.

i dont know how can i do this because im starter.

thanks,

M.H.H

Radio button - not reqire to select!

I am currently using a web form to insert data into SQL DB.
The radio button for Gender is not a require field.
currently if I use this command is in VB
' cmd.Parameters.Add(New SQLParameter("@.Gender", rblGender.SelectedItem.text)) >
it work fine if the user select one of the radio button, if the user don't select any button then it will give an error.
so I thought I can try the if else statement to see if it work, unfortunately it didn't either and error out on rblGender.SelectedItem.text
ex.
If rblGender.SelectedItem.text <> "" then
cmd.Parameters.Add(New SQLParameter("@.Gender", rblGender.SelectedItem.text))
Else
cmd.Parameters.Add(New SQLParameter("@.Gender", ""))
End IF
Any help would be appreciated.
hydro

You could do something like this:
If rblGender.SelectedIndex <> -1 then
cmd.Parameters.Add(New SQLParameter("@.Gender", rblGender.SelectedItem.text))
Else
cmd.Parameters.Add(New SQLParameter("@.Gender", DbNull.Value))
End IF
|||

That work!! Thanks!!

hydro

sql

R

Hello,

I want to make a stored procedure to search row :

How can I do to search only on begin of field ?

Thanks

Don′t know if I understand you right, but that should be something like that:

CREATE Table SomeTable
(
SomeColumn VARCHAR(200)
)

GO

CREATE PROCEDURE SomeProcedure
(
@.SomeSearchValue VARCHAR(200)
)
AS
SELECT SomeColumn FROM SomeTable
WHERE SomeColumn LIKE @.SomeSearchValue + '%'

GO

INSERT INTO SomeTable VALUES ('SomeValue')

EXEC SomeProcedure 'Value'

--(0 row(s) affected)

EXEC SomeProcedure 'Some'

--SomeValue

--(1 row(s) affected)

--Clean the House

DROP PROCEDURE SomeProcedure
GO
DROP Table SomeTable

HTH, Jens Suessmeyer.

Quoted Field and Escape Character Issue

I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.

So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?

Unfortuanately, the current Flat file parser does not know how to parse embedded qualifiers.

As a workaround, you should probably keep the qualifiers in the flat file source and process them downstream using either the script component or Derived Column.

Thanks.

Quote in input field yeilds error

Hello!

I have an input form that contains a textarea in which people can input the description of an item. They then click Insert or Update and the information is inserted or updated to a SQL Server database. Everything works fine unless someone includes a quote in the description. For example:

The item is Bob's computer.

The apostrophe in Bob creates a problem. I receive the following:

Incorrect syntax near 's'. Unclosed quotation mark before the character string '.

I understand the problem. How do I correct it?

Thanks!

PS I am using C#.Use parameters.

See here|||OF COURSE!! I knew I had done this at some time...thanks for JOGGING my brain!! :)|||I have the same problem, but I don't see how that tutorial would work for an imput text box. If the user types in something like "Mike's car" (without the quotes), it ruins the sql string. How can I code around, or get the server to accept single quotes or apostrophes?
Thanks,
Sean|||The previous link will in fact resolve the problem. Honest.

A poorer alternative is replacing all ' with two ' characters ('' - this is NOT a regular quote, but two single quotes). Doing this still allows SQL Injection attacks to occur.|||Sorry, but I don't see how to apply it to an update statement. Here's a piece of my code:

Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim strPurpose as string =txtPurpose.text
Dim MySQL as string = "Insert into tbl_ExpsReports (expsPurpose) values ('" & strPurpose & "')"
Dim myConn As New OLEDBConnection(configurationSettings.AppSettings("MSDBconn"))
Dim Cmd as New OleDbCommand(MySQL, MyConn)
MyConn.Open()
cmd.ExecuteNonQuery
MyConn.close()
End Sub

How do I allow the user to key in a single quote or apostrope into the txtPurpose text box? The tutorial seems to be geared towards a return rather than input statement.
Thanks,
Sean|||What you are doing is not that unusual:

Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim strPurpose as string =txtPurpose.text
Dim MySQL as string = "Insert into tbl_ExpsReports (expsPurpose) values (?)"

Dim myConn As New OLEDBConnection(configurationSettings.AppSettings("MSDBconn"))
Dim Cmd as New OleDbCommand(MySQL, MyConn)
Cmd.Parameters.Add("expsPurpose",strPurpose)

MyConn.Open()
cmd.ExecuteNonQuery
MyConn.close()
End Sub
</code>|||So, what you are saying is, if I use a perameterized insert statement, then the user can key in an apostrophe or single quote? Cool! ;^]|||Yes. And prevents SQL Injection.|||SQL injection... hmmmm... sounds bad.

Quick method to determine relative position of the index entry

I have a large table with some indexes.
And I have a value for an indexed field. So I'd like to get the order of the first entry of this value in a specified index.

I'd like to write a stored procedure, which returns such number for given value and index name.

How can I do it?I have found a quik decision!

select count(distinct indexed_col_name)
from Table
where index_col_name < value

Other ideas (with Cursor , for example) are slower.

Quick Method to delete from Two Tables

I have two tables ( a & b ) Both are linked by a ledgerref field. table what
would be the quickest and easiest way to delete records from both when
a.textStatus = 1the only way.. the usual way
delete from b from a,b
where b.ledgerref = a.ledgerref
and a.textStatus = 1
delete from a where textStatus = 1
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||and of course enclose it with a transaction :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If the two tables are PK-FK linked, you could use CASCADE DELETE.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:1523DBF8-B27F-4351-970C-AF93A4316705@.microsoft.com...
>I have two tables ( a & b ) Both are linked by a ledgerref field. table
>what
> would be the quickest and easiest way to delete records from both when
> a.textStatus = 1|||Or getting it out of dialect, and correcting the "textStatus" data
element name (test and status are both suffixes to an attribute in
ISO-11179). I will not comment on the practice of using flags in SQL
to mimic an assembly language programming, or redundant tables to mimic
scratch tapes.
DELETE FROM Beta
WHERE EXISTS
(SELECT *
FROM Alpha
WHERE Beta.ledger_ref = Alpha.ledger_ref
AND Alpha.foobar_status = 1);
DRI action would be better. The best solution would be a proper
relational design.