Creating Access tables with SQL

I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?uuuuuummmm...

maybe a little more details?

What error, what syntax...|||I think you are looking for the IDENTITY property. It's not a separate data type like in Access, but a property available for certain data types (principally INT) in SQL.

Do a search on Autonumber on this forum. I recently answered a similar question...

Regards,

hmscott|||Originally posted by Brett Kaiser
uuuuuummmm...

maybe a little more details?

What error, what syntax...

Good point, it was a bit vague.

Trying to create a table with field SID as an Autonumber and the primary key.

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="creattable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT NOT NULL
PRIMARY KEY
DEFAULT 1,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"


Which is really helpful as you can see.

Thanks in advance for any help.|||Try SID INT IDENTITY (1,1)

Regards,

hmscott|||Originally posted by hmscott
Try SID INT IDENTITY (1,1)

Regards,

hmscott

Tried it but no joy.|||Did you try it this way?

Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)

regards,

hmscott|||Did a copy and paste on your code, still throws up the same error.
Might have to convince the client to stop using Access.|||Managed to get the answer in another forum, in case anybody is interested the solution is:

SID COUNTER PRIMARY KEY

Thanks for all the help.

Creating Access tables in SQL

I'm using Coldfusion and trying to create a Access table with a autonumber field (SID).

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="createtable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"

Which is really helpful as you can see.

Thanks in advance for any help.Create Table #tbl.code#_Users
(
SID COUNTER PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)|||Thanks for that works a treat.sql

creating access database with tables fail in SSIS package

I'm writing a package for SSIS and need to create a destination access database with a table on the fly. I've tried the code below - whcih works to create a database - but it doesn't create a table in that database to send data to. Instead of tbl.Parentcatalog = cat I've also used cat.Tables.Append(tbl) but this fails with a type problem. What is going wrong here?

private static void CreateDatabase(string currentDirectory)

{

if (!File.Exists(currentDirectory + DESTINATIONNAME))

{

// Create Database

ADOX.Catalog cat = new ADOX.CatalogClass();

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME);

// Need to add columns using CREATE TABLE

ADOX.Table tbl = new ADOX.TableClass();

tbl.Name = "Currency";

tbl.Columns.Append("CurrencyCode", DataTypeEnum.adVarChar, 3);

tbl.Columns.Append("Name", DataTypeEnum.adVarWChar, 16);

tbl.Columns.Append("ModifiedDate", DataTypeEnum.adDate, 24);

tbl.ParentCatalog = cat;

}

}

replaced adVarWChar with DataTypeEnum.adWChar and it worked.

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

EndWith

Catch ex As Exception

MsgBox(ex.ToString)

EndTry

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

EndWith

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating a Yes/No field in SQL Server

hi,

i need to create a yes/no field in a SQL Server database table. I know that i could previously do this in Microsoft Access and i believe that SQL Server now uses Boolean(0 or 1) for this. Does anyone have any ideas as to how to convert these 0 or 1 to yes or no with a default of yes?

I am taking the output from this table and displaying in Excel as yes or no not 0 or 1.

Thanks

If you are using a SQL Server BIT datatype and you import data into excel, the provider will tell Excel to convert this into Yes / No Value, or the appropiate language equivalent for the machine.

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de|||

hi,

thanks for the reply...., excel converts this into true/false instead of yes/no.....can i amend this?

also can i change the default value in sql server from 0 to 1?

thanks

|||

If you want yes/no you either have to transform the data at the SQL Server side first, converting the data into VARCHAR columns with the static text yes/no or you have to use a expression in Excel which evaluates the value and displays the appopiate text.

The default value can be change by putting a default constraint on a table, like:

ALTER TABLE TableName
ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR ColumnName

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi

where do i add the default constraint, is it Properties, Check Constraints?

when i added that text there an error validating came up.

Thanks

|||Hi,

via GUI you can do that by navigating to the column properties and the default constaint, per script you can do that either during creation of the table (Although you will get a automatic name assigned to the default constraint) or by altering the table:

CREATE TABLE #TempTable

(

SOMEColumn BIT DEFAULT 1

)

GO

DROP TABLE #TempTable

GO

CREATE TABLE #TempTable

(

SOMEColumn BIT

)

GO

ALTER TABLE #TempTable

ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR SOMEColumn

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Creating a view to retrieve data from more than one database sql server 2000

Hi everyone,

we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?

Thanks

