Database

kubben16-Mar-07 2:47

kubben

16-Mar-07 2:47

Need a little more info here.

What is your database?
If it is Sql server then:
First you will need a SqlConnection object:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx[^]

Then you will need a Sqlcommand object:
http://msdn2.microsoft.com/en-us/library/system.data.dataset.aspx[^]

In your Sqlcommand object you set the stored procedure name, add the parameters and set them.

Next the question is: Does this stored procedure return data or insert records or what?
Based on that answer you will do a ExecuteReader or ExecuteNonQuery.

Hope that helps.
Ben

Marcus J. Smith16-Mar-07 2:47

Marcus J. Smith

16-Mar-07 2:47

Googled it[^] Big Grin | :-D

_Cle^aKO

"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)

Colin Angus Mackay16-Mar-07 3:37

Colin Angus Mackay

16-Mar-07 3:37

jayvaishnav82 wrote:
how we can use stored proceder with asp.net

You don't. ASP.NET is a framework for building web applications.

ADO.NET is the component of the .NET Framework that deals with databases.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = myConnection;
cmd.ExecuteNonQuery(); // If you don't expect the stored procedure to return anything
// or
SqlDataReader reader = cmd.ExecuteReader(); // If you expect results

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

LongRange.Shooter16-Mar-07 7:48

LongRange.Shooter

16-Mar-07 7:48

Good one Collin. Now if only we had a bitch-slap smiley down below. Cool | :cool:

jayvaishnav8216-Mar-07 20:01

jayvaishnav82

16-Mar-07 20:01

Dear sir
thanx for responding pls send me full method for storedproceder with parameters i m beginer in asp.net(c#).

jayant vaishnav
jayant.vaishnav@rediffmail.com

thanx

copy data between 2 instances

Colin Angus Mackay16-Mar-07 23:50

Colin Angus Mackay

16-Mar-07 23:50

jayvaishnav82 wrote:
method for storedproceder with parameters

My previous post gave away enough information for you to have been able to look up the answer on MSDN.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = myConnection;
cmd.Parameters.Add("@parameterName", parameterValue);
cmd.ExecuteNonQuery(); // If you don't expect the stored procedure to return anything
// or
SqlDataReader reader = cmd.ExecuteReader(); // If you expect results

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Ista16-Mar-07 2:35

Ista

16-Mar-07 2:35

select * INTO [Instance1].targetDB.dbo.TargetTable from [Instance2].SourceDB.dbo.SourceTable

What I would like to do is copy the table from one instance to another.

How do I specify copying from one instance to another. I have already created the database.

The target is Sql 2000 and I have full control.

The source is Sql 2005 and I have only select permissions.

How would I forulate a correct select into statement?

Thanks,
Nick

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

1 line of code equals many bugs. So don't write any!!

My mad coder blog

kubben16-Mar-07 2:43

kubben

16-Mar-07 2:43

It should be an insert statement
Insert into [Instance1].targetDB.dbo.TargetTable
select * from [Instance2].SourceDB.dbo.SourceTable

Hope that helps.
Ben

Ista16-Mar-07 3:08

Ista

16-Mar-07 3:08

the problem is, how do I specify the Instances because "[Instance2].SourceDB.dbo.SourceTable" doesn't work. It has too many qualifiers.

I think I add a linked server on my side. But how do I specify in one line which instance to use?

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

1 line of code equals many bugs. So don't write any!!

My mad coder blog

kubben16-Mar-07 3:21

kubben

16-Mar-07 3:21

Your [Instance2] includes the sqlserver name right?

You know it might just be easier to DTS the table that is the way I do most of my table moving. Then you don't need to worry about link servers or any of that.
It is the Import export Data. If you have access to sql server 2005 management studio, you go to the database you want to export a table from right click -> tasks -> export data.
Then you just need to enter your destination sql server and database. Then select the table you want to copy.

Hope that helps.
Ben

Re: Multiple User tables?

Colin Angus Mackay16-Mar-07 3:38

Colin Angus Mackay

16-Mar-07 3:38

You need to link the two instances together before you can do that. See: sp_addlinkedserver in the books online.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Multiple User tables?

kbalias16-Mar-07 1:41

kbalias

16-Mar-07 1:41

Hi
What is the best way to manage the user logins in the database for a school information system where there are students, teachers, system administrators, etc. Would you have separate tables for students, teachers and administrators, each with columns for username and password or one Users table with all the info including usernames and passwords? The problem is then that teachers and students may have different information that is captured for the database.

Kobus

N a v a n e e t h16-Mar-07 1:50

N a v a n e e t h

16-Mar-07 1:50

kbalias wrote:
Would you have separate tables for students, teachers and administrators, each with columns for username and password or one Users table with all the info including usernames and passwords?

I prefer to use single table with all info

kbalias wrote:
The problem is then that teachers and students may have different information that is captured for the database.

Add one column in the user table for identifying the user type. It can be something like user_type and store the user type in that colunmn

string Cheers = "Navaneeth!!"

www.w3hearts.com

Re: Multiple User tables?

Pete O'Hanlon16-Mar-07 2:13

Pete O'Hanlon

16-Mar-07 2:13

I would normalise that out a bit more, and have a role table with the user table having a foreign key to the role table. By doing this, you can associate permissions to roles so you could have a student being able to view an item, and a teacher being able to view or edit it.

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

Re: Multiple User tables?

LongRange.Shooter16-Mar-07 7:51

LongRange.Shooter

16-Mar-07 7:51

First at the DB security level you create roles of student, teacher, sysAdmin

Next you create only one user table and define your users.

Finally you bind each user to a specific role and use permissions to define the CRUD for each role.

converting

topcatalpha16-Mar-07 1:07

16-Mar-07 1:07

hi,

anyone any idea if there is some SQL syntax for converting an integer to hexadecimal ?

Thx

Kurt

topcatalpha16-Mar-07 1:26

16-Mar-07 1:26

i've found HEX in the books online but seems that it isn't available in SQL2005...

but i've found this (ofcourse microsoft undocumented) one :

DECLARE @x BINARY(16) ( or int if you please)

SET @x = 255

SELECT master.dbo.fn_varbintohexstr(@x)

greetz...

Mike Dimmick16-Mar-07 2:17

Mike Dimmick

16-Mar-07 2:17

SQL Server is not designed to present data. It is designed to store and retrieve data efficiently.

If you need to show integers in hexadecimal in your application, make the client-side application do it. The same goes for things like string concatenation.

Stability. What an interesting concept. -- Chris Maunder

topcatalpha16-Mar-07 2:47

16-Mar-07 2:47

That's true,

but the (my concept) SQl server needs to prepare some things so the (dum and transparant) applications just need to pickup data end send it to hardware...
if the hardware gets a firmware update we don't need to replace all the applications, we only need to modify one stored proc.. so easy to maintain..
the apps are installed on embeddeds also so we don't want them to do a lot of time absorbing work.

ofcours we can also use services on the server who can do the work but that takes a lot of extra work and my experience is that MSSQL is very powerfull en can handle those things without any problems..

but i guess this is my opinion..ofcourse any suggestions or better concepts to do this are very welcome..

Thx..

Kurt

topcatalpha19-Mar-07 1:33