Re: SQL Server 2005 - Database Discussion Boards

DIMPLE_R4-Jul-06 19:14

4-Jul-06 19:14

Hi Glen,
You may want to check this link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkwalkthroughdisplayingdatainwindowsformusingparameterizedquery.asp

Hope it helps

--Dimple

Re: getting next row data into focus

Glen Harvy5-Jul-06 0:43

Glen Harvy

5-Jul-06 0:43

Thanks for the reference.

It's close enough for me to learn from Smile | :)

Glen Harvy

Re: getting next row data into focus

Mike Osbahr6-Jul-06 7:36

Mike Osbahr

6-Jul-06 7:36

If you are using the Visual Studio IDE, there is a template form for data. The wizard will ask if you want to display one record or multiple records. Selecting one record will generate a form that has the text boxes for editing data values as well as the navigation controls. The code generated should be very similar to the example you've already seen. For a simple application it might be close to what you need without all the cut & paste.

>>>-----> MikeO

Re: getting next row data into focus

Glen Harvy6-Jul-06 10:51

Glen Harvy

6-Jul-06 10:51

Mike Osbahr wrote:
If you are using the Visual Studio IDE

I am.

Thank you for this - I'm learning the IDE as well so you've helped with two areas that I need development on Big Grin | :-D

Glen Harvy

Add Tables

Brendan Vogt4-Jul-06 5:01

Brendan Vogt

4-Jul-06 5:01

Hi,

I want to make use of the .NET 2.0 memberships and roles tables in my database.

I'm referring to the following tables (not all is listed):

aspnet_Applications
aspnet_Membership
aspnet_Roles
aspnet_Users
aspnet_UsersInRoles

What is the best possible way of getting these tables in my database? Creating and running the SQL script? And how do I write code to access and modify the contents of these tables?

Is this the best possible way to go about it?

Regards,
ma se

Re: Add Tables

-Dr_X-5-Jul-06 7:27

-Dr_X-

5-Jul-06 7:27

Take a look at this article. It may be helpful.

http://aspnet.4guysfromrolla.com/articles/121405-1.aspx#postadlink[^]

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

SQL Server 2005

Brendan Vogt4-Jul-06 4:16

Brendan Vogt

4-Jul-06 4:16

Hi,

The creation of a new login is a bit confusing to me in SQL Server 2005. I usually use username sa and it's password. But now I'm trying to add the DotNetNuke database, with a different login name. They say I must go and create a new login, and a new user. What is the difference btween the 2?

This does not just apply to the DNN database, I want to do the same when adding any other new database.

Please can someone help me with my confusion??

Regards,
ma se

Re: SQL Server 2005

Colin Angus Mackay4-Jul-06 6:39

Colin Angus Mackay

4-Jul-06 6:39

ma se wrote:
I'm trying to add the DotNetNuke database, with a different login name. They say I must go and create a new login, and a new user. What is the difference btween the 2?

Using sa as your login is a security risk. If you use DotNetNuke with an sa user name and password then you are exposing your server to unnecessary risk. Follow the instructions in the DotNetNuke documentation and create a reduced account.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: SQL Server 2005

Eric Dahlvang5-Jul-06 3:45

Eric Dahlvang

5-Jul-06 3:45

If you do not prefer to use the Enterprise Manager, you can run something like this from the Query Analyzer:

DECLARE @cNewUser VARCHAR(20)<br />
DECLARE @cNewPassword VARCHAR(20)<br />
SELECT @cNewUser = 'MyName'<br />
SELECT @cNewPassword = 'MyPassword'<br />
<br />
EXEC sp_addlogin @cNewUser,@cNewPassword, 'DNN'<br />
  <br />
EXEC sp_grantdbaccess @cNewUser

--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

Re: SQL Server 2005

Mike Dimmick5-Jul-06 4:33

Mike Dimmick

5-Jul-06 4:33

SQL Server has the concept of a login, which is global to a server, and a user, which is specific to a database.

Logins are what get authenticated - you must tell SQL Server either about a Windows username or group, or you must specify a username and password. You can assign fixed server roles to a login (alternatively you can say you make a login a member of a role). These roles control whether a login can create their own databases and various other sorts of administrative rights. You'll find these in Management Studio under Security, Logins.

