ok this is insane...

I have been looking for an answer to what shouldn't be too hard of a question to answer, yet nobody can answer it. I have found literally hundreds of people asking the same question with 0 replies every single time.

The question? how do you save data from a datagridview to an SQL database?

is it even possible? seems stupid that it wouldn't be possible, but since nobody knows how, I'm starting to wonder.

I would REALLY like to make a program that can access and save data to an SQL database using VB 2005 express edition and sql express server.. is it not possible?

I have watched several hour long videos on the subject. I have read tons of documention. I have searched endlessly, all to no avail. nothing works.

how would someone programically save data to an SQL database in VB 2005? what commands would there be? is there somewhere on the internet where there is an example that acutally works? is there some unknown setting that needs to be set to enable the writing to the database? I get no errors with the code and it returns a number indicating the number of lines written to the database, which is suppose to mean it was successful, but I close out the program, reopen, and it wasn't saved.

This is beginning to be VERY frustrating as I have a company who wants me to write a simple database program for them, and it would be easy if only I could somehow save data to it. (I will also need to figure out how to access the database remotely, another thing I see 0 info on the net about, but that is later on)

Thanks for taking the time to read this, and please, if you know anything that might help, please respond.

No. A datagridVIEW is just that, a VIEW. A view is, in some context, read only. You will need to propagate the changes yourself.

You need to utilize the datasource if your datgridview, not the actual datagrid. It would depend what your datasource was....

Quote:

---

Originally Posted by nomadowns

This is beginning to be VERY frustrating as I have a company who wants me to write a simple database program for them, and it would be easy if only I could somehow save data to it. (I will also need to figure out how to access the database remotely, another thing I see 0 info on the net about, but that is later on)

---

Well, are you paid to program at your company? If not, then it's not your job. If you are, then how did you get hired? I haven't seen 1 employer who wanted a VB.Net programmer that didn't require database connection knowledge.

Also, where are you looking for this information? Every book I've seen about .Net and VB have examples of storing data in a database and retrieving it. The MSDN and MSDN2 also go over, in great detail with examples, using the different classes to do so.

Also, look at codeproject.com for full source code projects that go over how to do this.

Search this forum too. There are many examples posted.

I also have a very simple VB.Net project I did for college. It was a simple assignment and we had to store and retrieve data from an access database. I could upload that if you really need something.

Quote:

---

Originally Posted by kasracer

Well, are you paid to program at your company? If not, then it's not your job. If you are, then how did you get hired? I haven't seen 1 employer who wanted a VB.Net programmer that didn't require database connection knowledge.

---

this might seems rude, but it's true... it's not that hard, just google vb.net+retreive+database and you'll find more than you have time to read.

for the dataviewrow, I can tell, never worked with that. good luck

Quote:

---

No. A datagridVIEW is just that, a VIEW. A view is, in some context, read only. You will need to propagate the changes yourself.

---

Then why, do tell, when I drag and drop a datagridview from the data source, does it automatically add and code a save icon onto the toolbar? Is M$ out to trick people into believing it will save? I seriously doubt this.

Quote:

---

You need to utilize the datasource if your datgridview, not the actual datagrid. It would depend what your datasource was....

---

Yes, I understand this. It is coded to use the datasource.

Quote:

---

Well, are you paid to program at your company? If not, then it's not your job. If you are, then how did you get hired? I haven't seen 1 employer who wanted a VB.Net programmer that didn't require database connection knowledge.

---

No, I'm attempting to do this for a friend. Not sure why this question was even asked.

Quote:

---

Also, where are you looking for this information? Every book I've seen about .Net and VB have examples of storing data in a database and retrieving it. The MSDN and MSDN2 also go over, in great detail with examples, using the different classes to do so.

---

I've searched those places and what I did find didn't work. Most were exactly what I have, some others were different, but also didn't save. I have read hundreds of pages of text and watched days worth of videos on the matter.

Quote:

---

Also, look at codeproject.com for full source code projects that go over how to do this.

---

I'm not sure if I've looked at anything from there or not, as I haven't really been keeping track, but I'll go check them out after I'm done here.

Quote:

---

Search this forum too. There are many examples posted.

---

I have, a lot. If there is something, I just can't find it.

Quote:

---

I also have a very simple VB.Net project I did for college. It was a simple assignment and we had to store and retrieve data from an access database. I could upload that if you really need something.

---

I'll not bother you with that, I'm sure I'll eventually find something.

Quote:

---

this might seems rude, but it's true... it's not that hard, just google vb.net+retreive+database and you'll find more than you have time to read.

for the dataviewrow, I can tell, never worked with that. good luck

---

No, it's not rude, but I have done so many google searches simular to that it's not even funny. It's really not that easy to find. That search would find information on retrieving data from a database, but I can do that already. I just can't write back to it. Also, most of the code comes back as C#. What I'm looking for is so specific it's like looking for a needle in a haystack.

Off to look some more. [5 days running]

Another day of searching.. still nothing that works. No new code snipplets found anywhere. VB6 was tons easier.. wish they would of just expanded on that. What used to take 1 line of code now takes 20. So silly.

The dataadapter has an update method that you use in order to update your database. The datagridview displays the data according to the datasource. The changes you make in the datagridview makes changes to this datasource. In order to save these changes, you have to call the .Update method of your dataadapter, using the datasource in one of the parameters of the .Update method. If this data is all under one table of data, you can set a CommandBuilder for you data adapter, in order to automatically generate the correct INSERT, UPDATE, and DELETE statements required in order to update it appropriately. If you have the commandbuilder set, then it is quite easy to update the database, with ONE call... which can be found all over the internet...

VB Code:

MyAdapter.Update(MyDataSet) 'in this case, MyDataSet is the DataSource of the DataGrid

lol... that doesn't work. That's the problem. I'm obviously not making it clear.

Let me post my code here...

Me.Validate()
Me.TestingBindingSource.EndEdit()
Me.TestingTableAdapter.Update(Me.TestingDataSet1.Testing)

That code is AUTOMATICALLY generated when I drag and drop the component from the data source like I've explained a few times.

The problem is IT DOESN'T WORK.

If I run the program, add data, click the save button where that code gets called (no errors), then go back into the database, there is nothing there.
If I add data via the database explorer from inside vb 2005, it does save.
as you can see, I've named most things "testing". I've tried other names as well.

Sorry for not making my point clear before. I hope this helps with any confusion.

This is pretty interesting. I've always either done all the work manually or used business objects, so I'm curious.

I'm trying to duplicate what you've done, but I don't get how you "drag and drop the component from the data source". Can you explain how you go about that?

Thanks,
Mike

Nevermind on the instructions, I see all that business now. Like I said, I've never used it.

Interesting thing is, it works fine for me. I set up a new DataSource, dragged a table over, saw that it put up the tool bar, entered a new record, saved it, and it persists in the database.

Wonder what's happening on your end?

Mike

Yeah me too, very strange. I have found others having the same problem here and there in differnet forums on the net as well. For some reason, it doesn't work on every computer. It's not a common problem, but I'm not alone.

Thanks for duplicating it on your end. I was fairly sure it did work for most people. Do you have IIS installed? I do not and have no idea if I'm supposed to or not. I just have VB 2005 express edition and SQL server 2005 express edition installed.

I have no idea what to do next to try to resolve this.

Yeah, I have IIS installed, not sure if that would make a difference. I've got VS2005 pro and I was running against Sql Server 2000 developer edition.

To try to see what's happening, I'd check for inserted rows to make sure things really got inserted, before the call to .Update. I don't know if Sql Server express edition has the Sql Profiler or not, but that'd be another place to look.

Mike

okay I did this:

Me.Validate()
Me.TestingtableBindingSource.EndEdit()
Dim x As Integer = me.TestingtableTableAdapter.Update(Me.TestingbaseDataSet.Testingtable)
MessageBox.Show(x)

and it pops up a number with however many rows I added
so at this point it seems to be working correctly

But I guess you still don't see any changes in your database? Did you look to see whether there's a profiler available in Sql Express?

Mike

yeah, it still doesn't save the changes.. and no, I don't see a profiler.. at least not as a compenent.. might it be somewhere else?

really stinks. my best guess is SQL server express edition is a joke. Almost like a demo, where you got to buy VB 2005 pro and the real SQL server to make it work. Another way M$ can make money.

SQL Server isn't the joke. It's the method you're using that a 'joke'. It's not even programming, it's an open invitation to all sorts of headaches, migraines and delays.

read this ADO.NET tutorial, learn to do things the right way, and you won't be facing these problems again.

You know what's funny about that, is that isn't the way I'M doing it.. that's the way MICROSOFT is doing it. Honestly, have you not read anything here? Are you really _that_ stupid or just trolling?

That code is automatically placed when you drop the control from the data sources. I've only said that a ton of times... but apparently reading isn't one of your strong points. However, looking like a douche is.

So seriously.. anyone have any real ideas as to what might be going on?

When you added the datasource to the project it asked you if you wanted to copy the mdf file to the output. If you answered yes to that, then it will copy the original to the output every time you compile, and your new data will, to no one's surprise, be gone.
I just tried that, and I had the same problem as you.

If this is the case, then there's no joke, as you so nicely put it. It's just doing what YOU told it to do. Change your Copy To Output property of the mdf file.

Btw. calling people stupid is generally a very good way of not getting any help here.
We understand your frustration, but namecalling is definitely not the way to go.

EDIT: You should read this. it explains everything. Especially the "Where's my data" part.

Quote:

---

Originally Posted by nomadowns

You know what's funny about that, is that isn't the way I'M doing it.. that's the way MICROSOFT is doing it. Honestly, have you not read anything here? Are you really _that_ stupid or just trolling?

That code is automatically placed when you drop the control from the data sources. I've only said that a ton of times... but apparently reading isn't one of your strong points. However, looking like a douche is.

---

If you can't get this to work and you hate Microsoft so much, I'd suggest looking elsewhere. Perhaps MySQL on Fedora?

Insulting someone isn't going to get you anywhere.

Quote:

---

Originally Posted by kasracer

If you can't get this to work and you hate Microsoft so much, I'd suggest looking elsewhere. Perhaps MySQL on Fedora?

Insulting someone isn't going to get you anywhere.

---

Exactly :thumb:

I spend almost 2 whole minutes searching in MSDN forums for the answer. :D

So can anybody test this on Express? Wouldn't be the first time that a feature was left out of a VB lite edition. Since it worked for Hildner, and doesn't work for you, I would certainly be looking at the stripped down version as being a likely culprit.

i think there is something like a allowaddnew property for a datagrid if i am not mistaken.

Quote:

---

Originally Posted by Shaggy Hiker

So can anybody test this on Express? Wouldn't be the first time that a feature was left out of a VB lite edition. Since it worked for Hildner, and doesn't work for you, I would certainly be looking at the stripped down version as being a likely culprit.

---

I've allready given the solution. I'm pretty sure that this is the problem.
It displays the exact behaviour that he's describing.

Quote:

---

Originally Posted by pax

When you added the datasource to the project it asked you if you wanted to copy the mdf file to the output. If you answered yes to that, then it will copy the original to the output every time you compile, and your new data will, to no one's surprise, be gone.
I just tried that, and I had the same problem as you.

If this is the case, then there's no joke, as you so nicely put it. It's just doing what YOU told it to do. Change your Copy To Output property of the mdf file.

Btw. calling people stupid is generally a very good way of not getting any help here.
We understand your frustration, but namecalling is definitely not the way to go.

EDIT: You should read this. it explains everything. Especially the "Where's my data" part.

---

Quote:

---

Originally Posted by pax

When you added the datasource to the project it asked you if you wanted to copy the mdf file to the output. If you answered yes to that, then it will copy the original to the output every time you compile, and your new data will, to no one's surprise, be gone.
I just tried that, and I had the same problem as you.

---

It never asks me anything like that. It runs a wizard and I follow the steps exactly how the Microsoft video shows me. I also do these exact steps on another machine and it works fine. I really don't mean to be rude when I say this, especially to someone trying to help me, but I've already said this.

The only things that is asks me are:

1. Which data connection to use (there is only one option)
2. Do I want to save the connection string to the application file? (MS says to keep it checked as Yes
3. Which database objects do I want in my dataset (I select tables only - which is all I have in the database)

That is all it asks, I honestly appreciate the attempt at helping.

Quote:

---

Originally Posted by pax

If this is the case, then there's no joke, as you so nicely put it. It's just doing what YOU told it to do. Change your Copy To Output property of the mdf file.

---

Not even sure what that means.

Quote:

---

Originally Posted by pax

Btw. calling people stupid is generally a very good way of not getting any help here.
We understand your frustration, but namecalling is definitely not the way to go.

---

Well to be honest, I'm actually a nice person. I've just been around internet boards a whole lot and I know what works. I've asked this same question nicely here in another thread and got 0 replies. Yes, it's a dumb tactic to get replies (rude replies, but replies nonetheless). I've gotten more help from people telling me basicially "Look dumbface you're doing this and this is why its not working! duh!" And that is what I was hoping for.

Quote:

---

Originally Posted by kasracer

If you can't get this to work and you hate Microsoft so much, I'd suggest looking elsewhere. Perhaps MySQL on Fedora?

Insulting someone isn't going to get you anywhere.

---

I never said anything about hating Microsoft in that statment. That is implying that Micosoft wrote that code and not me, thus it should be working. If anything, it was a complement to Microsoft. And insulting someone has gotten me everywhere. It's a shame, but thats how internet forums work.

Quote:

---

Originally Posted by Shaggy Hiker

So can anybody test this on Express? Wouldn't be the first time that a feature was left out of a VB lite edition. Since it worked for Hildner, and doesn't work for you, I would certainly be looking at the stripped down version as being a likely culprit.

---

This is still my best guess as to the problem. I'm going to load these both up on another computer and try it. The other computer I have tried this on was using a different SQL server. Thanks for your response!

But honestly, mendhak's statement _was_ pretty dumb. :P

Well, I would still check the "Copy to Output" property if I were you, because I tested it WITH VB Express and WITH SQL Express and it worked.
When I added my datasource it did indeed ask me wether or not I wanted to copy the database to the output. Initially I said "Yes" and I had the exact same experience as you.
When ever I started my app, my new data was gone. This was of cource because my mdf file was being overwritten every time I compiled. Changing that property made all the difference.

So, in reply to Shaggy's post: Yes, someone has tried it with Express, and it works just fine.

Have you read the link i gave you? It explains it all in quite nicely.
http://blogs.msdn.com/smartclientdat...26/456886.aspx

Quote:

---

Well to be honest, I'm actually a nice person.

---

Could have fooled me.

Quote:

---

I've asked this same question nicely here in another thread and got 0 replies

---

Well, it looks like people were in fact trying to help you here, even before the namecalling. Perhaps you didn't get the answer you were looking for, but people tried. And you can't ask for more than that.

Mendhak was a bit harsh, but his sentiment is common. However, it might be closer to the truth.

<Fool system! Not used to this computer, managed to post before I had said much of anything>

Sorry Pax, I had managed to overlook your post.


You are following an MS video? What Pax is suggesting is very unlikely to be part of the video, simply because it sounds like a step that MS would leave out. No surprise there, I think they write those things for a fairly simplified case, and leave it up to you to expand on it.

Can you find the property that Pax refers to, and tell us what the status of that property is?

I forgive you, Shaggy :D

nomadowns: Just for the record, I have attached a screendump of the question (in question).

Please pay attention to the last 2 lines of the messagebox.
This is the kind of messagebox where most people (including myself) just clicks Yes without actually reading the message.

I have just read all of this thread with great interest because I have had similar problems to nomadowns both with getting the datagridview to work and with finding useful code samples that utilise the grid to update to a SQL Server database. I have a lot of sympathy for his frustration and think he has been treated a little harshly in this thread by some people.

The comment about a datagridview being read-only is contradicted by the fact that it has a DataError event and Validating events. It is clearly designed with updating a datasource in mind.

If you want the grid to update the datasource when a row is changed or added by a user a la most other grids designed for user data entry, you need somewhere sensible to put the dataadapter update method. Some events that look like good candidates get you into all sorts of trouble because of the order in which the built-in events take place.

I still have a problem with user defined error messages when data is updated, I have posted the details here but unfortunately had no replies. Please see my other post:

http://www.vbforums.com/showthread.php?t=387958

To all of the respondents who have the opinion that this is child’s play, I’d appreciate a solution to this problem or a better event/methodology to use to auto update rows using the datagridview. If you are testing such a methodology be sure to test the behaviour of the Escape key (undo) because this throws up another issue.

Thanks in advance for any help.

That messagebox never pops up for me, wish it had.

I went and changed the property value of testingbase.mdf's "copy to output" from "always copy" to "never copy" and now it saves the data.

I wonder why you get asked that question and I don't? Well, doesn't really matter now.

I do not understand what the property does or why it is saving now, but I'm really happy that it is.

A big thank you to all who have helped, especially Pax and Shaggy. I sincerely appologize for being a jerk to get some responses, I always feel like an @$$ after doing that.

Anyway, problem solved and thanks again!

Quote:

---

Originally Posted by nomadowns

I do not understand what the property does or why it is saving now, but I'm really happy that it is.

---

The data always did save. Having changed that property means that you won't overwrite the database with a fresh copy that doesn't contain the changes each time you compile the project, which means every time you run it in the debugger.

Quote:

---

Originally Posted by nomadowns

You know what's funny about that, is that isn't the way I'M doing it.. that's the way MICROSOFT is doing it. Honestly, have you not read anything here? Are you really _that_ stupid or just trolling?

That code is automatically placed when you drop the control from the data sources. I've only said that a ton of times... but apparently reading isn't one of your strong points. However, looking like a douche is.

So seriously.. anyone have any real ideas as to what might be going on?

---

I see, so because the code is automatically placed, it's how MS does it? :)

My statement wasn't stupid. It was an observation based upon experience. You are placing all your complaints upon one single action, from which you have obviously not ventured in another direction at any point. Did you go through the link I gave you? I am guessing not, from your posts in this thread.

The drag-and-drop method (what I call it) was created for amateur programmers, it's to get simple tasks accomplished and above all, it teaches you nothing about the true nature of ADO.NET or database operations in the .NET framework. So while you can continue with this method for all your future applications, you will end up with a persistent headache and zero knowledge.

And yes, I _have_ read a lot from 'there' and have seen how MS works.

Yes, I am a troll, I have been here for 4 years now. http://www.vbforums.com/images/ieimages/2006/01/1.gif

Quote:

---

Originally Posted by nomadowns

No, I'm attempting to do this for a friend. Not sure why this question was even asked.

---

yeah right

Quote:

---

Originally Posted by nomadowns

This is beginning to be VERY frustrating as I have a company who wants me to write a simple database program for them, and it would be easy if only I could somehow save data to it. (I will also need to figure out how to access the database remotely, another thing I see 0 info on the net about, but that is later on)

---