RAISERROR

Hello,

I am raising an error on my SQL 2005 procedure as follows:

RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)

How can I access it in my ASP.NET code?

Thanks,

Miguel

See the following post

http://forums.asp.net/p/639921/639921.aspx#639921

Radiobutton List from SQL Server Table

Hi! There must be a very simple answer to this question, but it's my first venture into using SQL Server data.

I've set up an ASP.NET site to provide for on-line sign-up of events. The site has a masterpage and utilizes a wizard to create an e-mail to request sign-up for an event.

I'd like to make my radiobuttonlist dynamic so that events past the signup deadline will not be displayed. So, I've created a database table that includes two fields: event_description and event_date – such that the radiobuttonlist will only show those events that are at least three days beyond today's date.

I've configured the data source and the radiobuttonlist so that I can display all the entries in the event table, but I can't figure out how to restrict them to today + 3 events only.

I'm guessing my problem is in the "Add Where Clause" of the "Configure Data Source" wizard. I'm sure the "Column" should be set to event_date and the "Operator" should be set to ">", but what should I set the "Source" & "Parameter Properties" to? I've set a variable in my program as "Dim deadline As Date = Today.AddDays(3)", but the wizard doesn't seem to see it.

Can anyone steer me in the right direction?

Thanks, Jerry

Try this for your where clause:

WHERE

(event_date>DATEADD(Day, 3,GETDATE()))|||

limno:

Try this for your where clause:

WHERE

(event_date>DATEADD(Day, 3,GETDATE()))

Limno,

Thanks, works like a "champ!"

From your profile, I see you're from Iowa. ...Go 'Clones!

...Jerry

|||

Hi Jerry:

I should add Ames to my profile. Have a nice day.

Quotation Marks in SQL Server

I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website.

The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.

What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')

If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.

Try the links below for all the info you need including how to enable QUOTED_IDENTIFIER option in your create database statement and the restrictions. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

http://msdn2.microsoft.com/en-US/library/ms176027.aspx

|||

Search the forums for:

A) Parameterized SQL Query (What you should do)

B) SQL String concatenation (What you are probably doing)

C) SQL Injection attack (The security problems of doing B instead of A)

|||

Motley:

Search the forums for:

A) Parameterized SQL Query (What you should do)

B) SQL String concatenation (What you are probably doing)

C) SQL Injection attack (The security problems of doing B instead of A)

Not worried about SQL Injection attacks. This is an intranet app.|||

Caddre:

Try the links below for all the info you need including how to enable QUOTED_IDENTIFIER option in your create database statement and the restrictions. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

http://msdn2.microsoft.com/en-US/library/ms176027.aspx

Can you enable the Quoted_Identifier only when you create a new table?|||You can do it in your create database statement or create table, the how for table is covered in the second link. Hope this helps.|||After reading the replies and links that were posted, I feel that I need to reiterate my question.

There is a page that displays records from a SQL Server DB. These records are "announcements" on an internal bulletin board. An admin has a special page that gives the admin the ability to edit, add or delete any of these records.

For instance, an admin can add an announcement (using a textbox) that says, 'This sentence has "Quotation Marks" in it'. I want to be able to search that specific phrase for the quotation marks and replace them with the appropriate code so that they may be displayed on the bulletin board. I don't want the admin to have to type double quotes or double apostrophes in order for them to show up.

The page needs to be user friendly with any concates or alterations to occur server side.

So if anyone can tell me the proper way of say

if str.chars(x) = "<quotation>" then ...

that would be much appreciated.

This is the javascript that generates the pop-up window:
Alert Descrip

<script language="javascript">
//popup function which recieves the email group description and name as parameters
function popitup3(description, name)
{
newwindow2=window.open('','name','height=400,width=600,scrollbars=yes');
var tmp = newwindow2.document;
tmp.write('<html><head><title>Alert Description</title>');
tmp.write('</head><body><font face="verdana, tahoma, sans-serif" size="2"');
tmp.write('b><br><p align="justify">');
tmp.write(description);
tmp.write('</p><p><a href="javascript:self.close()">close</a> this window.</p>');
tmp.write('</body></html>');
tmp.close();
}
</script>

