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 multiple fields together

Okay hopefully this is quick and easy.

I have 3 fields (Annual Salary, Medical, Pension) and I now need to working out Cost To Company which is just adding those three together.

I tried to do that in a formula but it doesn't return me anything... This is what I had.

=(Sum(Fields!AnnualSalary.Value, "CoreEmployeeData")+Sum(Fields!AnnualMedicalAid.Value, "CoreEmployeeData")+Sum(Fields!AnnualPension.Value, "CoreEmployeeData"))

So it should add the 3 records?

While you are here :), Can you tell me how I can put a currency symbol in the front of these numbers and add the thousand comma delimiter?

ok, What is the "CoreEmployeeData" section, I would think this is affecting your calcs.

Also i would braket each individual sum too as below, I have also added in the ￡ and thousand delimiter too.

="￡" & round(cdec((Sum(Fields!AnnualSalary.Value))+(Sum(Fields!AnnualMedicalAid.Value))+(Sum(Fields!AnnualPension.Value), 2)), 2)

I have not tested this but should provide what you are looking for.

Andy

|||CoreEmployeeData is the DataSet name.
If I leave that out I get an out of scope error.

If I leave it there I still get no response at all....?|||Okay well it's working now.
I had to paste the code in the "Format" and "Value" fields.

Not sure why it was required in both but it seems to work now. I think I need some more practice :)

Thanks,
Gavin|||

the easiest way to get aorund this issue is split out your sum to individual columns, create 3 new columns in your report, and put the Sum() in each, make sure that return the correct data in the new columns.

If so then just copy and paste each one into your total column and + them together making sure you bracket each one and wrapper the whole thing in brackets too.

((sum(A)) + (sum(B)) + (sum(B)))

Now add in my code above to format to dec and add ￡ and you should be ok

|||

Cool! - Its is one of those things, to be honest i dont use the format area at all but add my formatting only into the expression for each field, at least then you only have to change one area of needed.

SSRS certianly has its quirks but you get used to it.

Andy

Adding logins and permissions

