|
Wow! that was a long reply, thank you very much!! 
I had already solved my problem with what you suggested in your last post about denormalizing my table. I developed a simple SP that would update the customer record with the ID_address of the latest address after all addresses had been inserted/updated. The only problem was that I had to call that SP at the end of every customer save operation.
I've copied and tested your query now, and after adding some WHERE clauses, I think it works beautifully. So I've just updated my search stored procedure, and removed the ID_lastaddress field from my Customers table. All I can say is THANKS!
That thing about unique search column, I don't think it'll be a problem. Even if somehow the customer could move twice in the same day, the address is stored in a datetime, and using DateTime objects in C# makes the column contain a date and a time, so they'll always be different.
So, I won't be needing triggers for this particular use anymore, but it sure will be useful to know about them for a later need. I've never had any formal SQL training (not even read a book). I only recently learned about stored procedures, and mainly design all my queries (JOINs and all that) in Access. But know I look back to some other projects I developed earlier and I see how much code I could have replaced with a little stored procedure. I guess it happens to everyone, once you learn a new thing you see a thousand places where that could have been useful in the past.
Well, I better keep on working that I still have plenty of things to do today (and it's Saturday noon for me.) Again, thank you very much for your help!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I'm tring to add a row at my database .mdb but i get the follow error: "Sintax error in the INSERT INTO instruction" why? what do i wrong?
this is the schema of my database:
Nome: text 15
Domanda: text 20
Risposta: text 15
Email: text 20
this is the code:
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= myDatabase.mdb;";
try
{
conn.Open();
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM myTable", conn);
OleDbCommandBuilder myCmd = new OleDbCommandBuilder(myAdapter);
DataSet myDataSet = new DataSet("myTable");
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myAdapter.Fill(myDataSet, "myTable");
DataRow myRow = myDataSet.Tables["myTable"].NewRow();
myRow["Nome"] = mNuovoUtente.mName;
myRow["Domanda"] = mNuovoUtente.mQuestion;
myRow["Risposta"] = mNuovoUtente.mAnswer;
myRow["Email"] = mNuovoUtente.mEmail;
myDataSet.Tables["TabellaUtente"].Rows.Add(myRow);
myAdapter.Update(myDataSet, "myTable");
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
|
|
|
|
|
What is the syntax for your myAdapter.UpdateCommand and myAdapter.InsertCommand?
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
Sorry i don't understand what you mean, please can you explain me?
Thanks.
|
|
|
|
|
In order for you to be able to update or insert from your dataadapter, you need to specify an insert SQL statement in the OleDbDataAdapter.InsertCommand, and an update SQL statement in the OleDbDataAdapter.UpdateCommand. If you used the wizard to create your adapter, you probably already have these statements.
In debug, or by writing out to a label on the screen, you should output both of these properties and see if their SQL Syntax is correct. This will allow you see the sql commands you are using with the database.
These strings are probably also listed in the WinForms or WebForms generated code if you are using Visual Studio .NET
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
I'm a newbie can you tell me how run the wizard to create the adapter?
|
|
|
|
|
If you drag a data adapter to a form, then it will open a wizard for you.
No worries you can do this the manual way.
<br />
OleDbCommand cmdUpdate = new OleDbCommand(SQLCOMMAND);<br />
OleDbCommand cmdInsert = new OleDbCommand(SQLCOMMAND);<br />
<br />
myAdapter.UpdateCommand = cmdUpdate;<br />
myAdapter.InsertCommand = cmdInsert;<br />
<br />
And that's how it's done.
For a more detailed explanation you can look at the .NET sdk documentation or this example from csharpcorner.
http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=103[^]
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
Hi, tojamismis.
Thanks for the answer...
Another user has wrote me this:
the first problem is that your table doesn't show a primary key. Without it, .Update() won't work. Second, you, apparently, don't have an Update command associated with the DataAdapter. Third, if the Primary Key information is missing, you set the .MissingSchemaAction, but never called .FillSchema before you called .Fill
In your opinion what other things have i miss?
|
|
|
|
|
Can add you directly the missing rows of code, i'm not success. 
|
|
|
|
|
I don't know the structure of your tables so I can't write any of the sql statements.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
It is compose just by a table "myTable" with:
Name: text 20 primarykey
Surname: text 20
Image: OLEOBJ Byte[]
|
|
|
|
|
I can't think of anything else. It is true that you need these additional things if you aren't using a strongly typed dataset.
But that should be it.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
That is correct, without strongly typed datasets you should add this line right before you call myAdapter.Fill.
<br />
myAdapter.FillSchema(ds, SchemaType.Source, "MyTable");<br />
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
How come when I want to reset a date field to NULL my Enterprise manger locks up and I have to kill it?
This is after I have right clicked on a table and choose return all rows. Then I click on the date/time field and delete what was in there. Is there another way to do this besides running a SQL statement?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
I've never seen it do that before. Is it a big table? Does it have the relevant indexes on it?
Database FAQ
|
|
|
|
|
small table. with only three records. We use it as a heartbeat table so that other apps know that my app is still working.
Davey wrote:
Does it have the relevant indexes on it?
Yeah.
It's strange. I might see if there is a SP for it.
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Have you tried Running a Query from enterprise manager on the table? You can get only the row or rows you need and then update from the results pane in the Query builder.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
yeah. If I do an update on that field, passing NULLS, it works fine. I just cannot click in that field, delete what's in there and click out of it. if I do it freezes up.
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Pressing Delete in Enterprise Manager is not the same as resetting to NULL. Delete will update the field with '' (Empty string)
If you want to update the field to NULL in Enterprise Manager press Ctrl-0
Wout Louwers
|
|
|
|
|
If I create and XP, and I want to connect to my
XP. How do I go about doing that? Will i do an execute from my app?
Right now I do all my selects and updates using a connection pointer like this:
m_pConnection->Execute(_bstr_t(mySelect),NULL, NULL);
Will I also connect to my XP this way? I'm making an XP that will alert me to a trigger in a table that I have set. So that any new record placed in there will alert me so that I do not have to loop every 3 seconds to see if there's new data.
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
As far as TSQL is concerned, an XP is the same as a normal SP. If you can execute SPs using this method (I don't see why you can't), then you should be able to execute XPs also.
Database FAQ
|
|
|
|
|
cool I'll try it. Just gotta write my XP and find out how to return a value. I'm going to assume that, that is the same as SP's too?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
|
Hi everybody, I'm building my very first database application.
I have an Access database and I work with ADO with C# + Jet 4.0. I have 3 tables in my database.
For now, my dataAdapter is constructed like this, to work with only one table:
myDataAdapter = new OleDbDataAdapter("Select * From Table1", myOleDbConnection);
But now, I want my DataAdapter to work with my 3 tables... what should be my connection string?
"Select * From Table1, Table2, Table3" ??? Probably not...
Thanks!!!
|
|
|
|
|
Your connection string won't change. A connection string is per database, not per table.
In order to return info from more than one table, you will need to use a JOIN.
Here's a SQL Query I am using for a product of mine - also MS Access:
SELECT Staff.FirstName, Staff.LastName, ClassSchedule.ClassName, ClassSchedule.Duration, Staff.ID
FROM (StaffClassSignIns INNER JOIN Staff ON StaffClassSignIns.StaffID = Staff.ID) INNER JOIN ClassSchedule ON StaffClassSignIns.ClassScheduleID = ClassSchedule.ID
WHERE (((StaffClassSignIns.ClassScheduleID)=[@ClassSchedule]));
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
