How to call VBA function on command event in access 2007? - Database Discussion Boards

First Prev Next

Re: Is there an SQL Formatter based on Celko's SQL PROGRAMMING STYLE?

--CELKO--20-Jul-09 14:26

--CELKO--

20-Jul-09 14:26

It is a book; try Amazon.com or whoever you like.

Temporary Table

abcurl20-Jul-09 5:31

abcurl

20-Jul-09 5:31

Hi,

My database is sql server 2005
I have a city table.
Which contains 300 records.

i am inserting those records in temporary table. using the following code

In first window i wrote following code.

Select * Into #TCity FRom Cp_City
select * from #TCity
select * from sys.objects where name = '#TCity'

In second window i wrote following code,
Select * Into #TCity FRom Cp_City
select * from #TCity
select * from sys.objects where name = '#TCity'

My question how can i create a temporary table with a same name in two different windows.

Thanks

Re: Temporary Table

David Mujica20-Jul-09 5:54

David Mujica

20-Jul-09 5:54

I believe you would need to create 2 different connections to the database. I believe the temp tables are unique to the session ID that is created when a connection is made.

Q: When you say "In first window ...", do you mean window in the Sql Server Management Studio ? If yes, then it might become a bit tricky to make multiple connections to the same server using the same username.

You could start a second instance of SQL server Management Studio and that will work.

Give it a shot. Wink | ;)

Re: Temporary Table

abcurl20-Jul-09 7:49

abcurl

20-Jul-09 7:49

Thanks for the reply

One more qquestion, can u plz tell me the meaning of following line

I cannot define a table without using a Transact SQL

Re: Temporary Table

David Mujica20-Jul-09 8:19

David Mujica

20-Jul-09 8:19

"I cannot define a table without using a Transact SQL"

This statement is a big vague without knowing some background, but I will try to clarify it.

Transact SQL (TSQL) is the language that MS SQl server uses to manipulate both data and the structure of information stored in the server. TSQL is a superset of the ANSI SQL language, meaning it has additional capabilities that are not defined by the ANSI standard.

Therefore, in order to create a table you must use the language that is supported; in this case the language is Transact SQL.

Does that answer your question, or make it more confusing ?

Re: Temporary Table

Ashfield20-Jul-09 20:58

Ashfield

20-Jul-09 20:58

In Sql Server Management Studio each time you click New Query it opens a new connection, so there is no problem. In SQL 2005/2008 Management Studio (can't remember 2000) it shows you the SPID number in the status bar, next to your login name.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Temporary Table

Robin_Roy21-Jul-09 18:08

Robin_Roy

21-Jul-09 18:08

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

How to call VBA function on command event in access 2007?

monika_vasvani20-Jul-09 3:34

monika_vasvani

20-Jul-09 3:34

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

Re: How to call VBA function on command event in access 2007?

riced20-Jul-09 3:53

riced

20-Jul-09 3:53

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

Import XML to SQL

Vimalsoft(Pty) Ltd20-Jul-09 1:57

Vimalsoft(Pty) Ltd

20-Jul-09 1:57

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/

Re: Import XML to SQL

Ashfield20-Jul-09 2:19

Ashfield

20-Jul-09 2:19

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. Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Import XML to SQL

Vimalsoft(Pty) Ltd20-Jul-09 3:01

Vimalsoft(Pty) Ltd

20-Jul-09 3:01

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.

Re: Import XML to SQL

Ashfield20-Jul-09 4:44

Ashfield

20-Jul-09 4:44

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

Re: Import XML to SQL

Vimalsoft(Pty) Ltd20-Jul-09 4:46

Vimalsoft(Pty) Ltd

20-Jul-09 4:46

Yes but not in a Row Level. Because the Defination of the Field allowed Duplicates in a Field Level.

Re: Import XML to SQL

Ashfield20-Jul-09 4:52

Ashfield

20-Jul-09 4:52

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 Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Import XML to SQL

Vimalsoft(Pty) Ltd20-Jul-09 4:53

Vimalsoft(Pty) Ltd

20-Jul-09 4:53

Thanks i never thought there would be an index there, i just inherited the System. thanks Smile | :)

Data Migration- ORACLE and VB.NET

cbe_pav20-Jul-09 0:43

cbe_pav

20-Jul-09 0:43

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

Re: Data Migration- ORACLE and VB.NET

dan!sh 20-Jul-09 0:54

dan!sh

20-Jul-09 0:54

Why not use SSIS?

sql code problem Help pls

TALHAKOSEN19-Jul-09 22:21

TALHAKOSEN

19-Jul-09 22:21

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

Re: sql code problem Help pls

Blue_Boy19-Jul-09 22:29

Blue_Boy

19-Jul-09 22:29

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.

Re: sql code problem Help pls

TALHAKOSEN19-Jul-09 23:10

TALHAKOSEN

19-Jul-09 23:10

thank you, it works !

thanks for everything i have...

Re: sql code problem Help pls

Blue_Boy19-Jul-09 23:13

Blue_Boy

19-Jul-09 23:13

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.

Generating dynamic where clause in stored procedure in sqlserver 2005

nainakarri19-Jul-09 20:34

nainakarri

19-Jul-09 20:34

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

Re: Generating dynamic where clause in stored procedure in sqlserver 2005

Robin_Roy19-Jul-09 21:32

Robin_Roy

19-Jul-09 21:32

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

Re: Generating dynamic where clause in stored procedure in sqlserver 2005

Blue_Boy19-Jul-09 21:59

Blue_Boy

19-Jul-09 21:59

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.

Last Visit: 31-Dec-99 18:00 Last Update: 22-May-24 22:31

Refresh

ᐊ Prev 1...802 803 804 805806807 808 809 810 811 Next ᐅ

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