Have a look at the topic Crossdatabase Ownership chain. It only possible under certain circumstances, the ownership chain cannot be broken.

Jens K. Suessmeyer

http://www.sqlserver2005.de

creating a user with dbo access

Hi,

I need to create a user with dbo access to a specific database using sql. I created a login and added a user to the login. How do i grant dbo privilege to this user (using sql)? Is there any system stored procedure for this? Please help.

Thanks in advance

Hi. Try

sp_changeowner [@.loginame=]'login'

Changes the owner of the current database.

sql

Creating a user

Hi,
I need to give access to my collegue to access my database(say Sales),we are both in same network domain,i tried it but all in vain.
thnx in advance.
sudheerRefer to books online for SP_GRANTDBACCESS & SP_GRANTLOGIN topic.

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, update
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:
> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:

> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, updat
e
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:

> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Never update a value just to call a trigger. Create a stored procedure and
call it when you need to. This is a job for a stored procedure, called by
the application, not a job for a trigger. Triggers are used to enforce
rules on your data, and sometimes for auditing changes to data, but not for
this.
<iamalex84@.gmail.com> wrote in message
news:1148415231.372278.151590@.g10g2000cwb.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||There is no SELECT trigger. You could have the logon process and/or table
access be done only via a stored procedure.
HTH
Vern
"iamalex84@.gmail.com" wrote:

> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you both for your responses.

