individual SqlDataSource() or common SqlDataSource() ?

i am using visual web developer 2005 with SQL Express 2005 with VB as the code behind

i haveone database andthree tables in it

for manipulating each table i am using separate SqlDataSource()

is it sufficient to use one SqlDataSource() for manipulating all the three tables ?

i am manipulating all the tables in the same page only

please help me

Hi,
In my opinion I think that the best would be to use a different SqlDataSource object for each database table. I think that to give you a better answer you should have to give us a little piece of your code.

Luis Ramirez.
www.sqlnetframework.com
The SQL framework for .NET.

Infinite Loop in BOL code!?

This code is from BOL (in index type: "DDLs-SQL Server"):
Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?

It looks to me like we would have an infinite loop! .. am I missing something here?

BOL CODE EXAMPLE:
----------
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command

' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute

Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

GoTo Done

End Sub
-----------------Where?|||Originally posted by Brett Kaiser
Where?

If an error occurs within the [Done:] handle then the compiler goes to [AdoError:] handle and when executing the last statement there "GoTo Done" we will end up going back to [Done:] ... then same error occurs .. then back to [AdoError:] .. etc .. that sure looks like an infinite loop!|||If a user who can not create tables runs this, yes, it could run forever. Fortunately, Microsoft makes everything run as "administrator".|||lack of permission to Insert would cause the same problem .. But it's not just lack of permission that might cause this code to run forever .. for instance a network failure might raise the same problem .. a server failure might also do the same thing .. the point is: Error handling is supposed to be very helpful but in this code it is not. Because it is not done right.

Indicating the NULL value in Report Expression Syntax

Hi,

I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.

I am trying to code :

IIF(Value_A = 0, <NULL>, Value_A)

It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.

I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.

Thanks.

regards,
Kenny

Use the VB keyword "Nothing"

=Iif(Fields!Value_A.Value = 0, Nothing, Fields!Value_A.Value)

|||Hi Adam,

Thanks for the tip. Worked like a charm.

Where else can I get a list of VB keywords I can use in Report Designer / SSRS / SQL Server 2005. I have some background with VB6 prior and was hoping I could port a whole bunch of them over to be used here.

Kenny
|||Most global functions were ported to VB.NET and that's what's supported in RS. For more details about RS expressions and the functions start from here http://msdn2.microsoft.com/en-us/library/ms159238.aspx

Indicating the NULL value in Report Expression Syntax

Hi,

I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.

I am trying to code :

IIF(Value_A = 0, <NULL>, Value_A)

It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.

I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.

Thanks.

regards,
Kenny

Use the VB keyword "Nothing"

=Iif(Fields!Value_A.Value = 0, Nothing, Fields!Value_A.Value)

|||Hi Adam,

Thanks for the tip. Worked like a charm.

Where else can I get a list of VB keywords I can use in Report Designer / SSRS / SQL Server 2005. I have some background with VB6 prior and was hoping I could port a whole bunch of them over to be used here.

Kenny
|||Most global functions were ported to VB.NET and that's what's supported in RS. For more details about RS expressions and the functions start from here http://msdn2.microsoft.com/en-us/library/ms159238.aspx

Indexing Columns

If you have a table with 3 columns,

ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently

Code Snippet

Select ID where Col1='SomeValue' and Col2='SomeOtherValue'

Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.

You could create an INDEX on Col1, Col2, and include [ID].

Something like this:

CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );

This is a 'covered' index. The entire query is satisfied by the index.

|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.

|||

For SQL 2000, if you use this query frequently, index all three columns.

|||

you can not define ID as non clustered since it is a PK.

ID can be clustered index and check the unique checkbox.

you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.

|||

You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.

|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.

If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.

So there is no reason to add ID to your non-clustered index since it will be there already.

I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.

Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.

Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.

|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;We could arrive at a decision of using the index on&nbsp;columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database&nbsp;trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left>&nbsp;</P>
<P align=left>Thanks.</P>