|
Hi,
I have a form which including different sort of information. for instance, some information is regarding to personal data which related to Table1 and some are for Table2 and the some are for Table3. all this information are in 1 form.
Now, I just wondering to know, when I wanna insert data via Store procedure do I have to write SP for each of them?
is there any approach to insert data with writing just one SP.
how can I write SP, inserting data in Multi table. is it possible
|
|
|
|
|
If you insist on using stored procedures then there should be one that performs all the required statements in a transaction that can be rolled back.
Many practitioners seem to forget that by definition a procedure should contain more than one statement.
|
|
|
|
|
future3839 wrote: is there any approach to insert data with writing just one SP
This is a basic aspect of an SP
Receive values via variables in the procedure declaration
CREATE PROC StoreDumy
-- Declare
@Var1 VARCHAR(100),@Var2 VARCHAR(100),@Var3 VARCHAR(100),@Var4 VARCHAR(100),
@Var5 VARCHAR(100),@Var6 VARCHAR(100),@Var7 VARCHAR(100)
Insert the variables in the tables required. You may want to wrap the inserts in a transaction so that if 1 insert fails all inserts are rolled back and you are not left with orphaned data
As
DECLARE @ID int
begin TRAN
INSERT dbo.Country (Fld1, Fld2)
VALUES(@Var1, @Var2)
SET @ID = SCOPE_IDENTITY()
INSERT dbo.State (CtryID, Fld1, Fld2)
VALUES(@ID, @Var3, @Var4)
SET @ID = SCOPE_IDENTITY()
INSERT dbo.Town (StateID, Fld1, Fld2)
VALUES(@ID, @Var5, @Var6)
COMMIT TRAN
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
anyone has the URL for the english official forum of PostgreSQL NOT the Postgre Advanced Server..
|
|
|
|
|
jrahma wrote: anyone has the URL
And you want to use a database product that you cannot even find the URL for. What level of support do you think you are going to get for this DB. Oh wait, you are looking here, a MS centric site, for support 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Oh wait, you are looking here, a MS centric site, for support
Thanks, good point, I could not fathom why the question got down voted. I guess I look at things too narrowly.
|
|
|
|
|
djj55 wrote: I could not fathom why the question got down voted
Some twat who probably has not heard of Postgre.
I nearly got involved in it in the 90s but chose Access spit instead and from there went naturally into SQL Server. I had just spent some years working with SuperBase only to have it sold 5 times in as many years and it got totally marginalised. I'll stick to mainstream!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can I deploy PostgreSQL database in InstallShield 2010 so it will install the database server and create my database as well?
|
|
|
|
|
how can I create a script for the entire database inPostgreSQL including the structure, rows, functions, views, etc?
|
|
|
|
|
You could use PgAdminIII and it's database backup function. This allows you to select which objects you would like to re-create. This could then be run using restore from either PgAdmin or the command line, depending on your requirements
|
|
|
|
|
I have a demo application that uses SQL Server via ADO to create and populate a database. I connect to the master database and then execute a "CREATE DATABASE ..." SQL statement. Here is the connect:
bool CMsSqlDemoView::Connect(LPCTSTR szDatabase)
{
Disconnect();
m_DBConnection.CreateInstance(__uuidof(Connection));
if (!m_DBConnection)
{
m_listErrors.AddString("Failed to create ADO::Connection instance");
return(false);
}
m_DBConnection->CursorLocation = adUseClient;
// Note: The "sqloledb" provider does not fully support XML.
// For XML support, the Provider and DataTypeCompatibility values must be set as indicated.
CString strConnection;
strConnection.Format(
"Provider=SQLNCLI10;DataTypeCompatibility=80;Integrated Security=SSPI;Data Source=%s;Database=%s;Trusted_Connection=Yes;",
m_strDataSource.GetString(),
szDatabase);
HRESULT hr = E_FAIL;
bool bResult = true;
try
{
hr = m_DBConnection->Open(strConnection.GetString(), "", "", adConnectUnspecified);
}
catch(_com_error &e)
{
CString strText;
strText.Format("Error code '%d' : '%s'", e.Error(), (LPCTSTR)e.Description());
m_listErrors.AddString(strText);
bResult = false;
}
return(bResult);
}
So far so good. I have now created a second demo application that uses SQL Server CE 3.5. I set the provider as "Microsoft.SQLSERVER.CE.OLEDB.3.5" but I am unable to create a connection object, and I have no idea how to create a database since I cannot connect to a master database. Anyone know how to do this (C++)? Thanks. BY the way I don't want to use managed C++, or OLEDB.
modified on Tuesday, October 26, 2010 8:52 AM
|
|
|
|
|
In case anyone else needs the answer, I had to use the MS_ADOX::_Catalog COM interface which is imported from msadox.dll. For example:
MS_ADOX::_CatalogPtr catalogPtr;
catalogPtr.CreateInstance(__uuidof(MS_ADOX::Catalog));
catalogPtr->Create("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Temp\\sqlcedemo.sdf;");
The above crap non error checking code creates the C:\\Temp\\sqlcedemo.sdf database, which can then be opened and populated using ADO in the normal fashion.
|
|
|
|
|
is there an advantage to laying out fields of certain data types in a particular order?
For example, in MS Access 2007, is it best to have all Memo fields at the end of the table definition?
Thanks,
JJ
|
|
|
|
|
|
|
While there is no technical benifit I have always followed these rules
Primary keys is the first field (I almost never use complex keys) and is usually an identity or a GUID inder duress
Foreign key fields (you know all this ####ID fields)
Primary description field(s)
All other fields
Modified and ModifiedBy are generally the last 2 fields on most tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even though it may look nicer to have columns sorted in a particular order, sooner or later you will have no influence on the order any more. For as far as I know it is not possible to insert a column inbetween to existing columns without dropping the entire table and recreating it, which in most large live systems is a big no no as it cost way to much time to do.
|
|
|
|
|
Hi,
Scenario:
I have a database which is going to be replicated via a set of web services. The web services will select all records where [AwaitingSync] = true. I have cerated two triggers for each table which sets [AwaitingSync] to true when a record is inserted or updated. The web service then sets [AwaitingSync] to false for all records.
Problem:
Due to a combination of my inexperience and lack of thought there is a slight problem(you'ver probably already noticed what the problem is!). After synching, when the web service sets [AwaitingSync] to false the update trigger fires and sets it back to true! 
My question is this then; what can I do about this? Can I add a conditional statement to the trigger so that it only fires if a particular column is being updated(or not being updated in this case). Or maybe the trigger be temporarily disabled while the [AwaitingSync] column is being updated. Or are there any other solutions to this?
Thanks for your time.
EDIT: Think I've found a solution but I would still appreciate your views if there is a better way of doing this. I have modified the trigger to check the value of [AwaitingSync] before updating.
IF(SELECT [AwaitingSync] FROM DELETED) = 0
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
modified on Monday, October 25, 2010 11:21 AM
|
|
|
|
|
Your update trigger should exclude the column [Awaiting Synch] from the list of columns that the trigger should fire on. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Ah! I didn't realise that you could specify what columns fired the trigger.
This is the current trigger definition:
CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
Which fires when any column is updated.
Are you saying that it should look something like this:
CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(CategoryName)
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END
END
Thanks
|
|
|
|
|
Yes that is correct. I'm not familiar with the SQL Server flavour of doing things, but when defining a trigger you can specify a list of columns as opposed to the entire table, that the trigger is fired on. Glad that has helped you out.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Is is my first time i touching SQL server.
I'm using sql server 2008 express.
I created simple table using Management studio - and i connected to the table from C# code using dataGridView.
I have small simple question ...
1. How can i define a new SP that can take parameters from C# code and how can i call it from C# code ??
2. I add some new raw from the dataGridView - and i don't know why they are not save in the database ( i try to call 'AcceptChanges' - but still this is not working ) .
Thanks for the help.
modified on Monday, October 25, 2010 8:43 AM
|
|
|
|
|
Yanshof wrote: dataGridView
Yuck.
If all you're doing is seeing that it can be done, then use a DataAdapter's Fill and Update methods to move data between the Grid and the database. But it only works for very simple data and you will soon find that that technique is not appropriate for real applications.
For one thing, Grids are generally not a proper way to get user input -- can you imagine an eCommerce site where you placed orders by filling in columns in a grid? No, you should define a form for entering and editing data. You can use a Grid with a subset of the data to allow the user to locate the record he wishes to edit, but never edit within a Grid.
For actual applications you will need a Data Access Layer. A layered application makes swapping parts in and out easier. For instance, at some future time you may want the client to access the database via a Web Service -- if your client is too-tightly bound to your database, that will be much more difficult.
"A stitch in time saves nine." Learn to use proper data access techniques now, before you really need them.
Here[^]'s an article I wrote on one of my data access techniques.
|
|
|
|
|
PIEBALDconsult wrote: never edit within a Grid.
And I would say to that: Never say "never". Without disputing the general thrust of your post, there are occassions when it makes perfect sennse to edit directly wihtin a grid, particularly if the underlying dataset has only a small number of "simple" fields (strings of limited length, or enum types from which the user has to choose for example.) One can (and should) still separate out the data access code into its own class, or layer, but as far as the user is cnncerned the editing can take place right there in the grid.
It all depends on the exact situation, of course, but solutions should fit the problem; one shouldn't force a problem to fit a pre-determined solution.
Just my tuppenceworth.
|
|
|
|
|
In a couple of places I have allowed editing with a Grid, but only out of laziness to allow a knowledgable administrator to configure certain things, never for an average user to muck around with.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
