Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Go Back  Xtreme Visual Basic Talk > > > Accessing SubdataSheets in Access through VB .NET


Reply
 
Thread Tools Display Modes
  #1  
Old 06-10-2004, 03:14 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Exclamation Accessing SubdataSheets in Access through VB .NET


I am working on a database to hold different material properties In the database there is one table called materials which holds all these properties. I want to create two subdatasheets with each material (each material is on a sepereate rs) to hold Crack Growth Data and S-N Curve data. I need to access these subdatasheets in my program and I need to edit and delete fields. Any suggestions on accessing these sheets? Im also having problems even making two subdatasheets. Im new to VB .NET and Access so bear with me please I'm accessing the database by using ADODB connections. But Ive also read alot about OLedb connections and dataadapters. Can both of these be used together? Cause thats kinda how Im doing it right now. I'm also having problems deleting just one rs. When I set up my SQL statement it deletes the whole table. This is the code Im using
'Delete from Database
Dim Ans As Integer
Ans = MsgBox("Are you sure you want to delete this material?", vbYesNo + vbExclamation, "Delete Material")
If Ans = vbYes Then
MsgBox("Deleting...")
cn = New ADODB.Connection
cn.Open("Provider=MSDASQL.1;Persist Security Info=False;Data Source=Materials", , , )
rs2 = New ADODB.Recordset
rs2.Open("delete FROM material WHERE matno = " & matno, cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
rs2.Close()
cn.Close()
Else
MsgBox("That was a close one...")
End If

The matno is the primary key and when the user clicks on a certain row of the datagrid, the primary number is stored. I know the matno is correct because Ive done some error checking, and it gets the correct number. But it for some reason deletes the entire table.
Any help would be great, thanks.

Last edited by Stacey244; 06-10-2004 at 03:24 PM.
Reply With Quote
  #2  
Old 06-11-2004, 07:28 AM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

As a first step, I would browse to our .Net Knowledge Base and review the list of websites and books we provide as resources. Yes, I think it would benefit you to learn some .Net data access strategies and start working with DataAdapters and command objects.

In terms of your Delete command, this is not a returnable query, this is a "non query". So it should be run by the connection or command object. You should not be using a rs.Open method for this. Instead, say cn your ADODB connection

Code:
cn.Execute "DELETE FROM Material WHERE matno" = & matno

There are no record locking or cursor locations to specify.

And you are correct almost ALL database engines take the DELETE FROM method. However, while Access does understand this method....you can also use DELETE * FROM WHERE...

Note DELETE * is something Access understands...other databases do it the right way ( ) with DELETE FROM
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 06-11-2004, 08:14 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Actually I got the delete part to work using that code now, which is strange. And I have been doing all my SQL statements using rs.Open. Am I going to run into problems in the future if they are working now?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #4  
Old 06-11-2004, 11:23 AM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

I wouldn't recommend this. You are assigning a table altering command to a recordset object, and in turn, assigning un-needed cursorlocation and lock information. Use a command or connection object to do this, via the .execute method.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 06-14-2004, 12:24 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Quote:
Originally Posted by MKoslof
I wouldn't recommend this. You are assigning a table altering command to a recordset object, and in turn, assigning un-needed cursorlocation and lock information. Use a command or connection object to do this, via the .execute method.
OK if I use the .execute command, how can I fill a datagrid with that information? Before I was using OLEDB with:

Code:
oledbadapter1.fill(dsmenu1, rs, "material")

rs returned the correct recordsets with the rs.open command. If I use the .execute, how can I encorporate that into the datagrid?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #6  
Old 06-15-2004, 07:55 AM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

No I was speaking of non-query commands . For a SELECT * command you should be using the rs.Open method and simply pass it a string query.

Again we are talking ADO COM strategies, NOT .Net data access techniques:

Code:
rs.Open "SELECT * FROM ThisTable", cn,ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic

Any DELETE, INSERT, UPDATE, ALTER command should be run from the command object.

Code:
cn.execute "DELETE......"

So in terms of populating your datagrid, you want to use a Select statement and the recordset object
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 06-15-2004, 08:37 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok, I get it now, awesome, thanks. One more quick question. Back to subdatasheets. I read some of the items listed in the forum here but Im still confused. Im having problems creating the sub datasheets first off, but once I figure that out, how are the SQL statements going to access the sub data sheets? Also, is it possible to select an entire field of a table and not just recordsets, and then also add and delete fields of one of those sub datasheets from within a .NET program? Thanks.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...

Last edited by Stacey244; 06-15-2004 at 08:50 AM.
Reply With Quote
  #8  
Old 06-15-2004, 09:46 AM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

Not sure I follow the question but, you can select only one field from a table via SQL. Something like:

Code:
Dim sSelect as string sSelect = "SELECT Field1 FROM myTable"

This returns all values present in Field1.

Now deleting records doesn't work that way . A delete command removes RECORDS not fields. You can not delete a specific field from a record without removing the entire record. You can update an existing field in a record.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 06-15-2004, 12:08 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ah, ok, that saves me a lot of time Thanks. If I'm searching through a table and I want to retrieve the field where the first record is equal to a number from a drop down bar, could I say something like:

Code:
rs.open("Select * from dadn where rs.fields(0).value = '" & ComboBox1.SelectedItem & "'", cn, , ,)

or not?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #10  
Old 06-15-2004, 03:00 PM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

Place the select command in its own string variable the ADODB connection object goes as the second parameter, after the first comma.

And rs.Fields(0).value, should only be the name of the field, don't try to access the field object, since the recordset object is not open.

Code:
Dim sSelect as string sSelect = "Select * from dadn where field1 = '" & ComboBox1.SelectedItem & "'" rs.Open sSelect, cn 'etc
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 06-16-2004, 08:38 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

OK thanks, Ill try that
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #12  
Old 06-16-2004, 09:19 AM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

Good luck.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #13  
Old 06-16-2004, 01:32 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

I have pretty much everything working, thanks so much for the help. One last question (lets hope so at least ) I am having data sent to a datagrid. The way the table is set up is that the first record of each field contains a certain "R" value, and then the next 25 items in the field are the "dk" values. I want to be able to show R= " " on the header of the column in my datagrid and then have the pertaining 25 dK values underneath. Right now, heres how the columns are being set up:

Code:
column1.MappingName = "R2" column1.HeaderText = "dK2" column1.Width = 60 If rs.Fields(2).Value = "NaN" Then Else tableStyle.GridColumnStyles.Add(column1) End If

and so on for all the columns. I tried having

Code:
column1.headertext = "dk2 = " rs.fields(2).Value
but that doesnt work. any ideas how I can have it say that value, and then skip over the first record in the column and just display the next 25 underneath??
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #14  
Old 06-16-2004, 01:35 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok I figured out how to fix the header, but I stilkl don't want to display the first record in the datagrid. How can I skip it?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...

Last edited by Stacey244; 06-16-2004 at 01:47 PM.
Reply With Quote
  #15  
Old 06-16-2004, 04:15 PM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

When you populate the Grid from the recordset can't you specify to only include records that include "dk" or whatever string comparison you need. Basically if it is not within this string requirement, don't bring it into the dataGrid.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #16  
Old 06-17-2004, 09:51 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok ill see what I can figure out, thanks
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #17  
Old 06-17-2004, 10:01 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

If you are searching thorugh a database and have no matches, how can you check. Ive tried using rs.EOF or rs.BOF and they dont seem to be working. Any other ideas?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #18  
Old 06-17-2004, 10:26 AM
ac3d912 ac3d912 is offline
Newcomer
 
Join Date: Jun 2004
Posts: 3
Smile Datagrid

One thing I found on skipping fields in a datagrid is to set-up some properties at Design time... (This may only work if your using OleDB, I've not got much experience with the ADO)

Select your datagrid and select the TableStyles...
Add a TableStyle then Select the GridColumnStyle...
Add a DataGridTextBoxColumn for each column you want to see and set the MappingName to the particular field, note you can also set the HeaderText...

It's worked well for my needs, maybe you can adapt it to yours.
Reply With Quote
  #19  
Old 06-17-2004, 12:00 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ya thats what Ive done:

Code:
column1.MappingName = "R2" column1.HeaderText = "R2 = " & rs.Fields(2).Value column1.Width = 75 If rs.Fields(2).Value = "NaN" Then Else tableStyle.GridColumnStyles.Add(column1) End If

But the first record shows R2, so I dont want it to show up underneath the column as well. Can I do that? So that it will look like

R2 = 60
-------
10
15
20
.
.
.
and so on instead of

R2 = 60
-------
60
10
15
20
.
.
.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #20  
Old 06-17-2004, 01:25 PM
MKoslof's Avatar
MKoslofAccessing SubdataSheets in Access through VB .NET MKoslof is offline
Cum Grano Salis

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

You are not going to be able to do this using your current method. A DataGrid is a bound control, you have sent a datasource to the Grid. If the field value "60" is within the dataset, you can't simply "not show" the first record. And, how do you know this will always be the first record..there is no set routine to verify this for you.
__________________
"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
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
 
Accessing SubdataSheets in Access through VB .NET
Accessing SubdataSheets in Access through VB .NET
 
-->