Whats wrong with my Add File Routine?

[Troy]

Hey guys it's me again and it tooks some time to understand everything you were feeding me. I'm still a bit fuzzy but now I'm using Parameterized Query's. I'm getting the following error when i attempt to add a new record to my DB.

An error occured. Error Number 5: Description: Syntax error in INSERT INTO Statement. Source: Microsoft Database.

Here is my code:

conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & _
                My.Settings.dbPath & "; Persist Security Info=False")
                cmd = New OleDbCommand("INSERT INTO Contact", conn)
                cmd.CommandText &= " WHERE LastName1 = ?LastName1"
                cmd.Parameters.AddWithValue("?LastName1", OleDbType.VarWChar)
                cmd.Parameters("?LastName1").Value = txtLastName1.Text
                cmd.CommandText &= " WHERE FirstName1 = ?FirstName1"
                cmd.Parameters.AddWithValue("?FirstName1", OleDbType.VarWChar)
                cmd.Parameters("?FirstName1").Value = txtFirstName1.Text
                cmd.CommandText &= " WHERE MiddleInitial1 = ?MiddleInitial1"
                cmd.Parameters.AddWithValue("?MiddleInitial1", OleDbType.VarWChar)
                cmd.Parameters("?MiddleInitial1").Value = txtMiddleInitial1.Text
                cmd.CommandText &= " WHERE LastName2 = ?LastName2"
                cmd.Parameters.AddWithValue("?LastName2", OleDbType.VarWChar)
                cmd.Parameters("?LastName2").Value = txtLastName2.Text
                cmd.CommandText &= " WHERE FirstName2 = ?FirstName2"
                cmd.Parameters.AddWithValue("?FirstName2", OleDbType.VarWChar)
                cmd.Parameters("?FirstName2").Value = txtFirstName2.Text
                cmd.CommandText &= " WHERE MiddleInitial2 = ?MiddleInitial2"
                cmd.Parameters.AddWithValue("?MiddleInitial2", OleDbType.VarWChar)
                cmd.Parameters("?MiddleInitial2").Value = txtMiddleInitial2.Text
                cmd.CommandText &= " WHERE CurrentStreet = ?CurrentStreet"
                cmd.Parameters.AddWithValue("?CurrentStreet", OleDbType.VarWChar)
                cmd.Parameters("?CurrentStreet").Value = txtCurrentStreet.Text
                cmd.CommandText &= " WHERE CurrentCity = ?CurrentCity"
                cmd.Parameters.AddWithValue("?CurrentCity", OleDbType.VarWChar)
                cmd.Parameters("?CurrentCity").Value = txtCurrentCity.Text
                cmd.CommandText &= " WHERE CurrentState = ?CurrentState"
                cmd.Parameters.AddWithValue("?CurrentState", OleDbType.VarWChar)
                cmd.Parameters("?CurrentState").Value = txtCurrentState.Text
                cmd.CommandText &= " WHERE CurrentZipCode = ?CurrentZipCode"
                cmd.Parameters.AddWithValue("?CurrentZipCode", OleDbType.VarWChar)
                cmd.Parameters("?CurrentZipCode").Value = txtCurrentZipCode.Text
                cmd.CommandText &= " WHERE CurrentCounty = ?CurrentCounty"
                cmd.Parameters.AddWithValue("?CurrentCounty", OleDbType.VarWChar)
                cmd.Parameters("?CurrentCounty").Value = txtCurrentCounty.Text
                cmd.CommandText &= " WHERE FutureStreet = ?FutureStreet"
                cmd.Parameters.AddWithValue("?FutureStreet", OleDbType.VarWChar)
                cmd.Parameters("?FutureStreet").Value = txtFutureStreet.Text
                cmd.CommandText &= " WHERE FutureCity = ?FutureCity"
                cmd.Parameters.AddWithValue("?FutureCity", OleDbType.VarWChar)
                cmd.Parameters("?FutureCity").Value = txtFutureCity.Text
                cmd.CommandText &= " WHERE FutureState = ?FutureState"
                cmd.Parameters.AddWithValue("?FutureState", OleDbType.VarWChar)
                cmd.Parameters("?FutureState").Value = txtFutureState.Text
                cmd.CommandText &= " WHERE FutureZipCode = ?FutureZipCode"
                cmd.Parameters.AddWithValue("?FutureZipCode", OleDbType.VarWChar)
                cmd.Parameters("?FutureZipCode").Value = txtFutureZipCode.Text
                cmd.CommandText &= " WHERE FutureCounty = ?FutureCounty"
                cmd.Parameters.AddWithValue("?FutureCounty", OleDbType.VarWChar)
                cmd.Parameters("?FutureCounty").Value = txtFutureCounty.Text
                cmd.CommandText &= " WHERE HomePhone = ?HomePhone"
                cmd.Parameters.AddWithValue("?HomePhone", OleDbType.VarWChar)
                cmd.Parameters("?HomePhone").Value = txtHomePhone.Text
                cmd.CommandText &= " WHERE Fax = ?Fax"
                cmd.Parameters.AddWithValue("?Fax", OleDbType.VarWChar)
                cmd.Parameters("?Fax").Value = txtFax.Text
                cmd.CommandText &= " WHERE CellPhone = ?CellPhone"
                cmd.Parameters.AddWithValue("?CellPhone", OleDbType.VarWChar)
                cmd.Parameters("?CellPhone").Value = txtCellPhone.Text
                cmd.CommandText &= " WHERE OfficePhone = ?OfficePhone"
                cmd.Parameters.AddWithValue("?OfficePhone", OleDbType.VarWChar)
                cmd.Parameters("?OfficePhone").Value = txtOfficePhone.Text
                cmd.CommandText &= " WHERE Extension = ?Extension"
                cmd.Parameters.AddWithValue("?Extension", OleDbType.VarWChar)
                cmd.Parameters("?Extension").Value = txtExtension.Text
                cmd.CommandText &= " WHERE Email = ?Email"
                cmd.Parameters.AddWithValue("?Email", OleDbType.VarWChar)
                cmd.Parameters("?Email").Value = txtEmail.Text

                conn.Open()

                cmd.ExecuteNonQuery()

                conn.Close()

 