Users are the same sort of concept within the database itself. A login must be granted access to, and mapped to a user in, a database to be able to access it (unless they're a member of the sysadmin fixed server role). The name of the user in the database can be different from the user's login - normally the database owner's login is given the name dbo within the database. Permissions in a database are assigned to a user or to a role - again, you can make users members of roles to make the permissions easier to manage. Create users in Management Studio by going to Database, Security, Users.

Normally I simply make users members of the public database role then set permissions for that role. I allow Management Studio to add the login to the database by checking the box next to the database in User Mapping in the login's Properties. If you need different permissions for different subsystems - probably a good idea - then create more logins and users.

I'm afraid I don't know much about using application roles. I can see they're there, and I think they would allow you to keep and use a single login but assign different permissions for different applications accessing the same database. For DNN this is almost certainly overkill. It looks like you need to code explicitly to use different application roles (the documentation mentions using sp_setapprole to change to a different role).

Stability. What an interesting concept. -- Chris Maunder

Unable to read binary field type

BadKarma4-Jul-06 3:52

BadKarma

4-Jul-06 3:52

Hi,

I've been googling all day, found info but it still doesn't work

I have a binary field of 50 bytes long. I need to load this from the database and
store it into a byte array in my application VC++ (NO .NET). But it seems I just can't convert the SafeArray data into a BYTE[]. I'm really stuck on this. I am getting despreratly, this shouldn't be this hard so what don't i see.

...
_RecordsetPtr rsFoo;
_variant_t vtHolder;
int iMyID;
BYTE* pMyData = new BYTE[50];
try
{
  rsFoo.CreateInstance(__uuidof(Recordset));
  rsFoo->Open((_bstr_t)strQuery, GetConnectionPtr().GetInterfacePtr(), adOpenDynamic,adLockReadOnly, adCmdText);
  
  if(rsFoo->adoEOF && rsFoo->BOF)
    return;

  vtHolder = rsFoo->GetCollect("ID");
  if(vtHolder.vt != NULL)
  {
    iMyId = (int)vtHolder;
  }

  vtHolder = rsFoo->GetCollect("Data");
  if(vtHolder.vt != NULL)
  {
    //
    // i come here, and getting in the watch window
    // safearray of UI1  = [50](0,0,0,0,0,0,0,0,0,0,0,...)	tagSAFEARRAY 

....
    // WHAT TO DO HERE TO PUT THIS INT pMyData 
....
  }
}
catch...

codito ergo sum

help on auto-increment datatype for SQL

Kenzy864-Jul-06 0:44

Kenzy86

4-Jul-06 0:44

Hi,
I am trying to set one of my data cloumn to auto-increment but there isn't any option in the column properties to do that. I am using VS 2005 to add column to my database, so can someone point me in the right direction? Thanks! And btw, I looked at some of the help which uses VB codes to set the column datatype and set autoincrement to true and stuff... How do you work those thing? haha.. Sorry no experience in VB.

-=Ken=-

Re: help on auto-increment datatype for SQL

jamesr33784-Jul-06 1:51

jamesr3378

4-Jul-06 1:51

Add the identity attribute to your column

Error In Attcah DataBase

md_refay4-Jul-06 0:37

md_refay

4-Jul-06 0:37

when i try to attach database to sqlserver
ths error appear
"Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Connection Broken"
i cann't connect to db what is the soulation?!

ma_refay

*.dtsx file and Query execution problem

For_IT3-Jul-06 23:01

For_IT

3-Jul-06 23:01

Hi, I am asked to work on ASP.NET2.0 web development project which is already in development stage. This project development is in ASP.NET2.0 and SQL Express.
I have downloaded the project related files from the server.
I saw few files as having ".dtsx" extenstion. and one script file (text file, consisting a script to create tables.)

I have following questions.
1. What is .dtsx file meant for?
2. How can I run the query to create datatables? I mean here, in SQL 2000, I used query analyzer to execute queries. How can I do it in SQLExpress? Can I execute the query from VS.NET2005?

Please let me know. Thanks in advance.

Improve the efficiency of Stored Procedures

Uma Kameswari3-Jul-06 19:27

Uma Kameswari

3-Jul-06 19:27

Hi,
Are there any techniques /rules/measures that should be followed to develop stored procedures so that the efficieny of the appliaiton increases.
Regards,
Uma

Re: Improve the efficiency of Stored Procedures

Colin Angus Mackay3-Jul-06 21:53

Colin Angus Mackay

3-Jul-06 21:53

Uma Kameswari wrote:
Are there any techniques /rules/measures that should be followed to develop stored procedures so that the efficieny of the appliaiton increases.

There is no single catch all answer. It depends on the scenarios that they are being used in.

Re: Improve the efficiency of Stored Procedures

Frank Kerrigan4-Jul-06 0:14

Frank Kerrigan

4-Jul-06 0:14

* Don't use cursors (there cursed)
* Set NoCount off
* have efficient SQL SELECT statements
* Sometimes using temp tables is quicker than complex joins from many tables

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: Improve the efficiency of Stored Procedures

VK-Cadec11-Jul-06 8:56

VK-Cadec

11-Jul-06 8:56

You could use the "show plan" in SQL200 or "display estimated execution plan" in SQL2005 to see exactly how costly each process is and maybe modify it accordingly.

Thanks.

Delete or update

leckey3-Jul-06 3:38

leckey

3-Jul-06 3:38

I have an ASP datagrid in which the user can look up an existing record and update the fields, or insert a new record. So I have a couple of questions...

1. Is it considered better to delete the old record and insert the new (updated) record? I am rewriting this application and the old programmer did it this way which seemed odd.

2. If it is better to update, how do I program in SQL
if partNumber already exists, update
if PartNumber does not exist, insert

Thanks!

Re: Delete or update

Paul Conrad3-Jul-06 7:25

Paul Conrad

3-Jul-06 7:25

leckey wrote:
2. If it is better to update, how do I program in SQL
if partNumber already exists, update
if PartNumber does not exist, insert

You could use a stored procedure to do this.

Re: Delete or update

Frank Kerrigan3-Jul-06 22:14

Frank Kerrigan

3-Jul-06 22:14

something like this. 0 is default value for PartNumber and if it does not exist than it will be created and a new part number will be returned,

create proc myProc
@PartNumber int = 0,
@PartDesc varchar
AS

Declare @partNo_Used

if @PartNumber = 0
Begin
-- Insert into Table
insert parts ( partdesc ) Values ( @PartDesc)
--- Get identity or part number
Set @partNo_Used = @@Indentity
End
ELSE
Begin
-- Update Record
update parts
SET partdesc = @PartDesc
Where Partnumber = @PartNumber

@partNo_Used = @PartNumber
End

-- Return Part and Desc
SELECT @partNo_Used , @PartDesc

GO

---------------------------------------------------------------------------
Look where you want to go not where you don't want to crash.

Bikers Bible

Huge DataBases [modified]

hamidreza_buddy2-Jul-06 23:44

hamidreza_buddy

2-Jul-06 23:44

Hi I wanted to know how should i deal with huge databases.
i mean is there any special technique ?
or i can do processes things with those simple command and queries?
if there is, please give me resource url and Book names.
(it is a Database project for a Store and I've been told it has a giant DataBase and your application should be fast and ...)
thanks

-- modified at 5:50 Monday 3rd July, 2006

Re: Huge DataBases

Colin Angus Mackay3-Jul-06 1:52

Colin Angus Mackay

3-Jul-06 1:52

hamidreza_buddy wrote:
I wanted to know how should i deal with huge databases.

There is no correct answer. You just have to be more aware of the scenarios within which it will be used and optimise it for those scenarios.

Re: Huge DataBases

Frank Kerrigan4-Jul-06 1:26

Frank Kerrigan

4-Jul-06 1:26

How huge is huge. I've tun dbs upto 10Gb with no real problems. If you want realtime analysis then you have to get inventive with your data design, indexes etc..

you can split tables across databases or even tune your disks and datafiles as well i.e have data and indexes on seperate raid arrays or even have different tables on different drives etc...

This all depends wether its transactions, logging or reporting etc... As Colin said its all realative to your application.

Look where you want to go not where you don't want to crash.

Bikers Bible

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.