Creating a Trigger on Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.I don't think a 'SELECT' trigger would be a good idea since data could be
access for reasons other than login. IMHO, a stored procedure would be the
best approach since you can include other login activities, like password
validation. For example:
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30)
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<iamalex84@.gmail.com> wrote in message
news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you very much. I have customized and created the procedure. One
more problem though... How do I use it in ASP? I have never worked
with stored procedures before. I'm having trouble with passing the
username and password to it. Also, how do I retrieve the returned
values? Also, is there a way to grab the current value before it is
updated?
Dan Guzman wrote:[vbcol=seagreen]
> I don't think a 'SELECT' trigger would be a good idea since data could be
> access for reasons other than login. IMHO, a stored procedure would be th
e
> best approach since you can include other login activities, like password
> validation. For example:
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30)
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <iamalex84@.gmail.com> wrote in message
> news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...|||> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
Below is a VBScript example as well as a proc that will return data via an
output parameter.
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30),
@.LastLoginTime datetime OUTPUT
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET
@.LastLoginTime = LastLoginTime,
LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
'methods to include ADO constants in ASP
connection.Open connectionString
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_LoginUser"
Set returnCodeParameter = command.CreateParameter( _
"@.ReturnCode", adInteger, adParamReturnValue)
command.Parameters.Append returnCodeParameter
Set userNameParameter = command.CreateParameter( _
"@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
command.Parameters.Append userNameParameter
Set passwordParameter = command.CreateParameter( _
"@.Password", adVarChar, adParamInput, 30, Request("Password"))
command.Parameters.Append passwordParameter
Set lastLoginTimeParameter = command.CreateParameter( _
"@.LastLoginTime", adDBTimeStamp, adParamOutput)
command.Parameters.Append lastLoginTimeParameter
command.Execute()
If returnCodeParameter.Value = 0 Then
Response.Write "Login succeeded. Last login was " &
lastLoginTimeParameter.Value
Else
Response.Write "Login failed"
End If
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <iamalex84@.gmail.com> wrote in message
news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...
> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
>
> Dan Guzman wrote:
>|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...sql

Creating a table in Access from an SSIS package

I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts?

Did you tried with a script task ? Use the excel connection from the Connection Manager to connect to your Access DB and execute your create table query from a OleDbCommand object.

I've not tried that method, it just a thought.

sql

Creating a Stored Procedure from 3 queries

Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.StageNameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GOHi

A few points:

Check out http://www.aspfaq.com/show.asp?id=2319 for multiple record sets.

All three queries look like they should be combined into one, if not, you
will need to use a cursor.

Your second query is trying to set variables and also return a result set,
which is not allowed.

It is not good to use select * in production code.

I think you should also check for errors and add a SET NOCOUNT ON.

John

"Satvic" <fuck@.spammers.com> wrote in message
news:bl64ub$6de$1@.ins22.netins.net...
Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times
DB gets access from 1 asp page. The result procedure only works 1/2 way
(does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the
number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's
info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted
manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted,
ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID =
ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.Stage
NameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GO|||Satvic (fuck@.spammers.com) writes:
> CREATE PROCEDURE csp_AuthorAccountInfo
> @.CandidateID int,
> AS
> DECLARE @.ScriptsNo int, @.ManuscriptID int
> SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
> CandidateID = @.CandidateID

It would probably be better to return the count as an output parameter:

CREATE PROCEDURE csp_AuthorAccountInfo @.CandidateID int,
@.ScriptsNo int OUTPUT AS

DECLARE @.ManuscriptID int

SELECT @.ScriptsNo = count(*) as ScriptsNo
FROM Manuscripts WITH (NOLOCK)
WHERE CandidateID = @.CandidateID

And don't use NOLOCK unless you understand exactly what you are doing.

> /* this is where it stops all the time :(
> Theoretically speaking, next SELECT will only return 1 row with
> Candidate's info*/
> SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State,
> cn.Country
> FROM Candidates c INNER JOIN
> Manuscripts m ON
> c.CandidateID = m.CandidateID INNER JOIN
> Locations l ON
> c.LocationID = l.LocationID INNER JOIN
> cn ON
> l.CountryCode = cn.CountryCode
> WHERE c.CandidateID = @.CandidateID

As John Bell pointed out, you cannot assigned variables and return
result sets in the same query.

And your comment that the SELECT would only return one seems a bit funny.
I don't know your tables, but if this row returns 1 row, then the
SELECT COUNT(*) always returns 1.

I don't know about your tables, but I would guess that a Candidate can
zero or more Manuscripts, in which case the above could return 0 or
more rows.

Since I don't know the data you are accessing, it is difficult to give
precise advice, but you should probably investigate the use of temp
tables and table variables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a SQL statement

Hello all - newbie post forthcoming...

I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:

sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"

The response I get is... "Data Type Mismatch in Criteria Expression"

The post data does not have quotes around the data - I am thinking this is the problem.

Any help would be appreciated!Welcome to the ASP.NET Forums, mr_atlanta2kx.

Before you proceed any further, you must read the following:
Please, please, please, learn about injection attacks!|||Thank you for the heads up. This particular function is only available via a password and IP address protected page that is going to be used on an Intranet.|||Assuming subcatid is numeric -> don't use the 'quotes' function on this field.|||Use params, apart from lots of other goodies they take care of irratating things like SQL within a value (including single quotes).

Creating a shared ODBC dsn for Access to SQL Server connection

I have a Microsoft Access application which uses linked SQL Server tables. I would like to create an ODBC DSN which would be available to all users so that I don't have to create a DSN on each machine. Can this be done? The Access application resides on a shared drive (Windows). Thanks for your help.

Don,

Will a File DSN work for what you are trying to do? I don't have much experience with those, so I can't help you more in that area.

I too had a similar problem as you do, and I didn't want to go through manually creating a DSN on everybody's machine. I found this script that will make your life slightly easier:

http://www.enterpriseitplanet.com/resources/scripts_win/article.php/3089341

With a little modification to the example included in that web page, I had all my users that needed a DSN ready to rock within a mouse-click!

Thanks,

Chuck

|||Thanks, Chuck. The script will certainly make the process easier if there isn't a way to create a global ODBC DSN. Appreciate the help.

Creating a new links server to MS ACCESS DB

How do you create a link to MS Access DB in SQL Server 2005. Thanks

The following sp example can get you started.

exec sp_addlinkedserver @.server='AccessDb',
@.srvproduct='Access',
@.provider='Microsoft.Jet.OLEDB.4.0',
@.datasrc='C:nwind.mdb'

|||

Hi, sorry I am new at this. Where do you enter this information?

Disregard the above. I used this query from support, and it worked wonderfully:

sp_addlinkedserver 'Nwind', 'Access', 'Microsoft.Jet.OLEDB.4.0',

'E:\DatabaseLocation\test.mdb'

Creating a new links server to MS ACCESS DB

How do you create a link to MS Access DB in SQL Server 2005. Thanks

The following sp example can get you started.

exec sp_addlinkedserver @.server='AccessDb',
@.srvproduct='Access',
@.provider='Microsoft.Jet.OLEDB.4.0',
@.datasrc='C:nwind.mdb'

|||

Hi, sorry I am new at this. Where do you enter this information?

Disregard the above. I used this query from support, and it worked wonderfully:

sp_addlinkedserver 'Nwind', 'Access', 'Microsoft.Jet.OLEDB.4.0',

'E:\DatabaseLocation\test.mdb'