.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

importing table with Access "memo" field to SQL

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :ASP.Net
I imported an access database using the DTS wizard, into SQL 2005. There is a field in one of the Access tables that is a memo field, filled with a bunch of text including linebreaks. After importing, the field is indicated as the nvarchar(MAX) datatype when I check in SQL Enterprise Manager. The text in that field is displayed without line breaks, both in EM and when viewed on a web page. I changed the datatype of that column to "text" and now can see line breaks when I hover over a value in that column while viewing the table data in Visual Studio 2010's Server Explorer.But when I display the data in a ListView, there are no linebreaks. When I view my TableAdapter in my dataset, there are no linebreaks either.I must be missing something here? Thanks. 

View Complete Post

More Related Resource Links

trouble getting dates right when importing Access table

I was given a bunch of data in Access and am putting it in SQL 2005 for an ASP.NET web app. Tryingto do a query to select only records where the current date is after the "postDate" field and on or before the "expireDate" field. WHERE (postDate <= CURRENT_TIMESTAMP) AND (expireDate >= CURRENT_TIMESTAMP) I'm getting no records returned even though there are some records that qualify. Tried using CURDATE() and NOW(), no difference. At this point I think it may be because the dates are not in the correct format. The columns were originally "Date/Time" format in Access and when I imported them into SQL using the DTSWizard, the datatype for those columns became "smalldatetime." Dates are in the format M/DD/YYYY HH:MM:SS. I'm guessing the query won't work because SQL is expecting YYYY-MM-DD. How do I fix this? Been spending a depressing amount of time running in circles with this.

I am updating a currency field of an access table through detailsview, cannot input decimals.


I changed a text field to a template field assigning the selected text and the selected value is being assigned to another currency field also templated. I get aa error

[FormatException: Input string was not in a correct format.]

Could you please give me a few tips on this one. Thanks

Data Points: Deny Table Access to the Entity Framework Without Causing a Mutiny


Julie Lerman shows database administrators how to limit access to databases from the Entity Framework by allowing it to work only with views and stored procedures instead of tables-without impacting application code or alienating developers.

Julie Lerman

MSDN Magazine August 2010

Under the Table: How Data Access Code Affects Database Performance


In this article, the author delves into some commonly used ways of writing data access code and looks at the effect they can have on performance.

Bob Beauchemin

MSDN Magazine August 2009

Why my BDC is not importing new profiles from DB table?

I have 72000 profiles records in SSP profiles database. I connected this BDC with SQL table. SQL table has 81000 records. When I do full Import it is importing only 72000 records only, not complete 81000 records. How to get them into SSP?

How to access the refrenced table fields in Ilist object of table MVC asp.net



I have 2 tables, master, detail. 1.master table have fields (id, username, plan)-->id is primary key (PK) 2. detail table have fields (srNo,id, worksummary, ... )--> srNo is PK.

I have created foreign key relationship from detail to .master table for "id" field.

the code is:

IList<detail> objDetail=new List<detail> (); 
IList<master> objMaster = new List<master> ();
string[] sarray = queryFields.Split('|');//

for (int i = 0; i < sarray.Length; i++)
string[] sfields = sarray[i].Split(',');

if (sfields[0] != "")

objDetail.Add(new _detail { Id = count , modify = sfields[1].ToString(), verified = sfields[2], I});


I have problem to add fields in "objDetail" using Add method. But I am unable to access the reference field "Id" , rest of the field of detail table can be accessed using objDetail.

How can I access the "Id" field from objDetail object to add in IList.

I want to solve this problem

InfoPath and Access - two parent tables and one child table


Hi there,

I'm trying to link an InfoPath form to an Access database, I want to connect the InfoPath form to 3 tables in the Access database but InfoPath will only recognise parent-child relationships in a series (e.g. the "Company" table is the parent to the "Customer" table, which is again parent to the "Orders" table)

I need to have two parent tables and one child table, though (e.g. "Customer" is parent to "Orders", but "Inventory_Item" is also parent to "Orders"). Is there any way to establish this in InfoPath? I'm using Windows XP, InfoPath 2007 and Access 2007.

Cheers, Patrick

Importing Access 2010 tables to SQL Server 2008 R2

I'm trying to import a series of Access 2010 tables to Sql Server 2008 R2.  The Access import drivers are for *.mdb (which if I recall was the file extension for Access when I was a kid, and don't recognize the .accdb file extention).  Similarly, the Excel driver is for Excel 2003.  Isn't there a driver and method to import directly to SQL 2008 from Access 2010? SQL is installed on my server, but Access is not installed on the server.  When I copy the file onto the server and try and open it directly into SQL, I get a 'no editor installed' error. I can't get the 'upsize' wizard to work becuase it won't open the connection to SQL, even though I enter the userid and password of the SQL DB owner.  I get the following error: ===================================================== Connection failed: ============================================================= I have to say I'm stumped.  The rest of the Office 2010 suite works really well together - perhaps I'm missing something very simple? Thanks!     I guess I could export my tables as Excel 2003 and then import them using Integration Services, or install SQL Express on my laptop and 'upsize' to that instance, but SQL State: ‘0100’ SQL Server Error: 11004 [Microsoft][ODBC SQL Server Dirver][TCP/IP Sockets]ConnectionOpen (Connect()). Connection failed: SQL

