Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB DB

Posted on 1999-08-02
2
Medium Priority
?
478 Views
Last Modified: 2013-12-25
I am used to working with databases and recordsets using
vc++ and odbc, which is quite simple. I would like to know how to do the same in vb. like, declaring db database, pointing it to an odbc, opening, getting a recordset (i assume that hence i have a recordset, navigating and editing the database will be trivial; hell, even my question is trivial:))
0
Comment
Question by:Blastarr
[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
2 Comments
 
LVL 4

Accepted Solution

by:
CraigLazar earned 120 total points
ID: 1507370
Hi try thisd
Dim DB As New ADODB.Connection
Dim Rs As New ADODB.Recordset

on form load
    SqlAdo = "Select * from Custdetails"
    DB.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" _
    & "User ID=" & tmpLogonName & ";Password=" & tmpPassword & ";" _
    & "Initial Catalog=Customers;Data Source=Devserv"
    Rs.Open SqlAdo, DB, adOpenStatic, adLockReadOnly, adCmdText
    DB.Open
    If Rs.RecordCount <> 0 Then
        Rs.MoveFirst
        txtCompany.Text = Rs("Company")
        txtAddress.Text = Rs("Address")
        txtTelNumber.Text = Rs("TelNumber")
        txtContact.Text = Rs("contact")
        txtFax.Text = Rs("Fax")
        txtRate.Text = Rs("Rate")
        Call StartEnable
    Else
        MsgBox "There are No records in the Database"
    End If
eh:
    If Err.Number = 40002 Then
        MsgBox "Sorry Login Failed !", vbCritical
        End
    ElseIf Err.Number = 40009 Then
        MsgBox "There are no records in the Database !"
        Call MoveButtonsDisable
        Exit Sub
    End If

Private Sub cmdMoveFirst_Click()
    Rs.MoveFirst
    txtCompany.Text = Rs("Company")
    txtAddress.Text = Rs("Address")
    txtTelNumber.Text = Rs("TelNumber")
    txtContact.Text = Rs("contact")
    txtContact.Text = Rs("contact")
    txtFax.Text = Rs("Fax")
    txtRate.Text = Rs("Rate")

Private Sub cmdInsert_Click()
On Error GoTo eh:
    Dim sqlInsert As String
'   Check Fields Have Ben Filled In
    If txtCompany.Text = "" Then
        MsgBox "Please Fill in a Company Name !", vbInformation
        txtCompany.SetFocus
        Exit Sub
    ElseIf txtTelNumber = "" Then
        MsgBox "Please Fill in a Company Name !", vbInformation
        txtTelNumber.SetFocus
        Exit Sub
    ElseIf txtContact = "" Then
        MsgBox "Please Fill in a Company Name !", vbInformation
        txtTelNumber.SetFocus
        Exit Sub
    End If
   
    sqlInsert = "INSERT INTO CustDetails" _
    & " (Company,Address,Telnumber,Contact,Fax,Rate) VALUES " _
    & "('" & txtCompany & "','" & txtAddress & "'," & txtTelNumber & ",'" & txtContact & "'," & txtFax & "," & txtRate & ");"
    DB.Execute sqlInsert
    MsgBox "Record has Been accepted into the Database", vbInformation
   
    Call DisableFields
    Call StartEnable
    Call MoveButtonsEnable
eh:
    If Err.Number = 40002 Then
        MsgBox "This will Create a Duplicate Record for  " & tmpCompany & " in the Database !", vbInformation
        Exit Sub
    End If

On Error GoTo eh:
    Dim sqlUpdate As String
    Dim tmpTelNumber As Long
    Dim tmpCompany As String
    Dim tmpContact As String * 255
    Dim tmpAddress As String * 255
     
'   Need to pass values of fields to temporary variables for field validation checks
    tmpCompany = txtCompany.Text
    tmpAddress = CStr(txtAddress.Text)
    tmpContact = CStr(txtContact.Text)
    tmpTelNumber = CLng(txtTelNumber)
    tmpFax = CLng(txtFax.Text)
    tmpRate = CCur(txtRate.Text)

'   Set up Update Sql Statement
    sqlUpdate = "UPDATE CustDetails " _
    & "Set Address = " & "'" & tmpAddress & "'" _
    & "," & "TelNumber = " & "" & tmpTelNumber & "" _
    & "," & "Fax = " & "" & tmpFax & "" _
    & "," & "Rate = " & "" & tmpRate & "" _
    & "," & "Contact = " & "'" & tmpContact & "'" _
    & "WHERE company = '" & tmpCompany & "' ;"
   
'   Execute the Sql Statement above using the db object
    DB.Execute sqlUpdate
    MsgBox "The Record has been Successfully Updated ", vbInformation
    Call DisableFields
    Call StartEnable
    Call MoveButtonsEnable

eh:
    If Err.Number = 13 Then
        MsgBox "Please Fill In the correct values for each field !", vbInformation
        txtAddress.SetFocus
        Exit Sub
    End If


just somesample code
remember to go into your project refrences and check the ADO activex data library :)

good luck

'Craig
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6822790
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.

This is the Community Support link, if help is needed, along with the link to All Topics since many new ones were recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thanks,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

704 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