Adding to 2 different tables

Hi Everyone,

I have a page with a textbox and a dropdown list.
The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.

I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.

Here is the codebehind file:

Imports System.Data.SqlClientImports System.Web.ConfigurationPartialClass cms_Management_Company_NewCompanyInherits System.Web.UI.PageDim companyNameAs String Dim companyActiveAs Boolean Dim companyArchivedAs Boolean Dim companyDelegatesAs Integer Dim userForeNameAs String Dim userSurnameAs String Dim userEmailAs String Dim userUsernameAs String Dim userPasswordAs String Dim userActiveAs Boolean Dim userTypeIDAs Integer Dim companyIDAs Integer Dim iAs Integer Dim NoLoopsAs Integer Protected Sub btnSave_Click(ByVal senderAs Object,ByVal eAs System.Web.UI.ImageClickEventArgs)Handles btnSave.ClickDim conStringAs String = WebConfigurationManager.ConnectionStrings("General").ConnectionStringDim conAs New SqlConnection(conString)Dim cmdAs New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@.CompanyName, @.CompanyActive, @.CompanyArchived, @.CompanyDelegates)", con) cmd.Parameters.AddWithValue("@.CompanyName", companyName) cmd.Parameters.Item("@.CompanyName").Value = txtCompanyName.Text cmd.Parameters.AddWithValue("@.CompanyDelegates", companyDelegates) cmd.Parameters.Item("@.CompanyDelegates").Value = lstDel.SelectedValue cmd.Parameters.AddWithValue("@.CompanyActive", companyActive) cmd.Parameters.Item("@.CompanyActive").Value =True cmd.Parameters.AddWithValue("@.CompanyArchived", companyArchived) cmd.Parameters.Item("@.CompanyArchived").Value =False Using con con.Open() cmd.ExecuteNonQuery() con.Close()End UsingDim con2As New SqlConnection(conString)Dim cmd2As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@.UserForeName, @.UserSurname, @.UserEmail, @.UserUsername, @.UserPassword, @.UserActive, @.UserTypeID, @.CompanyID)", con2) cmd2.Parameters.AddWithValue("@.UserForeName", userForeName) cmd2.Parameters.Item("@.UserForeName").Value ="First Name - Delegate 1" cmd2.Parameters.AddWithValue("@.UserSurname", userSurname) cmd2.Parameters.Item("@.UserSurname").Value ="Surname - Delegate 1" cmd2.Parameters.AddWithValue("@.UserEmail", userEmail) cmd2.Parameters.Item("@.UserEmail").Value ="Email Address - Delegate 1" cmd2.Parameters.AddWithValue("@.UserUsername", userUsername) cmd2.Parameters.Item("@.UserUsername").Value ="Username - Delegate 1" cmd2.Parameters.AddWithValue("@.UserPassword", userPassword) cmd2.Parameters.Item("@.UserPassword").Value ="Password - Delegate 1" cmd2.Parameters.AddWithValue("@.UserActive", userActive) cmd2.Parameters.Item("@.UserActive").Value =True cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 2 cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 1 Using con2 con2.Open()For i = 1To NoLoops cmd2.ExecuteNonQuery()Next i con2.Close()End Using Response.Redirect("~/cms/Management/Company/Company.aspx")End SubEnd Class

The other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)

I hope this makes sense.

Thank you very much guys.

Scott.

Hi,

To get the recently added record's ID use

SELECT SCOPE_IDENTITY()

and catch the returned value using

cmd.ExecuteScalar()

HTH
Regards

|||

Hi,

Thanks for the reply, where in the code would I put these elements? I am very new to .NET.

thanks again,

Scott.

Adding new lines into results in the

I used to be able to enter new lines into the result pane cell for text
(and varchar) data in Enterprise Manager, but now that I am using SQL
2005 Management Studio, this feature is gone.

Is there any way to do this?

Also, copying to/from excel chops off part of the text in a cell and is
very infuriating.

Any help would be appreciated.

Dan(monkeyboydan@.gmail.com) writes:
> I used to be able to enter new lines into the result pane cell for text
> (and varchar) data in Enterprise Manager, but now that I am using SQL
> 2005 Management Studio, this feature is gone.
> Is there any way to do this?
> Also, copying to/from excel chops off part of the text in a cell and is
> very infuriating.

Time to learn to write INSERT and UPDATE statements, I see!

There are plenty of differences between the tools in SQL 2000 and SQL 2005.
Keep in mind that Open table is intended to be a fairly simple tool to
view and edit data. For more heavy-duty stuff, you would use an application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.

Your point is noted and I am quite happy to do insert and update
statements but there are occasions where a little ad-hoc editing and
copying and pasting is useful and this isn't possible any more and it
seems a bit silly 'cause it makes the 'open table' functionality
vritually pointless is a lot of occasions.

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.