[ajeeshco]

Hi,
make sure that you are not using any keywords as the field names in the insert statement. for eg, insert into password etc if so, then enclose those words within [].

 

[Troy]

The only one I can think of that might be is CurrentState so I changed it to " WHERE [CurrentState] = ?CurrentState" but I still Get the error.

 

[cjard]

As far as I was aware, youre not allowed to name your parameters when using the OleDB driver, (or if you do, and naming is legal, names are ignored)

Thus an insert query should look like:

INSERT INTO table(a,b,c)VALUES (?,?,?)


And the order of addition of parameter values is critical


Further FYI, I don't think you can reuse parameters, because they are nameless, so:

UPDATE x SET y = ? WHERE z1 = ? or z2 = ?


You must use three parameters, here if youre looking to update any Y whose Z1 column is "A" or Z2 is also "A":
params.AddWithValue("the_y", "new Y")
params.AddWithValue("some z1", "A")
params.AddWithValue("zed two", "A")

I've named them, but there's no sense to the names, it would jsut allow me to say:
params("zed two") = "B"

i.e. meaningful to developer, not to jet driver




In a real db you can of course do:

UPDATE x SET y = Y WHERE z1 = Z or z2 = Z


But this is oracle/sqlserver. Remove the names from your parameters and double check the order of addition

And its then down to the driver concerned over whether it wants names with or without the symbol:

params.AddWithValue("@sqlserver", "example")
params.AddWithValue("pZ", "oracle example")

 

[Troy]