SharePoint 2007 to Access 2007 - Issues grouping by "Person or Group" field type/column

We are on SharePoint 2007 and using Access 2007.  The SharePoint list contains a column called Assigned Person, column type: Person or Group, single selection, displaying only the name 1) When we Open with Access and Export a copy of the data, then create a report from that table, we do not have the option to group on Assigned Person.  2) When we Open with Access and Link to data on the SharePoint site, then create a report from that list, when we group by Assigned Person... The same person has 2 groups - there are is no other grouping or sorts on the list, and i can't locate a correlation to another field to show why they would have to collections for the same person The Person or Group column sorts "from smallest to largest" as though it is a number field. The list is a task/activity tracking system we created custom for our needs.  We want to pull a weekly report, grouped by Assigned Person, but cannot get access to work.  We do not want to resort to anything more complex, as it takes a much longer process to have reports pulled by Chrystal or one of those types of applications.  And we cannot change out the field for something like a Choice column as the list already has a very large amount of content.  Can anyone assist? Is there a setting hiding somewhere that is causing the problem? 

Workflow Instance ID field in MS Access

Hello, please help! When I open a SP list with MS Access (Action > Open with Access), the Workflow Instance ID field is available with no data.  How can I get this field to display so I can create queries within MS Access using joins/relationships?  Thank you!

Splitting a memo field into lines and adding a value at the start of each line

I have one memo field which has a bunch of text in it. I want to update another memo field from the first one but i want to divide it into lines of 50 characters and put a carriage return at the end of each line and a string value (Note:) at the beginning of each line But I am not sure how to go about it. The text in the first field may have line breaks and carriage returns or it may not. So is there a way i can strip out anything like that first See example below Memo Field1 The quick brown fox jumped over the lazy dog. And the Dish ran away with the spoon, the little dog laughed to see such fun and the Cow fell of the wall Memo Field2 Note: The quick brown fox jumped over the lazy dog. And Note: the Dish ran away with the spoon, the little dog l Note: aughed to see such fun and the Cow fell of the wal Note: l   ideally it wouldn't cut words in half but I think I may be asking too much.   Is this possible?

how to force null in date field - access 2007 ?

hi i have table that contain date field. how i can force null or empty value on this field ? thank's in advance

How can I get the cumulative sum of a field in a table

For Eg.   I have a table like gias given below: Name                  Amount ------------------------------ aaa                       10 bbb                      20   and I want an output like one given below on running SQL query or stored procedure Name                  Amount                  cumulative amount --------------------------------------------------------------------- aaa                       10                              10 bbb                      20                              30   can anyone plz help me on this

xml as text will not save in table field in some cases

I have a stored procedure that saves 4 fields into a data table named xml.  One of the fields is called xml, and is just a text field.  Usually, my sp properly stores the data, but occassionally the xml portion of my record is not stored.  It's on a remote server, so I'm having trouble hooking up a debugger to it, but I may have identified some charactoristics of the data in the cases where the data is not saving into the field.  I think it has a lot of "......."s mixed in with the xml.  I'm wondering if SQL could be identifying this as some kind of security threat.  Any thoughts would be super appreciated. 

Update duplicate field in same table

  hi I have a table with some other duplicate field as below   IssueID Title A1 Test1 A1 Test2 A2 Test3 A2 Test4 A3 Test5 A4 Test6   i want update all duplicate field based on IssueID and the final result will show as below   IssueID Title A1 Test1 A1 Test1 A2 Test3 A2 Test3 A3 Test5 A4 Test6 may someone help me?  

Import Access table or Excel spreadsheet into Oracle table


I am trying to import from an Excel spreadsheet or an Access database into an Oracle table.  I used the SSIS Import and Export wizard to create the SSIS packages, but everytime that I attempt to run the package, SSIS stops once it gets to the Pre-Execute phase.  There are never any errors.  Am I doing something that cannot be done?

I'm using the following:  SSIS 2005, Microsoft Oracle OLEDB provider (MSDAORA.1), Excel and Access 2003

By the way, I am able to successfully export from the Oracle database to either Excel or Access using SSIS.


Field Population Count over whole table



I have a huge table and would like to know of a way to count field population across the whole table, this can be stored in a separate table to refer back to, i am using sql 2008. A visual display could be:

TableName FieldName Blank Populated MinValue MaxValue DateCreated

Person       Address1    1500  5000        <blank>    9 any street   30-sep-2010

Person      First Name    6000  500          <blank>     Zac               30-sep-2010

has anyone done something like this before, would like something quite reusable and generic?

any help greatly appreciated.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend