Adding values of rows in ms sql 2000

I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created.

ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar)

1 Elmwood Murdock 7 2
2 Louisville Manley 4 3
3 Manley Elmwood 9 8

ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this:

Elmwood: 1 Win 1 Loss. .500

Thanks for your reply.

I don't understand how to determine which team lost and which team won. You have columns for the name of the home team, the name of the away team, the winner's score and the loser's score. From what I can see, there is no indication if the home team or the away team won.|||

Oh, that's true. I can add two more columns. One for the losing team and one for the winning team. Thanks for pointing that out. However, I am still confused on how to add the total number of times a team is in the win column and have that value inside a datagrid.

|||

In SQL, you can sum up the wins and losses. If you can modify your table first with WINS and LOSSES columns, it should be a strsight SQL operation.

Something looks like this:

SELECT TeamID, TeamName, SUM(WINColumn) as Wins, SUM(LOSSColumn) AS Losses , (SUM(WINColumn)/SUM(LOSSColumn)) AS GameRatio

FROM YourTable

GROUP BY TeamID, TeamName

|||Thanks for your help. I appreciate it. I attempted this and was unable to link it to my datagrid. Is this a function I need to call from within sql and then pass to the datagrid? Thanks for your help. I haven't been doing asp.net for very long and this forum has helped a lot.|||You can treat the SUM fields as you would to other columns in your datagrid. In your case WINSColumn, LOSSESColumn and GameRatio are your new columns. If you still have problem, post your code here.

Adding Users to MSDE

I am having a authentication problem.. users log into my website... I authenticate them against Active Directory.. and then I try to query a MSDE database... my connection string is as follows:

 Dim connectionString As String = "server='srv_sql'; user id='sa'; password='MyPassword'; Database='MyDB'"
 Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

However since I have

<identity impersonate=true>

in my web.config file... it tries to login to the MSDE database as the user.. not as the user SA.

I cannot change the web.config file, since I need that for the active directory authentication to work...

in SQL Server you can add users to a database through the enterprise manager.. how can I do a similar thing using MSDE ?

Is it even possible? or do I have to upgrade to a full SQL instance?

any help would be appreciatedTo answer your question, use the osql command line utility. See this KB article for more info:HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility.

what you need to do is use the sp_grantlogin system sp to add the login:

EXEC sp_grantlogin 'Corporate\Test'

Then give it access like this:

EXEC sp_grantdbaccess 'Corporate\BobJ', 'Bob'

You'll also need to give it permissions.

That said, there may need to be adjustments for use with Active Directory.

BUT, you should never, ever, NEVER use the sa login for database access. Not for any app and certainly not for an ASP.NET app. You're opening yourself up to a whole lot of hurt doing it this way. Instead, create a login that has only the specific permissions needed to run the app. No more.

It's more work, but you'll have made the app far more secure.

Don|||...for sure don't use SA on the page itself. What about permissioning the database with Windows authenticated logins since you are using AD...if you haven't already. If you do then you can set windows authentication via Internet Services Manager on the directory hosting the page (be sure to get rid of anonymous users)

Adding the parameters dynamicaly to SP

In our website we have created the reportengine which passes the parameters dynamicaly means whatever parameters are provided in Stored procedure is automaticaly binded to grid from where values are passed to sp as follows
.Actualy this code is written by some other developer
If Not ParameterTable Is Nothing Then
RecordCount = ParameterTable.Rows.Count
If RecordCount >= 1 Then
For Index = 0 to (RecordCount - 1)
ParameterRow = ParameterTable.Rows(Index)
Select Case ParameterRow(1)
Case "231"
RecordType = "SQlDbType.NVarChar"
Case "60"
RecordType = "SQlDbType.Money"
Case "61"
RecordType = "SQlDbType.DateTime"
Case "62"
RecordType = "SQlDbType.Int"
Case "59"
RecordType = "SQlDbType.Float"
Case "104"
RecordType = "SQlDbType.Bit"
Case Else
RecordType = "SQlDbType.Int"

