Thursday, December 20, 2007

Year End

Well, another year is coming to an end and what a year it has been.  I have seen my Wife and daughter grow to be best little buddies.  Also my daughter learning and an incredible pace.  As for my life in the development world I took on a new job.

 

Switched from R.J. Reynolds to Bank of America in July.  This was a great move with more challenging work.  This year I can add to my resume that I am a Microsoft SharePoint 2007 Guru, Microsoft InfoPath Developer, .NET Web Service Developer, along with SQL Server Reporting Services.

 

Visual Studio 2008, so that will be my new challenge next year.

Tuesday, December 18, 2007

InfoPath - Hyper Link with data field

Not mentioned in this article is that you should create another Data Element in your infopath form, example - DynamicHyperLink

concat('http://www.website.com/index.aspx?ID=',DataField)

Be sure to then use that as your data source as indicated below.

 

Create a dynamic hyperlink in a form template

If you want a hyperlink to change depending on values in the form, you can include a dynamic hyperlink in your form template. To create this type of hyperlink, you must bind (bind: To connect a control to a field or group in the data source so that data entered into the control is saved. When a control is unbound, it is not connected to a field or group, and so data entered into the control will not be saved.) the hyperlink to a field (field: An element or attribute in the data source that can contain data. If the field is an element, it can contain attribute fields. Fields store the data that is entered into controls.) in the data source (data source: The collection of fields and groups that define and store the data for an InfoPath form. Controls in the form are bound to the fields and groups in the data source.). If the design of your form requires it, you can bind the hyperlink to one field and the display text to another field.

For example, imagine that you are designing a real-estate listing form, which is connected to a database that contains information about properties that are for sale. In the database, you might have a field that contains distinct URLs for each real estate agent. In your real-estate listing form, you can insert a hyperlink and bind it to the field in the data source that contains the URL value. If you want the hyperlink to display the name of the real estate agent rather than the URL, you can bind its display text to a field in the data source that contains the name value. When users click the agent's name on the form, that agent's Web page opens in a browser window.

  1. In design mode, click in the form template where you want to insert a hyperlink, or select the text or picture that you want to turn into a hyperlink.
  2. On the Standard toolbar, click Insert Hyperlink Button image .
  3. Under Link to on the General tab, select Data source, click Select XPath Button image, and then select a field or group.
  4. Under Display, do one of the following:
    • To display a specific string of text for the hyperlink, click Text, and then in the Text box, type the text that you want to display.
    • To have a value inside a control on the form template display as the hyperlink's text, click Data source, click Select XPath Button image, and then select the field or group that the control is bound to.
  5. To assign a ScreenTip that will appear when a user rests the mouse pointer over the hyperlink, click the Advanced tab, and then, in the ScreenTip box, type the text that you want to display.

Tip  You can use XPath (XML Path Language (XPath): A language used to address parts of an XML document. XPath also provides basic facilities for manipulation of strings, numbers, and Booleans.) expressions to create hyperlinks that are a combination of static and dynamic data. For example, imagine that you want to create a hyperlink that links to "http://www.example.com/my name.html" where the Web server name, http://www.example.com, doesn't change, but the file name, my name, varies depending on the data that users type into a specific field in the form. In this case, you would type concat('http://www.example.com/', my:name, '.html') in the Data source box, where my:name is the name of the field to which the text box is bound. The Concat function allows you to combine static text with data from other fields in the form.

dbcc checkident('table_name', reseed, 0)
 
Reset Database identity

Friday, December 14, 2007

Answer Re: A transport-level error has occurred when sending the request to the server
Answer Was this post helpful ?
Reply Quote

For some reason the connection was made through Named Pipes instead of Shared Memory (the default for local connections), which suggests that SqlClient did not recognize the connection was local, and most likely tunneled Named Pipes over TCP, hence, disconnecting the network has impact. 

How do you specify the server name - perhaps by its fully qualified domain name (FQDN), or IP address? 

If you specify it by the hostname or "." or "(localhost)" SqlClient should recognize the local connection, and you should not see the error. 

Stored Procs

Please be aware if you are inserting data into a data field with a value type of int and allows null be sure to set the @Param int = null

This will prevent errors when submitting.

Wednesday, December 12, 2007

InfoPath class file from export

xsd.exe myschema.xsd /classes /l:cs /n:[NAMESPACE]

Identity Information

Introduction

 

About Identity Columns

If you have worked with SQL Server, you are probably familiar with identity columns. These are equivalent to the "AutoNumber" columns in Access. The main purpose of these columns is to provide a primary key to the table when a primary key cannot be defined using other fields in the table.

 

These columns are like any other column except that their value is not inserted by the user, but by the system itself.

Syntax

IDENTITY [ ( seed , increment ) ]

Where:

seed - Is the value that is used for the very first row that is inserted into the table.