Fixed it , Works perfectly!!!

               conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & _
               My.Settings.dbPath & "; Persist Security Info=False")

                cmd = New OleDbCommand("INSERT INTO Contact (LastName1, FirstName1, MiddleInitial1," & _
                " LastName2, FirstName2, MiddleInitial2, CurrentStreet, CurrentCity, CurrentState," & _
                " CurrentZipCode, CurrentCounty, FutureStreet, FutureCity, FutureState, FutureZipCode," & _
                " FutureCounty, HomePhone, Fax, CellPhone, OfficePhone, Extension, Email) VALUES (" & _
                " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn)
                cmd.Parameters.AddWithValue("?LastName1", OleDbType.VarWChar)
                cmd.Parameters("?LastName1").Value = txtLastName1.Text
                cmd.Parameters.AddWithValue("?FirstName1", OleDbType.VarWChar)
                cmd.Parameters("?FirstName1").Value = txtFirstName1.Text
                cmd.Parameters.AddWithValue("?MiddleInitial1", OleDbType.VarWChar)
                cmd.Parameters("?MiddleInitial1").Value = txtMiddleInitial1.Text
                cmd.Parameters.AddWithValue("?LastName2", OleDbType.VarWChar)
                cmd.Parameters("?LastName2").Value = txtLastName2.Text
                cmd.Parameters.AddWithValue("?FirstName2", OleDbType.VarWChar)
                cmd.Parameters("?FirstName2").Value = txtFirstName2.Text
                cmd.Parameters.AddWithValue("?MiddleInitial2", OleDbType.VarWChar)
                cmd.Parameters("?MiddleInitial2").Value = txtMiddleInitial2.Text
                cmd.Parameters.AddWithValue("?CurrentStreet", OleDbType.VarWChar)
                cmd.Parameters("?CurrentStreet").Value = txtCurrentStreet.Text
                cmd.Parameters.AddWithValue("?CurrentCity", OleDbType.VarWChar)
                cmd.Parameters("?CurrentCity").Value = txtCurrentCity.Text
                cmd.Parameters.AddWithValue("?CurrentState", OleDbType.VarWChar)
                cmd.Parameters("?CurrentState").Value = txtCurrentState.Text
                cmd.Parameters.AddWithValue("?CurrentZipCode", OleDbType.VarWChar)
                cmd.Parameters("?CurrentZipCode").Value = txtCurrentZipCode.Text
                cmd.Parameters.AddWithValue("?CurrentCounty", OleDbType.VarWChar)
                cmd.Parameters("?CurrentCounty").Value = txtCurrentCounty.Text
                cmd.Parameters.AddWithValue("?FutureStreet", OleDbType.VarWChar)
                cmd.Parameters("?FutureStreet").Value = txtFutureStreet.Text
                cmd.Parameters.AddWithValue("?FutureCity", OleDbType.VarWChar)
                cmd.Parameters("?FutureCity").Value = txtFutureCity.Text
                cmd.Parameters.AddWithValue("?FutureState", OleDbType.VarWChar)
                cmd.Parameters("?FutureState").Value = txtFutureState.Text
                cmd.Parameters.AddWithValue("?FutureZipCode", OleDbType.VarWChar)
                cmd.Parameters("?FutureZipCode").Value = txtFutureZipCode.Text
                cmd.Parameters.AddWithValue("?FutureCounty", OleDbType.VarWChar)
                cmd.Parameters("?FutureCounty").Value = txtFutureCounty.Text
                cmd.Parameters.AddWithValue("?HomePhone", OleDbType.VarWChar)
                cmd.Parameters("?HomePhone").Value = txtHomePhone.Text
                cmd.Parameters.AddWithValue("?Fax", OleDbType.VarWChar)
                cmd.Parameters("?Fax").Value = txtFax.Text
                cmd.Parameters.AddWithValue("?CellPhone", OleDbType.VarWChar)
                cmd.Parameters("?CellPhone").Value = txtCellPhone.Text
                cmd.Parameters.AddWithValue("?OfficePhone", OleDbType.VarWChar)
                cmd.Parameters("?OfficePhone").Value = txtOfficePhone.Text
                cmd.Parameters.AddWithValue("?Extension", OleDbType.VarWChar)
                cmd.Parameters("?Extension").Value = txtExtension.Text
                cmd.Parameters.AddWithValue("?Email", OleDbType.VarWChar)
                cmd.Parameters("?Email").Value = txtEmail.Text

                conn.Open()

                cmd.ExecuteNonQuery()

                conn.Close()

Now I'm trying to figure how I reconstruct the SQL to create and edit/UPDATE string.


Nevermind Got that as well.... I finally understand it I think!

 

[cjard]

correction:

AddWithValue is for you to provide a name and a value, NOT A TYPE

Incorrect:

cmd.Parameters.AddWithValue("?FutureStreet", OleDbType.VarWChar)
cmd.Parameters("?FutureStreet").Value = txtFutureStreet.Text

Correct:

cmd.Parameters.AddWithValue("?FutureStreet", txtFutureStreet.Text)

If you want to declare a parameter to have a praticular type, use the relevant overload of Add() or constructor overload of Parameter. AddwithValue will figure out the type on its own, you SHOULD NOT pass the type in as the value (it doesnt make sense/it's not provided for that reason)


Lastly, I should point out that had you used a wizard to generate this code it would have:

Got it right
Done it in a fraction of a second
Been way more comprehensive
Been better OO

I know youre a big anti-wizard fanboy, but this really is one place where you should have used it. Not using it has cost you lots and gained you nothing

 

[Troy]

Funny, I used .add to begin with then VS 2008 suggested I use .addwithvalue as .add was outdated it said.

I know youre a big anti-wizard fanboy, but this really is one place where you should have used it. Not using it has cost you lots and gained you nothing

Ok for one, having a wizard generate your code for you isn't always going to work and get it right in every situation now is it?

Also, I lost nothing..... Doing this myself taught me how it works! As I am self taught seeing how every little piece of code contributes to what I'm wanting to achieve is not losing anything.

Now you can get off your high horse, I know when you started creating databases your code was:

1. Sloppy
2. Incorrect
3. Not done using a wizard.
4. Probably not the same after you figured out how everything worked.


If I knew exactly how to structure it as the wizard did, I wouldn't have come here and asked for:

1. Help
2. Listened to your constant belittling me everytime you post.
3. Put up with your "Holier Than Thou" attitude.

Try being contructive, offer suggestions and be patient with people posting on these boards. If We all knew everything or as much as you feel we should we wouldn't be here .

And if you feel compelled not to add anymore wisdom to any of my posts, I'll understand.

Incidentally, I got it working...

1. Without a wizard
2. On my own
3. And it works perfectly.

 

[cjard]

Funny, I used .add to begin with then VS 2008 suggested I use .addwithvalue as .add was outdated it said.

Hmm.. MS deprecated Add(string, object) for adding named parameters and values, but as far as I can see in 3.5, Add(name, type) is not obsolete. Can't quite explain why VS led you a merry dance as it should have detected that you were using a non-obsolate overload..

Ok for one, having a wizard generate your code for you isn't always going to work and get it right in every situation now is it?

For generating basic, single tables I/U/D sqls, i'm struggling to understand how it will get it wrong..

Also, I lost nothing..... Doing this myself taught me how it works!

How what works? Parameterized queries? That's probably akin to the usefulness of learning how a pencil works before you write with it

As I am self taught seeing how every little piece of code contributes to what I'm wanting to achieve is not losing anything.

Therein lies a bigger problem: You're so focused on the micro, that you can easily lose sight of the big picture and that has significant ramifications on how you code in general.

Now you can get off your high horse

OK, if you'll ditch the inherent hypocrisy in "delving into everything in micro detail so I know how it works". Last time you had cheese on toast, did you make the cheese? Did you learn how it was made?
Last time you used a textbox, did you get the windows API out and draw a few rectangles on screen to make your own textbox? No.. It's just too much work to reinvent a wheel that someone else already invented really well!

The point i'm trying to get across here is that what youre doing, writing your own SQLs into button click handlers might well be halping you understand something, but it's so fine grained that its purpose is moot with respect to the overall logic layer youre trying to create.. It's also rather distracting because you've probably packaged all this up in a method to let you Insert into some table, and every time you want to insert, youre gonna call that method i.e. recreate a whole load of stuff that is already created. Great, you use parameterized queries, but the other thing about them is that you create them once, and then reuse them over and over simply by changing some values. Can you see what I'm getting at about being distracted by the micro and generating bad OO code as a result?

However, if you'd felt it necessary to know how to build one, your fastest option would have been to run a wizard then read the resulting code. It's conceptually no different from finding an example on the net and then writing your own out by hand..

I know when you started creating databases your code was

1. Sloppy
2. Incorrect

I'd love to know how you can be so certain of something that simply isn't true; I first learned about query parameterization at the same time as I was learning about databases (at university, many years ago), thus every query I've written (in any production system) has been a parameterized one. Now, I use tools to generate SQLs, but theyre still as parameterized as they were when I started

3. Not done using a wizard.

On this one you would be correct.. It was a few months of writing them by hand before I created a tool that would help write these queries for me. I wrote a primitive wizard because I wanted the best solution with the least effort. Fast forward ten years and MS already made the wizard, so I don't have to do so much work now. It is still a little incomplete w.r.t oracle queries, so I've written code to supplement it

4. Probably not the same after you figured out how everything worked.

Mmmm.. PQs have been around for 20 years or so, and I learned my db access at some point along that timeline; they've always been available while I've been around so pretty much now my PQ code is what it was then.. SOme things in IT don't drift so much


In response to the final paragraphs; I watch a lot of people making their lives unnecessarily harder than needs be. Overwhelmingly I learn that people just arent interested in any suggestions on how to make them easier, and they are determined to make all the mistakes that have already been made so they can learn not to make them again. It strikes me as something of an intrinsic hindrance to progress. There are so many aspects to your life that you use something without the slightest question of how it works; what's the difference with issues such as this? (I'm genuinely curious)

 

[Troy]

There are so many aspects to your life that you use something without the slightest question of how it works; what's the difference with issues such as this? (I'm genuinely curious)