End Select
myCommand.Parameters.Add(ParameterRow(0), RecordType).Value = (ParameterRow(2))

[red]When Sp is executed with link[/red]<A href="http://links.10026.com/?link=Main.aspx?ReportID=1075443551&ReportName=RptAPandARComparative&ReportDescription=RptAPandARComparative"
target="main">

I get the error that nvarchar can not be converted int

Why is is So?
Is anybody having same type of experiance
In above link I got reportid as queries
select id from sysobjects where name='RptAPandARComparative'

Swati Jain:

I get the error that nvarchar can not be converted int

Is your stored procedure parameter declared as an int or varchar?

|||

I tried all datatypes still I got the same error with sp as well as code

|||

Swati Jain:

I tried all datatypes still I got the same error with sp as well as code

Can you show us your input parameters for the stored procedures?

You would also have to specify the parameter value size as well to make sure there are no data type mismatch errors.

|||

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

AS

No Code is written for specifying the size of the datatype

|||

Swati Jain:

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

your stored procedures are declared with INTEGER input paramters, so you have to set the type for your parameters toSqlDbType.Int for both input parameters.

You should also set the size as well, I believe int datatypes in SQL Server is 8 bytes.

Adding SQL Server Database file to the website

Hi!!!

I have a database called "test_DB" i have one table and some data on it. I uploaded the website and created an SQL Server account to upload the database. Can you please help me to do that task. Is there any step by step directions to do it and what files i need to add ( like ".mdf" or others...) I appreciate your help!!!

Thanks...

You are using an ISP for the hosting? They should have some way of setting that up. You probably should send them an email for uploading a sql server database. If you own the server, do a database detach/attach, which is simple.

Adding rules,roles and administrators to my website

Hi ,

I am trying to add users,rules,roles and administrators to my website, of course on my local server everything was ok, but now I can't login with my users on the server.

Basicly I created users and rules on my comp, and copied the App_data DB file to the root\app_data that is on the server(Using godaddy).

I understand that if the data is on the server it is no longer uses the DB in the root\app_data , but only the asp tables which are on MSSQL database.

I need help in copying the data from the data base in the App_data folder to the hosting database. I couldn't find the answers in the godaddy website help nor how to use their stored procedures to insert all the above.

(if I need to use the stored procedures please explain how)

Thanks very much for your help

Hi yrcaesar,

From the description, I understand you want to copy the datas from an absolute database (mdf, ldf) to a SQL Server on the server. If I have misunderstood your concern, please feel free to let me know.

I think you can simply attach the database for this scenario. Copy the database files to the server. Attach the database by right click "Databases" node in SQL Server Management Studio and select "Attach…" in the content menu.

Adding Reference to App_Code of current website

I know it's possible to add a reference to a report to a custom class, but I was wondering if it was possible to refer to the code in the App_Code directory of the current website?

We have a lot of built in functionality that I could take advantage of, if I can make a call to the classes in the current site. If I have to create a new class, there's a lot of things that would have to be duplicated to get it to work and we definately don't want to have to duplicate efforts.

TIA

Well, you can reference custom assemblies from within your reports (it could be private or signed ones)

herehttp://msdn2.microsoft.com/en-us/library/aa179513(SQL.80).aspx you gonna find all you need to do so.

Hope this helps

Adding execute permission to stored procedures

Hi

I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.

For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.

In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.

Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.

If anyone can point me in the right direction on this...

I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?

Thanking you in advance

A db owner can execute, alternatively you could add server roles to the login such as sysadmin may be overkill though

Function SetLoginsDbOwner()Dim strcmdAsString

strcmd = "exec sp_addrolemember N'db_owner', N'MyLoginName_IWantSPExecutePerm'"

Dim dcAsNew OleDbConnection(connstringL)Dim cmdAsNew OleDbCommand(strcmd, dc)Try

dc.Open()

cmd.ExecuteNonQuery()

Catch exAs OleDbException

