?
Solved

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

Posted on 2006-10-27
6
Medium Priority
?
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress
Suggested Courses

771 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