Can ADO.Net query/update a MS Access database that contains table and fields that contain blank spaces and characters such as "#" in their names?????

I've recently been assigned an asp.net/MS Access project. My code works fine; however, there are table and fields in the Access database that contain blank spaces and characters such as "#" in their names.  

For example there is a table named "Purchase Orders" and in this table there are a few column fields with names such as "PO#" and “Customer PO#".
My question: Is there some sort of wrapping method that I need to implement to allow my ADO.net objects (particularly the "OleDbDataAdapter" and the "DataRow") to accept these naming convention without altering the underlying database. Or is altering the database necessary?
Here's the piece of code that I've written to better illustrate the problem.
I'VE ALSO INDICATED THE LINE ON WHICH MY ERRORS OCCURS.
---------------------------------------------------------------
'lets add a new row ro the table
Dim strConnection As String
Dim strSQL As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter
Dim objBuilder As OleDbCommandBuilder
Dim objTable As DataTable
Dim objNewRow As DataRow
Dim record_count As Integer
Dim PO_Count As Integer
Dim New_PO As Integer
Dim siteName As String
siteName = "Status"
'set the connection and query details
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\pgi-
cms\PurchaseOrders\MDBs2000\USA_Purchase_Order_Reqs_be.mdb"
strSQL = "SELECT record, PO, CUSTOMER, Status FROM Purchase Orders;"

'Open the connection and set the command
objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(strSQL, objConnection)!!!!!!!ERROR OCCURS HERE!!!!!!!!!!!!!!!!!!!!!!
'fill the dataset and dataTable
objAdapter.Fill(objDataSet, "Records")
objTable = objDataSet.Tables("Records")
'''''''''''''''''''''''''''''''Add the new row'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
objNewRow = objTable.NewRow()
objNewRow("CUSTOMER") = txtCustomerName.Text()
objNewRow("PO#") = txtPONum.Text !!!!!!!ERROR OCCURS HERE!!!!!!!!!!!!!!!!!!!!!!
objTable.Rows.Add(objNewRow)
'''''''''''''''''''''''''''''''Generate the update command''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
objBuilder = New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()

'''''''''''''''''''''''''''''''update the datastore''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
objAdapter.Update(objDataSet, "Records")
0
akieonb
11/29/2004 3:04:43 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

5 Replies
1060 Views

Similar Articles

[PageSpeed] 10

use [,]'s to enclose the column names..

