Solved

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

Posted on 2006-10-27
6
332 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A short article about problems I had with the new location API and permissions in Marshmallow
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

861 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