Hi,
I am at a company with 18 employees and I have 11-12 databases in SQL server. I can't seem to give logins and permissions to groups. Is there a simpler way, or do I have to add every single employee to each database and give permissions?
You may create roles (it's a group analogue) within databases and then assign your employees to these groups. There are no serverwide groups except of builtin server roles.

Adding Fields to a large table

Dear all
I implemented a datawarehouse for a midsized retail company on SQL Server
2005. One of their facttables is a daily stock snapshot with about 50
millions of records.
Business now want to create a new report. This report requires several
calculated values. The requirement says this values should be stored in the
warehouse.
Here is my question: When I add 3 more decimal fields (18,9) to this stock
table - how does this affect the size of the record/table when they have NULL
values or a value?
Thanks in advance for your help,
Regards,
Marc
Marc,
They will take up 9 bytes each regardless of whether or not they are null.
-- Bill
"Marc" <Roger_Rombooth@.community.nospam> wrote in message
news:CB83237B-BDE4-467F-8C4B-AD9C88D972B5@.microsoft.com...
> Dear all
> I implemented a datawarehouse for a midsized retail company on SQL Server
> 2005. One of their facttables is a daily stock snapshot with about 50
> millions of records.
> Business now want to create a new report. This report requires several
> calculated values. The requirement says this values should be stored in
> the
> warehouse.
> Here is my question: When I add 3 more decimal fields (18,9) to this stock
> table - how does this affect the size of the record/table when they have
> NULL
> values or a value?
> Thanks in advance for your help,
> Regards,
> Marc
>
|||Hello Marc,
As Bill mentioned, fixed length data will take up the bytes no matter it is
Null or not. Also, in SQL Server 2000, a NULL bitmap is always present,
even if no NULLs are allowed in any column. You may want to refer to the
following link for more details:
Poking about with DBCC PAGE (Part 1 of ?)
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/08/09/692806.aspx
If you have any further questions or comments, please feel free to let's
know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Bill and Peter!
Best Regards,
Marc

Adding Fields to a large table

Dear all
I implemented a datawarehouse for a midsized retail company on SQL Server
2005. One of their facttables is a daily stock snapshot with about 50
millions of records.
Business now want to create a new report. This report requires several
calculated values. The requirement says this values should be stored in the
warehouse.
Here is my question: When I add 3 more decimal fields (18,9) to this stock
table - how does this affect the size of the record/table when they have NUL
L
values or a value?
Thanks in advance for your help,
Regards,
MarcMarc,
They will take up 9 bytes each regardless of whether or not they are null.
-- Bill
"Marc" <Roger_Rombooth@.community.nospam> wrote in message
news:CB83237B-BDE4-467F-8C4B-AD9C88D972B5@.microsoft.com...
> Dear all
> I implemented a datawarehouse for a midsized retail company on SQL Server
> 2005. One of their facttables is a daily stock snapshot with about 50
> millions of records.
> Business now want to create a new report. This report requires several
> calculated values. The requirement says this values should be stored in
> the
> warehouse.
> Here is my question: When I add 3 more decimal fields (18,9) to this stock
> table - how does this affect the size of the record/table when they have
> NULL
> values or a value?
> Thanks in advance for your help,
> Regards,
> Marc
>|||Hello Marc,
As Bill mentioned, fixed length data will take up the bytes no matter it is
Null or not. Also, in SQL Server 2000, a NULL bitmap is always present,
even if no NULLs are allowed in any column. You may want to refer to the
following link for more details:
Poking about with DBCC PAGE (Part 1 of ?)
https://blogs.msdn.com/sqlserversto.../09/692806.aspx
If you have any further questions or comments, please feel free to let's
know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Bill and Peter!
Best Regards,
Marc

Adding date to filename in report subscription

My company sends reports on a daily basis to our customers. Now I want to save all the sent reports on disc with the date in the filename. I have set up a subscription which daily saves the files where I want them. However, I haven't found a way to add the date easily. I already have a parameter when creating the report, it is called Date. Does anybody know if I can use a parameter or something else?

Thank's

Hello,

Sorry, I don't believe there is a way to modify the filename from a subscription, but you can specify a filename from a Data-Driven Subscription. Do a data-driven subscription to a file share, and just include an extra column in your subscription query to have something like this:

select 'Report or file name here ' + convert(varchar, getdate(), 101) as FileName, ...

Then, when you are setting the delivery extension settings, use this field as your File name.

Hope this helps.

Jarret

Adding a Web Server to RS 2000

I currently have my report server and my reporting services database on two
different machines. My company is now wanting to add another machine on the
web server/service side. Can anyone refer me to a good article on how
exactly to do this without killing my first machine and database?
Thanks
ScottHello Scott,
This scenario is called Enterprise Deployment Model in Reporting Services
2000.
You could add the addtional server to the existing server farm.
Please follow this article:
Installing a Report Server Web Farm
http://msdn2.microsoft.com/en-us/library/aa179321(SQL.80).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Looks like it was what I needed. I'll let you know if I need anything else.
Thanks
Scott
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:GXiC0$CGHHA.2204@.TK2MSFTNGHUB02.phx.gbl...
> Hello Scott,
> This scenario is called Enterprise Deployment Model in Reporting Services
> 2000.
> You could add the addtional server to the existing server farm.
> Please follow this article:
> Installing a Report Server Web Farm
> http://msdn2.microsoft.com/en-us/library/aa179321(SQL.80).aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>

Adding a web reference to a Report Server project

Hi there,

My company has a centralised user profile repository that all new applications worldwide use for authorization and user details; we access it from the various apps via a web service.

What I'd like to do in a report is to pass a user's ID to the web service and return their name and contact details to display in the header. I imagine I'd need to do this in a custom function and I can see where I can add a reference to use in this function but not where to add a web reference. Is this possible?

Regards,

Uzum4k1i.

One possible solution to the issue is to add custom code to the report: custom code (e.g., a C# library project in which a web reference could be added) calls the web service and exposes the web service call result for the report to reference. the assembly containing custom code needs to be deployed on the report server. More details are available here:

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

Another possible option is to configure the web service as an XML data source in which the web service URL is the connection string. Details here:

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

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

|||Cheers Tau Liang, both of those answers sound like they'll fit the bill nicely

Adding a web reference to a Report Server project

Hi there,

My company has a centralised user profile repository that all new applications worldwide use for authorization and user details; we access it from the various apps via a web service.

What I'd like to do in a report is to pass a user's ID to the web service and return their name and contact details to display in the header. I imagine I'd need to do this in a custom function and I can see where I can add a reference to use in this function but not where to add a web reference. Is this possible?

Regards,

Uzum4k1i.

One possible solution to the issue is to add custom code to the report: custom code (e.g., a C# library project in which a web reference could be added) calls the web service and exposes the web service call result for the report to reference. the assembly containing custom code needs to be deployed on the report server. More details are available here:

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

Another possible option is to configure the web service as an XML data source in which the web service URL is the connection string. Details here:

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

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

|||Cheers Tau Liang, both of those answers sound like they'll fit the bill nicely