Solved

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

Posted on 2006-10-27
6
333 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
0
Comment
Question by:psuscott0483
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 17821822
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
 
LVL 5

Expert Comment

by:consulteware
ID: 17822517
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
 

Author Comment

by:psuscott0483
ID: 17833970
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:psuscott0483
ID: 17843225
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
 
LVL 5

Accepted Solution

by:
consulteware earned 500 total points
ID: 17848274
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
 

Author Comment

by:psuscott0483
ID: 17852602
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question