I had to reply to this statement, because it sounds to me like you don't care to understand why things are the way they are. You seem to just say oh this does what I want and move along. Not everyone feels this way. Forexample let's say I like the way a control works but not enough that I don't want to tweak it for myself. Or My computer works but I don't like how slow it acts in games but then I didn't care to learn how to make it better I know it works so therefore I'll just use it the way it is.

By learning how something does something we can not only understand the process we can fine-tune it to something more akin to what we want.

Yes there is a bindingNavigation bar control you can use in applications. But I don't like it and would rather creat my own form of navigating through my applications.

Yes you can drag an access database into your project creating a self contained Control bound application that everything is tied to. But for argument sake, I want to be able to use the same type of database but with different data in the same application. I would have to look at the project in an entirely different manner.

One thing programmers tend to forget is the people using their applications usually don't know the first thing about pc's. They are secretarys, doctors, auto mechanics, etc. They most generally don't know the first thing about data entry and if something looks too confusing for them it scares them away from it.

Now as far as reinventing the wheel, It was reinvented many many times down through history.

If we hadn't have added tires to a wooden wagonwheel, think of how rough a ride your car would be right now.

Oh the car still gets you from point a to point b faster then walking so that works as intended but it's not to my liking.

So then why is it difficult to understand why someone would want to do something a different way even if there was a way already in done in front of them. Why would I want to go to my house down a different road than the one that leads right to it.

Maybe by going a different way I might discover a shortcut that will get me there faster, or perhaps I might discover some new territory no one had seen before.

I'm using Visual Studio 2008, I'm learning how to use it by myself not by an instructor. Just because you had the opertunity to attend a university and be instructed doesn't give you the right to be critical of someone that came here asking for help.

Here's another example maybe you can relate too. If you came to me asking how to change the points in your car and I handed you a chilton manual and said there you go, how would that make you feel? If I wanted to take six months to a year to lookup the answer decipher the book and figure out what maybe you could have helped explain in 20 minutes I wouldn't have bothered posting on a forum.

 

[cjard]

I had to reply to this statement, because it sounds to me like you don't care to understand why things are the way they are. You seem to just say oh this does what I want and move along.

That's because it does do what I want and I have assessed that I can do it no better myself

Not everyone feels this way. Forexample let's say I like the way a control works but not enough that I don't want to tweak it for myself. Or My computer works but I don't like how slow it acts in games but then I didn't care to learn how to make it better I know it works so therefore I'll just use it the way it is.

I understand your point but please don't assert that I'm merely accepting what the wizard churns out because it works. I have zero complaints with the code that it generates because (as I've said before) it is:

Good OO
Correct
Fast
Smart
Requires minimal effort to create


If you moved the discussion onto something crap like DateTimePicker and how it is useless for databinding because it doesnt handle nulls, then we'd be having more of a discussion

By learning how something does something we can not only understand the process we can fine-tune it to something more akin to what we want.

I couldnt agree more.. It's just in this context, there is next to nothing to learn that will be of real benefit. Writing an SQL and putting parameters in it is, as noted before, akin to learning the composition of the pencil lead before you write with it

Yes there is a bindingNavigation bar control you can use in applications. But I don't like it and would rather creat my own form of navigating through my applications.

Yep, there are aspects I dont like about it to, but it's very easily customisable, and it provides a somewhat consistent look and feel which leads onto your next point...

One thing programmers tend to forget is the people using their applications usually don't know the first thing about pc's. They are secretarys, doctors, auto mechanics, etc. They most generally don't know the first thing about data entry and if something looks too confusing for them it scares them away from it.

..that one can never underestimate the power of a defacto standard. You and I as systems engineers have to find the middle ground between crafting a system that is so simple to use it needs no documentation and is perfectly suited to task, and getting a solution out the door that costs less to build than it will ultimately sell for, because we have to eat too. The phrase "familiarity breeds content" applies here; when people who are operating in a problem domain that is alien to them see something familiar, it makes them more at ease and confers a feeling of control. For this reason, all Windows forms apps look similar; it's good sense. See what happens when coders do arse-about-face things like turning a group of checkboxes into only-one-item-can-be-ticked instead of using a radiobutton group.. There are many other examples of HCI atrocities on the web and they make for interesting viewing. The programmer no doubt thought he was being very clever but people ended up confused because something that looks like <X> actually behaves like <Y>, when they always formerly knew it to be <X>. Take the Close button in the top right of a form; it's pretty much always closed the app like File.. Quit does, but somewhere along the way, people started using it to close the window and keep the app running in the task tray: messenger, downloader programs etc etc. Even Microsoft is guilty of violating defacto standards.

Coming back to the nav bar.. The more people use it, or something like it, the more they will feel happy that they know what it does in your app. I'm sure you think it's wonderful that you prefer to cook something up yourself "in the name of being nice to the users" but examine the hypocrisy in there should you end up violating the standard behaviour a user expects, or providing a behaviour like <X>, but not via a control that looks like <X>

I want to be able to use the same type of database but with different data in the same application. I would have to look at the project in an entirely different manner.

Er, yes. That kind of interface is called "Microsoft Access" - it allows you to use entirely different data in the same app, and you have to be a coder, not a secretary or an auto mechanic to use it. I'm having trouble following this thread of your argument. If you want something massively flexible that a developer can quickly use to generate a data access layer within his app, then that would be the "Visual Dataset Designer" - the very wizard youre arguing against using. If you want something you can show to a user who struggles to turn their computer on, but will enable them to craft the most complex data mining queries with a few button clicks, then there's a job waiting for you at Crystal Decisions, Microsoft, any number of companies who have tried to crack that nut and ended up with a product that really, you have to be highly technically competent to get the best out of. Secretaries are never going to write mass aggregate window analytic SQL queries, theyre just going to use the nice interface you provided with a few buttons, to show the patient trends in their doctor's surgery on a year-by-year basis.

Now as far as reinventing the wheel, It was reinvented many many times down through history.

Please tell me how someone really, truly, invents a wheel more than once?

If we hadn't have added tires to a wooden wagonwheel, think of how rough a ride your car would be right now.

Yes, but youre banging on like your work here in replicating 60% of what the dataset wizard does, is some sort of reinvention! You've succeeded in writing a parameterized query, something that has been hanging around for 20 years. You havent coded a whole new way to access a database. Further, no amount of query writing and understanding how it all works will mean that you will reinvent any wheel because youre not attacking an unsolved problem!

So then why is it difficult to understand why someone would want to do something a different way even if there was a way already in done in front of them. Why would I want to go to my house down a different road than the one that leads right to it.

Maybe by going a different way I might discover a shortcut that will get me there faster, or perhaps I might discover some new territory no one had seen before.

Your analogy doesnt make sense to me: you have already stated there is a road directly to your front dor. What do you hope to discover on a less direct route? That the direct route is not actually the direct route? Why did you thus state it was a direct route in the first place? Also, given that youre travelling an indirect road that someone else has built, how can you assert that you will see something that has not been seen before? Someone else must have seen it, because it was there when the road on which you now travel, was built.

Actually, maybe I am starting to understand the analogy, I just don't see where it goes.

I'm using Visual Studio 2008, I'm learning how to use it by myself

I'd actually assert that youre using Notepad. i.e. youre using VS2008 like a text editor and coding by hand everything that it was built to do by machine. You bought a sports car and now youre pushing it along the road. Actually, youre probably not completely adopting this, because I never saw you post on the Windows.Forms forum saying "I'm laying out all my form controls by hand instead of using the visual designer and..."
Hence, youre satisfied that the forms designer wizard is capable of doing a good job, so you choose not to trouble yourself with doing/learning the art of laying out controls manually. Please resolve the logic of accepting the Forms Designer but rejecting the DataSet Designer

Just because you had the opertunity to attend a university and be instructed doesn't give you the right to be critical of someone that came here asking for help.

Uni didnt teach me Visual Studio .NET; it didnt exist back then. Uni didnt really teach me how to write code.. It taught me how to engineer software. So much of my time was spent NOT coding, but learning how to think about what to build.

Here's another example maybe you can relate too. If you came to me asking how to change the points in your car and I handed you a chilton manual and said there you go, how would that make you feel?

A Chinese proverb?
“Tell me and I'll forget; show me and I may remember; involve me and I'll understand.”

Honestly, how would it make me feel? Empowered to be able to do it; i no have the means and the knowledge. If you took 20 minutes out of your life to deliver a monologue on how to change the points, well.. like the Chinese saying goes "Tell me, i'll forget". No way could I sit and listen to everything you said for 20 minutes, then carry it out and get it right. Using the workshop manual I can get it right because it tells me in a detail you won't, and in a format I can requery. It's much closer to "involve me and I'll understand" (but it's probably more like "show me...")
If you took the time out of your life to come over my house and mentor me on changing the points then probably (because it's the kind of guy I am, even though I havent owned a car possessing a mechanical distributor for some years now) I'll learn not "how to change those particular points on that particular car in that order with those tools" but the generic process of changing points on any car.. This is the "involve me and I'll understand" but it's not something that can easily be taught on a forum.. Teaching someone how to think using this limtied interface takes a massive amount of time and effort, and because ultimately, we all have to eat, you will usually get deferred to the DW2 link or something because it's the best thing available that advances away from the "tell me" stage, and gets you toward your own learning.

Simialrly, to trip back to the wizard argument: youre not really here to learn how to make a parameterised query because it's so low level that it's navel fluff pointless. Youre best off honing your SQL skills, writing clever queries that can solve all the user requirements. Getting distracted, learning how the electrons move through the processor isn't really going to help.. The wizard exists to remove the low level tedium and get you on with making your SQLs good.. If i took a little while too long to get that across, I apologise.. Do please believe me that knowing how to write a parameterized query is going to advance your SQL crafting skills very little

If I wanted to take six months to a year to lookup the answer decipher the book and figure out what maybe you could have helped explain in 20 minutes I wouldn't have bothered posting on a forum.

Er, well.. Before this discussion broke out, did I or did I not provide that answer in 20 minutes? I did, but the ensuing discussion has hopefully outlined that one cannot always rely on there being someone there with the answers, nor will they always serve as an intelligent dictionary lookup that parses your problem, and formulates the answer.
There's comething of a crossed purpose here: I'm trying to say "Don't make your life needlessly hard, making a worse job of delving into the micro than someone else already did. Focus on the bigger problem at hand" but somehow it's become a debate of what sort of answers (vs teaching) can be delivered over an internet forum. To contrast with the modus operandi of another internet resource, that's what I hate about ExpertSexChange.com - because everything is points based and people compete for points, all they do is answer your question. You want someone to do the work for you and give you that targeted answer in 20 minutes, you go there.. You want someone to say "er.. hangon.. let's think about the logics of this and maybe come up with another way to think about the problem", you come here.

The critical difference is, I guess:
The developer faces a problem
The developer devises a solution
The developer encounters a problem implementing the solution
The developer asks other developers
Other developers can see the flaw in the solution, but the question is: Do we fix the flaw, or fix the solution so it doesnt suffer the flaw?

ExpertSexChange: fix the flaw in the solution, grab the money and run
a more "teaching" based forum: devise a better (fix the) solution

 

[JohnH]

I've always found "learning by example" is my best approach in combination with the pure class reference material, so I also often post code samples with some explaining when trying to help others. This is also something I have found very useful with the designer generated code for forms, data access etc; generate stuff with the wizard and see what code it produces, which make you learn both the standard development wizards and the underlying code implementation at the same time. When you know both it is easier to see limitations and benefits and easier to choose what solution to apply for various problems. Learning how to "push the buttons" (and where to find them) may for some be easier with video examples or screenshots rather than step-by-step text lists, video tutorials has become very common recent years.

The generated data access classes may seem a bit overwhelming at first because it really generates lots of code and uses many components that connects to and interact with each other in a certain order, and it applies failsafes and OOP that can expand the amount of code from the core principles. So for the absolute beginner that wants to learn the code interaction with databases textbook examples using a connection + command + dataset + datagridview and such is better than the full wizard show, there should be books/tutorials and people available willing to teach/support this also.

In conclusion, starting with data access I'd say learning the wizards comes first, and eliminates much beginner problems, also when it comes to manual coding later. While not an expert and DBs not my favorite interest field, I still usually agree with cjard in his DW2 mission when browsing such topics here

 

[MattP]

...Learning how to "push the buttons" (and where to find them) may for some be easier with video examples or screenshots rather than step-by-step text lists, video tutorials has become very common recent years.

I've found that the Forms over data video series is an excellent source.

 

[Troy]

Let me first start by saying I really do appreciate the time you put into replying to my posts.... all of you.

I'm not trying to be stubborn here, maybe it's just inherent to me, Right now it's because I don't know of any alternative to do what I want my app to do.

I want the user to be able to backup and restore the database...Period. It's that simple.

The only way I know how to right now is using OLEDB and NOT using the Data Wizard and a bound Database. I do know how to copy files in VB, so letting them navigate and select then copying it to a location is acceptable to me.

But, With the Data Acess Wizard, having it place the DB into my project, I don't know how to allow for later restoring it from a different location and usable by the client.

For instance, I could copy the internal DB to an external location again not really an issue that I can think of there.

I don't know about the reverse. If the internal becomes corrupt, how do I allow them to select and external, perhaps copy it to a specific location that the bound controls will know where it is?

This whole project is basically just a learning project for me. I've been learning from doing and changing it for years. Since VB 3.0.

Back then If you wanted a DB you created you're own structure. I did this with GBasic it worked very nice.

Yes I've been programming since The 8086 processors came out. Back then I knew several languages. But it's progressing so fast I have difficulty keeping up so I chose VB and some C++, C# as my languages.

My point is before I digress too much is I have tons of code here, I've rewritten, Thats never truely been completed, but lets say 90% of this version is with just a few hiccups. Now I'm basically being told that to get it to do what I want it to do I need to rewrite the whole thing again.


Normally I wouldn't be opposed to this but I can already forsee having a lack of understanding to complete it if rewritten.

For instance like I previously said. I don't know how to take a bound Database and allow for an external/backup to copy to an internal.

 

[cjard]

A few options exist and it largely depends on how you answered that initial question.. You are also free to re-form your connectionstring at any time in the settings and all the tableadapters will pick it up.

When you opt to copy the db into your project, it becomes your "clean room initial state" database and will be packaged as such every time the DB goes out. It will, by default, be put in the app's folder and the connection string will contain a relative reference to the file. As such it is subject to replacement like any other file; simply have another copy of the db under a different name, and copy it over the top of a corrupt one, or have a folder for backups, and copy the living database to the backup folder and back alongside the app as your backup or restore routine

If you have developed your app such that it is referencing an online SQLServer database, i.e. no linked-at-runtime file, then the connection string will basically containa server and a db name; there isnt the option of backing up the db by copying the file, you would instead issue a command to get SQLServer to do it.

If you opted not to copy the db into your project, hten the location it is at on the dev machine is the location it must be at on the install machine. Likely you will change the connection string before you release and hence you will know where to find the db for copying in and out.

The tableadapters generated by wizard automatically open and close database conenctions, though the connection string is set when the TA is created (Dim x as New ...). To have a TA realise a change in the connection string during runtime you must either recreate it, or expose the connection property via the ConnectionModifier property on the designer so that you can access the connectionstring and alter it yourself. I'd advocate the easier route of renewing the tableadapter itself at runtime. Because the default connection behaviour is connectionless you should be able to replace the DB while the app is running, provided no TAs are accessing it at the time. Note that wizard TA code will seek to preserve a conenction state set by the developer, so if you manually open your TA connections prior to using the TA (advisable only in cases of connection-based transactions) it will not close them for you