|
Temporary Tables are session/connection specific.
So the scope and lifetime of the temporary table is directly linked with the connection or session in which the table is created and another table with the same name cannot be created in the same session.
Similar to the fact that in a database you cannot have more than one table with the same name.
Usually, in most of the occassions using Table Variables instead of temporary table solves the purpose and using table variable is a better practise that using temporary table... also it solves your issue of table creation
|
|
|
|
|
Hi All
I am new with VBA in access.
I have two buttons on access form.when i click on 1 button,my vba code has to run.
how to do that?
pls help me.
Thanks
|
|
|
|
|
Open the form in design view, double click on the button.
That should put you in a code window in the buttonX_Click event (where buttonX is the name of your button).
Now add a call to your code (or place your code here if its not in a sub/function.
Buy a book on Access programming if you intend to do any more - this is something that is one of the first steps in Access programming.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Good Day all
I have a Sp that is written like this
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Import_RegistrationsXM] @xml ntext
AS
set nocount on
DECLARE @doc int
EXEC sp_xml_preparedocument @doc OUTPUT, @xml
TRUNCATE TABLE _RegistrationXM
INSERT INTO _RegistrationXM
SELECT *
FROM OPENXML ( @doc , 'Import/Item' , 2)
WITH (
Student varchar(32),
SubjectCode varchar(32),
Campus varchar(32),
Repeat bit
)
EXEC sp_xml_removedocument @doc
truncate table _Duplicates
exec( 'sp_RegistrationsXM_Populate' )
select 'XM Registrations successfully imported!' [Result]
i have an XML File that is 3.21mb, when i this Sp with the XML i get the Following Error
Exception caught in: ExecuteStoredProc: Cannot insert duplicate key row in object 'dbo._RegistrationXM' with unique index '_RegistrationXM_index'. The statement has been terminated.
in the table _RegistrationXM there are no Records and in the File there are no Duplicates too.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: in the table _RegistrationXM there are no Records
Its probably rolled everything back when it got the error
Vuyiswa Maseko wrote: in the File there are no Duplicates
I bet there are, SQL doesn't lie about these things.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Good Day Ashfield
There are no Duplicates as i said.The Problem is that there was a Unique index on a certain Field.
Thank you i solved the Problem.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: The Problem is that there was a Unique index on a certain Field
.. which means there were duplicate values for this field, otherwise your unique index would not have caused a failure
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Yes but not in a Row Level. Because the Defination of the Field allowed Duplicates in a Field Level.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: Yes but not in a Row Level
I kind of expected you would look at the fields that form the unique indices, not look for duplicate rows (although obviously they would also cause the problem).
Anyway, all solved now
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks i never thought there would be an index there, i just inherited the System. thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi all,
I am doing Data Migration using ORACLE and Vb.net 2005. Im transferring Values from one database to another.
Currently,getting values from Source DB and those values are filled in a Dataset,using For loop,the values are inserted into the Destination DB.
The above mentioned method is working fine for Less number of records(500 approx.)
For more than one lakh records,it is not a gud solution. It is taking more than an hour...
How to do BULK INSERTION using ORACLE and VB.NET.
Pls any one help me...
Thanks in advance
|
|
|
|
|
|
Hi all, i know maybe its is really easy but I've been working on it for ages ! (
Name Diploma year
A 1991
A 1986
A 1981
B 2001
B 1996
B 1992
C 1990
C
C
D 2006
D
D
i have a 3-4 tables and join them, then it returns lots of people have diplomas from different
school, but i need to show the last graduated school for everyone. like that
A 1991
B 2001
C 1990
D 2006
.
.
.
.
.
thanks for now ...
Talha
thanks for everything i have...
|
|
|
|
|
select max(diplomayear),[name]
from mytable
group by [name]
Hope this will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
thank you, it works !
thanks for everything i have...
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi
I have a stored procedure with an input parameter varchar.
I am passing the parameters from UI to stored procedure by looping through the parameters and making them as string and passing to stored procedure. now i want to execute this where clause in select query.
the input parameter is like this
@value = 'statusid=2 or statusid=3 or statusid=4'
select * from table1 where @value
i dont know how query the dynamically generated where.
Kindly help. Thanks in advance
Naina
|
|
|
|
|
Hello Naina,
You can use EXEC statement to execute your dynamic query inside your stored procedure....
Refer the exeample below...
CREATE PROCEDURE dbo.sproc_SampleProc
@strTableName VARCHAR(100),
@strColumnNames VARCHAR(255) --Can be comma seperated column names...
AS
BEGIN
SET NOCOUNT ON;
DECLARE @strDynamicQuery VARCHAR(500)
SET @strDynamicQuery = 'SELECT '+@strColumnName+' FROM '+@strTableName
EXEC (@strDynamicQuery)
END
GO
Enjoy Programming !!!
Robin
|
|
|
|
|
CREATE PROCEDURE dbo.spSelectData
@whereConditions VARCHAR(max)
AS
BEGIN
declare @selectqeury as nvarchar(max)
set @selectqeury='SELECT * from mytable where '+ @whereConditions
exec (@selectqeury)
END
Hope this will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This is a very bad SQL programming technique. Read about the poor performance and dangers of SQL Injection. Since T-SQL can handle a long parameter list, use that instead.
CREATE PROCEDURE X (p01 INTEGER, p02 INTEGER, ..p99 INTEGER)
AS
BEGIN ..
SELECT ..
FROM ..
WHERE p IN (COALESCE (p01, 0), COALESCE (p02, 0), COALESCE (p99, 0)zzz0;
.. END;
There are better ways to write this in SQL-2008 and Standard, but the idea is to build a one-coolumn table from parameter and remove the NULLs. Also, by definitio, there is no such thing as a "status_id"; nan attribute can be an identifier or a status, but not both at once.
|
|
|
|
|
You may have better luck just using a parameterized command rather than a stored procedure.
|
|
|
|
|
Hi
I am using MS Access 2003 as database and also as front end. I want to open crystal reprot on MS Access form using coding in access. I want to open report when a buton on form is clicked.
I m searching for this but could not find the solution
|
|
|
|
|
You're more than likely going to have to use VBA to accomplish this task.
I believe that you would be better served creating your reports by using Crystal Reports to hit the MS Access db than the way you seem to be approaching this problem. YMMV.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
The mans a masochist, Access AND Crystal Reports, what a nightmare.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a product table with 100000 products and also I have a user table with 5000 records. and suppose a user can give us feedback about any combination of this products. So suppose he chooses to compare products 1,3,100,200,400,500 and so on. (he can send us another feedback about some other products)
So here is my question, I just want to know what an expert database designer would think about this situation where the number of products and users are big. One way to store this ratings are in a single string like: 1#5 3#4 100#5 .... x#y means that he gave y stars to product with id of x. I can load this vote_string in my php script for example and extract the details of it.
So the rating table would likely have this structure: id, user_id, vote_string
Another way is to store this feedback in this structure:
vote Table: id, user_id, date
vote_details Table: vote_id, product_id, vote ==> (vote_id,products_id) as primary key and vote_id is foreign key.
(or may these 2 tables can be compressed in a single table like id,user_id,product_id,vote [id,user_id,product_id] as primary key)
So i guess it's very easier to query in the second design but it consumes more space and time for each query, also in case of product deletion it's quite better to use the second design. What would you do?
Any kind of idea is appreciated.
|
|
|
|