Additional INSERT, UPDATE, and DELETE Statements Disabled.

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.
I have been looking for an answer to my problem, but unfortunately I couldn't find one. So I created a user here on www.asp.net just for making this post.

Before I continue I just want to apologies if there is another post where this question is already answered.

Please watch this Print Screen I just took: "http://www.bewarmaronsi.com/Capture.JPG "

As you can see the "INSERT, UPDATE, and DELETE Statements" are disabled, and that's exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.

The MDF file is located in the App_data folder and is called ASPNETDB.

And when I try to add custom SQL statements, it gives me Syntax error near "=". Something like that. I bought the Total Training Set1 package and it works perfect in their examples.

I just want to thank you for reading my post and I hope that you got some useful information for me.

By the way, I', from Sweden so you have to excuse me if my English is rusty.

Thanks!

PS: Can it be that I'm running windows Vista?

Try adding a primary key column to the the table and check.

If any issues, let me know.

Sri

|||

Thank you for your postsridhar.av, I don't exactly know what you mean, but I think this might be it… please look at this new print screen:

"

http://www.bewarmaronsi.com/Capture2.JPG

"

And if that's the case, it still doesnt work.

Thanks

|||Right click on the Column -->Set Primary key|||

Thank you so muchsridhar.a, it works perfect now!

I'm a designer so if you need any help in that area, dont hesitate and email me anytime you want.
From Graphics to 3D modeling and Animation.

Thanks!

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 SqlServer2000

More of a SQL Server problem than a ASP one but hope you can help me :)

Im tryin to do my first data based application using ASP.net and silly me I forgot to add users when I created then database so now Visual Studio can't access it.

Can someone tell me the syntax to add/create a user to MSSQL database so I can then grant them privelidges to the tables.

thanks very much

Tomif you go to enterprise manager, xpand on databases then yourdatabase, theres a tab called users. there you cann add/modify users permissions.

hth

adding up column values (asp & access 2000)

Hi

I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.

I want to add up the total spent by the client and put the results to a

field.

I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.

For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006

The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)

I'm sure that there is only a basic tweak required but i'm banging my
head over what to do

The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"

Any help gladly received.Hi Dan,

What is the SQL behind: q_monodraught_total ?
Quote:
The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"
I'm thinking you might benefit from a SELECT SUM... query
SELECT SUM(fieldname) FROM table WHERE condition ORDER BY fieldname; SELECT SUM(Age) FROM Persons WHERE Age>20good luck ;o)

Cheers,
Douglas

------------------------

"Dan" <mail@.daneverton.comwrote in message news:1164792444.626863.242620@.h54g2000cwb.googlegr oups.com...
Hi

I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.

I want to add up the total spent by the client and put the results to a

field.

I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.

For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006

The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)

I'm sure that there is only a basic tweak required but i'm banging my
head over what to do

The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"

Any help gladly received.

Adding Roles/Users using SQL Procedures...

Hi everyone out there in ASP.NET land.

Have a quick question...

How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?

Thanks ahead of time,
DenvasCheck in this link a couple of PageDowns in the sectionCreate a New Login

The Sql syntax for adding a role member:

EXEC sp_addrolemember N'db_owner', N'SomeUserName'

|||Thank you so much for the info. Going to apply it tomorrow.

-Denvas

Adding photos to SQL table

Hello all I am new to this site.
I am trying to use ASP.NET to build an application but I am not surehow to add photos and other data to the sql table that I have created.

Thanks in advance.If you are using SQL 2005 I would store the image as BINARY(MAX) or if 2000 us IMAGE for the field type. However, you would need to write some sort of handler to read the SQL table and write the image to a stream, an HttpHandler might work best for that. As for storing the image, you would need to convert the image into a byte array (byte[] or byte()).|||

You can take a look at this post:

http://forums.asp.net/thread/1257487.aspx

adding image to the webpage

hi

i am a beginner of asp and want to add an image to the page ?

would u guide me?

See the thread here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1769613&SiteID=1

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Adding file information to SQL database on file upload

Hi there :)

I'm in the final stage of my asp.net project and one of the last things I need to do is add the following file information to my SQL server 2000 database when a file is uploaded:

First of all I have a resource table to which I need to add:
- filename
- file_path
- file_size
(the resource_id has a auto increment value)

so that should hopefully be straight forward when the file is uploaded. The next step is to reference the new resource_id in my module_resource table. My module resource table consists of:
- resource_id (foreign key)
- module_id (foreign key)

So, adding the module_id is easy enough as I can just get the value using Request.QueryString["module_id"]. The bit that I am unsure about is how to insert the new resource_id from the resource table into the module_resource table on file upload. How is this done? Using one table would solve the issue but I want one resource to be available to all modules - many to many relationship.

Any ideas?

Many thanks :)Since Resource_ID is an Identity column, you should be able to perform a SCOPE_IDENTITY() after the INSERT into the Resource table to pick up the value.

For example:

DECLARE @.resourceID bigint
INSERT INTO
 Resource
(
 filename,
 file_path,
 file_size
)
VALUES
(
 @.filename,
 @.file_path,
 @.file_size
)
SELECT @.resource_ID = SCOPE_IDENTITY()
INSERT INTO
 Module_Resource
(
 module_ID,
 resource_ID
)
VALUES
(
 @.module_ID,
 @.resource_ID
)

Terri

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 data to more than one table

Hi there,

I am currently setting up a registration system where customers can registers their details and the details of the product, using ASP.net and MS SQL.

There is a column called customerID in the Custoemrs table, and a column of the same name in the Products table, so that I can have relationships between the tables.

For obvious reasons (ie. people that quit half-way through), I want to hold all the information until the end. The ID in the Customer table is unique, and auto-increasing, and therefore not assigned until the data enters the database.

However, I wish to submit information to the Products table at the same time, but what shall I put in for the custoemrID (which hasn't yet been assigned)

Thank you in advance for your help,

Nathair

It's a Referential Integrity issue. You need a ForeignKey with Cascading on UPDATE/DELETE to maintain the Referential Integrity on UPDATE/DELETE: take CustomerID column on Customer table as PrimaryKey, and CustomerID on Products table as ForeignKey, you can refer to this link:

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

To maintain the Referential Integrity when INSERT, you can create an INSRET Trigger on the Customer table as following:

create trigger trg_Customer on Customer for insert
as
insert into Products select CustomerId,'myProduct' from inserted
go

You can take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

|||Thanks for that. UPDATE/DELETE isn't really gonna be an issue.

Normally I hard-core my SQL statement into the actual page - however, this time I'm thinking of using an SP in MS SQL. For both ways, I am unsure on how to incorporate the trigger?

Thanks,
Nathair

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 image to details view

I'm some what of a noob when it comes to ASP.NET 2.0 and SQL Server 2005 Express editions, so please bare with me.

I have created a basic database that is only one table. One of the fields is LogPicture with type of image.

I just want to go into the table and manually place a picture in the table, but it seems like I can not get the format down. I have tried including the picture I want into the project, but no dice. I've also tried putting the physical pathname (on the harddrive) and the web address of this field in there. Suggestions please?

*bump*

I am basically just looking for the syntax of how an image is inserted. Anyone..... please?|||

Image data is stored as binary stream in SQL Server. You can either use bcp.exe utility to insert BLOB data (image/text/ntext) or write your own code to input binary stream to the column. You can take a look at this post:

http://forums.asp.net/thread/1257487.aspx

And here is a article that introduces bcp utility:

http://msdn2.microsoft.com/en-us/library/ms162802.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)