SELECT record, [PO#], [CUSTOMER PO#], Status FROM Purchase Orders

hth
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
11/29/2004 4:34:09 PM
my recommendation is to change the database to a better form - remove all '#' characters from the field names and remove all spaces from the table and field names, while guarding against using Reserved words for any of the above.
David Wier
MCP/ASPInsider
ASPNet101.com - where to look first!
Control Grouper - easily control properties for multiple controls with one control!
Calendar Express - The Best HTML Calendar Generator on the web!
(Please 'Mark as Answer' when it applies)
0
augustwind
11/29/2004 8:18:47 PM
ndinakar,

I appreciate your reply. I took that approch at one point and it worked fine for building my sql string statement and passing them into the Adapter object along with the connection obj. However when I tried to pass say, "[PO#]" into the DataRow obj, I receive an error that tells me that the column name cannot be found. This makes perfect sense considering the DataRow is looking to accept string value representing the name of the column. So if 'objNewRow("[PO#]")' and 'objNewRow("PO#")' both throw errors is there a different approach I should take. Any additional advice of direction would be appreciated. I really want to avoid altering the database considering an existing VB application uses it as well.
Please respond.
0
akieonb
11/29/2004 10:39:08 PM
augustwind,

Thank you for your response. I agree with you that the database should have been formed better. I don't think that its inital design was intended to support online access. However is there some .net method which can allow me to perfom the desired task without changing the underlying data source?
Any input it welcomed. Thanks in advanced.
0
akieonb
11/29/2004 10:43:57 PM
hey i just tried a sample application and it works without the [,]'s when accessing through dataadapter. 

hth
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
11/30/2004 12:43:06 AM
Reply:

Similar Artilces:

How can I access < input type="hidden" name="objHidden" id="objHidden" > from a .net server side function
Topic says it all..please help!! Right Click the control in the design view and select run as server control. ...

Mail resending duplicates in the "from" field the (Given Name Last Name) if name contains a "-"
Hello, An other incredible behavior with client GW8.01 and GW8.01HP if you resend one of your mail and if you given name or last name contains a "-" in eDir, the from field of your email is populated with a second occurrence of (givenname lastname); if you resend this again, then you get it 2 times (givenname lastname)(givenname lastname)etc... so an example: Suppose you are Snow White, with a frenchy touch, eDir Given Name: Blanche-Neige eDir Last Name: Disney and your GW address book is updated with it in first name/last name/name according eDir, Then if...

Using ((DataRowView)Container.DataItem)["field"] vs DataBinder.Eval(Container.DataItem, "field")
I have a table with 1000 records and am displaying 15 columns databinding to a repeater control. It's large enough (with a page load time of 8 seconds) that I'd like to try and optimize further. I read that using ((DataRowView)Container.DataItem)["field"] is more efficient (20%) than DataBinder.Eval(Container.DataItem, "field"). However, I've not been able to figure out how to make it work.My error message is: CS0246: The type or namespace name 'DataRowView' could not be found (are you missing a using directive or an assembly reference?)page.aspx.cs=================using System;using Syste...

Is there a "Starts With" or "Contains" I can use in my SQL query ??
Hi,   I have a piece of code written where I am trying to pull out certian fields based on them containing a certain letter eg C or S ???? Is there a simple piece of code I can use for this. At the moment I am using "AND sl.Name IN ('CDL') " & _.......but this will not return the rows that contain CDL ?  Any Ideas much APPRECIATED ?  You would use the LIKE keyword for that:http://msdn.microsoft.com/en-us/library/ms179859.aspxLike "AD sl.Name LIKE '%CDL%'"That will return anything where that name has CDL in it. Cheers, &nb...

Can siteA.com access an "Access Database" on siteB.com?
Practically, can siteA.com access (for reading and writting purposes) a "Microsoft Access Database (.mdb)" on siteB.com? Knowing that, both siteA.com and siteB.com have the same administrator and the developer knows everything about them (db path, username, password, etc...). I know I could do that locally on my machine; putting the db file in a particular path and both projects/sites (localhost/siteA and localhost/siteB) could read and write to it. But I am asking, could I do that practically? I mean when I do hosting for both sites, could I do that? If so, is there any rules/conditions t...

SQL: Column names, can ONLY get "name" instead of "table.name"
Hey all, When iterating through column names returned from a query, I need to get "table.name" rather than just "name" Check it out: If I have an SQL statement "SELECT table1.ID, table2.ID FROM table1, table2;" And table1 has a column name 'ID' and table2 has a column name 'ID' aswell, and i run the query, the dataset will contain column names 'ID' and 'ID' instead of 'table1.ID' and 'table2.ID' It is extremely important that I can differenciate between the 2 fields, and know where each of them come from. The same thing happen...

The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table.
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below: EXEC sp_addlinkedserver @server = 'test1', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'testsource' exec sp_addlinkedsrvlogin @rmtsrvname = 'test1', @useself = 'false', @rmtuser='sp', @rmtpassword='sp'   When I execute select * from test1...COUNTRY I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table." The ...

From "unrestricted access" to "Collection Owner Access"...
... and back. I ran the RBS wizard after upgrading to 2.7 and now my user only have Collection Owner Access. How do I make my user a Unrestricted user again? Stalle -- Staffan ------------------------------------------------------------------------ -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Just as a note they are essentially the same thing. For some other info see the following TID numbers: 10090014, 10095953, 3739679. The documentation also covers how to go from RBS to Unrestricted mode as well as I recall. Good luck. Staffan wrote: | .. an...

"Access database" and "Login error"
Hey guys,I'm trying to get an Access database to be the location of the username/password combinations. I've done it is as far as I can tell, but I'm now stuck with the following error.These are some of things I have doneSet up the database in ODBC under the name "OdbcServices"Put the database in the "App_data" folder in the website directoryThanks for any help that you guys can give about the error.The Naked Programmer------------------------------------------ Configuration Error Description: An error occurred during the proc...

Inserting data to the database using ADO.NET "Update" method...slow!
Hi, I am using Odbc ADO.NET code to insert data to a database. I add around 30,000 reocrds to a dataset, and then using a dataadapter if I call the "Update" method to save it takes ages - about 40 minutes!! Is this normal, surely it shouldn't take so long? Admittidly the oracle db server is slow, but not that slow ;) I'm pointing at an Oracle 9i database. The reason I use Odbc rather than the specific Oracle objects is that I also need to be able to point at other databases. I tried changing to Oracle specific objects but it made little difference to performance. My insertCommand looks li...

Trouble updating a gridview to a MS Access database "No value given for one or more required parameters. "
asp.net with c# and ms Access.  Upon clicking the update button i get the error No value given for one or more required parameters. As far as i know all values are given. I am not entirely sure about my datasource update query, as it was cut n pasted of the net and i am not 100% understanding what it does. I am assuming the @Title variable is what i have input into the text box under the Title heading. I havent specified this, but from what i read it seems that this is automatic. Maybe this is my problem? Another potential area could be the WHERE clause. Filename is readonly so isn...

Error found "Access to the path "C:\Program Files\Microsoft Visual Studio .NET\Crystal Reports\Viewers" is denied. "
Hi guys! i'm trying to solve the error "Err Msg: "Object reference not set to an instance of an object" in ASP.NET app" but when i followed the instructions in the http://community.crystaldecisions.com/library/kbase/articles/c2011144.asp link, this error appeared ""Access to the path "C:\Program Files\Microsoft Visual Studio .NET\Crystal Reports\Viewers" is denied. " What's going on? i already went to the iis to add new virtual directory (CrystalReportWebFormViewer) but different error appeared when i compile and browse my application. pls help ...

System.Web.Mail does not contain definition for &quot;Fields&quot; VS.NET 2002 Compile error
**************************************************************** Code tags added by moderator. Please use &lt;code&gt; and &lt;\code&gt; tags when posting code. Thanks! **************************************************************** Hi all. Sorry if this is an easy fix, but I am a newbie and I've spent about 6 hours trying to figure this out over 2 days: I have the following code: ********************* namespace Double.Pages { using System; using System.Web.Mail; using System.Collections; using System.ComponentModel; using System.Da...

connecting to a database "MS Access"
hi guys, I have a project in MS Access database, I need to access my database using VB.NET, my questing is, how can i connect to my database and show the result to a datagrid? Thanks in advance, sandman16. Look here for connection string examples. In ASP.NET QuickStart Tutorial (section Data Binding Server Controls) you will find examples of using datagrid.Leon LangleybenMCSD, ASP.NET MVPBlog Check out ASPNet101.com - there are many MS Access examples - once you get past displaying records, you'll most likely need to edit/update, etc.... http://www.aspnet101.com/aspnet101/a...

Web resources about - Can ADO.Net query/update a MS Access database that contains table and fields that contain blank spaces and characters such as "#" in their names????? - asp.net.access-datasource

Resources last updated: 1/10/2016 1:39:52 AM