Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 12th, 2009, 02:51 AM
Authorized User
 
Join Date: Mar 2009
Posts: 23
Thanks: 2
Thanked 0 Times in 0 Posts
Question Filling SQL database table from a txt file - How do I do it?

I accidentally overwrote all the data in a column of one of my tables with a bad SQL command, and now want to rebuild the table with data from a text file. The table is just an Id field (auto generated primary key), a field for "Name", and one for "Category". At runtime, I'd simply like to take each value in my text file, and insert it into the "Name" field of my table.

I've read a bunch of tutorials on using the System.IO.StreamReader object to read files, but can never get anything to work. The project can't find my file, or I get other errors that I can't decode even after I copy the code verbatim. Here are a couple I looked at:

http://aspnet.4guysfromrolla.com/articles/051802-1.aspx
http://msdn.microsoft.com/en-us/libr...eamreader.aspx

It's one small text file with a value on each line, and I just want to create a table record for each. Any help would be GREATLY appreciated!
  #2 (permalink)  
Old March 12th, 2009, 09:07 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hello. Probably you can't find the file because IIS doesn't have permitions to reach it??

Anyway, if this is one time process, just use internal SQL to upload the file to any table and them update the correct table. That will be a lot faster and will avoid any problems...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
  #3 (permalink)  
Old March 12th, 2009, 12:10 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Not hard to do. But, a couple of things you've said are somewhat confusing.

First, you say you want to do this "at runtime." This makes no sense given what you've said. To do it at runtime would mean you want to do it on the fly while the program is running. Why would you need to do that if this is only a one-time thing to replace some missing values?

Second, the existing IDs and categories might not match what you had before unless the values in the text file are in the exact order as the existing values.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
The Following User Says Thank You to Lee Dumond For This Useful Post:
DavidM (April 6th, 2009)
  #4 (permalink)  
Old March 12th, 2009, 03:54 PM
Authorized User
 
Join Date: Mar 2009
Posts: 23
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by gbianchi View Post
Hello. Probably you can't find the file because IIS doesn't have permitions to reach it??

Anyway, if this is one time process, just use internal SQL to upload the file to any table and them update the correct table. That will be a lot faster and will avoid any problems...
You may be right about that, but if that's the case, I have no idea how to correct the permissions. Also, I'm only using Visual Web Developer Express 2008, so I don't think I have the full IIS 7.0 installed on my computer (unless it's part of the install).

And yes, it will be a one-time process, but I don't know what you mean by, "just use internal SQL to upload the file to any table and them update the correct table". I'm only on chapter 14 of Beginning ASP.NET 3.5 and he hasn't talked about stuff like this.

Quote:
Originally Posted by Lee Dumond View Post
First, you say you want to do this "at runtime." This makes no sense given what you've said. To do it at runtime would mean you want to do it on the fly while the program is running. Why would you need to do that if this is only a one-time thing to replace some missing values?

Second, the existing IDs and categories might not match what you had before unless the values in the text file are in the exact order as the existing values.
Thanks for the reply, and sorry that I confused you. Let me try to answer your questions:
  1. I suppose you're right. Doing it at runtime would be a dumb idea. I just want to fix them one time, as I already have almost all the original "Name" values loaded into my text file. Thanks for clearing that up.
  2. As far as the ID's and Categories, it's not a big deal at all. This was my SQL table I've ever created, and it's more just to learn this stuff. The specific ID's don't matter at all at this point because I haven't referenced the records in my code at all yet.
  3. About the Categories, that's not a biggie either, because they didn't have any data in them (I screwed up the Name column before I could get anything entered :D). I actually have another table with category names & ID's that I'll use as choices (from a dropdown box) for my original table. I'm actually having trouble with that too, and have posted about it here: Using a dropdown box in a GridView - How do I update the record with the dd's value?. That's another issue I'm having a tough time with as well.
I hope that made a bit more sense, and I again appreciate your help. This has already proved more fruitful than the ASP.NET forums :)
  #5 (permalink)  
