Proper use of IF statement in stored procedure?

I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:

Cannot use empty object or column names. Use a single space if necessary.

So, what's the correct way of doing the following?

IF @.filename <> ""
BEGIN
UPDATE Experimental_Images SET contentType = @.contentType, filename = @.filename WHERE (id = @.iconNo)
ENDThe problem you are having is that you are using double quotes when youshould be using single quites. The rest of your statements are fine.

What do you mean exactly by "if the parameter is empty". If youmean if it contains an empty string or a NULL, try it like this:

IF ISNULL(@.filename,'') <> ''

If you mean it contains a NULL, try this:

IF @.filename IS NOT NULL

And if you mean it contains an empty string, try it like this:

IF @.filename <> ''

Proper Syntax of CASE() ion a cell in RS?

Hello All;
I was wondering if there is a way to make decisions on a particular cell in a
report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
(SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
can be expanded on Cell's content so as to get the Effect of CASE ot IF
ElseIF Else as follows:
If (SOMTHING) Then (DoSomething)
(ElseIF DOSomethingElse1)
(ElseIF DoSomethingElse2)
Else
(DoOtherwise)
Thank you.
--
Message posted via http://www.sqlmonster.comThe best way is to write some code and call it.
In Report properties go to the code page and enter a VisualBasic .Net
function eg:
Function SetColour(ByVal N As Double) As String
Select N
Case 0
Return "White"
Case >0
Return "Blue"
Case Else
Return "Red"
End Select
End Function
then call the function in your expression to set the colour of a cell eg:
=Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
"James Woo via SQLMonster.com" wrote:
> Hello All;
> I was wondering if there is a way to make decisions on a particular cell in a
> report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> can be expanded on Cell's content so as to get the Effect of CASE ot IF
> ElseIF Else as follows:
> If (SOMTHING) Then (DoSomething)
> (ElseIF DOSomethingElse1)
> (ElseIF DoSomethingElse2)
> Else
> (DoOtherwise)
> Thank you.
> --
> Message posted via http://www.sqlmonster.com
>|||Mary .. Thank you :), very nice code.
--
Message posted via http://www.sqlmonster.com|||Based on the information below I have been able to create code, however I
need to test for a value between 2 numbers..ex
Case < 366
Return "1 yr or less"
Case >0 and <731
Return "1-2 years"
I am running into an issue with line 3. Is it possible? Am I using the
wrong syntax? If this is not the best approach what would that be?
Thank you for any guidance.
"Mary Bray [SQL Server MVP]" wrote:
> The best way is to write some code and call it.
> In Report properties go to the code page and enter a VisualBasic .Net
> function eg:
> Function SetColour(ByVal N As Double) As String
> Select N
> Case 0
> Return "White"
> Case >0
> Return "Blue"
> Case Else
> Return "Red"
> End Select
> End Function
> then call the function in your expression to set the colour of a cell eg:
> =Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
> "James Woo via SQLMonster.com" wrote:
> > Hello All;
> >
> > I was wondering if there is a way to make decisions on a particular cell in a
> > report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> > (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> > can be expanded on Cell's content so as to get the Effect of CASE ot IF
> > ElseIF Else as follows:
> > If (SOMTHING) Then (DoSomething)
> > (ElseIF DOSomethingElse1)
> > (ElseIF DoSomethingElse2)
> > Else
> > (DoOtherwise)
> > Thank you.
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||I was able to find some information, this may helps others...
Case < 366
Return "1 yr or less"
Case 0 To 731
Return "1-2 years"
"DigitalVixen" wrote:
> Based on the information below I have been able to create code, however I
> need to test for a value between 2 numbers..ex
> Case < 366
> Return "1 yr or less"
> Case >0 and <731
> Return "1-2 years"
> I am running into an issue with line 3. Is it possible? Am I using the
> wrong syntax? If this is not the best approach what would that be?
> Thank you for any guidance.
> "Mary Bray [SQL Server MVP]" wrote:
> > The best way is to write some code and call it.
> > In Report properties go to the code page and enter a VisualBasic .Net
> > function eg:
> > Function SetColour(ByVal N As Double) As String
> > Select N
> > Case 0
> > Return "White"
> > Case >0
> > Return "Blue"
> > Case Else
> > Return "Red"
> > End Select
> > End Function
> >
> > then call the function in your expression to set the colour of a cell eg:
> >
> > =Code.SetColour( Fields!CurrencyAmount.Value-Sum(Fields!AmountExpensed.Value))
> >
> > "James Woo via SQLMonster.com" wrote:
> >
> > > Hello All;
> > >
> > > I was wondering if there is a way to make decisions on a particular cell in a
> > > report (Tabular of Matrix) where the "CASE" effect can be achieved. I use IIF
> > > (SOMEHTING >0,"RED","Green") all over, but I wonder if the decision making
> > > can be expanded on Cell's content so as to get the Effect of CASE ot IF
> > > ElseIF Else as follows:
> > > If (SOMTHING) Then (DoSomething)
> > > (ElseIF DOSomethingElse1)
> > > (ElseIF DoSomethingElse2)
> > > Else
> > > (DoOtherwise)
> > > Thank you.
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> > >

programmatically create SqlServer JobSchedule

I wanted to programmatically create SqlServer JobSchedule

The job is to copy data from a particular table in one Database to another table in another DataBase
User should be able to Schedule/modify Date and Time on which a job is to be executed is to be configured through a UI Screen (WebForm.aspx)

I am using VSStudion2003 ,ASP.net with VB.net with SQL2000 as database .

ANY idea on how to do this ???

Please help me?

Thank u all in advance

There are some job related system stored procedures in SQL2000. To schedule a existing job, you can use a SQLCommand to call 'sp_add_jobschedule' in your application, with parameters from textboxes of UI. Please go to this website for more information:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_6ijp.asp?frame=true

Programatically viewing the data in a particular column

This is my first attempt to create a Vb.net class as well as my first attempt to programmatically view SQL retrieved data as opposed to using the built-in data controls...so be gentle.

My question is looking at the code below which I have already put together...how do I actually see what the data is in a particular column... One of my columns for example is the "Title" which contains the Page Title for Search Engine Optomization. I need to be able to read this value so I can assign it to the Page.

'First Do a Database Connection

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsString = _

ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString

'Create a SqlConnection instance

Using myConnectionAsNew SqlConnection(connString)

'Specify the SQL query

Const sqlAsString ="SELECT * FROM SEO where ParentType='Department' AND ParentLevel='0' "

'Create a SqlCommand instance

Dim myCommandAsNew SqlCommand(sql, myConnection)

'Get back a DataSet

Dim myDataSetAsNew DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet,"SEO")