Variable description is where the text with the quotes would most likely be.|||

I am sorry I did not understand your original post you are looking for ANSI SQL LIKE and Pattern search. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp

Quickest method of updating a Table

I have a DataSet which I am holding in .NET and I would like to know the quickest way to get the DataSet in to a Table on SQL Server?

Any sample code would be great.

How many tables you have in dataset?

Fastest and easiest way to do it is probably using Data Adapter.

Thanks

|||Only one table, with over 1,000,000 records|||

And How you generate this data in your DataSet it is loaded from somewhere?

Fastest way will be to save it to file and next import to SQL using SSIS package or bulk copy command.

You can also try DataAdapter but

Thanks

it|||

And How you generate this data in your DataSet it is loaded from somewhere?

Fastest way will be to save it to file and next import to SQL using SSIS package or bulk copy command.

You can also try DataAdapter but

Thanks

it is|||

And How you generate this data in your DataSet it is loaded from somewhere?

Fastest way will be to save it to file and next import to SQL using SSIS package or bulk copy command.

You can also try DataAdapter but it

Thanks

|||

Hi CopperMill,

I agree that you can use SSIS to import data. You can also use bcp utility to perform fast bulk copy.

The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

For more information, please check

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

If the data you need to insert has to be stay in the DataSet, The only way I can see is to use a SqlDataAdapter

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and we will look into it again. Thanks!

Quick Transaction Question.

If in .NET I open a connection to my database, then use some sql text to start a transaction, then reuse that same open connection to call several stored procedures (using SqlCommand with CommandType.StoredProcedure), before ending the transaction. Will that run as a single transaction that can be rolled back? or are the stored procedure calls unable to roll-back after each one completes?

the stored procedures are themselves atomic and would need their own rollbacks. your use of transaction would work if all your statements were sql strings executing one after the other. -- jp

|||

Did a bit of research, and it appears there is a BeginTransaction method that can be used as part of the SqlConnection object. Which allowed me to start a transaction on a connection, then call serveral stored procedures using a SqlCommand object set to CommandType.StoredProcedure, and it all works as a single transaction, rolling everything back if any of then calls fail (internally or enternally). Here is a quick code snippet:

1protected void btnSave_Click(object sender, EventArgs e) {2//Create connection string for SQL query3 String strConnect;4 strConnect = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;56//Generate call to stored procedure7 SqlConnection con =new SqlConnection(strConnect);8 SqlTransaction trans =null;910//Make Calls11string myNull =null;12try {13 con.Open();14 trans = con.BeginTransaction();15int ret1 = spCall(con, trans,"two");16int ret3 = spCall(con, trans, myNull);17int ret2 = spCall(con, trans,"three");18 trans.Commit();19 Master.Message.CssClass ="Text_Message";20 Master.Message.Text = ret1.ToString();21 Master.Message.Visible =true;22 }23catch (Exception sql) {24 Master.Message.CssClass ="Text_Error";25 Master.Message.Text = sql.Message;26 Master.Message.Visible =true;27if (null != trans) {28 trans.Rollback();29 }30 }31finally {32 con.Close();33 con.Dispose();34 }35 }3637protected int spCall(SqlConnection myConn, SqlTransaction myTrans,string myParam) {3839//Generate call to stored procedure40 SqlCommand storedProcCommand =new SqlCommand("spTest", myConn);41 storedProcCommand.CommandType = CommandType.StoredProcedure;4243//Build SQL parameter list44 storedProcCommand.Parameters.AddWithValue("@.value", myParam);4546//Return code47 SqlParameter retParam = storedProcCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int);48 retParam.Direction = ParameterDirection.ReturnValue;4950//Bind to transaction51 storedProcCommand.Transaction = myTrans;5253//Run stored procedure54int retCode = 0;55 SqlDataReader Reader = storedProcCommand.ExecuteReader();56 retCode = (int)storedProcCommand.Parameters["@.ReturnValue"].Value;57 Reader.Close();5859return retCode;60 }

 
1CREATE PROCEDURE [dbo].[spTest]2--Parameters3 @.valuevarchar(50) =null4AS56BEGIN78SET NOCOUNT ON;910INSERT INTO tbTest11 (12 [value]13 )14VALUES15 (16 @.value17 )1819RETURN@.@.Identity2021END
My first call inserts okay, the second call fails because nulls are not accepted by [value] in the table definition., and the third call never happens due to the exception thrown by the second call, which forced a rollback of the entire transaction, of which each stored procedure is a call of. Did a fair amount of testing and everything appears to be in order, if anyone notices anything I overlooked and or that could be problematic, please let me know.

Questions on SQLXML

Hello all,
Newbie here and have a few questions. I wish to use the classes
SqlXmlAdapter,SqlXmlCommand,etc in my ASP.NET project but when I add
'using Microsoft.Data.SqlXml', the IDE doesn't see the namespace. I
tried to add the reference but I dont see it.
1. Why is that? Do I have to download and install SqlXml 3.0 SP2? Does
that add the namespace? I searched for Microsoft.Data.SqlXml.dll on my
hard drive and didn't find it.
2. If I do need to install this patch, why can I do a "Select * from
Product FOR XML AUTO" and have it work already? Does SQL need this
patch or is it a development thing.
3. What does this patch change. I don't mind installing it on my
development PC but kinda worried about my production server. Can I
uninstall this if anything goes wrong?
4. Production server is Windows Server 2003. I am using Windows
Professional. Does WinSvr 2003 need this patch?
5. The patch has XML 3.0 in it. Is this install xml 3.0 or 4.0? I have
already installed MSXML 4.0 on my production server, would installing
this patch downgrade it to xml 3.0 if in fact it is install xml 3.0?
Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
Some, but not all, answers below.
Best regards
Michael
"Ralph Krausse" <gordingin@.consiliumsoft.com> wrote in message
news:49eb6317.0408191143.37ca057f@.posting.google.c om...
> Hello all,
> Newbie here and have a few questions. I wish to use the classes
> SqlXmlAdapter,SqlXmlCommand,etc in my ASP.NET project but when I add
> 'using Microsoft.Data.SqlXml', the IDE doesn't see the namespace. I
> tried to add the reference but I dont see it.
> 1. Why is that? Do I have to download and install SqlXml 3.0 SP2? Does
> that add the namespace? I searched for Microsoft.Data.SqlXml.dll on my
> hard drive and didn't find it.
Yes, you need SQLXML 3.0 (SP1 or SP2) downloaded.

> 2. If I do need to install this patch, why can I do a "Select * from
> Product FOR XML AUTO" and have it work already? Does SQL need this
> patch or is it a development thing.
That is because FOR XML is a server side command that is executed on the
database server and not part of ASP.Net or any other mid-tier component.

> 3. What does this patch change. I don't mind installing it on my
> development PC but kinda worried about my production server. Can I
> uninstall this if anything goes wrong?
It fixes some bugs and adds identity propagation for Bulkload.
I don't know about uninstall, but I assume that you can.

> 4. Production server is Windows Server 2003. I am using Windows
> Professional. Does WinSvr 2003 need this patch?
WinSvr 2003 does not ship with SQLXML. So you need the ASP.Net enabled
version downloaded.

> 5. The patch has XML 3.0 in it. Is this install xml 3.0 or 4.0? I have
> already installed MSXML 4.0 on my production server, would installing
> this patch downgrade it to xml 3.0 if in fact it is install xml 3.0?
No. MSXML 4.0 should not be affected by you installing SQLXML 3.0.

>
>
>
> Thanks
> Ralph Krausse
> www.consiliumsoft.com
> Use the START button? Then you need CSFastRunII...
> A new kind of application launcher integrated in the taskbar!
> ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg