|
alter database <DatabaseName>
set RECURSIVE_TRIGGERS on
Wout Louwers
|
|
|
|
|
Hello,
I'm writing a little app for a doctor. Each patient can have many appointments, and each appointment can have many prescripted drugs, like the tables below:
Patient
-------
ID_patient (key)
Appointment
-----------
ID_appointment (key)
ID_patient
Drugs
-----
ID_drug (key)
ID_appointment
If I want to delete a patient, I have to delete all his appointments, and all their drugs.
A simple DELETE FROM Appointment WHERE ID_patient=@PatientID; query will delete all my appointments. How can I delete all the Drugs for each appointment in a single query, without using a DataReader to walk all the appointments and executing a DELETE query for each appointment?
Any help will be greatly appreciated. Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Use a stored procedure to contain the delete logic or, as a second choice, place triggers on the patient table to cascade delete all dependent tables' associated records. Wait, you're not using some weak open-source dbms that doesn't support triggers, are you? 
|
|
|
|
|
TimWallace wrote:
Wait, you're not using some weak open-source dbms that doesn't support triggers, are you?
No 
I'm using a VistaDB database. But I found how to do it, just ask my relationships to cascade deletes.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
First my snippet of code:
try<br />
{<br />
_CommandPtr pCommand;<br />
pCommand.CreateInstance(__uuidof(Command));<br />
pCommand->ActiveConnection = m_pConnection;<br />
pCommand->CommandText = "Select * from emg3";<br />
<br />
<br />
_RecordsetPtr pRecordset;<br />
pRecordset.CreateInstance(__uuidof(Recordset));<br />
pRecordset->CursorLocation = adUseClient;<br />
pRecordset->Open((IDispatch*) pCommand, <br />
vtMissing,<br />
adOpenStatic,<br />
adLockBatchOptimistic,<br />
adCmdUnknown);<br />
<br />
<br />
_variant_t DEST_IP;<br />
_variant_t PORT;<br />
_variant_t DEST_ID;<br />
int nDest = 0;<br />
<br />
while (!pRecordset->adoEOF)<br />
{<br />
int nRecCount = pRecordset->GetRecordCount();<br />
char cRecCnt[10];<br />
itoa(nRecCount, cRecCnt, 10);<br />
m_cRecCnt.SetWindowText(cRecCnt);<br />
DEST_IP = pRecordset->GetCollect("EMG3_DEST_IP");<br />
PORT = pRecordset->GetCollect("EMG3_PORT");<br />
DEST_ID = pRecordset->GetCollect("EMG3_DEST_ID");<br />
CString cDestID = (char*)_bstr_t(DEST_ID);<br />
if (DEST_IP.vt != VT_NULL)<br />
{<br />
int iIndex = m_lstBox.InsertItem(nDest++, (char*)_bstr_t(DEST_IP));<br />
m_lstBox.SetItemText(iIndex, 1, (char*)_bstr_t(PORT));<br />
m_lstBox.SetItemText(iIndex, 2, (char*)_bstr_t(DEST_ID));<br />
}<br />
pRecordset->MoveNext();<br />
}<br />
}<br />
catch (_com_error &e)<br />
{<br />
_bstr_t bstrError(e.ErrorMessage());<br />
CString strError = (char*)bstrError;<br />
AfxMessageBox(strError);<br />
}<br />
catch (...)<br />
{<br />
AfxMessageBox("Unknown Error!");<br />
}
Is it possible to setup my _variant_t's on the fly...for instance what if I did not how many _variant_t's to set. Is there a way to do these by passing the number of varants to this function along with the field names?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Hi,
Do you know the way how to add paging into datalist? (example, article...)
|
|
|
|
|
Okay so i'm having some trouble with adding to my database through the dataset. i can read from it and it prints just fine, i run the code okay with no errors, but it simply doesn't add the record, i've left it simple here just so i can get it right before i start adding the extra functionality. okay so the table name is UserID, and i want to add into the table, what am i doing wrong?
private void btnAddRecord_Click(object sender, System.EventArgs e)<br />
{<br />
string ConnectString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\salesleadmgmt.mdb";<br />
OleDbConnection AddConnection = new OleDbConnection(ConnectString);<br />
OleDbDataAdapter addAdapter = new OleDbDataAdapter("Select * from UserID", AddConnection);<br />
DataSet addSet = new DataSet();<br />
DataRow addRow;<br />
<br />
OleDbCommandBuilder addCommandBuilder = new OleDbCommandBuilder(addAdapter);<br />
<br />
addAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;<br />
addAdapter.Fill(addSet, "UserID");<br />
<br />
<br />
addRow = addSet.Tables["UserID"].NewRow();<br />
addRow["UsrName"] = "Bob";<br />
addRow["passwr"] = "Bobpwd";<br />
addRow["UsrID"] = "6";<br />
addSet.Tables["UserID"].Rows.Add(addRow);<br />
addSet.AcceptChanges();<br />
addAdapter.Update(addSet, "UserID");<br />
addAdapter.Fill(addSet, "UserID");<br />
UsrGrid.DataSource = addSet;<br />
UsrGrid.DataBind();<br />
<br />
<br />
}
Now, its working so far as on the UsrGrid i see the "bob" entry, but afterwards its not in the database, why isnt the DataSet writing to my database???
this runs in the .CS file, i don't get any of the write lines or anything, it does run on a button click, i have another button click that works fine, i'll show that one, whats wrong with this first one?
working display click
private void Button1_Click(object sender, System.EventArgs e)<br />
{<br />
string ConnectString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\salesleadmgmt.mdb";<br />
OleDbConnection CustConnection = new OleDbConnection(ConnectString);<br />
CustConnection.Open();<br />
<br />
DataSet CustSet = new DataSet();<br />
OleDbDataAdapter CustAdapt = new OleDbDataAdapter("SELECT * FROM CustInfo", CustConnection);<br />
CustAdapt.Fill(CustSet);<br />
CustConnection.Close();<br />
CustGrid.DataSource = CustSet;<br />
CustGrid.DataBind();<br />
}
|
|
|
|
|
Hello,
I am developing a library system. I have 2 tables, book and student. The relationship is 1 student can have many books. I am having updating the book table when the student returns the book, the studentID (foreign key) will be cleared from the book table. I think this is the problem l am having. My code is listed below with the error description.
When the student returns the book, their studentID number should be cleared from the studentID number in the book table.
try<br />
{<br />
cnnReturnBook.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibrarySystem.mdb;Persist Security Info=False";<br />
<br />
OleDbCommand cmdReturnBook = cnnReturnBook.CreateCommand();<br />
cmdReturnBook.CommandText = @"SELECT * FROM BOOK<br />
WHERE RefNumber = '" + txtBookRef.Text + "' ";<br />
<br />
OleDbDataAdapter daReturnBook = new OleDbDataAdapter(cmdReturnBook);<br />
OleDbCommandBuilder cbReturnBook = new OleDbCommandBuilder(daReturnBook);<br />
<br />
if ( daReturnBook.Fill(dtReturnBook) == 1 )
{<br />
txtDateOut.Text = "";<br />
txtDateDue.Text = "";<br />
txtStatus.Text = "Available";
txtIDNumber.Text = "";
txtName.Text = "";<br />
txtSurname.Text = "";<br />
<br />
dtReturnBook.Rows[0]["Status"] = txtStatus.Text;<br />
dtReturnBook.Rows[0]["DateOut"] = txtDateOut.Text;<br />
dtReturnBook.Rows[0]["Remarks"] = txtRemarks.Text;<br />
dtReturnBook.Rows[0]["StudentID"] = txtIDNumber.Text;<br />
dtReturnBook.Rows[0]["DateDue"] = txtDateDue.Text;<br />
<br />
daReturnBook.Update(dtReturnBook);
}<br />
}<br />
catch ( OleDbException ex )<br />
{<br />
MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK,MessageBoxIcon.Warning);<br />
}<br />
catch ( Exception ex )<br />
{<br />
MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK, MessageBoxIcon.Warning);<br />
}<br />
finally <br />
{<br />
cnnReturnBook.Dispose();<br />
}<br />
}
Error description
System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Student'.
At System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
At System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
Thanks in advance,
Steve
|
|
|
|
|
I have recently written a program in Visual Basic 6.0 that use ADO to talk to both a SQL Server database and also an MS Access 2000 database. They both use the same code to talk to the database.
When run on the development machine (XP Pro) the program can talk to the SQL Server database and MS Access database with ease.
When run on a virginal XP Pro machine the program can talk to the SQL Server database - SELECT, UPDATE, INSERT and DELETE all work fine. However, when trying to do the same on the MS Access database only SELECT and DELETE works? INSERT and UPDATE cause the program to freeze. No errors are thrown up, the program just locks up!
I have checked the folder / file permissions of the MS Access database to ensure I have the correct priviledges (I do) but cannot resolve the problem?!
I am a bit desperate here as the program needs to be delivered in the next week and this was the final stage of packaging!
Many thanks in advance!
- Pete -
|
|
|
|
|
Try a MDAC (2.8) upgrade on the virginal machine. Just search MDAC an microsoft.com.
|
|
|
|
|
dbms_utility.get_time
It seems like dbms_utility.get_time is not giving me the correct time...?
SET TIMING ON;
DECLARE
i NUMBER :=0;
time1 NUMBER;
time2 NUMBER;
BEGIN
time1:=dbms_utility.get_time;
LOOP
i:=i+1;
DBMS_OUTPUT.PUT_LINE(i);
EXIT WHEN i>1000;
END LOOP;
time2:=dbms_utility.get_time;
DBMS_OUTPUT.PUT_LINE(time1);
DBMS_OUTPUT.PUT_LINE(time2);
DBMS_OUTPUT.PUT_LINE('time elapsed in ms: ' || (time2-time1) /100*1000 );
END;
The output of the above script:
...
...
997
998
999
1000
1001
862757
862757
time elapsed in ms: 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.25
*** It seems like get_time is not returning the correct time... What's happenning? ***
THANKS in advance!
Norman Fung
|
|
|
|
|
dbms_utility.get_time will only give you resolutions of 1/100 second. try to use TIMESTAMP instead.
dirk
|
|
|
|
|
Can't figure out datasets to save my life. I like the idea of having a little relational database at my fingertips that can keep track of changes and update the datasource automatically. I can get my head around that part. What I can't figure out is how to get data in and out of a dataset. For instance I need to read in data from three separate tables in my database into a dataset. All the documentation I have read only deals with one table. can't figure out how to read in multiple tables without using multiple dataadapters/datacommands. Not to mention that I already have to deal with three sets of objects. One for OleDb one for Sql and One for ODBC. The application must be able to work with Many different providers. It's already driving me up the wall that I have to do everything three different ways everytime I do anything. It's making my code a little long winded. Now I have to implement these blasted datasets and I don't want to have 9 separate dataadapters 9 separate datacommands and 3 separate connection objects. Can't find any good documentation, I have many books and have been searching the internet for days. About ready to tear what's left of my hair out. Can anyone explain how to fill and update datasets containing multiple tables in a simple way that a stupid dummy like me can understand? Thanks.
|
|
|
|
|
Use stored procedures (if you can, not all DBMS support them).
CREATE PROCEDURE dbo.MyProc
@SomeKey int
AS
SELECT * FROM TableA WHERE Id = @SomeKey
SELECT * FROM TableB WHERE Id = @SomeKey
SELECT * FROM TableC WHERE Id = @SomeKey
GO Then on the .NET side use
SqlConnection conn = new SqlConnection(theConnectionString);
SqlCommand cmd = new SqlCommand("MyProc", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapeter da = new SqlDataAdapter(cmd);
da.Fill(theDataSet);
You might find this code generation tool useful: http://www.dotnetnuke.dk/Default.aspx?tabid=64[^] It is quite simple, but there is an offline version you can download, if you are willing to pay for it. Even if you don't use the code it generates, it gives a great example of how to build your data abstraction layer clearly and efficiently while dealing with multiple database systems. IMO.
Does this help?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
> Use stored procedures (if you can, not all DBMS support them).
Yea, that's one of the major problems. The application absolutly must be able to use MySQL in addition to Access (we use jet for standalone versions) and SQL Server/MSDE. We market to many elementary/middle schools and we offer MySQL as a low cost alternative to MS SQL Server. Most elementary/middle schools can't afford SQL Server and use too many concurrent connections for MSDE to handle. I don't think MySQL supports stored procedures. I could be wrong hovever. I've never messed with stored procedures and a lot of the code examples I can find on this subject use them, so it makes it that much more confusing to me. In most of my code I just use datareaders and datacommands to interact with the database. I've been told that this is "doing it the hard way". But, it seems to work just fine for what I am using it for. Now I am doing something else that lends itself nicely to using datasets and I am having a heck of a time figuring it out.
I'ts making me feel really dumb. Although I did teach myself C# and VB.Net using only books and no help from anyone and the only programming experience I had prior to that was vb6 and a tiny bit of C++/MFC, so I figure I'm not totally brain dead. What I really need is for somone to sit down with me and look at my code and tell me what I am doing wrong and how to implement datasets into this class I am working on. Unfortunatly, the only other programmer I know is my boss and he knows nothing of .NET only C++/MFC.
Dave is frustrated!
|
|
|
|
|
David Bliss wrote:
In most of my code I just use datareaders and datacommands to interact with the database. I've been told that this is "doing it the hard way".
I think what these people are saying is just rubbish. DataReaders are just doing it differently. If you access the data for quick throw away results, or are going to store it internally in a format other than in a DataSet they are an excellent choice. Lots of people blindly load data into DataSets and then copy it to where they need it and end up with extra copies of data and really crappy inefficient data access. Like Scotty said in Star Trek V "How many times do I have to tell ye! The right tool for the right job!". From my experience DataReaders have been more appropriate than DataSets. (But I do a lot of data manipulation tasks and not a lot generally gets to the User Interface)
If you are using DataGrids and the link DataSets can work really well - Having said that, you can use a DataReader as the source in many cases and it works just as well (if you just need display only access). If you need random access to the records once they are client side then DataSets are quite useful.
Also, the DataAdapter just uses DataReaders and Commands to get the information in and out of the database anyway. So, you're still using them, you're just one layer of abstraction further away.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
That's what I always thought and that is why I've never tried messing with them until now. Datasets always seemed over redundant to me. Why have a copy of my tables, they are already in the database!?
I don't understand how they can make anything easier if 400 pages of your avarage ADO.NET book is dedicated to datasets (and their associated classes) and only about 10 pages or less on datareaders. Datareaders and Datacommands are very straight forward and easy to understand and use. Using datasets is just overcomplicating things. Also, I've never bound anything to anything and I don't see the need.
I thought that maybe insted of using datamembers inside my class, I could just use a dataset and have properties that access the dataset directly.
I'm giving up at this point. I'm just going to do it a different way. I've wasted almost three days on this now. I wanted to use datasets because they can keep track of changes and what has been deleted, then do the appropriate things with the data source when updating. This is all well and good, but it seems to me like there is just as much work involved with using the datasets as it is to just keep track of that stuff myself. Maybe if I was able to use stored procedures across the board It would be a more viable option.
Also, I wanted to use datasets because, I guess you can store strings in the database and not have to worry about special characters like ' and ;. Is this true? Right now I'm having a hell of a time with storing strings containing special characters.
Thanks.
|
|
|
|
|
David Bliss wrote:
Also, I wanted to use datasets because, I guess you can store strings in the database and not have to worry about special characters like ' and ;. Is this true? Right now I'm having a hell of a time with storing strings containing special characters.
You should use parameterised queries. The syntax is slightly different depending on the database. SqlServer uses named parameters, while Access uses placeholders and you must supply the parameters in the same order that they appear in the query.
Here is an example using Sql Server
SqlCommand cmd = new SqlCommand("SELECT * FROM TableA WHERE ColumnA = @ParameterA AND ColumnB = @ParameterB");
cmd.Parameters.Add("@ParameterA", "David's Data");
cmd.Parameters.Add("@ParameterB", "String with ; semi-colon in it");And in Access I think it is [disclaimer - I don't use Access much so this may be wrong]
OleDbCommand cmd = new OleDbCommand("SELECT * FROM TableA WHERE ColumnA = ? AND ColumnB = ?");
cmd.Parameters.Add("David's Data");
cmd.Parameters.Add("String with ; semi-colon in it");
I hope this helps.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Hmmm, I've heard of this before.
OK, pain in the butt changing syntax between providers, but doable. Does this work with ODBC/MySQL? If so, we might have something here. I'll have to look into this more deeply.
Thanks.
|
|
|
|
|
David Bliss wrote:
pain in the butt changing syntax between providers
It just depends on what the back end database supports.
David Bliss wrote:
Does this work with ODBC/MySQL?
Should do.
If you want another reason to implement parameterised queries then you should know that it helps prevent SQL Injection Attacks[^]
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
> Should do.
I'll have to check into it. Lord, I hope so. Keeping my fingers crossed.
> If you want another reason to implement parameterised queries then you should know that it helps prevent SQL Injection Attacks
Yea, I know about those. Although, In my case I doubt I would have to worry about that in an elementary school setting, but you never know I suppose. Also, This is not a web based product.
|
|
|
|
|
Another idea. I'm not sure if all databases support this (I think it is part of the ANSI SQL92 standard - but it may just be a Microsoft thing [sorry, that's not very definite])
You can supply semi-colon delimited SQL Commands.
SELECT * FROM TableA; SELECT * FROM TableB; SELECT * FROM TableC That might help, but I'm not sure about the update/insert/delete part of the data adapter.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Yea, I already thought of that. Problem is, I need to use data returned from the second select statement to create the third. So that doesn't work.
Or, wait a minute "SELECT *" am i just supposed to get a copy of the entire damn database? That just seems wacky.
Is that how you are supposed to use datasets? Copy the entire database in its entirety!? Holy crap, what if there are thousands of records and you only need to deal with a couple?
See, the whole dataset concept just dosen't make any sense to me.
|
|
|
|
|
David Bliss wrote:
Or, wait a minute "SELECT *" am i just supposed to get a copy of the entire damn database?
No, no... I'm just being lazy when I'm typing my reply. And remember your WHERE clause to filter the data.
It is always best to specify a column list in a query. If you do that and you add columns to the table it won't affect pre-existing code which might expect a certain number or order of columns.
David Bliss wrote:
Is that how you are supposed to use datasets? Copy the entire database in its entirety!? Holy crap, what if there are thousands of records and you only need to deal with a couple?
 I'm just thinking of one table in particular that I'm working on. Data is being generated at a rate of several million rows per year.
In SqlServer at least SELECT TOP xxx where xxx is the number of rows you want back is very useful.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
I thought not.
Yea, Yea I'm fairly familiar with SQL syntax and dealing with databases in general. Just not stored procedures, paramaterized querys or anything having to do with datasets or databinding.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
