Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database
Making new rows in Access Database Making new rows in Access Database
Making new rows in Access Database
Go Back  Xtreme Visual Basic Talk > > > Making new rows in Access Database


Reply
 
Thread Tools Display Modes
  #21  
Old 06-04-2004, 02:56 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default


Quote:
Originally Posted by MKoslof
I typically name my DataTables according to the table they represent in my underlying database. So if am working with a database table called "MyTable", I will name the appropriate .Net DataTable in the same regard. You should also look into .Tablemappings. This allows you to specify the relations between the DataSet Datatables and the underlying database table.

i would do the same but the person that created the db left a space in the table name. I have tyried to do this in the Dataset table name but I get an error. So I need to know to which table 'myTable' refers.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #22  
Old 06-04-2004, 03:05 PM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

As I said before, look into the .Tablemappings method..this will allow you to map a DataSet DataTable to an underlying database table and its properties. This is the best method.

Also are you sure you can't use spaces in your table name? Have you tried brackets? []
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #23  
Old 06-07-2004, 08:14 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
As I said before, look into the .Tablemappings method..this will allow you to map a DataSet DataTable to an underlying database table and its properties. This is the best method.

Also are you sure you can't use spaces in your table name? Have you tried brackets? []

How does the .tablemappings method work? Where do I use it, what is the syntax?

Are you sure this is the easiest method for entering info into the Db? It seems a lot more difficult then pulling info out.

Like I said, I get everything working but the final
Quote:
olap.Update(ds, "tblContacts")
line. And this gives me the unspecified error I quoted before.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #24  
Old 06-07-2004, 08:23 AM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

post your complete code piece please.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #25  
Old 06-07-2004, 09:02 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
post your complete code piece please.
Code:
Private Sub UpdateData() Dim dr As System.Data.DataRow Dim command As New OleDbCommand Dim updateCon = New OleDbConnection Dim olap As New OleDbDataAdapter Dim ds As System.Data.DataSet updateCon = OleDbConnection '<=== OleDbConnection is the name of my OleDBConnection updateCon.open() ds = DataSet1 '<==DataSet1 is a dataset on my form dr = ds.Tables(1).NewRow dr("Field1") = "CustomerID" '<=== "Field1" is in table, receives info ok when I watch dr("Field2") = "ContactName" dr("Field3") = "Phone" ds.Tables(1).Rows.Add(dr) 'set the insert command command.CommandType = CommandType.Text command.CommandText = "INSERT INTO [tbl Contacts] (Field1, Field2, Field3)VALUES('63','" & txtContactName.Text & "','" & txtPhoneNumber.Text & "')" command.Connection = updateCon olap.InsertCommand = command olap.Update(ds, "tblContacts") '<=== tblContacts is a table in DataSet1 End Sub
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #26  
Old 06-07-2004, 01:46 PM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

1) If you want to pass in a dataset from another form or module, pass it as a constructor byVal to the procedure.

Code:
Public sub updateMe(Byval d as dataset)

2) In your INSERT INTO statment a few things
a) is field1 numerical, if so '63' is wrong, this assumes a string
b) txtcontactName.text doesn't mean anything, you need to pass in the form reference. If it resides on the same form use Me.txtContactName.text. If it resides on another form, use a ByRef constructor to pass the value

Code:
Public Sub updateMe(ByVal d as dataset, ByRef f as MainForm) msgbox f.txtContactName.text

3) If you want to avoid tableMappings, name your datatable "tblContacts". After assigning the table in the Dataset, just rename it.

Code:
Ds.Tables(0).TableName = "tblContacts"
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #27  
Old 06-07-2004, 01:55 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
1) If you want to pass in a dataset from another form or module, pass it as a constructor byVal to the procedure.

Code:
Public sub updateMe(Byval d as dataset)

2) In your INSERT INTO statment a few things
a) is field1 numerical, if so '63' is wrong, this assumes a string
b) txtcontactName.text doesn't mean anything, you need to pass in the form reference. If it resides on the same form use Me.txtContactName.text. If it resides on another form, use a ByRef constructor to pass the value

Code:
Public Sub updateMe(ByVal d as dataset, ByRef f as MainForm) msgbox f.txtContactName.text

3) If you want to avoid tableMappings, name your datatable "tblContacts". After assigning the table in the Dataset, just rename it.

Code:
Ds.Tables(0).TableName = "tblContacts"

1) It's not comming from another form (i could have just as easily pu this code in the bitton click event, bit because I cut and pasted it I left it as was and called it instead)

