Database

Approach for CRUD transactions of a dynamic form

21-Jul-09 4:43

Pleased to help.

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

Member 288307020-Jul-09 23:04

Member 2883070

20-Jul-09 23:04

I am creating a C# web application to create dynamic forms. The user can define the form, generate its layout. This form would be availble for transaction.

I am having a query in saving the transaction details of the dynamic form. Can we build seperate tables for each form or can we use the same table to store data of all the forms. Which would be the effecient way?

The DB that we are using is Oracle 10g.

Thanks in advance.

Insert Query

sachees12320-Jul-09 22:07

sachees123

20-Jul-09 22:07

Friends,

I stored my 200 values in an array and that 200 values represent one record.

It is easy to insert 'n' record, that i did N no. of times with MAX of 20 values but this is first time im supposed to insert this large no. of values.

So donno how to do...

HELP!!!

Re: Insert Query

DoctorMick21-Jul-09 0:23

DoctorMick

21-Jul-09 0:23

I'd look at your database design and see if a table with 200 columns is really necessary. If it is, you can create an SP with 200 parameters or generate a massive insert statement (both of which are not good solutions!)

Re: Insert Query

Ashfield21-Jul-09 2:01

21-Jul-09 2:01

You could pass the values into the stored proc as xml and work from that.

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

Re: Insert Query

PIEBALDconsult21-Jul-09 5:18

SQL server 2005 Reporting Services

21-Jul-09 5:18

I prefer a method that will handle generating the query and parameters automatically, that isn't hard-coded to any particular table.

Aman786Singh20-Jul-09 21:10

Aman786Singh

20-Jul-09 21:10

Hi All,

I am new to reporting services in SQL Server 2005. I have been able to create a report using Microsoft's SQL Server Bussiness Intelligent Development IDE.
The problem is when I access the report from a web browser it asks me for Reposting server's username and password. I want to share these reports across my LAN creating limited users who can just view the reports and do nothing else.

Please suggest how to go about it.
Further more in my IIS when I go and browse the Report$Machinename it sometime does not load properly with all the components it should show. It behave correctly only randomly.

Regards,
Sandeep

UPSERT implementation

PIEBALDconsult20-Jul-09 10:36

Re: UPSERT implementation

20-Jul-09 10:36

I was looking for an implementation of "UPSERT" that I could execute with one call to ExecuteNonQuery against SQL Server 2000*. Not being familiar with EXISTS and HAVING, but being very familiar with JOIN, I wrote the following:

INSERT INTO MyTable ( Id )
SELECT T1.Id
FROM ( SELECT Id = @Id ) T1
LEFT OUTER JOIN MyTable T2
ON T1.Id = T2.Id
WHERE T2.Id IS NULL
;
UPDATE MyTable
SET Field1 = @Field1
    ...
WHERE Id = @Id

It works, and, for the particular task, I'm not concerned about scalability**, but I thought I'd post here to get feedback on whether or not there are commonly-used implementations which are held to be superior.

* I try to be database agnostic, so I prefer solutions that don't rely on a particular database system.

** This particular table has only the Id and one other field, and there will be fewer than 1000 records. However, the general case should be considered.

--CELKO--20-Jul-09 14:28

--CELKO--

20-Jul-09 14:28

DB2, Oracle and SQL Server all support the MERGE statement now and it is ANSI Standardm That is pretty agnostic Smile | :)

Re: UPSERT implementation

David Skelly21-Jul-09 2:19

David Skelly

21-Jul-09 2:19

I thought MERGE was new in SQL Server 2008? In which case, it won't work with older versions of SQL Server. The OP specifically says he is using SQL Server 2000.

Re: UPSERT implementation

PIEBALDconsult21-Jul-09 5:07

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

21-Jul-09 5:07

Right, I use a bunch of different databases, rarely the latest and greatest. Sigh | :sigh:

--CELKO--20-Jul-09 7:27

--CELKO--

20-Jul-09 7:27

Is there an SQL Formatter based on Celko's SQL PROGRAMMING STYLE? Someone has probably written something that can follow my rules, but I have not seen it yet.

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

PIEBALDconsult20-Jul-09 10:38

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

20-Jul-09 10:38

--CELKO-- wrote:
Celko's SQL PROGRAMMING STYLE

Got a link?

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

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

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

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 ?

Ashfield20-Jul-09 20:58

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

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

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

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