increment- Is the incremental value that is added to the previous identity value and thereby to get new value for the new row that is going to be added.

Note: You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

 

A few things you need to know about the identity columns:

·         They should be of data type int, smallint, tinyint, decimal or numeric with scale 0.

·         They cannot contain null values

·         They cannot have any default values

·         The identity increment is an integral value (1, -1, 5, etc.) and cannot contain decimals. Also, it cannot be 0.

·         Identity Seed is 1 by default, and so is the Identity Increment. If you leave the seed field empty, it becomes 0.

 

 

Functions associated with IDENTITY column

·               @@IDENTITY
When a record is inserted into a table with an identity column, the function @@IDENTITY returns the last identity value that was inserted in the database.

Syntax

@@IDENTITY

 

I emphasize the phrase "last identity value" here because this may be different from the identity value of that particular table where the record was inserted.

Why? When a record is inserted and if there is any underlying trigger that modifies other tables, the value can be different. If a trigger adds a record into another table, which happens to have an identity column, @@IDENTITY will now return this new value instead.

 

·               Scope_IDENTITY

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a stored procedure, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch. It may be more clear from the example below.

Syntax

SCOPE_IDENTITY()

 

·               IDENT_CURRENT

Returns the last identity value generated for a specified table in any session and any scope.

Syntax

IDENT_CURRENT('table_name')

 

·               IDENT_SEED

Returns the seed value specified during the creation of an identity column in a table that has an identity column.

Syntax

IDENT_SEED('table_name')

 

·               IDENT_INCR

Returns the increment value specified during the creation of an identity column in a table that has an identity column.

Syntax

IDENT_INCR('table_name')


Examples and Tips

 

Example

-- Consider the 2 Tables

CREATE TABLE Student(studId int IDENTITY(1,1), studName varchar(30))

CREATE TABLE Copy_Student(sid int IDENTITY(100,1))

GO

 

--Create trigger for insert on table Student

CREATE TRIGGER trgStudent ON Student FOR INSERT

AS

BEGIN

   INSERT INTO Copy_Student DEFAULT VALUES

END

GO

 

--Check The Tables

SELECT  *  FROM Student    --No records

SELECT  *  FROM Copy_Student --No records

GO

--Now do the following to check out the difference

 

 

INSERT INTO Student(studName) VALUES('Anjali Chelawat')

 

SELECT @@IDENTITY AS [IDENTITY]

 

--Returns the value 100, which was inserted by the trigger that is by the second insert statement.

 

 

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] 

 

--Returns the value 1, which was inserted by the Student table INSERT Statement before the trigger's insert statement.

 

 

SELECT IDENT_CURRENT('student') AS [IDENT_CURRENT]

 

--Returns last identity value inserted into Student.

 

 

 

SELECT IDENT_CURRENT('copy_student') AS [IDENT_CURRENT]

 

--Returns last identity value inserted into Copy_Student.

 

 

SELECT IDENT_INCR('student') AS [IDENT_INCR]

 

-- Returns the increment value of the identity column of the table Student. The value provided at the time of creation of the identity column

 

 

SELECT IDENT_SEED('student') AS [IDENT_SEED]

 

-- Returns the seed value of the identity column of the table Student. The value provided at the time of creation of the identity column

 

 

 

A Few Tips

·         Allowing inserts to identity columns:

 

If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the identity values, you can temporarily allow inserts to the identity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the identity column. For example, if I have a table named MYTABLE and I want to allow inserts into the identity column, I can execute the following:

Syntax

set identity_ insert  table_name on

 

Once you execute the command you will be able to insert values into the table's identity column. This will stay in effect in until you turn it off by executing the following:

 

Syntax

 

set identity_insert table_name off

 

Note: Be aware that at any time, only a single table in a session can have the identity_insert set to on.  If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the identity is larger than the current identity value then the new value will be used for the identity seed for the column.  

 

 

·         Reseeding the identity value:

 

You can reseed the identity value, that is, to have the identity values reset or start at a new predefined value by using DBCC CHECKIDENT.  For example, if I have a table named MYTABLE and I want to reseed the identity column to 30 I would execute the following:

 

dbcc checkident (‘table_name’, reseed, 30)

 

If you wanted to reseed the table to start with an identity of 1 with the next insert then you would reseed the table's identity to 0.  The identity seed is what the value is currently at, meaning that the next value will increment the seed and use that.  However, one thing to keep in mind is that if you set the identity seed below values that you currently have in the table, that you will violate the identity column's uniqueness constraint as soon as the values start to overlap.  The identity value will not just “skip” values that already exist in the table.

 

 

 

Points To Remember

·   IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

·   @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

·   SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

 

Credit to:

All About IDENTITY Columns in Sql Server 2000
by Anjali Chelawat
Published on 10/5/2006