parameterized query update question

As you may have seen in my other post, I have to switch over an all text SQL statement based database interaction program to using parameterized queries instead.

I remember doing it this way in college but I don't seem to be able to recall one part of it. The program I wrote back then and some code samples here both point to doing the code as I did so far:

Code:

---

m_strSQL = "UPDATE [OWNER DATA] SET [FIRST NAME] = @strFName"
m_strSQL &= ",[LAST NAME] = @strLName"
...etc

Dim upCMD As New OleDb.OleDbCommand(m_strSQL, m_Connection)
upCMD.Parameters.Add("@strFName", OleDb.OleDbType.Char, 30, "[First Name]")
...etc

m_DataAdapter.UpdateCommand = upCMD
m_DataAdapter.Update()

---

So by the second to last line there it has the SQL statement build and loaded into the command object and all the data for the fields is there so I just need it to actually do the update but that m_DataAdapter.Update() command is asking for a dataset to use to update it. My program is not set up to read in a dataset from the database, modify it, and send it back using that command so that whole model doesn't work for me in this case. Why can't it just take the SQL statement and run it without asking for a dataset to use? Am I missing something really obvious here?
My old version with just strings used:

Dim upCMD As New OleDb.OleDbCommand(m_strSQL, m_Connection)
intCount = upCMD.ExecuteNonQuery()

and tada, it ran the SQL statement and that's that. Can I just do that with a parameterized query too without using the adapter.update()? And for bonus points, why the heck did I use that command in my old college program cuz I sure don't know? lol.

Yep, you got it right. As long as the command object is hooked up to a connection object you just execute the parameterized query using the command object. I don't know why you would have used a data adapter in college - leave it to the ivory tower folks to... well, I don't need to get started on that soapbox.

If a process is a living creature, does that mean it tastes good grilled?

lol well my hard drive got heavily corrupted when I tried to merge two partitions on my hard drive with Partition Magic and VS2005 was on it and it get pretty well "grilled" but it mostly just tasted like I was really pissed off lol.

Btw ivory tower? You mean the cheating modders in Halo 2 on the Ivory Towers map? lol. Oh wait, no, you must mean that little guy from the Wizard of Oz who says "nobody gets in the see the data adapter, not nobody not no how"...unless that I was the emerald tower :P I must be thinking of Neverending story.

I looked back at the code and I filled in all the parameters but didn't give them and actual data and actually did do m_DataAdapter.Update(NewRentalTable) which apparently is some table I made. By the way, speaking of that I just noticed one big flaw. I didn't assign any values to the parameters, oops! I saw in a same you use the AddWithValue parameter method.

Do I have to do:

upCMD.Parameters.Add("@strFName", OleDb.OleDbType.Char, 30, "[First Name]")

then

upCMD.Parameters.AddWithValue("@strFName", strFName)

or can I just use the addwithvalue in the first place? And if so, why doesn't add with value ask for a column name, length, or datatype?

AddWithValue only needs the parameter placeholder - @strFName in this case - and the value to replace it with when the query runs. Why the standard 'Add' method would ask for the column name is beyond me. I suspect I don't know all there is to know about parameterized queries. Maybe one of the guru's will notice this thread and chime in on that topic.

yeah, I think I'll dump the target column part cuz it's optional and shouldn't necessary and I wasn't sure if it needed square brackets for table names with spaces anyway.
I'm not big on parameterized queries cuz none of my programs are hackworthy with SQL injections. Like why would you hack your own program that's importing data into your own database? So I just did it the easy way. Plus, I don't use objects and methods I didn't write whenever possible cuz then I have to guess exactly how they work and what they do or worse, look it up in the MSDN library lol.
So I kinda get what the purpose of the method is but if I already added a parameter, would using addwithvalue add it again? If so, how do I give it a value otherwise?

I don't know what it would do if you added it twice. I suspect it would throw an error. You'd have to test it to be sure. Personally I would just remove the 'Add' line and just go with 'AddWithValue'

And like you, I don't generally use parameterized queries unless there's a reasonable risk of a SQL injection attack - i.e. a textbox that is used to get user input for a database field.

well the reason I'm switching over to parameterized ones is because I need special characters like ' and * to be handled automatically instead of escaping them all manually in the SQL string. But if I use addwithvalue and it just asks for the name then throws a string in there as a value without me specifying the datatype, it won't handle special characters anymore, will it? If it did that would be pretty amazing and handy for me.
Btw trying to guess how Microsoft's methods and objects actually work like we're doing right now is exactly why I don't use their objects whenever possible :( encapsulation is no fun :(

EDIT: Well I just realized that data type, length, size, and source column are all optional in the overloads for the regular Add so basically all it needs is the parameter name and that's it. So the only difference with AddWithValue is you also give it a value. But then I still wonder...AddwithValue doesn't have an overload that lets me specify a data type so if I give it a string value that has a single quote in it, will it know "hey, this is a string and that's going to confuse it to put a single quote in it so I better escape it" Does it just get the datatype from the variable I feed in as a value and match it to the closest oledb datatype then format it and stuff? Like if I give it a date as a value will it know to convert it to the right date format with the pound sign and all that and if I give it a string variable as a value it will know to handle all special characters like single quotes?

My recommendation is build a test app and see what it does. Either that or look in the documentation. ;)

If your query parameter count <> your command parameter count you will get an exception saying that you have a count mismatch basically. Parameters is just a collection, so each time you call .Add() it will add a parameter at the end of the collection regardless if the same one was added.

Ah. That makes sense. Good answer.

FYI, the major difference with Parameter.Add and Parameter.AddWithValue is that the .Add allows you to explicitly declare the datatype you are sending the parameter in as, .AddWithValue does not.

D

Quote:

---

Originally Posted by dminder

FYI, the major difference with Parameter.Add and Parameter.AddWithValue is that the .Add allows you to explicitly declare the datatype you are sending the parameter in as, .AddWithValue does not.

D

---

Or to put it a slightly different way, it assumes that you know what datatype you need to put into that parameter.