|
Its sounds like you are on the right track, with a normalize table(Chairs). Why not just preform the join to the other two tables? Like such: SELECT {WHATEVER} FROM Chairs as c, Comms as Com, People as p WHERE c.FK_PeopleID = p.PK_peopleID AND c.FK_CommsID = com.PK_CommsID AND [Whatever time constraints for the chairs table here]
Although this SQL may not be the answer your are looking for, I think that given your boggle the best solution is a one sql solution, which will then require only a sigle trip to Access.
|
|
|
|
|
That's the approach I was looking for, but I haven't used a JOIN before. I usually let Access do it, and don't know how the SQL should be structured. I really need a decent SQL reference! Thanks for the idea!
"Ask not for whom the bell tolls;
It tolls for thee..."
|
|
|
|
|
When I input my first entry in a dataset with an autoincrement field, it starts at 0. When I do the same thing in MySQL, it starts at 1. How do I reconcile the difference?
Thanks
|
|
|
|
|
I am having trouble getting table constraints to work the way I think they should. I have unique constraints set up for two fields of a three field table, and when I add a row to the table, I was hoping the constraints would be honored at that time. I want the new row to have unique values in the fields for the entire column in the table. Here is what I am setting up:
private OdbcDataAdapter keyvalueDA;
.
.
.
keyvalueDA = new OdbcDataAdapter
("SELECT value_id, value, valuehash FROM keyvalue", conn);
keyvalueDACmdBuilder = new OdbcCommandBuilder(keyvalueDA);
keyvalueDA.FillSchema(ds, SchemaType.Source, "keyvalue" );
.
.
keyvalueDA.Fill(ds, "keyvalue");
.
.
.
// Now in a method, I insert the new values in the table.
DataTable myTable = ds.Tables[tableName];
myDataRow = myTable.NewRow();
myDataRow["value"] = valuepassedin1;
myDataRow["valuehash"] = valuepassedin2;
// and then call
myTable.Rows.Add(myDataRow);
At this point I would expect since valuehash is set to be unique, that if I tried to enter a duplicate, it would complain here.
It does not.
I get no indication of a problem until I try to update the dataset into the real database
keyvalueDA.Update(ds, "keyvalue");
which then throws an exception, duplicate key entry.
When I look at the dataset, it shows that the unique constraints are being enforced
in dataset:
enforceConstraints true bool
Do I need to explicitly set the constraints for the "new row" before I add it to the table?
|
|
|
|
|
Okay, the problem was in how the database was setup which I was getting the schema out of. The database had a table with three fields and had declared two of the fields to be primary keys. The unique constraint can only be applied to "THE" primary key. Once I removed the other primary key, the constraint worked.
|
|
|
|
|
If I create a stored procedure that uses a temporary table, I cannot get a .NET data adapter to configure correctly for that stored procedure. A workaround is to create a stored procedure with the same data columns, generate a data adapter and dataset, then change the stored procedure called by the data adapter. Is there a better solution to this problem?
Thanx...
>>>-----> MikeO
|
|
|
|
|
I am getting the following error when I call my dataTable.Update(dataset, tablename) method.
Microsoft.Data.Odbc.OdbcException: ERROR [07002][MySQL][ODBC 3.51 Driver][mysqld-4.0.12-nt]SQLBindParameter not used for all parameters
As this is a bulk update using a dataset, I am at a loss as to figure out which entry is causing the problem. Any troubleshooting tips?
Thanks.
|
|
|
|
|
The following SQL statement returns a recordset if I run it in query analyzer, but it returns NO recordset if I call it from ASP (i.e. via ADO):
insert into module_hyperlinks_all<br />
(definitionid, pageid, linktype, url, innerhtml, target, styleid)<br />
values<br />
(18,0,0,'http%3A///','link1','',0)<br />
select @@identity as yay
If anyone can explain to me why this statement doesn't return a recordset (i.e. there isn't even a recordset with no rows returned, it's simply a closed, uninitialised object) when I execute it from ASP, I would be extremely grateful! (SQL Server 7)
The ASP error when I try to do anything with the recordset is:
ADODB.Recordset error '800a0e78' <br />
<br />
The operation requested by the application is not allowed if the object is closed.
|
|
|
|
|
The reason is that the insert returns a completion marker. ADO is not smart enough to skip over to the first result set based return code.
To get around this, wrap the whole set up in a stored procedure and only the results will be returned. Don't forget to put SET NOCOUNT ON as the first line of your stored procedure.
|
|
|
|
|
Cool, that fixed it, thanks!
NATHAN RIDLEY
Web Application Developer
generalgherkin@yahoo.com
|
|
|
|
|
I am trying to search through a dataset dataTable to find a value based on a search string. I am using the DataTable.Select method but I am having trouble with the search string when it contains the single quote character.
I have tried escaping the single quote with a "\", but I still get errors.
How do you escape special characters in the select string when using the DataTable.Select method?
public string ReturnId(string tableName, string field, string searchValue)
{
// query table return DataRow
// searchValue must be Escaped for SQL special chars.
// this will return -1 for not found
//http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassselecttopic2.asp
string idFound = "-1";
DataTable myTable = ds.Tables[ tableName ];
string strExpr;
// strings must be enclosed in single quotes for these expressions
// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp
strExpr = field + " = '" + searchValue +"'";
DataRow[] foundRows;
// Use the Select method to find all rows matching the filter.
// In this app, these best be unique
try
{
foundRows = myTable.Select(strExpr);
if ( foundRows.Length != 0 )
{
switch (tableName)
{
case "asset":
idFound = (foundRows[0]["asset_id"]).ToString(); // row 0, column 0 asset_id
break;
case "keyvalue":
idFound = (foundRows[0]["value_id"]).ToString(); // row 0, column 0 value_id
break;
case "filevalue":
idFound = (foundRows[0]["value_id"]).ToString(); // row 0, column 0 value_id
break;
}
}
}
catch(Exception err)
{
MessageBox.Show(err.ToString() + "\r\n" +strExpr.ToString());
}
return idFound;
}
|
|
|
|
|
Try replacing all of the single-quote characters in searchValue are replaced by two single-quotes. So your final select expression for "O'Leary" becomes:
myFieldName = 'O''Leary'
Hope this works for you.
Andy
|
|
|
|
|
Thanks for the quick and accurate suggestion. That did the trick.
|
|
|
|
|
Hi,
I am creating a table using ADOX, in VC++ dynamically. And creating fileds in the table, but i am not able to create MEMO fields with zero length and not required attributes.
Can someone help me out on this one.
Mayank Goyal
|
|
|
|
|
Hi..
I have a table that i want to make some updates , inserts to it..
the new values are read form an XML file (which contains the new valuse)..like this:
rs.Open "Update.xml", , adOpenStatic, adLockReadOnly, adCmdFile
now I want a direct way to update existing fields and insert the new ones..
I can make a loop .. search for each existing record by ID , update it.. And if not found
I insert it ,, but this is not fast, smart enough, any suggestions ?
|
|
|
|
|
hspc wrote:
now I want a direct way to update existing fields and insert the new ones..
I can make a loop
Whatever the solution is, it is going to involve a loop of some sort and multiple UPDATE statements. It simply has to. (Though technically you could create a DTS package which opens the XML file at location and then runs. But DTS can be a PITA IMO.)
The main thing though is to get this loop out of your ASP file.
The easiest way I can think of is to open the XML file, pull out the values you need into a string which is comma delimited (how big is this XML file?) You then pass that string to a stored procedure and let the SP do the looping (far faster as it is "inside" the database.)
DTS might be the best way though actually as it will be more flexible if source fields need to change.
You could also write your own windows service/app and let it regularly check the XML file.
Main thing; Get the loop out of the ASP page.
Paul Watson
Bluegrass
Cape Town, South Africa
Chris Losinger wrote:
i hate needles so much i can't even imagine allowing one near The Little Programmer
|
|
|
|
|
thank you Paul for your suggestions
the XML file can be more than 50KB.. I can read it effeciently using RecordSet.Open mehod..
the insert and update is the problem..
|
|
|
|
|
Hello,
I develop a desktop application with MS VC++ 6/MFC/ADO that uses a MS Access DB to store my application data (in a .mdb - local database - file).
I would like to :
- protect the DB integrity by encrypting it or at least hiding that it's a MS Access DB to the user (renaming the .mdb to .dat is not enough)
- try different DB engine to handle the file, even if it's not a MS Access one anymore
- handle the local DB with NO server running (can't do it with MySQL++ for example, see below)
I'm not a DB expert so I don't know much about DB engines and Visual DB managers.
What do you guys use as a DB engine ?
Actually here's a list of the following DB engines or systems I want to try or already know :
- MS Access : can't design my DB and handle it from my app, everything is "perfect" but I want to try new things... And I don't know how to encrypt its data or protect it from the users
- Visual FoxPro : It's part of my MSDev 6 Pro package but never really gave it a try. It sounds like an other MS Access to me... What are the main differences ? Maybe I can easily protect my DB integrity with it... Does anyone know or use Visual FoxPro ?
- CodeBase : DB engine that supports tons of IDEs and languages ! If you know it or use it, what do you think of CodeBase ?
- MSDE : someone adviced me to use MSDE but It's part of MS SQL Server 2000, It seems that I can't download it from the MSDN website, it's not MSDev 6 compliant (only supported by .NET). Note that I can't affort MS SQL Server 2000, far far too expensive and I don't need such a DBMS for my the softwares I develop
- MySQL : I really like MySQL because I also develop web based applications, however I have to connect to a "host" in order to access the DB, I can't connect to a file with ADO or MySQL++ (MySQL C/C++ API)...
- XML Database : for small DB I thought It could be nice to use a DB engine that uses XML files (manage a configuration file, address book...), what do you think ? Do you know of any good API ?
Kind regards,
JM. Molina
Europe > France > Lyon
|
|
|
|
|
MS-Access does provide encryption. Choose "Tools->Security->Encrypt/Decrypt Database" from the main menu. I know at least one major bank that uses Access in this way.
You should be able to find a copy of MSDE on the Microsoft Office-2000 installation CDs (its not installed by default). MSDE is basically a stripped-down version of SQL-Server. I have a copy installed on my kids' Windows 98 box. The main problem is that no administration program is provided (like SQL-Server's Enterprise Manager). However, there are freeware program available that replicate much of this functionality for you. Note that MSDE is implemented as a server process (just like SQL-Server) - so this may rule it out as an option for you.
If you are not storing much data then you might take a look at ADO presisted-recordsets. These are written to you PC's disk as an XML file. The main problem with this (and XML in general) is that the files are not encrypted. For more complicated XML, you would probably use Microsoft's MSXML component.
Hope this helps.
Andy
|
|
|
|
|
Hi Andy,
MS-Access does provide encryption. Choose "Tools->Security->Encrypt/Decrypt Database" from the main menu. I know at least one major bank that uses Access in this way.
I know I can encrypt my DB. I also protect it with a password. I can't believe the passwords are not even encrypted if the DB is not encrypted too... If the DB is only encrypted, users can read it with MS Access and I don't want them to. If the DB is encrypted and password protected, they can't read it anymore... Unless they use one of the dozen of "password recovery tools" you can get everywhere on the web. Which means my DB is not protected at all. What really worries me, it's that they can retrieve my DB password. It's unreadable... first... my application connects to it using a password... But when the user uses a password recovery tool on it, they get the DB password AND users passwords. Passwords I would use for all my DBs, accounts... My solution would be to release a special DB, protected by a "dummy" password (only known by the application), with a special user account that has read and write access to the DB, tables... But It requires a lot of modifications and messes up my all release process.
You should be able to find a copy of MSDE on the Microsoft Office-2000 installation CDs (its not installed by default). MSDE is basically a stripped-down version of SQL-Server. I have a copy installed on my kids' Windows 98 box. The main problem is that no administration program is provided (like SQL-Server's Enterprise Manager). However, there are freeware program available that replicate much of this functionality for you. Note that MSDE is implemented as a server process (just like SQL-Server) - so this may rule it out as an option for you.
Alas! I can't use MSDE. Moreover I am sure there's somekind of perfect security system for MSSQL DB.
If you are not storing much data then you might take a look at ADO presisted-recordsets. These are written to you PC's disk as an XML file. The main problem with this (and XML in general) is that the files are not encrypted. For more complicated XML, you would probably use Microsoft's MSXML component.
There are hundreds of APIs and XML-DBMS and I don't really know which one to pick... The Apache one sounds like a good choice to me. About security, I am sure that some of the XML DBs have a security layer.
Decrypt Database
Did you ever tried to Decrypt a MS Access DB ? If I exclusively open an encrypted MS Access DB, when I select Encrypt/Decrypt, it doesn't ask me to decrypt it, it askes me to encrypt it... again.
Summary
What do you think of my "dummy password" hybrid solution and the password recovery tools issue ? If any, what DB engine do you use for your own softwares ?
JM. Molina
Europe > France > Lyon
|
|
|
|
|
The "dummy" password solution seems to work just fine. However, I am a contract programmer, so I've only have to show that I have taken sufficient steps to put security in place. As far as password recovery tools go, I would advise that you use a strong password that doesn't appear in the dictionary.
I never had to decrypt the MS-Access database. However, Microsoft provides a program named "JetComp.exe" on their support web-site. Normally I use it to fix MS-Access databases that have got seriously corrupted. If anything could decrypt your database it would be that program.
I don't have any real preferences for databases. I've used quite a few different ones (I tend to use what my clients ask for). My personal preference is not to use MS-Access (network performance sucks and it is fairly easy to corrupt the database files). You might want to consider Sybase's ASA database (used to be called SQL-Anywhere).
Hope this helps.
Andy
|
|
|
|
|
HI ALL
I am having problem with the custome error message display. As in Visual Basic 6.0 days we could give our custom error messages by getting the error code how we can achieve the same in C#. where can i get the mappings of errorcode returned by the exception so that instead of displaying the default error message i should be able to display my own custom error message on the same exception
THNX IN ADVANCE
Love U ALL
|
|
|
|
|
I would like to run a query on the results of a previous query.
I know I can use SELECT in a SELECT, but the query I'm doing is not allowed to be a subquery (it's a TRANSFORM).
I can run a query on a query from Access, I just want to do the same thing in MFC.
Any ideas?
Cheers
Neil
|
|
|
|
|
A query on a query in SQL Server can be implemented in several ways:
1) create the initial query as a view and then query the view
2) insert the initial query results into a temp table and then query the temp table
3) create a table variable and insert the results of the initial query into the table variable. Then, run the second query using the table variable.
4).... and on and on limited only by your imagination and the specifics of your query(s).
|
|
|
|
|
Can you tell me how to do this on a TRANSFORM statement. As far as I can see, you can't TRANSFORM INTO a temp table.
Maybe I'm wrong?
Cheers
Neil
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
