Sunday, March 11, 2012
Auditing permission changes
Like if a developer changed a permission to a view or a table or any
other object it would get logged into a table. Also logging database
and server level permission changes like some one giving some one a
dbowner access. Any suggessions?
Thank You,
-Pranay
> What I would like is to log all the permission changes into a table.
> Like if a developer changed a permission to a view or a table or any
> other object it would get logged into a table. Also logging database
> and server level permission changes like some one giving some one a
> dbowner access. Any suggessions?
Check the SQL Profiler in Books OnLine, specifically "Security Audit Event
Classes" topic.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Hi Dejan,
Now I am running a trace. But the trace captures events like temp
index creations. I tried not like 'Create Index #' in the text data
but that filter did not work. It is still capturing temp index
creations. I filtered out temp db so the temp table creations are not
captured now. Any suggessions on removing temp index creations?
|||> Now I am running a trace. But the trace captures events like temp
> index creations. I tried not like 'Create Index #' in the text data
> but that filter did not work. It is still capturing temp index
> creations. I filtered out temp db so the temp table creations are not
> captured now. Any suggessions on removing temp index creations?
Use % wildchar, not # - 'Create Index%' should work.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Saturday, February 25, 2012
Audit
Attributes of Database
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each syste
m
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
[vbcol=seagreen]
> and regarding the "created for attach" field bcoz SQL-DMO returns this value[/vbco
l]
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>
Attributes of Database
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
Prasad
Take a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>
|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>
|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>
|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/
|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/
|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/
|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan
|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegro ups.com...
> Hi, Prasad
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>
Attributes of Database
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
Pra
"Pra
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Pra
>|||Hi Pra
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pra
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Pra
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Pra
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Pra
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Pra
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each syste
m
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Pra
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/|||Hi, Pra
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value[/colo
r]
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Pra
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>
Attributes of Database
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Does anybody know how to get the following information for a particular
>> database in SQL 2000
>> 1. Whether it is a System Object or not.
>> 2. Create for Attach
>> 3. Replication Status
>> We can get this values through SQL-DMO, but can I get these values from
>> some system tables or in-built functions
>> TIA
>> Prasad
>>
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
--
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
--
http://milambda.blogspot.com/|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>> Isn't there a more cleaner way of finding the system databases,
>> comparing the names would mean hard-coding the stuff.
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>> and regarding the "created for attach" field bcoz SQL-DMO returns this
>> value
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>
Friday, February 24, 2012
Attention to details
Here's my bussiness object:
Imports System
Imports System.Data
imports System.Data.oledbNameSpace LoveShare
Public Class UserDetails
public UserID AS Integer
public FirstName AS String
public LastName AS String
public UserName AS string
public Password AS String
public Address AS String
public City AS String
public State AS String
public Zip As Integer
public Email AS String
End ClassPublic Class User
Private objConn As New oledbConnection("Provider=sqloledb;Data Source=GARAGESALE\LOVESHARE;Initial Catalog=LoveShare1;User Id=sa;Password=notonthispost;")
Public function Login(strUsername AS String, strPassword As String) As Integer
dim intID as integer
dim objparam AS new oledbparameter
dim objcmd AS oledbCommandobjCmd = new oledbCommand("dbo.SPLoginUser", objconn)
objcmd.commandtype = commandtype.Storedprocedureobjparam = New oledbparameter("@.UserName", oledbtype.Char)
objParam.Value = strUserName
objCmd.Parameters.Add(objParam)objParam = New oledbParameter("@.Password", OleDbType.Char)
objParam.Value = strPassword
objCmd.Parameters.Add(ObjParam)Try
objConn.Open
intID = CType(objCmd.ExecuteScalar, Integer)
objConn.Close
Catch e As Exception
Throw e
End TryIf intID.toString = "" Then
return 0
End if
Return intID
end functionPublic Sub AddUser(objUser As UserDetails)
Dim intId as integer
Dim objReader As oledbdataReader
Dim objCmdID As New oledbCommand("SELECT MAX(userID) FROM tblUsers", objconn)
Dim objcmd AS New oleDBCommand("spAddUser", objConn)
Dim objparam AS OleDbParameter
objCmd.CommandType = CommandType.StoredProcedureobjParam = New oleDbParameter("@.FirstName", oledbtype.Char)
objParam.value = objuser.FirstName
objCmd.Parameters.add(objParam)objParam = New oleDbParameter("@.LastName", oledbtype.Char)
objParam.value = objuser.LastName
objCmd.Parameters.add(objParam)objParam = new oleDbParameter("@.UserName", oledbtype.Char)
objParam.Value = objUser.UserName
objCmd.Parameters.Add(objParam)objParam = New oleDbParameter("@.Password", oledbtype.Char)
objParam.value = objuser.Password
objCmd.Parameters.add(objParam)objParam = New oleDbParameter("@.Email", oledbtype.Char)
objParam.value = objuser.Email
objCmd.Parameters.add(objParam)objParam = New oleDbParameter("@.Address", oledbtype.Char)
objParam.value = objuser.Address
objCmd.Parameters.add(objParam)objParam = New oleDbParameter("@.City", oledbtype.Char)
objParam.value = objuser.City
objCmd.Parameters.add(objParam)objParam = New oleDbParameter("@.State", oledbtype.Char)
objParam.value = objuser.State
objCmd.Parameters.add(objParam)objParam = New oleDbParameter("@.Zip", oledbtype.Integer)
objParam.value = objuser.Zip
objCmd.Parameters.add(objParam)Try
objConn.Open
ObjCmd.ExecuteNonQuery
objUser.UserID = CType(objCmdID.ExecuteScalar, Integer)
objConn.Close
Catch e AS Exception
Throw e
End tryIf objUser.UserID.ToString = "" then
objuser.UserID = 25
End if
End Sub
End Class
End NameSpace
Here's is my stored procedure for the registration:
CREATE PROCEDURE [dbo].[SPAddUser]
@.FirstName VarChar(255),
@.LastName VarChar(255),
@.UserName VarChar(255),
@.Password VarChar(255),
@.Email VarChar(255),
@.Address VarChar(255) ,
@.City VarChar(255),
@.State VarChar(255) ,
@.Zip [INT]AS
INSERT INTO tblUsers (FirstName, LastName, UserName, Password, Email, Address, City, State, Zip)
VALUES (@.FirstName, @.LAstName, @.UserName, @.Password, @.Email, @.Address, @.City, @.State, @.Zip)
GO
And here is the actual registration page
<%@. Page Language="VB" %>
<%@. Register tagPrefix="LoveShare" TagName="Header" src="http://pics.10026.com/?src=head.ascx" %>
<%@. Register tagPrefix="LoveShare" TagName="Menu" src="http://pics.10026.com/?src=Men.ascx" %>
<%@. Register tagPrefix="LoveShare" TagName="Stats" src="http://pics.10026.com/?src=Stats.ascx" %>
<%@. Register tagPrefix="LoveShare" TagName="Footer" src="http://pics.10026.com/?src=foot.ascx" %>
<%@. Import Namespace="system" %>
<%@. Import NameSpace="System.Data" %>
<%@. Import NameSpace="LoveShare" %
<script runat="server">
Public sub Submit(sender as object, e as eventargs)
If Page.IsValid thendim objuserdetails as New LoveShare.UserDetails
dim objuser As New LoveShare.UserobjuserDetails.FirstName=tbFirstName.Text
objuserDetails.LastName=tbLastName.Text
objUserDetails.Username=tbUserName.Text
objuserDetails.Password=tbPassword.Text
objUserDetails.Email=tbEmail.Text
objUserDetails.Address=tbAddress.Text
objUserDetails.City=tbCity.Text
objUserDetails.State=tbState.Text
objuserDetails.Zip=tbState.TextObjUser.AddUser(objUserDetails)
Session("UserID") = objUserDetails.UserID
FormsAuthentication.SetAuthCookie(objUserDetails.UserID, false)
Response.Redirect("index.aspx")
else
lblMessage.text="Information entered incorrectly"
End If
End Sub</Script
<html>
<body BGCOLOR="00ccFF">
<Table Width="800">
<tr>
<td>
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td>
<LoveShare:Header runat="server" />
</td>
</tr>
<tr
<td>
<Table cellpadding="0" cellspacing="15" Width="800">
<tr>
<td>
</td>
<td>
<td>
<form runat="server">
<table cellpadding="3" cellspacing="0" width="100%">
<tr>
<td colspan="2" Align="center">
<Font size="6">Sign Up Today!<BR><BR></Font>
</td>
</tr>
<tr>
<td Align="Center">
<Font color="red">
<asp:label id="lblMessage" runat="server" />
</font>
</td>
</tr
<tr>
<td Align="right">First Name:</td>
<td Align="Left"><asp:textbox id="tbFirstName" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbFirstName" ErrorMessage="First Name Required" Text="Forgot First Name" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Last Name:</Td>
<td Align="Left"><asp:textbox id="tbLastName" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbLastName" ErrorMessage="Last Name Required" Text="Forgot Last Name" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Desired UserName:</td>
<td Align="Left"><asp:textbox id="tbUserName" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbUserName" ErrorMessage="UserName required" Text="Forgot UserName" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Password:</td>
<td Align="Left"><asp:textbox id="tbPassword" Textmode="Password" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbPassword" ErrorMessage="Password Required" Text="Forgot Password" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Verify Password:</td>
<td Align="Left"><asp:textbox id="tbVPassword" Textmode="password" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbVPassword" ErrorMessage="Verify Password" Text="Forgot to verify Password" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Email Address:</td>
<td Align="Left"><asp:textbox id="tbEmail" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbEmail" ErrorMessage="Email address required" Text="Forgot Email address" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Verify Email Address:</td>
<td Align="Left"><asp:textbox id="tbVEmail" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbVEmail" ErrorMessage="Must verify Email address" Text="Verify Email address" Display="Dynamic"/>
</td>
</tr>
<td Align="right">Street Address</td>
<td Align="Left"><asp:textbox id="tbAddress" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbAddress" ErrorMessage="Street Address Required" Text="Forgot Address" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">City:</td>
<td Align="Left"><asp:textbox id="tbCity" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbCity" ErrorMessage="City Required" Text="Forgot City" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">State:</td>
<td Align="Left"><asp:textbox id="tbState" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbState" ErrorMessage="State Required" Text="Forgot State" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Zip:</td>
<td Align="Left"><asp:textbox id="tbZip" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbZip" ErrorMessage="Zip Code Required" Text="Forgot Zip" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right"> </td>
<td Align="Left"><asp:button id="btsignup" text="Sign Up" onclink="submit" runat="server" />
</tr>
</table>
</form
</td>
<td>
</td>
</tr>
</table>
</td
</tr>
</tr>
<td
</td>
</tr
<tr>
<td>
<LoveShare:Footer runat="server" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html
Please help, I cannot figure out what is wrong(1) i havent gone through youe entire code but make sure you are passing in values in the same order as they appear in your query since OLEDB parameters are positional.
(2) in this sql stmt :
INSERT INTO tblUsers (FirstName, LastName, UserName, Password, Email, Address, City, State, Zip)
username, password, email etc are keywords. use [UserName], [Password], [Email]..etc
hth
Sunday, February 19, 2012
attemp to fetch logical page
Hi all,
I ecountered this error serveral times.
"Attempt to fetch logical page (3:25921) in database 'DOCUMENT' belongs to object '6357108', not to object 'SYS_Documents'."
also, I captured this error and I'm not sure if this had caused the error above.
"Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
Thanks in advace,
Larry
You might want to start with a DBCC CHECKDB.
For the access violation I would contact PSS.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com