We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

using visual basic 6 or .net to update/delete/add records to sql database

psuscott0483
psuscott0483 asked
on
Medium Priority
409 Views
Last Modified: 2010-04-16
hi everyone, i have been out of school for only a few years and ive only had 2 visual basic classes in college as a background for this matter. ive been asked to create a vb form for people to update/add/delete records for suppliers in a sql database. ive started working with this in vb 6, i also have access to .net if that would be easier/more beneficial. the connection to the database is working for me (or it appears to as i step through it) however the rest is a jumbled mess at this point) ive been fighting with it for a week, it comes back to me as i see a result that works but as of right now its not. ive posted the script for the form and below the hashes is the script for the module. can someone please point me in the right direction? thanks!!

i would like to display the first record from the database in the forms textboxs when it first loads, and use page down/page up buttons to browse through the records if possible.

Option Explicit
Private Sub Form_Load()
    Open_connection
    Set rstSupplier = New adodb.Recordset
    rstSupplier.CursorType = adOpenKeyset
    rstSupplier.LockType = adLockBatchOptimistic
    rstSupplier.Open "supplier", PRC, , , adCmdTable
    Load_info
End Sub
Private Sub btnexit_Click()
    End
End Sub
Public Sub Open_connection()
    strPRC = "driver={SQL Server};server=tristar3;" & _
        "uid=baan;pwd=baan;database=PRC"
    Set PRC = New adodb.Connection
    PRC.Open strPRC
    PRC.BeginTrans
End Sub
Public Sub Load_info()
txtnumber.DataField = "suno"
Set txtnumber.DataSource = Supplier
End Sub
==========================================
Option Explicit
Public txtsuno As String
Public txtname As String
Public txtcontact As String
Public txtaddr1 As String
Public txtaddr2 As String
Public txtaddr3 As String
Public txtcity As String
Public txtstate As String
Public txtzip As String
Public txtcountry As String
Public txtphone As String
Public txtemail As String
Public strPRC As String
Public rstSupplier As adodb.Recordset
Public openrecordset As adodb.Recordset
Public PRC As adodb.Connection
Public errLoop As adodb.Error
Comment
Watch Question

Christopher KileSenior Software Analyst
CERTIFIED EXPERT

Commented:
You don't need a transaction unless you're planning database updates in the future.  Same for the keyset cursor (use a static cursor instead).  Even in the case that updates will be made, they'll never post because you never close the transaction.

You never close your database connection.  I know of NO copy of MDAC that will do this for you in such a way that the server will release its connection resources when a connection object is destroyed.  

Your button exit routine should include

PRC.CommitTrans
PRC.Close

before the End statement.

Also, binding doesn't work unless you specify a data source.  

In fact, you can eliminate most of this code by using an ADODC control as a data source (if you have MDAC 2.8 installed, this will be available in your list of Controls that you can install).  By setting the properties of this control, and then binding your text fields to the ADODC as a data source, all this other code pretty much becomes unnecessary (as initialization of the control will generate any required objects and states. and termination of the control will properly dispose of said objects).  

However, your current code should work if Load_Info is changed to read

txtnumber.Text = rstSupplier("suno")
You can startup with an example found at this link:

http://www.codeproject.com/vb/net/quickguide.asp

Just download it, and learn how to deal with all the data in it and how to treat it.

Author

Commented:
thank you both for your replies.

cpkilekofp - you were correct, if i change the load info code to your example the code fills in. except if there is a null in the db it will say run-time error 94 invalid use of null.

the end user will need to update, delete, and add records to the db daily. so after looking through what you mentioned over the weekend i suppose i will need an insert button to open the db then a save button to add the record. i would like in the next step that once the data is loaded perhaps to scroll through line by line in the db by using page up page down.

consulteware - thanks for the link i will check that over now!

Author

Commented:
can anyone give an example code for adding / updating / deleting a record? i suppose i would use a insert button to open the db then a save to post the new records, would that be easiest? same goes for delete, then hit yes or no to post the deletion. thanks very much!
Here is the small and exactly example that you need.

You have only one screen and the move next, previous, first and last, the insert, delete, duplicate and several other option, it is a very small application that shows you an example how to do it in vb and ADO just see it.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=50695&lngWId=1

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
thank you for your help.

i ended up actually starting a new windows application form in visual studio 2005 and i had the program running in less than 5 minutes. i just need to figure out how to complie it so the end user can run it downstairs without needing the whole visual studio software package.

thanks for everyone's input.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.