'Create a DataView

Dim myDataViewAsNew DataView

myDataView.Table = myDataSet.Tables("SEO")

'Ok...this is where I'm lost. Now how do I look up the value for the column "Title" in this Dataset

'Close the connection

myConnection.Close()

'End database connections

EndUsing

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

To access other columns, simply replace the column name ("Title") with a respective column name

Alternatively you can use the following line to access the value inside the1st row of the coloumn
Dim strAsString =CStr(myDataSet.Tables("SEO").Rows(0).Item("Title"))

To access other rows, simply replace the row number (0) with a respective row number

Hope this helps you.

|||

I'm relatively new too.

Instead of writing the entire class as 'codeaholic' did (looks daunting), can I use the drag'n'drop methodology to place a SqlDataSource1 control onto an ASPX page and configure the datasource with an appropriate Select statement, (easy part) and then use less VB code to find the value of a column for a given record?

In other words, if I want to extract a single column of a record do I have to 'throw away' all those luxury features of design time

|||

i'm personally not familier with using data controls in my pages as im currently using either 2-tier or 3-tier application development, but it's possible to do what you want.
just create the data controls one-by-one till you get a datagrid or dataview object containing the values you wanted, then use code-behind to access the datagrid or dataview objects for your values.
or you could even directly bind your values to controls using the datasource property and other respective properties.
however it may get tedious when you need a large number of data objects to access different values in different tables.

i would also advise strongly against doing so as it would affect the performance of your pages due to unneeded controls needed to be complied and loaded.
and there's always the security issue.
there's always better ways to do it.
hope this helps you.

|||

AnjinG:

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

Hmm, just getting around to trying this and it doesn't work. Using this code above. myColumnValue.toString is simply the word "Title". Its not actually extracting the value of the column Title but simply setting this to the name of the column...

|||

Hi,

you can try doing this to directly access the particular column value
you willl need to traverse the rows in the table

Dim i as integer

For i = 0To myDataSet.Tables("SEO").Rows.Count - 1

Dim vAsString = myDataSet.Tables("SEO").Rows(i).Item(columnIndex)

Next

|||Yeh, I just used the row syntax you indicated as the "second" possibility. Since there is only one row, we don't have to loop it. Thanks.

Programatically re-ordering a "DisplayOrder" column

Anyone know of any resources or information on writing a query that would accept a new "DisplayOrder" for a particular row and re-order the column for all other rows?

Thanks in advance!If you are asking the question I think you are asking, I had posted an answer to thishere.

The idea is to bump up the sequence number for each record that has a sequence number greater than or equal to the new sequence number...BUT only if the sequence number already is on file.

IF EXISTS(SELECT NULL FROM myTable WHERE sequence = @.NewSequence)
 BEGIN
 UPDATE myTable SET sequence = sequence + 1 WHERE sequence >= @.NewSequence
 END
UPDATE myTable SET sequence = @.NewSequence WHERE id = @.idToChange

Terri|||Thank You! A few questions...

IF EXISTS(SELECT NULL FROM myTable WHERE sequence = @.NewSequence)

This tests for the existence of the "@.NewSequence" value?
And what exactly is achieved by "SELECT NULL", I haven't seen that before?

I think this will work well for me...

Also, the sequence number must exist as it is selected from a DropDownList populated from the existing sequence.

Again, Thank You!|||The "SELECT NULL" works the same as "SELECT *" or "SELECT columnname" -- the column(s) selected have no bearing on the success of the EXISTS test. I tend to use NULL because I believe that requires the least amount of resources to process. Others choose to use the primary key column. I am honestly not sure how much of a difference there really is.

All that code is doing is checking to see if the new sequence number is already on file. If it is, then it pushes down all of the sequence numbers from that point forward to create a "space".

I'm glad you could make use of the method :-)

Terri