p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 3.5 Basics (http://p2p.wrox.com/forumdisplay.php?f=351)
-   -   Filling SQL database table from a txt file - How do I do it? (http://p2p.wrox.com/showthread.php?t=73267)

RushNP774 March 12th, 2009 01:51 AM

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!

gbianchi March 12th, 2009 08:07 AM

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

Lee Dumond March 12th, 2009 11:10 AM

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.

RushNP774 March 12th, 2009 02:54 PM

Quote:

Originally Posted by gbianchi (Post 236851)
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 (Post 236873)
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: http://p2p.wrox.com/asp-net-3-5-basi...tml#post236871. 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 :)

Lee Dumond March 12th, 2009 03:10 PM

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... [;)]

RushNP774 March 12th, 2009 03:23 PM

Quote:

Originally Posted by Lee Dumond (Post 236897)
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 (Post 236897)
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 (Post 236897)
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 [:D].
  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...

Lee Dumond March 12th, 2009 03:39 PM

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

RushNP774 March 12th, 2009 03:42 PM

Quote:

Originally Posted by Lee Dumond (Post 236902)
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.

gbianchi March 12th, 2009 03:47 PM

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).

Lee Dumond March 12th, 2009 03:54 PM

Quote:

Originally Posted by RushNP774 (Post 236903)
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?


All times are GMT -4. The time now is 08:32 AM.

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