Old March 12th, 2009, 04:10 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

So, let's see if I have it straight. You just want to take a table you have already set up, with no records in it, and populate the Name column of that table with a list of names from a text file (one name per line)?

As long as the file is INSIDE the root site folder, I can't see how IIS permissions would have anything to do with this, as long as you're only reading the file.

Just read the file one line at a time, then do an INSERT after each line read.

Or, you could always type the names in by hand. That's probably what I would do...
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #6 (permalink)  
Old March 12th, 2009, 04:23 PM
Authorized User
 
Join Date: Mar 2009
Posts: 23
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Lee Dumond View Post
So, let's see if I have it straight. You just want to take a table you have already set up, with no records in it, and populate the Name column of that table with a list of names from a text file (one name per line)?
You got it! I had over 200 records in it (with just the auto generated ID, and the Name that I typed, but nothing in Category), but have since cleared out the table out of frustration

Quote:
Originally Posted by Lee Dumond View Post
Just read the file one line at a time, then do an INSERT after each line read.
That sounds incredibly simple, but I haven't the slightest idea where to start, nor where to enter the code (in the SQL statement generator?). As I mentioned, I've read about the StreamReader object, but can't figure out from the tutorials how to get it working. Is that even what I should use? If so, I'll try to read more, but I feel like I'm at a dead end at the moment.

Quote:
Originally Posted by Lee Dumond View Post
Or, you could always type the names in by hand. That's probably what I would do...
I agree with you 100% that it would have been MUCH easier looking back at it. Shoot, I could have had them all typed in 20 times by now. Even so, I figured I'd turn this into a learning experience:
  1. Backup your SQL databases often in case you mess up a statement .
  2. Learn how to read from a text file
  3. Learn how to insert records into a DB via code
Guess I'm just stubborn, but I'm really trying to learn this stuff from the ground up, and am willing to beat myself up learning it well. Plus, this 200 record table could have been a client's 20,000 record table, and I'd rather make my mistakes now instead of then. Ah, the joys of being a newbie...
  #7 (permalink)  
Old March 12th, 2009, 04:39 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

I am pretty busy right now, but let me see if I can whip up a quickie example in the next couple of days, or maybe Gonzalo could jump back in at this point and give it a go for ya...
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
The Following User Says Thank You to Lee Dumond For This Useful Post:
RushNP774 (March 12th, 2009)
  #8 (permalink)  
Old March 12th, 2009, 04:42 PM
Authorized User
 
Join Date: Mar 2009
Posts: 23
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Lee Dumond View Post
I am pretty busy right now, but let me see if I can whip up a quickie example in the next couple of days, or maybe Gonzalo could jump back in at this point and give it a go for ya...
You're a godsend. I look forward to seeing it. I'll definitely make a blog post about it if I can get it to work so it can help others out too.
  #9 (permalink)  
Old March 12th, 2009, 04:47 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

even quicker way to solve it.. load the file in excel, copy all the cells, open the table in VS express, paste the cells :)

anyway, you want an example on how to open a file and do an insert? it can be done, but usually in web you won't do anything like that (or at least you shouldn't do it).
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
  #10 (permalink)  
Old March 12th, 2009, 04:54 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Quote:
Originally Posted by RushNP774 View Post
You're a godsend. I look forward to seeing it. I'll definitely make a blog post about it if I can get it to work so it can help others out too.
Curious, what is your blog address?
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
 


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
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
How to format txt file exported from access table bbqlee Access VBA 1 August 4th, 2006 03:16 PM
txt file generated from SQl BCP nidhigarg9 SQL Server 2000 1 June 16th, 2006 03:16 PM
Import a Comma separated txt file to SQL table arielote C# 2 April 17th, 2006 02:08 AM
Bulk Insert to Sql Server from .txt file avats BOOK: Beginning VB.NET Databases 1 June 3rd, 2005 05:44 AM



All times are GMT -4. The time now is 08:44 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.