Database

Updating many rows with ADO.net

8-Dec-06 13:01

ullk wrote:
My Problem: Why should i create an new sql 2005 db on the host server. I d'like to transfer my local sql 2005 db (microsoft visual web developer 2005) to the host?

Becuase the log in information is held in the master database. That has to map to the users set up in the individual database. If the internal user ids don't match between your database and the master database then when you log in it won't know what set of permissions to use on the database.

ullk wrote:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True

I'd imagine that your ISP has a full SQL Server set up. In which case you will be connecting to a database that is defined in the server rather than a file that it attaches to the server when called. You'll have to change your connection string to reflect that - You'll need to speak to your ISP for the details for the Data Source and Database elements of the connection string. And you'll drop the AttachDbFilename part. (most likely)

ullk wrote:
Do I have to add a user id and a password?

Only your ISP can answer that. It depends how they've set up their SQL Server. The login that you create in your ISP's control panel may be a SQL Log in, or it may be one you can use for a trusted connection. I'd imagine that it would be the SQL Login though - it would be easier for your ISP to manage.

Upcoming Scottish Developers events:
* We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker?
* Developer Day Scotland: are you interested in speaking or attending?

My: Website | Blog | Photos

Drathmar8-Dec-06 5:46

Drathmar

8-Dec-06 5:46

Right now I have a program that queries a database and saves the data into a a strongly typed dataset

Then I perform various calculations on each record and fill in 5 null fields with data

Lastly, I click upload and it does a for loop, for each data row and calls the update method to update the database with the calculated values.

This works all fine and dandy, except the upload part is rather slow.

it looks something like

DataAdapter.StoredProcedureUpdate(Primary key, NewValue1, NewValue2, NewValue3, NewValue4, NewValue5)

I'm guessing that the upload part is slow because its repeating a process where 3/4 of the work done is not the update function
1. creating the connection
2. connecting and authnicating
3. calling the update procedure
4. closing the connection

I'm still somewhat new to ADO.NET so I'm not quite sure if its actually doing this.

Does anyone have some good suggestions on a better method?

Perhaps a way to call a bulk update through ADO.net or SQL?

How to get 3rd Highest salary

karam chandrabose8-Dec-06 5:33

karam chandrabose

8-Dec-06 5:33

Guys , This is a popular Interview question,
i would like to know the various queries that can be written to achieve this.

Table: Employee
Columns: Name, Salary

Parwej Ahamad8-Dec-06 5:50

Parwej Ahamad

8-Dec-06 5:50

Select TOP 1 Name,Salary From (

Select TOP 3 Name, salary from Employee order by Salary DESC
) as temp

order by Salary ASC

Keep Smiling !!!
Parwej Ahamad
g_parwez@rediffmail.com

ed^nrgc8-Dec-06 8:49

ed^nrgc

8-Dec-06 8:49

knee pads

Pete O'Hanlon8-Dec-06 9:37

Pete O'Hanlon

8-Dec-06 9:37

... and lip balm to prevent chapped lips while kissing all those butts. Big Grin | :-D

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Populating datas from Sql db to CSV file

Guy Harwood12-Dec-06 2:03

Guy Harwood

12-Dec-06 2:03

ednrgc wrote:
knee pads

---Guy H ( Wink | ;-)

---

vijay_838-Dec-06 4:19

vijay_83

8-Dec-06 4:19

Hi
I have to populate some datas from sql server database and display it directly in a CSV Format or CSV file. When i run the project it should provide me an option to whether open or save or cancel the file. when i click open it should be opened and be viewed in a excel sheet in CSV format and when i click save it should ask the destination folder and should be saved there in CSV format and when i click cancel it should be cancelled and the application should be closed. pls note that all these actions should happen in the same browser and should not be redirected to anyother page. Can anybody help me?
Regards
Vijay.

Re: Populating datas from Sql db to CSV file

Colin Angus Mackay8-Dec-06 4:31

Re: Populating datas from Sql db to CSV file

8-Dec-06 4:31

This is too vague - we are not going to write your software for you. Please break down your problem into smaller parts and if you have difficulty on any particular part then you can come back and ask for help. Be specific about what it is you need help with.

karam chandrabose8-Dec-06 5:19

karam chandrabose

8-Dec-06 5:19

hey man,
please be specific, we r here for doubts at places where anyone is struck,
regards,
karam C Bose

Pasamulla manidharappa, naanu meesai vacha Kolzandhai yappa.

Problem with stored procedure....

Kasic Slobodan8-Dec-06 1:47

8-Dec-06 1:47

I have a problem with a stored procedure.

I create it with this code below, and when I refresh in MS Server Management, procedure is there. As soon as I refresh my application (ASP.NET) that cunsumes this procedure, I get an Object reference not set to an instance of an object, error.

I go back to MS Server Management, hit refresh and stored procedure that previously was there, is gone...

That explains the error, but I don't know why's this happening.

I've noticed that when I change the name of a procedure, then it works:

[spForumPost] - work
[spForumGetNumberOfPosts] - don't work

Sql is OK since I have run it.

Any ideas?

if exists(select * from dbo.sysobjects where id = object_id(N'[spForumGetNumberOfPosts]') and OBJECTPROPERTY(id, N'isProcedure') = 1)
drop procedure [spForumGetNumberOfPosts]
GO

CREATE PROCEDURE [spForumGetNumberOfPosts]
(
    @SubForumId        int
)

-- WITH ENCRYPTION

AS
begin
    select Count(*) as PostsNumber

    from posts left join threads on posts.threads_id = threads.threads_id

    where
    threads.subforums_id = @SubForumId;
end -- end spForumGetPostsNum

Colin Angus Mackay8-Dec-06 4:32

8-Dec-06 4:32

Kasic Slobodan wrote:
I get an Object reference not set to an instance of an object

You might want to show your C#/VB code rather than the SQL as it looks more likely the problem is there rather than with the SQL.

Kasic Slobodan8-Dec-06 5:19

8-Dec-06 5:19

Sorry about that...

When I execute the sql from the procedure, everything runs great, no error.

Here's the code:

MSSqlConn is a class for connecting to MSSql database.

public static int getPostsNum(int podForumId)
{
    int tmp;
    MSSqlConn s = new MSSqlConn();

    s.OpenConn("EXEC [spForumGetNumberOfPosts] " + podForumId);

    s.dr.Read();
    tmp = (int)s.dr["PostsNumber"];

    s.CloseConn();

    return tmp;
}

Colin Angus Mackay8-Dec-06 5:57

8-Dec-06 5:57

So which line is causing the exception?

Kasic Slobodan wrote:
s.OpenConn("EXEC [spForumGetNumberOfPosts] " + podForumId);

It is a bad idea to inject values into SQL Strings - it can lead to SQL Injection Attacks. Also ADO.NET has the facility to handle calling stored procedures directly.

e.g.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@paramName", paramValue);

Kasic Slobodan wrote:
s.dr.Read();

Does this return true or false - you don't check it. If it returns false then there is nothing to read and the following line could fail.

Kasic Slobodan wrote:
return tmp;

Sorry - this more code review like than helping with your problem - As a general rule nothing should be called tmp, temp, temporary or varients thereof. I generally use the name "result" for any value that is to be returned out of a method. It makes the purpose clearer and when reading the code later makes it easier to understand what is going on.

Kasic Slobodan8-Dec-06 9:44

8-Dec-06 9:44

Iam sorry. I haven't seen this post untill now.

Anyway, s.dr.Read() is line which is cousing the error.

s.dr.Read() is false.
Whenever condition I set, it always displays the error at s.dr.Read() line.

It's ok. From code review I can learn something.

Thanks for code review.

karam chandrabose8-Dec-06 5:31

karam chandrabose

8-Dec-06 5:31

Hey ,
don worry , this is a small flaw that u would have missed in your ADO.Net Code, debug it carefully ..and peacefully,

Peace is all that takes to succeed

Kasic Slobodan8-Dec-06 5:44

8-Dec-06 5:44

I don't see anything wrong in the code and either in store proceure.

When I take sql query from a procedure, and put it in s.OpenConn() method, there is no error, only if Iam doing it with a stored procedure.

Like I said, when I run a application and go back to MS Server Manager and refresh, the stored procedure is gone (any other procedures is still present), like I have roll back transaction (for that procedure) or something like that.

Similar method for getting the number of Threads works fine, I only have a problem with spForumGetNumberOfPosts stored procedure.

I get object reference error, probably becouse the stored procedure Iam trying to execute, exists no more, wich is true. I don't know why procedure always disapiers?

Colin Angus Mackay8-Dec-06 6:00

8-Dec-06 6:00

Kasic Slobodan wrote:
Like I said, when I run a application and go back to MS Server Manager and refresh, the stored procedure is gone (any other procedures is still present), like I have roll back transaction (for that procedure) or something like that.

Just a wee thought - Stored procedures with names starting with "sp" are reserved by SQL Server to refer to system stored procedures. This may or may not be part of the problem (I've never used the convention of starting stored procedure names with an "sp" so I don't know the results)

Kasic Slobodan8-Dec-06 9:30

8-Dec-06 9:30

I've tried removing sp prefix, but no luck. Same error, same stored procedure.

Pete O'Hanlon8-Dec-06 9:43

Pete O'Hanlon

8-Dec-06 9:43

Just a thought - save the SP, and then verify that it is present. Stop the database engine and start it again, then verify that it is still present. If it isn't then it looks as though it is being rolled back.

Once you have done this, you can then run your application. What I would do as well, is to check your code to make sure that you don't have anything in your code that executes a DROP PROCEDURE command.

Kasic Slobodan8-Dec-06 10:02

8-Dec-06 10:02

I did what you told me.

After stopping MSSql Server and running it again, sp wasn't beign deleted, but when I was run my application, error raise again, and sp was removed from database.

Iam not aware of any code/command that I have put in any part of my application that should delete sp.
Only drop procedure is in procedure decralation.

Iam trully confused...

Pete O'Hanlon8-Dec-06 9:40

Pete O'Hanlon

8-Dec-06 9:40

That wouldn't do it. When SQL Server sees SPs starting with sp_ the engine looks in Master first before looking for yours.

Eric Dahlvang8-Dec-06 6:00

Eric Dahlvang

8-Dec-06 6:00

You should be able to use the SQL Profiler[^] to figure out what is deleting the stored procedure.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Kasic Slobodan8-Dec-06 9:23

8-Dec-06 9:23

Unfortunatly, I don't know how to use it Smile | :)