|
Hello,
I have my application working perfectly on MSDE, now I want to downgrade it to using an Access database locally.
I'm using OleDb* classes, so I just changed my connection string to point to the database and it worked. Well, just partially.
I'm having two little problems.
First, all my INSERT queries are throwing exceptions on invalid syntax. I copied one of those queries to Access and created a query and it worked. Just that it reformatted and added those brackets around one parameter name.
INSERT INTO Users ( LoginName, Name, [Password], Permissions )<br />
VALUES ('pepe', 'Jose', 'p', 1007);
I added those brackets to my program, and it worked (on that particular INSERT query). It also worked (that query) using MSDE. My question is, what are those brackets, and how do I know which parameters I should enclose in them? Do I have to fix all my INSERT queries?
The second problem is that when using the Access database, an immediate refill of the list with the records (users in this case) does not show the new just-added record. Using MSDE it comes up.
Any ideas?
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
UPDATE queries also cause problems. I haven't seen what exactly is causing the error, but also throw exceptions.
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
The brackets are used to let Access know that you are calling a field in a database instead of using one of it's reserved keywords. You can do an MSDN search for Access keywords or look in Access and I think password is one of them. Thus, you would use the brackets for all fields that are named after reserved keywords.
I'm not sure about your second problem. Can you post the part of the code that's calling the database?
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
David Stone wrote:
I'm not sure about your second problem. Can you post the part of the code that's calling the database?
The code is simple:
oCmd = new OleDbCommand(sSQL, oConn);
oCmd.ExecuteNonQuery();
FillUsersList();
sSQL is the INSERT, DELETE or UPDATE query (modifying the database.)
FillUsersList is simply a routine that again opens the connection, performs a SELECT query and shows the results on a list view control.
The changes don't come up in the list view from this call to FillUsersList(). A later call will do.
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
I've noticed when I deal with Access databases there is a little bit of a lag with data entry. I think it's because you have to actually open a file and then insert the data. Whereas with MSDE or SQL Server, the server is always running so the data gets updated automatically. Can you have your program wait for a couple seconds before it calls the FillUsersList() method?
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
David Stone wrote:
Can you have your program wait for a couple seconds before it calls the FillUsersList() method?
I guess I will have to do that... thanks
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
No problem. Sorry I couldn't be of more help. My question is, why can't you install MSDE on the client machine?
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
David Stone wrote:
No problem. Sorry I couldn't be of more help.
It's OK. Don't worry.
David Stone wrote:
why can't you install MSDE on the client machine?
Well, I could... here is the story: my dad is building a car wash, and I'm developing the software for him (sales, customer control, reports, statistics, and so on). I won't charge him (after all, he paid for my vacations in Europe, and I spent a lot of money), but I'm planning on selling it to other car washes (two friends of his are building other, and he said he would make them buy it from me).
One of the features was support for several machines in the network (cashier registering sales while manager seeing stats), so I started with MSDE. I'm studying college about 800 km from my home city, so I cannot go to install MSDE (and to ask my dad to install it is too much). I went this past weekend, but the computer where I was going to install it was not ready (my dad at first wants it locally on just one computer) so I thought downgrading it to Access will be easier installation. I've been all morning working on it and it seems it's bringing more problems, so I think I better find a way to go for a day and install it myself (or find someone who can do it for me).
Another idea that occured to me is to sell it by licenses for each computer (say X for the first and Y for each additional computer, being X >> Y). So if someone just wants one license, I could sell the Access version instead of the MSDE version. (or hardware locks...)
And that's why...
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
But MSDE is a free installation. With a couple of limitations, it's just like SQL Server. I think there's a restriction on the size a db can be, and the connections, but that shouldn't be a problem. I don't think that a carwash would exceed that limit. I think MSDE is the way to go. Besides, if you need to, you can always just install SQL Server over that and you won't have to change around any of the "plumbing" code.
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
Yes, I know... MSDE is more than enough for a car wash. The main problem is installation, but I can sort that one out. My other idea was that using an Access DB can help limit the software to just running on one computer.
But I'll end up only using MSDE... too many problems.
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
If you look in the VS.NET\Setup\MSDE directory you should see the setup files needed for installing MSDE; all you *should* have to do is copy those out (change the instance name in setup.ini) and run the setup program. That should install MSDE on the client system.
James
"And we are all men; apart from the females." - Colin Davies
|
|
|
|
|
And creating the database?
I did it through Access... well, maybe create an empty Access database with all the structure and call the upsizing wizard on that machine.
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
You would need the SQL Client tools in order to do that. You can connect with VS.NET or you could connect via a local installation of SQL Server. I'm not sure if you have either, but it can be done that way. The Upsizing Wizard is fine. It does an OK job, but as the motto goes: "Never upgrade. Do a clean install." 
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
I think I'll use the upsizing wizard. I can use Access to export current tables to a database (just the design) and then use the upsizing wizard to put them back on another SQL server.
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
You can create it via a T-SQL script.
CREATE DATABASE MyDatabase
USE MyDatabase
CREATE TABLE yadda....
You can execute the T-SQL script by putting it into a string object and executing it via a SqlCommand object. Or put it in a file and run the osql command. Either can be done from within a setup program.
James
"And we are all men; apart from the females." - Colin Davies
|
|
|
|
|
Yes, but it's so much nicer to do it visually isn't it? 
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
I can go either way
I prefer having the scripts so that if I need to recreate the database I can do it without a problem; plus it makes installation a breeze no matter what version of SQL Server I'm using (so long as I don't use any features not found in earlier versions).
But getting the initial design is generally done quicker by using the visual tools.
James
"And we are all men; apart from the females." - Colin Davies
|
|
|
|
|
I havent worked with this for a while, but you dont state what version of Access you are try to downgrade to. If you are using Access2002 I would recommend setting the to ANSI-92 instead of ANSI-89. It solved alot of headaches for me.
Hope this helps 
MYrc : A .NET IRC client with C# Plugin Capabilities. See
http://sourceforge.net/projects/myrc for more info.
|
|
|
|
|
I'm using Access XP (2002) but my customer can also use Access 2000. But AFAIK, they both use the same format. My Access XP database says in the title bar (Access 2000 Format), and I just created it on XP.
How do I set the ANSI-92 standard?
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
They don't use the same standard. Open an Access database. Go to Tools->Options->Advanced(Tab)->Default File Format(Group Box) There you can change the format from 2002 to 2000 or back.
As for the ANSI 92 standard, I believe he means to go to the Tables\Queries Tab and then to the SQL Server Compatible Syntax group box.
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
I changed the standard (not the format) and nothing seemed to change. The problem was I was using reserved words (Date, Time, Password, Year, etc.) in several tables.
Thanks for the tip!
-- LuisR
──────────────
Luis Alonso Ramos
Chihuahua, Mexico
www.luisalonsoramos.com
"Do not worry about your difficulties in mathematics, I assure you that mine are greater." -- Albert Einstein
|
|
|
|
|
No problem. I used to have a nice pretty chart of Access' reserved keywords up on my wall. I know most of them.
David Stone
It seemed similar to someone saying, "Would you like to meet my knife collection?"
Ryan Johnston on Elaine's sig
|
|
|
|
|
Because Password is a Reserved Word...
When you make a query with reserverd words, you need to use those words into brakets...
Regards
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
The ListBox class' Item collection stores items of type object. I have stored a struct in there, at a certain point, i need to change the information in the struct, but when i do this [shown below] the compiler gives the error
The Left hand side of an assignment must be a variable, property or indexer.
((EBulletListItem)this.Items[index]).Checked = true;
 Email: theeclypse@hotmail.com URL: http://www.onyeyiri.co.uk
"All programmers are playwrights and all computers are lousy actors."
|
|
|
|
|
Dont use a struct, use a class! struct by Value, class by Ref. use structs to pass groups of values and use a class to store them 
Also, something looks fishy, what are u trying to do? I think I see. Try doing a cast first, then set the property...
MYrc : A .NET IRC client with C# Plugin Capabilities. See
http://sourceforge.net/projects/myrc for more info. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
