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
  #1  
Old 05-30-2004, 04:19 AM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Default Making new rows in Access Database


Hi. First off let me say that I am not real good with database stuff. I just started with it a week ago, because I need to do some simple stuff for my address book application, but I found out it wasn't so simple.

I have an Access Database with 4 tables. It was made in Access XP if that makes any difference. These are the imports I did:

Imports System
Imports System.Data
Imports System.Data.OleDB

I am able to bind listboxes to certain tables, but I can't seem to get it to add a new row to a table. I used code from the vb.net resource kit, but couldnt manage to make it work. This is the code that i tried changing to fit my app:

Dim i, sno As Integer
Dim sname As String
Dim rw As DataRow
'Add a new row to the Student table.
rw = DataSet11.Tables(0).NewRow
sno = InputBox("Enter the Roll no of the Student:")
sname = InputBox("Enter the Name of the Student:")
rw.Item("SNo") = sno
rw.Item("SName") = sname
Try
DataSet11.Tables(0).Rows.Add(rw)
'Update the Student table in the testdb database.
i = OleDbDataAdapter1.Update(DataSet11)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'Displays number of rows updated.
MessageBox.Show("no of rows updated=" & i)


All i need to do is add a value to a new row of a table. I will need to do more later, but if i get that far it will atleast make the program functional.

thx
Reply With Quote
  #2  
Old 05-31-2004, 09:12 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

Are you using the OleDbDataAdapter for this? If so, what command text are you using to update the database? I tend to code all my updateCommand and select commands and fire them when needed. But, you can use the Wizard for this. You could create your own INSERT INTO command to add new rows to your underlying database table.

And, this line here should also signify the table being modified, like so:

OledbDataAdapter1.Update(DataSet11, "myTable")
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 06-01-2004, 02:03 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Default Umm........ Uhhh....... Me.Begineer = true

Quote:
Originally Posted by MKoslof
Are you using the OleDbDataAdapter for this? If so, what command text are you using to update the database? I tend to code all my updateCommand and select commands and fire them when needed. But, you can use the Wizard for this. You could create your own INSERT INTO command to add new rows to your underlying database table.

And, this line here should also signify the table being modified, like so:

OledbDataAdapter1.Update(DataSet11, "myTable")
I don't know about all the command stuff and all that. I just did exactly what the walkthrough did, but it was only for one table. I could get the walkthrough one to work, but only with that database. It just had that code to add the row, and update it, but i just get an error now. I don't understand this stuff, but I really need to learn it, and am having a really hard time doing it.
Reply With Quote
  #4  
Old 06-01-2004, 02:48 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

What error do you get and at what line?

FYI

MKoslof is your best resource. I knew next to nothing of this .NET database stuff 2 weeks ago. If you are patient, the answer can be found. MKoslof was very patient with me while I was learning, but don't expect to be spoon fed. Try a few things, change a few lines and always say where things are going wrong.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #5  
Old 06-01-2004, 03:21 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

Very nice explanation VisuallyImpared .

What is your current error?

What database are you connecting to?

Do you have the proper System.Data imports?

And do you want to add a new data row to your .Net DataTable and send the changes back to the database? If so, again, you need to use a command text object. This would be a simple insert into statement. Then your oleDbAdaptor would take this as its InsertCommand.

Code:
Private Sub update_Me(ByVal s As String, ByVal ds As DataSet) Dim dr As System.Data.DataRow Dim command As New OleDbCommand Dim updateCon = New OleDbConnection(s) Dim olap As New OleDbDataAdapter updateCon.open() command.CommandType = CommandType.Text command.CommandText = "INSERT INTO myTable (Field1) VALUES('MKOSLOF')" command.Connection = updateCon olap.InsertCommand = command olap.Update(ds, "myTable") End Sub
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #6  
Old 06-01-2004, 07:45 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Default

I have the right imports. The database is 4 tables. I tried using your code, but it didnt work the way i have it. This is the code.

Private Sub addtaskbutton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addtaskbutton.Click
Dim dr As System.Data.DataRow
Dim command As New OleDbCommand

OleDbConnection1.Open()

command.CommandType = CommandType.Text
command.CommandText = "INSERT INTO personaltask task VALUES('MKOSLOF')"

command.Connection = OleDbConnection1

OleDbDataAdapter2.InsertCommand = command

OleDbDataAdapter2.Update(DataSet21, "personaltask")
OleDbConnection1.Close()

personalList.Update()
End Sub

personallist is the listbox that is bound to the database. Obviously oledbconnection1 is the connection i wanted to use, and DataSet21 is the dataset for OleDbDataAdapter2.

PersonalTask is the table im updating, and Task is the field i am updating.

I don't get any errors at all, but it doesnt update the database.
Reply With Quote
  #7  
Old 06-02-2004, 07: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

1) Run your code in a try/catch error handler
2) Your INSERT INTO statement is wrong, you need to bracket your fields () and values () for proper syntax
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #8  
Old 06-02-2004, 09:16 AM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Default

