|
Hi,
First I would like to complement you on your exellant to tutorials and articles, I'm new at database programing and they are a great resource!! However, I do have a small problem, I'm attempting to use your datacontainer and dbio to make a web based program that will enter marketing info into a database. However, It wouldn't work, so I steped throught the code and found the error was my sql statement. However, the statement looks fine to me and I was hoping you could share some insite.
Thanks,
John
Code:
private void Summit_Click(object sender, System.EventArgs e)
{
RegisterUser(TextBox2.Text,TextBox1.Text,TextBox3.Text,TextBox4.Text,TextBox5.Text);
Response.Redirect("Thanks.aspx");
}
private void RegisterUser(string Name, string Where, string Os,string Occ,string Air)
{
DataContainer dc=new DataContainer(); dc.Set("Name", Name);
dc.Set("Where", Where);
dc.Set("Os", Os); dc.Set("Occ",Occ);
dc.Set("Air",Air);
DBIO dbio=new DBIO(dc); dbio.Execute("sql_SurveyAddEntry");
}
SQL Statement:
<add key="sql_SurveyAddEntry" value="insert into SURVEY (AIR,NAME,OCC,OS,WHERE) values ('{Air}','{Name}','{Occ}','{Os}','{Where}')">
|
|
|
|
|
I must say, seeing my name at the top of the "question time" section REALLY got my attention. Anyways, the SQL statement got chopped off in your message post. Why don't you send me an email directly to:
webmaster@knowledgeautomation.com
Thanks!
Marc
|
|
|
|
|
Not sure if I should reply, since i'm not Marc Clifton.
But,
insert into SURVEY (AIR,NAME,OCC,OS,WHERE) values ('{Air}','{Name}','{Occ}','{Os}','{Where}')
NAME and WHERE are reserved keyword in most SQL implementations. You should avoid using them for column names.
However,
insert into SURVEY ([AIR],[NAME],[OCC],[OS],[WHERE]) values ('{Air}','{Name}','{Occ}','{Os}','{Where}')
ought to do the trick.
Signature space for rent. Apply by email to....
|
|
|
|
|
Thanks for the reply, I tried and I still had no luck any other ideas??
|
|
|
|
|
Can Anyone tell me how to call Stored Procedures in DAO
Samir Sood
|
|
|
|
|
|
I am writing SQL statements for MS Access 2000 and SQL server 7.0. In MS Access 2000, Dates have to be enclosed with #, like WHERE (datefield=#10/3/2002#). But SQL Server doesn't like that. I have to use WHERE (datefield=10/3/2002). Is there any way to write the queries so they work in both programs?
BTW. I am using ADO, but not ADO.NET.
|
|
|
|
|
I don't think there is a solution. There are other conversion problems also, having to do with Access using VB functions like UCASE, which in true SQL is TOUPPER (at least, I don't think SQL server handles UCASE, but even if it does, I believe my point is true non-the-less).
My recommendation is to either implement a pre-parser that converts from Access syntax to SQL server syntax or maintain two dictionaries of SQL statements.
Maybe someone else can shed some light on this.
Marc
|
|
|
|
|
The following works with ODBC, not sure about ADO.
select * from table where datefield={d 'yyyy-mm-dd'}
Dave.
|
|
|
|
|
Thanks for the tip. That works in SQL server but not Access. Access thinks {} is a GUID.
|
|
|
|
|
Hi,
I have two tables t1 and t2 and they look like this:
t1:
t1_id | t1_title | t1_content |...
t2:
t2_id | t2_language | t2_t1_id |...
where t2_t1_id is id from the first table. Now if I want to select elements from both tables Im using sometnihg like this:
SELECT * FROM t1, t2 WHERE t1_id = t2_t1_id AND [condition]
and i have nice results  ... but my question is how can I delete rows
with only one query  ...
so I do not want to use this:
DELETE FROM t1 WHERE [condition]
DELETE FROM t2 WHERE [condition]
but I would like to do something like:
DELETE FROM t1, t2 WHERE t1_id = t2_t1_id AND [condition]
is this possible and how?... please post some examples or links
|
|
|
|
|
You need to set up a foreign key with cascading deletes. t1_id is the primary key, t2_t1_id is a foriegn key referencing table t1, field t1_id. In the foreign key you can tell it that whenever you delete rows in t1, to "cascade" those deletes to any rows in t2 that reference the deleted rows in t1. Then, all you do is "delete from t1 where...".
Hope that helps! BTW, this is why it's nice to use an abstract key like an ID, instead of a meaningful field, say, a part number. Assume your primary key was a part number. If the user wants to change the part number, you'd have to go through a lot of consternation when the foreign key association breaks. This is why there's now something called "cascade updates" too! (us old timers, ha, ha, ha) didn't have that capability in older databases.
Marc
|
|
|
|
|
How can I setup runtime ADO with minimal files / registration.
I would like to distribute the runtime files with my application. What files are needed ?
Thanks
|
|
|
|
|
I'm fairly sure M$ license agreement for ado means you need to redistibute the whole lot, unchanged, in the installer from their site.
Signature space for rent. Apply by email to....
|
|
|
|
|
Working with Access 2000 Database and ADO(Visual C++)
My Database tables and their relationships resemble a Tree or a Family tree. (ie a lot of one-to-many relations between father/child tables)
The task is to keep the size of the database within certain limits. As the database grows beyond certain limit, i would like to keep the latest N records and delete the old stuff. Unfortunately, this doesnt seem to reduce the size of the database (I don;t know why) may be because I am using "Autonumber" fields.
The second step would be to reindex my autonumber fields so that I don't run over the limit of long integer datatype.
Please help with some tips on how to approach.
Thanks
|
|
|
|
|
You have to use "Compact and Repair Database" from the Tools->Database Utilities menu to shrink a database after records are deleted. This can also be done programmatically.
|
|
|
|
|
How to do it programatically ?
Any solution to "Autonumber" field running out of limits ?
Thanks
|
|
|
|
|
How to do it programatically ?
Microsoft Knowledge Base Article - Q230501
Any solution to "Autonumber" field running out of limits ?
Autonumber is a long integer, which will give slightly more than 2 billion numbers. If you will have more than 2 billion records, you can either use a compound key or create an unpopulated table when you reach 2 billion and start fresh.
|
|
|
|
|
I would suggest that access doesn't scale well to two billion records.
Signature space for rent. Apply by email to....
|
|
|
|
|
Jon Hulatt wrote:
I would suggest that access doesn't scale well to two billion records
Or even a couple of thousand in most cases
Paul
|
|
|
|
|
Granted, Access doesn't scale well to 2 billion, but he intends to archive excess records and only keep N (hopefully a fairly small number) records online. I've had success with Access up to about 200K records. More than that typically causes it to blow up.
|
|
|
|
|
I am Using ADO(Visual C++) and Access 2000 database.
Trying to limit the number of records returned by the recordset with PutMaxRecords(x), and the recordset returns all records.
Thanks for your help in advance.
|
|
|
|
|
This will not work on an Access database, see here [^]. If you are still want to do something like that you could change your SELECT statement to look something like this:
<code>
"SELECT TOP 10 * FROM [myTable] WHERE user_id = 1"
</code>
Hope This Helps
Nick Parker
The goal of Computer Science is to build something that will last at least until we've finished building it. - Unknown
|
|
|
|
|
|
Hi, i'm writing an app which will have its own DB that it will add information to
from searches from various databases (so they have a local copy of search results etc)
Now, which database option should i use, bearing in mind that its a stand alone app and the user could be anywhere ?
I'm told there is a free sybase db, i could use the jet engine?
erm what do people recommend?
Bryce
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
