Updating Database

  • Thread starter Thread starter Age
  • Start date Start date
A

Age

I need help.

I am trying to figure out the best way to uodate a database with
information. I am gathering my information from text fields on a form
and I just want to insert them into the database that I have.

I tried builing the SQL string and then adding it to a command object
and then executing a non query but .NET does not seem to like this
approach. I have been reviewing the various VB .NET manuals and they
seem to want me to fill a dataset. Can this be done without the
dataset?

Would I be better off just sticking with regular ADO for this kind of
operation?

I was trying something like the following and getting errors:

Dim cn As OdbcConnection
cn = New OdbcConnection("dsn=CMI;uid=;pwd=;")


strSQL = "INSERT INTO Customer
_(FirstName,LastName,Address,City,Province,PostalCode,Email)"

strSQL += " VALUES ('"
strSQL += "'" & Replace(txtFirstName.Text, "'", "''") & "',"
strSQL += "'" & Replace(txtLastName.Text, "'", "''") & "',"
strSQL += "'" & Replace(txtAddress.Text, "'", "''") & "',"
strSQL += "'" & Replace(txtCity.Text, "'", "''") & "',"
strSQL += "'" & Replace(txtProvince.Text, "'", "''") & "',"
strSQL += "'" & Replace(txtPostal.Text, "'", "''") & "',"
strSQL += "'" & Replace(txtEmail.Text, "'", "''") & "')"

cn.Open()
Dim cmdInsertCustomer As New OdbcCommand(strSQL, cn)

cmdInsertCustomer.ExecuteNonQuery()

cn.Close()


Can anyone suggest a way I could accomplish what I need to do here.
Many thanks.
 
Well, some of the books (like mine) don't force you to build a DataSet to
update data. Sure you can roll your own Command objects, but you're going
about it by way of Brazil (which is not all bad--I had a great time down
there several years ago).
When you want to build a Command object to execute an action query, build
the Parameters collection to contain the values to be inserted into the SQL.
When using ODBC (which is fine--it's actually faster than OleDB), you mark
where each parameter is to be placed with a "?". So, your INSERT statement
would look something like this:
strSQL = "INSERT INTO Customer
(FirstName,LastName,Address,City,Province,PostalCode,Email) " _
& "( ?, ?, ?, ?, ?, ?, ?)"

Ok, now you need to create the Parameters collection. Add a Parameter for
each column in the INSERT something like this:

Dim cmdInsertCustomer As New OdbcCommand(strSQL, cn)
with cmdInsertCustomer
.Parameters.Add("@Firstname",txtFirstName.Text)
.Parameters.Add("@LastName", txtLastName,Text)
....
etc.
end with

This approach automatically deals with the framing quotes and the O'Malley
issue (imbedded single quotes in strings) and correct date formatting. It's
also faster than concatenating strings.

Need more help? It's all in my book...

hth



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

I could have written "buy a better book" however I knew you would do that.

:-)

Cor
 
OK...here is what I have now...

Dim cn As OdbcConnection
cn = New OdbcConnection("dsn=CMI;uid=;pwd=;")

Dim strSQL As String

strSQL = "INSERT INTO Customer
(FirstName,LastName,Address,City,Province,PostalCode,Email)"

strSQL += " VALUES (?,?,?,?,?,?,?)"

cn.Open()
Dim cmdInsertCustomer As New OdbcCommand(strSQL, cn)

With cmdInsertCustomer
.Parameters.Add("@FirstName", txtFirstName.Text)
.Parameters.Add("@LastName", txtLastName.Text)
.Parameters.Add("@Address", txtAddress.Text)
.Parameters.Add("@City", txtCity.Text)
.Parameters.Add("@Province", txtProvince.Text)
.Parameters.Add("@PostalCode", txtPostal.Text)
.Parameters.Add("@Email", txtEmail.Text)
End With

cmdInsertCustomer.ExecuteNonQuery()

cn.Close()


What am I missing here? I keep getting:

An unhandled exception of type 'Microsoft.Data.Odbc.OdbcException'
occurred in microsoft.data.odbc.dll

Additional information: System error.

I've been banging my head trying to get this to work. I even went to
the local book store and took a peek inside another book from
Microsoft Press and everything listed here should be working...

I do appreciate the information on the parameters being much more
efficient. And just a note for Cor the reason I am using ODBC is you
can never be sure where a user may wish to place their database so it
just as easy to have ODBC keep track of it. Plus it makes swapping
between databases much easier than rewriting the code to do it...as
for the books (WROX)...I seem to be able to find the information I'm
looking for much more quickly paging through a book than trying to get
a google search to bring up the exact information I'm looking for. I
also haven't been able to find information on doing this so that is
why I've come to the groups here looking for wisdom from the experts.

I thank you all for your help and if you can help me get over this
last hurdle it would be much appreciated.

If I have to create a wrapper for ADO to do what I want here I guess
that may be the road that I'll have to go down...but ADO .NET must be
able to perform this. I think I'm just missing one key piece of
information to make this work.


Again, thanks for the responses.
 
OK, forget my last question that I added to this string of
questions...

Apparently, I have not got enough sleep in the last few days because I
overlooked one important thing...it was my fault it was not working
and both solutions work just fine...in my application I was controling
the primary key and it slipped my mind that I was as I was just trying
to get into the grove and learn .NET

The only thing I can add is the importance of using try-catch
statements to get fully descriptive error messages so they will point
out to you clearly the mistakes made.

Again, thank you both for your wisdom...I'm feeling silly for asking
my question but I was really banging my head against the wall with
this one...

And as for using the parameters, will that also deal with the problem
of trying to add Null values to an Access database? I'm thinking that
maybe I will use this format from now on if it is going to take care
of the "quotes" issue and date issues. Very useful knowledge...

Regards,
 
Age,

One little thing, using OleDb you can set your database as well everywhere,
the register is very easy to maintain in VBNet, so you can look if there is
a connectionstring in that and when not, just ask the user for the place
using a folderbrowserdialog (bug resolved in sp1.1)

Add that place and the file to the connectionstring from the connection in
the load event, that makes it possible to set your access file everywhere,
it is even much more easy than ODBC in past.

Cor
 
Back
Top