like this?

command.CommandText = "INSERT INTO personaltask Fields(task) VALUES('MKOSLOF')"
Reply With Quote
  #9  
Old 06-02-2004, 09: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

Yes, that should work..see below..this works fine:

Code:
command.CommandType = CommandType.Text command.CommandText = "INSERT INTO myTable (Field1, Field2, Field3)VALUES('New Value', 'Changed things', 50000)" 'the established OLEDB connection command.Connection = updateCon 'have the DataAdapter fire the insert command olap.InsertCommand = command 'update the appropriate table in the DataSet olap.Update(ds, "myTable")
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #10  
Old 06-02-2004, 11:06 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Question Grr! Still having problems.

I don't know why, but this isn't working. I know your not suppose to give code handouts, but would it be possible for someone to write some simple code that will update a db with four tables by and entrance in a textbox? Please. I dont get errors! I have it in a try/catch but still on errors(and yes, it is suppose to msgbox the error)

I dont understand!!!!
Reply With Quote
  #11  
Old 06-03-2004, 07:18 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

I think you really need to read a tutorial on databases in general AND .Net.

1) INSERT INTO and UPDATE are totally different sql commands
2) Update four tables? Do these table have a relationship, are you using a JOIN type to bring in data from all four fields? Are you trying to insert records into all four tables at the same time?
3) The code I provided should, I use something comparable to this all the time. If it does not work, you probably have not set up your dataAdapter correctly or you are not updating the proper table. If you get no error, the code is fine, but your structure is probably wrong.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #12  
Old 06-03-2004, 01:36 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Ok I think I can see a few problems with the code here...

dr id dimmed but never used
ds is used but never dimmed ( if you change ds sot it reads dr, you get an error saying it cannot convert to datarow)

What is the relationship between the command.commandtext and the ds or dr?

every time i run this code it fails at command.commandtext telling me " Object reference not set to an instance of an object."


I hope this will shed some light on what is going wrong.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #13  
Old 06-03-2004, 01:41 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

Visually I responded to your private IM regarding this.

Yes I agree in his code, he is using a lot of variable he does not need. In my code sample, the ds or dataset object needs to be passed to the sub byVal or declared at the top of the sub . That is probably why YOU are getting an object not defined error. This is only A PIECE of the complete code where I focus on the command object and the adapter.

In my reply message I showed the complete version of code..how it could look. I provide advice and insight, I don't do it all .
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #14  
Old 06-03-2004, 02:03 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
Visually I responded to your private IM regarding this.

Yes I agree in his code, he is using a lot of variable he does not need. In my code sample, the ds or dataset object needs to be passed to the sub byVal or declared at the top of the sub . That is probably why YOU are getting an object not defined error. This is only A PIECE of the complete code where I focus on the command object and the adapter.

In my reply message I showed the complete version of code..how it could look. I provide advice and insight, I don't do it all .
Trust me, I know you don't do it all I just wanted to point out a few things to look at incase any lights went on for ROt13. These are the points where I either have corrected the issue (the ds thing ) or have yet to find my problem. Just some insight from a quasinoobi
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #15  
Old 06-03-2004, 03:10 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Question reply

First, the database has four tables that have no relationship. They are for 4 different listboxes and forms.

Let me make sure I have the basics right.

I have only one oledbconnection.
I have 4 oledbdataadapters that i generated with the wizard and with 'Select [all fields here seperated by commas] from [tablename]
then i have 4 datasets that i generated by right clicking the dataadapters and clicking on 'generate dataset'

I have tried other ways, but this is the way i was shown to do it in the vb.net resource kit walkthrough. Is this all right?

thx
Reply With Quote
  #16  
Old 06-03-2004, 07:43 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

Yes, you can use the wizard to set up your select, insert and update statements. What is the default insert and update command you generated? With the wizard you can define these commands, so that you don't have to code it yourself.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #17  
Old 06-03-2004, 08:12 PM
rot13 rot13 is offline
Regular
 
Join Date: Apr 2004
Location: Johnson City, Tennessee
Posts: 63
Default

well, i erased all that stuff to start over, but when the wizard got to that part, i just typed the select from stuff manually. I have seen a way to use the query builder thing, but that hasnt worked in the past. Do i need to use it to do all that other stuff with the commands, or do i just type the commands in the box when i type in the select from part?
Reply With Quote
  #18  
Old 06-04-2004, 06:58 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

I suggest reading a few .Net tutorials.

If you want a nice tutorial regarding the DataApadter wizards, you can browse here:

http://www.informit.com/articles/art...=26956&redir=1
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #19  
Old 06-04-2004, 11:44 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

OK, i now have the code running up to this point :

Code:
olap.Update(ds, "myTable")
then I get an error:
Quote:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

is myTable a table in the DB or is it a table in the dataset or ???
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #20  
Old 06-04-2004, 03:40 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

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.
__________________
"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
 
-->