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

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
psuscott0483Asked:
Who is Participating?
 
consultewareConnect With a Mentor Commented:
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
0
 
Christopher KileCommented:
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")
0
 
consultewareCommented:
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.

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
psuscott0483Author 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!
0
 
psuscott0483Author 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!
0
 
psuscott0483Author 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.