2)took out the ' and ' around the 63, still got error. AND refered to all my controls using me.txtWhatever.text, still got error.

3) added the line ds.tables(1).tablename = "tblContacts" and still got error.

I know there is something very basic going wrong here.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #28  
Old 06-07-2004, 02:22 PM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, show me the whole thing. Show me the code in pieces

1) where do you instantiate the first dataset..post that code piece
2) show me all your import statements and oledb definitions
3) show me where you fill the dataset with the first table and try to define the tablename.

Basically break into the procedures you use..show me the code step by step. I agree you are missing something small. I just can't tell from here or via the code I see in broken up, small pieces.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #29  
Old 06-07-2004, 02:52 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
OK, show me the whole thing. Show me the code in pieces

1) where do you instantiate the first dataset..post that code piece
2) show me all your import statements and oledb definitions
3) show me where you fill the dataset with the first table and try to define the tablename.

Basically break into the procedures you use..show me the code step by step. I agree you are missing something small. I just can't tell from here or via the code I see in broken up, small pieces.

Before we go to far down this road.....did you say first dataset as in ther should be more than one dataset?

as for the rest
Code:
Imports System.data.OleDb Imports System.Data.Common

and as far as filling the dataset I have only 'filled' it with the code I have been posting (ie. the sql) untill that is filled the table is empty.
(maybe my mistake?)
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #30  
Old 06-07-2004, 02:54 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Hold your reply iam going to try a few thinks here
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #31  
Old 06-07-2004, 03:12 PM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

No, if you have one Dataset that's fine. But, if you want to pass it to another module or class, etc..use it as a constructor and pass it byVal.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #32  
Old 06-07-2004, 03:49 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Question Primary Key

I think I get how to do everything, except this. How do you update a certain primary key. You have to have a different primary key for every recordset, so how do i put the text 'my update' in with a primary key that is +1 of the last primary key?
Reply With Quote
  #33  
Old 06-07-2004, 03:57 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Default key

Also, do i even need a primary key for somethin like this? if it automatically reads all of the recordsets in a field into a listbox, is there a way that i can just add a new recordset and not worry about it? This isnt for the one ive been doin, i need to make a small database application to store simple stuff so i can keep track of some jobs i gotta do.

Last edited by rot13; 06-07-2004 at 04:00 PM. Reason: lack of explination
Reply With Quote
  #34  
Old 06-07-2004, 05:58 PM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

It is always good practice to have a primary key on your database table. A primary key provides a unique identifier, something that will enforce data integrity for the table (no duplicates, valid data, etc.), and the ability to index that particular field. Indexing makes searching your table much quicker.

In terms of updating your Primary Key if an autoNumber (appears that is what you are using), if you are inserting a new record into your table, this will update automatically..that is the point of an autonumber.

To update an existing Autonumber you need to do an ALTER Table sql statement. This would be an executeNonQuery event that would run from the command object. But, really, I never advise to do this. There is no reason to alter an existing autonumber. It will always be unique, and always in chronological order. So what does it matter if the number are 2, 3, 4, 5 or 2,3,5,9...either way they serve their purpose.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #35  
Old 06-08-2004, 09:59 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by rot13
Also, do i even need a primary key for somethin like this? if it automatically reads all of the recordsets in a field into a listbox, is there a way that i can just add a new recordset and not worry about it? This isnt for the one ive been doin, i need to make a small database application to store simple stuff so i can keep track of some jobs i gotta do.

Care to give us a peek at your final code?
S-P-O-O-N F-E-E-D M-E

ha ha ha

Seriously though, I am curious as to what you did to fix the problem (I am still stuck)


Do i need to fill the dataset first before I try my insert statement?

Actually, try to explain what has to happen, all code aside.

1) User enters info on form
2) User hits submit button
3) Text from Comboboxes and text boxes are sent to a table in the dataset
4) ???
5) ???
__________________
Win Win Situation: Swapping my salary with my golf score

Last edited by VisuallyImpared; 06-08-2004 at 10:52 AM.
Reply With Quote
  #36  
Old 06-09-2004, 10:31 AM
MKoslof's Avatar
MKoslofMaking new rows in Access Database MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Do i need to fill the dataset first before I try my insert statement?

Not neccesarily. Where are you stuck. What part of the code is not working?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database Making new rows in Access Database
Making new rows in Access Database
Making new rows in Access Database
 
Making new rows in Access Database
Making new rows in Access Database
 
-->