|
Hi,
I am trying to learn ADO.net using C# and I keep running into problems trying to insert data into my Access Database: data.mdb.
here is my code:
<br />
private string connectionStr = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""C:\data.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";<br />
private string selectStr = "Select id, date, day, time, comments, timeout, status, process from data";<br />
<br />
private System.Data.DataSet myDataSet;<br />
private System.Data.OleDb.OleDbConnection myConnection;<br />
private System.Data.OleDb.OleDbDataAdapter myDataAdapter;<br />
private System.Data.OleDb.OleDbCommandBuilder myCmdBuilder;<br />
<br />
private System.Data.OleDb.OleDbCommand mySelectCmd;<br />
<br />
public Schedule()<br />
{ <br />
this.InitilizeDb();<br />
this.PopulateDataSet();<br />
}<br />
<br />
private OleDbConnection ConnectDb()<br />
{<br />
return new OleDbConnection(connectionStr);<br />
}<br />
<br />
public void InitilizeDb()<br />
{<br />
myDataAdapter = new OleDbDataAdapter();<br />
myCmdBuilder = new OleDbCommandBuilder(myDataAdapter);<br />
myDataSet = new DataSet();<br />
}<br />
<br />
private void PopulateDataSet()<br />
{<br />
myConnection = (OleDbConnection) this.ConnectDb();<br />
<br />
mySelectCmd = new OleDbCommand(selectStr, myConnection);<br />
<br />
mySelectCmd.CommandType = CommandType.Text;<br />
<br />
try<br />
{<br />
myConnection.Open();<br />
<br />
myDataAdapter.SelectCommand = mySelectCmd;<br />
<br />
myDataAdapter.Fill(myDataSet,"data");<br />
}<br />
finally<br />
{<br />
myConnection.Close();<br />
}<br />
}<br />
<br />
public string InsertSchedule(string date, string day, string time, string process,<br />
string comments, string timeout, string status)<br />
{<br />
myConnection = (OleDbConnection) this.ConnectDb();<br />
<br />
try<br />
{<br />
myConnection.Open();<br />
<br />
DataRow newRow = myDataSet.Tables["data"].NewRow();<br />
<br />
newRow["id"] = 3;<br />
newRow["date"] = date;<br />
newRow["day"] = day;<br />
newRow["time"] = time;<br />
newRow["process"] = process;<br />
newRow["comments"] = comments;<br />
newRow["timeout"] = timeout;<br />
newRow["status"] = status;<br />
<br />
myDataSet.Tables["data"].Rows.Add(newRow);<br />
<br />
myDataAdapter.Update(myDataSet, "data");<br />
<br />
return "true";<br />
}<br />
catch(Exception e)<br />
{<br />
return e.Message + "\n\n" + e.StackTrace + "\n\n" + e.InnerException;<br />
}<br />
finally<br />
{<br />
myConnection.Close();<br />
}<br />
}<br />
the problem is when I try to insert into the database, I keep getting this Exception:
Syntax error in INSERT INTO statement.<br />
<br />
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)<br />
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)<br />
at ShutdownMgr.Schedule.InsertSchedule(String date, String day, String time, String process, String comments, String timeout, String status) in c:\documents and settings\azel\my documents\visual studio projects\shutdownmgr\schedule.cs:line 99<br />
I have tried different ways but none were successful. Is anyone able to spot where I am going wrong?
thanks.
|
|
|
|
|
Because you haven't specified a INSERT SqlCommand . The SqlDataAdapter isn't magical - you need to provide SELECT, INSERT, UPDATE, and DELETE SqlCommand s for full functionality. Specifying just a SELECT SqlCommand doesn't generate the others. If you're unsure about how to do this, read about the SqlCommand and SqlDataAdapter classes in the SDK documentation, or use drop a SqlDataAdapter onto your form or control in VS.NET and run through the wizard. Be sure to look at what source it generates though to fully understand what is going on.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Oic,
I thought the commandbuilder class is suppose to auto generate the insert, update and delete commands?
so even if i have set the commandbuilder to my data adapter, I must still initilize my insert, update and delete commands?
I will also take a look at the SDK documentation..
thanks.
|
|
|
|
|
I missed that line, sorry. The SqlCommandBuilder may have trouble with your SELECT statement. Another possibility is with the table mappings of the SqlDataAdapter . You're setting your table name as "data". Does that actually map to the name of the table from which you're selecting records? These should match and you might have to set up a table mapping using the SqlDataAdapter.TableMappings property. This fairly straight-forward.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
the dataset table name is "data" which is the same as the one from the source table name.
i got the idea from MSDN that table mapping is use when your data source and dataset names are different? So if I have used the same name do I still need tablemapping?
thanks
|
|
|
|
|
Yes, unless you use a strongly-typed DataSet . In most cases, the SELECT SqlCommand (with one or multiple result sets) loads tables by default named "Table", "Table1", "Table2", ..., "TableN". Your TableMappings should map these table names to your actual tables in your database.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
humm.. still wondering y i got the exception thrown when i try to perform an update to the database.
just wondering if you are able to point me or share with me some sample that has insert, delete and update without the use of stored procedure?
|
|
|
|
|
I did actually. Drag a SqlDataAdapter to your form or control and go through the wizard, specifying that new commands should be created instead of using new or existing stored procs. Take a look at what's generated in your source file.
You can also read the SDK documentation for SqlDataAdapter , which does have examples in various class and method documentation.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
If you do have to browse to find the assemblies and all you find are xml files of the managed assemblies then go here for the latest DirectX 9 managed download: http://msdn.microsoft.com/library/default.asp?url=/downloads/list/directx.asp[^]
Just follow the instructs and viola you should have the assemblies. You'll know right away if you've got them installed correctly because when you fire up VS.Net you'll see the green directx logo along with your other languages on the splash screen.
Have fun!
Best,
Jerry
The only way of discovering the limits of the possible is to venture a little past them into the impossible.--Arthur C. Clark Toasty0.com
|
|
|
|
|
Wrong thread
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Oh damn! Sorry!
The only way of discovering the limits of the possible is to venture a little past them into the impossible.--Arthur C. Clark Toasty0.com
|
|
|
|
|
Hi,
I want to ask a question about how to return messages from a general class.
How should I warn the users.
When something happened that must not be done what the object must do?
The senario is, there are several classes. Each of them has specific functionality.
It seems to me that the best way is defining a class that has properties like MessageID and and the Message. User can compare the MessageID with the const integers on the class to understand what happens and takes action.
Is there a general approach?
Kind Regards
|
|
|
|
|
From what I have seen everybody has a different opinion on this one.
I say clean up and throw a descriptive exception, but there will undoubtedly be many who will say that is a bad idea and recommend output params or returning strings etc.
But my vote is on exceptions
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
|
|
|
|
|
Paul Watson wrote:
But my vote is on exceptions
Ditto. Exceptions are the best way to notify the class consumer of an error.
When I can talk about 64 bit processors and attract girls with my computer not my car, I'll come out of the closet. Until that time...I'm like "What's the ENTER key?"
-Hockey on being a geek
|
|
|
|
|
I cast my vote for exceptions, too. A callee can pass far more data about any errors that occur to its caller in a polymorphic way that would be difficult - if not stupid - to do with out params.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
|
FxCop rocks! Did you know it's included in the Longhorn SDK download?
When I can talk about 64 bit processors and attract girls with my computer not my car, I'll come out of the closet. Until that time...I'm like "What's the ENTER key?"
-Hockey on being a geek
|
|
|
|
|
As we know, In The Dialog Window of MSN Messenger We Can Change the BackColor.
How can i do this in C#.Thanks a Lot!
|
|
|
|
|
Type "this." and search through the intellisense. You *should* be able to find it.
|
|
|
|
|
Does someone know how to view a System.Windows.Forms.Panel in a fullscreen mode?
Thanks in advance for your help ,
Thomas
|
|
|
|
|
You can't display a Panel without a parent window, but you can display a Form with that Panel in fullscreen mode (it's just how window managers work).
If you want the form maximized, just set its WindowState property to FormWindowState.Maximized . If you want it to be really full-screened (like a game normally would be), it's similar: set FormBorderStyle to FormBorderStyle.None , and then set WindowState to FormWindowState.Maximized . The trick is in what the BorderStyle property does under the cover, since it's really just a wrapper for a dialog in Win32. See the Windows Styles for more information if you're interested.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Hi,
I'm creating a windows form that has master-detail data on it.
I've implemented my own classes that take care of the bussiness logic and retreiving/writing data from/to database.
When I change the master record I call a method from the detail class to retrieve all the detail rows from the database and put this into dataset.tabel and then I return the default dataview to the method of the form. Then in the method I change the datasource of the datagird to the new dataview. This is very slow (I have to wait for 1 second for data to come up on the datagrid.
Please help.
<br />
private void FillDetail()<br />
{<br />
dvDetail1=refdata.RetrieveData();<br />
dgRefData.SetDataBinding(dvDetail1, "");
cm_d1 = (CurrencyManager)BindingContext[dvDetail1];<br />
cm_d1.PositionChanged+=g2td1;<br />
Grid2Text_D1(new object(),new System.EventArgs());<br />
}<br />
Dejan Mitev
|
|
|
|
|
First, you should just grab all the data you need into the DataSet , including the master and detail tables. Unless this is an extremely huge DataSet , often-accessed data is better to pull down in one shot.
Then, take a look at a previous thread I posted a solution to in this forum: http://www.codeproject.com/script/comments/forums.asp?msg=665954&forumid=1649&XtraIDs=1649&searchkw=DataGrid&sd=10%2F1%2F2003&ed=11%2F25%2F2003#xx665954xx[^]
Basically, if you bind your master DataGrid.DataSource property to the DataSet and specify the appropriate table in the DataGrid.DataMember property, and then set the detail's DataGrid.DataSource to be the relationship between the two tables (implying that you create a strongly-typed DataSet and form a named relationship between the two DataTable s, which is really easy using the DataSet designer), clicking on a row in the master DataGrid will automatically filter the detail DataGrid with the related child rows. This way, no fetching is required.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Thank you for your quick answer,
I know that the way you are showing in the reply is the preffered way, but I don't trust microsoft in the realisation on the data bindings and I'm just not comfortable with the datagrid control, except for viewing readonly data.
Any way this is a realy huge database (e.g. master table has about 16,000,000 and the detail is about 31 milion record) and I can't put this in a dataset.
So I must stick with this way of retrieving data.
Do you know any particular reason why is setting the datasource of a datagrid so slow. I'm thinking of creating my own control that will display the data (substitute for datagrid).
Dejan Mitev
|
|
|
|
|
The DataGrid does work and we use it a lot in our commercial application. Mostly likely, you're having such problem because the DataGrid loads all records instead of creating a virtual buffer in which to load records as they become visible. You might be better off with a third-part control. There are many out there, just google for them.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|