|
I have an application the adds records to an MS Access database. When the DataAdaptor.update command is issued I get a Syntax error in insert into statement. I am using command builder and only get the error on one table. That table has two fields which are the problem. They are called "Password" and "Usergroup." If I rename the fields it works without any problem. So why not just rename them? Simple right. No, the database is used by another application and chances of getting it changed is slim at best.
Any ideas as to how to work around this?
Any help would be greatly appreciated.
Mike 
|
|
|
|
|
|
Password is a reserved word Can't use it
|
|
|
|
|
Then how is it used by the other application?
I don't use Access but, maybe it permits escaping of column names with square brackets. e.g. [Password]
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 have a access table and want to export the data in the table to a html file every night. i also need to clear 2 fields in the table after the data has been exported. Any ideas on how to do this?
I'm using ADO on the VB6 forms
|
|
|
|
|
Write a VBScript and use the Command scheduler.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Where I can get the subj? Thanks in advance
|
|
|
|
|
I have a C# application for generic database access that does all sorts of extra processing. It is a port of an existing COM application written in C++.
The application uses objects from Microsoft.Data.Odbc namespace.
So I'm opening an OdbcConnection
and using an OdbcDataApaptor for retrievals, and an OdbcCommand for executing updates using ExecuteNonQuery.
All is working fine except for one thing involving the SQL Server RaiseError command being executed in a trigger.
The code I'm using in the application for update commands is this:
public long ExecuteChangeQuery(string sSQL)
{
long lResult;
try
{
m_cmd.CommandText = sSQL;
lResult = m_cmd.ExecuteNonQuery();
}
catch(OdbcException e)
{
// log the exception
throw e;
}
return lResult;
}
Under normal circumstances everything is fine. And if a normal error occurs, such as a SQL syntax error, etc. the exception is caught as expected.
However when the SQL server trigger calls RaiseError with a severity level of 16, which should throw an exception. Nothing happens. No exception is thrown and the call to ExecuteNonQuery simply returns -1 to indicate that a rollback occurred.
The existing COM application works properly with the same trigger.
Any ideas as to what's going wrong, or what I'm not doing?
Help greatly appreciated.
|
|
|
|
|
I have a VB 6 Application that writes to a ms access DB. My connection works fine ( using ADO) problem is I'm trying to do a search on the table using a varible. the varible is string. I'm not sure if i have the syntax correct. PLEASE HELP
Here is the code
Private Sub CmdSearch_Click()<br />
Dim strSearch As String<br />
<br />
strSearch = lblSearch.Caption<br />
<br />
adoRecords.RecordSource = "SELECT * from Volenteers where In [last_name] Like strSearch ; "<br />
<br />
adoRecords.Refresh<br />
<br />
End Sub
I get a error of syntax error in FROM clause
strSearch is from a text box from a dialog form
i had the same set up in a different program almost same sytax, and it works there I'm lost
any help would be appreciated
thanks
|
|
|
|
|
Hm - I would have written it as:
for exact matches -
"Select * from Volunteers Where [Last_Name] = '" & strSearch & "'"
for wild cards -
"Select * from Volunteers Where [Last_Name] Like '" & strSearch & "%'"
I believe this will work with access, try the query builder tool.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
tried get error in from clause
under the sql i have adorecords.recodset.refresh
that gets highlighted
???
|
|
|
|
|
Try setting a breakpoint on the line where to set your sql variable. Run the program in the debugger and set a watch for your sql variable. Once you know *exactly* what your sql statement looks like, run it in access to make sure it is syntactically correct. You may be missing a "'" or something -
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Hey all, just for giggles, I was trying to figure out how these works. I created the following table:
Create Table Emps(<br />
EmployeeID int identity(1,1),<br />
EmpName varchar(100), <br />
ManagerID int, <br />
ActiveFlag char(1)<br />
)
insert into emps(empname)values('Big Boss')<br />
insert into emps(empname, managerid)values('David', 1)<br />
insert into emps(empname, managerid)values('Holly', 2)<br />
insert into emps(empname, managerid)values('Samwell', 3)<br />
insert into emps(empname, managerid)values('Ian', 4)
-- set everyone active
update emps set activeflag = 'Y'
CREATE TRIGGER EmpTrig ON emps For UPDATE <br />
AS<br />
UPDATE Emps SET ActiveFlag= i.activeflag <br />
FROM (Emps e INNER JOIN Inserted i ON e.managerid = i.employeeid)<br />
GO
The idea was that this would set everyone in a heirarchy "Inactive" if the manager was set inactive. Unfortunately, it does this only one level deep. How can I enable recursive triggers?
Thanks in advance -
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