Label3.Text = ex.ToString()

Finally

dc.Close()

EndTryEndFunction

Adding entry to DB and getting unique ID at the same time

Hi all,

I'm writing a website with Cold Fusion and when a user submits a
request and it's stored in the MS SQL database, I want the unique ID
(Identity field in table) to be given to the user on screen plus
emailed to user.

Now can I store data to the database (where the ID is created) and
return this as a variable in the same statement? I've seen this done
on many websites, but I have no idea how to do it in one step.

Thanks,

Alex.Return to the user the value from @.@.identity (check this out in the BOL).
You can also have SQL trigger off the email if this fits within your
project's stated performance requirements. Send the email via
xp_smtp_sendmail(http://sqldev.net/xp/xpsmtp.htm) But basically your
requirement to do it in 1 step could all be handled with a stored procedure.

hth
Eric

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0401291136.6ec0ee16@.posting.google.c om...
> Hi all,
> I'm writing a website with Cold Fusion and when a user submits a
> request and it's stored in the MS SQL database, I want the unique ID
> (Identity field in table) to be given to the user on screen plus
> emailed to user.
> Now can I store data to the database (where the ID is created) and
> return this as a variable in the same statement? I've seen this done
> on many websites, but I have no idea how to do it in one step.
> Thanks,
> Alex.

Adding CLR stored procedure in ASP.Net website

Hi all,

I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.

You should be able to add the datbase project as part of the same solution as the ASP.NET web site. It is unlikely that if can be done within the same project.|||

I would not recommend mixing ASP and CLR stored procedures in one solution but you probably can do this. CLR stored procedures are deployed different way then T-SQL stored procedures or ASP web pages and they are very sensitive to server configuration (not all users can do this, server admin have to allow you to do this and server have to be setup to use CLR, and in most cases you need very high security right to deploy them) . If it is possible try to not use CLR and USE T-SQL, and from my experience it is almost always possible if you only access data on the server in your logic.

Anyway Good luck

Adding an SQL Server database to a Websolution

Dear Forum

I really hope someone can help me with the following - it's eating me up.

I'm creating a website in VS2005 and want to add an SQL Server database to the Solution, og I've created a subfolder to the App_Data folder called Database. Now when I click Add New Item and choose SQL database in the Add New Item dialog box I get the following error message

Connections to SQL Server files (*.mdf) require SQL Server Express to function properly. Please verify installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId49251

Now at some point in the past I installed Visual Basic, Visual Web developer and SQL Server express edition, but when I got hold of VS2005 Professional Edition I carefully made sure that all Express version of any VS software was uninstalled beforehand. So now I have only VS2005 Professional Edition and SQL Server 2005 Developer Edition (32 bit) installed on my computer.

I have checked all the environment settings, and can't find a single thing that should point to SQL Server Express Edition.

I would really appriciate if you could give me a helping hand before I loose my sanity.

Thanks a hole bunch.

Tina

My guess is that is because the purpose of placing mdf files in the app_data folder is to attach them to a SQL Server instance and out of the box you cannot attach mdf files to SQL Server except for the Express edition but it is possible to attach mdf files to any edition of SQL Server but it is not recommended to enable this feature on Servers.

There is an article on the MSDN Library I know of that explains how to enable this feature but I cannot seem to find it, as I recall it, you had to start the mssqlserver with the -express argument, but I am not sure about this.

|||

Also I have the same problem and I have resolve it installing sql express 2005 (your link dont work correctly)

you can download sql 2005 form the following link

http://go.microsoft.com/fwlink/?LinkId=49251

Microsoft SQL Server 2005 Express Edition

http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en

|||

If I am not mistaken the developer edition of SQL Server 2005 does not support hosting a database.

You can download SQL Server 2005 express and create an instance of a database using that application.

Once the database has been created inside SQL Server 2005 (IF Developer works all the more power to you). You can then establish a connection within Visual Studio 2005 to that database in order to use in your projects.

Hope this helps.

Chris McNear

Adding an SQL Server database to a Websolution

Dear Forum

I really hope someone can help me with the following - it's eating me up.

I'm creating a website in VS2005 and want to add an SQL Server database to the Solution, og I've created a subfolder to the App_Data folder called Database. Now when I click Add New Item and choose SQL database in the Add New Item dialog box I get the following error message

Connections to SQL Server files (*.mdf) require SQL Server Express to function properly. Please verify installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId49251

Now at some point in the past I installed Visual Basic, Visual Web developer and SQL Server express edition, but when I got hold of VS2005 Professional Edition I carefully made sure that all Express version of any VS software was uninstalled beforehand. So now I have only VS2005 Professional Edition and SQL Server 2005 Developer Edition (32 bit) installed on my computer.

I have checked all the environment settings, and can't find a single thing that should point to SQL Server Express Edition.

I would really appriciate if you could give me a helping hand before I loose my sanity.

Thanks a hole bunch.

Tina

My guess is that is because the purpose of placing mdf files in the app_data folder is to attach them to a SQL Server instance and out of the box you cannot attach mdf files to SQL Server except for the Express edition but it is possible to attach mdf files to any edition of SQL Server but it is not recommended to enable this feature on Servers.

There is an article on the MSDN Library I know of that explains how to enable this feature but I cannot seem to find it, as I recall it, you had to start the mssqlserver with the -express argument, but I am not sure about this.

|||

Also I have the same problem and I have resolve it installing sql express 2005 (your link dont work correctly)

you can download sql 2005 form the following link

http://go.microsoft.com/fwlink/?LinkId=49251

Microsoft SQL Server 2005 Express Edition

http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en

|||

If I am not mistaken the developer edition of SQL Server 2005 does not support hosting a database.

You can download SQL Server 2005 express and create an instance of a database using that application.

Once the database has been created inside SQL Server 2005 (IF Developer works all the more power to you). You can then establish a connection within Visual Studio 2005 to that database in order to use in your projects.

Hope this helps.

Chris McNear

Adding additional fields to ASPNETDB.mdf

I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website. Is it possible to add additional data fields to this file? Where can I find the commands to do this?

Hey,

Welcome to the forum. Sure, it's just a database; however, depending on the changes you make, you may break the existing SQL providers code, and then it won't work... You will have to provide customization to get the features you want out of it.

|||

You can store additional user data in your application using the ASP.NET profile object. If you've already enabled Roles, the basic process is...

1. Add Profile Properties to your Web.Config file.
<profile>
<properties>
<addname="FirstName"type="string" />
<addname="LastName"type="string" />
<addname="samAccountName"type="string" />
<addname="DisplayName"type="string" />
<addname="Email"type="string" />
</properties>
</profile>

2. Populate the user's Profile (you might do this when the user logs on to your app)
Profile.samAccountName = strSamAccountName
Profile.FirstName = strFirstName
Profile.LastName = strLastName
Profile.DisplayName = strFirstName &" " & strLastName
Profile.Email = strEmail

Scott Mitchell wrote a very useful 7 part article on the process athttp://aspnet.4guysfromrolla.com/articles/120705-1.aspx.
Microsoft's has a good starter article as wellhttp://msdn2.microsoft.com/en-us/library/ms379605(VS.80).aspx.

Adding a total column

I have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.

Hi, maybe you can consider to add a computed column to the table, if all data used for computing the new column is in the same table and you know how to write the function that get the computed result. For example let's say you have create a function to do the computing and return a result:

create function udf_count_KB (@.tx xml)
returns int
begin
declare @.n int
select @.n=@.tx.value('declare namespace x="http://iorijay.com/KBs";
count(/x:KBRec/x:Record)','int')

return @.n
end
go

then you can alter the table like this:

alter table KBs
add KBCount as dbo.udf_count_KB(KBRec)

Then you can access the new added computed column as other columns, and it is possible to create indexes on computed columns (seehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp)