Database problems


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Database problems

Hybrid View

  1. #1
    Robert Rieth Guest

    Database problems


    I have had a number of errors doing SQL programming with in my Visual Basic
    Application. This is the latest one and it seems to have finaly stoped me.
    If anyone could give me the solution as to what is wrong I would be very
    greatful. Here is the Code to both the Application and Module that uses
    the Database.
    APPLICATION:

    Private Sub SaveData_Click()
    Dim con As adodb.Connection
    Set con = New adodb.Connection
    con.Open "dsn=LifeLinePD"
    Dim rstAdmission As adodb.Recordset
    Set rstAdmission = New adodb.Recordset
    Dim SQLstr As String
    Dim Last_Name As String, First_Name As String, Middle_Initial As String,
    PhoneNum As String
    Dim PAddress As String, RCity As String, RState As String, RZip As Integer,
    RMonth As String
    Dim RDate As Integer, RYear As Integer, SSNumber As Integer, MaritalStat
    As String
    Dim ReligousStat As String, Church_Name As String, CCity As String, RFuneralHome
    As String
    Dim RRace As String, ADate As Date, AdmitText As String, AdmitAdressTxt
    As String, AdmitCityTxt As String
    Dim AdmitCombo As String, OMRecordNum As Integer, RHospitalPref As String,
    RVAClaim As String
    Dim RMedicadeNo As String, RPrivateI As String, AllergieList As ListBoxConstants
    Dim PLastName As String, PFirstName As String, PRelation As String, ELName
    As String
    Dim EFName As String, EHCA As String, EDREP As String, ERelation As String
    Dim EAddress As String, EHPhone As String, EWPhone As String, ECPager
    As String
    Dim ECellPhone As String, NOK1LName As String, NOK1FName As String
    Dim NOK1HCA As String, NOK1DREP As String, NOK1Rel As String, NOK1Address
    As String
    Dim NOK1HPhone As String, NOK1WPhone As String, NOK1PNumber As String,
    NOK1Cell As String
    Dim NOK2LName As String, NOK2FName As String, NOK2Rel As String, NOK2Address
    As String
    Dim NOK2HPhone As String, NOK2WPhone As String, NOK2PNumber As String,
    NOK2Cell As String
    Dim AttPhyLN As String, AttPhyFN As String, AttPhyAddress, AttPhyHPhone,
    AttPhyWPhone
    Dim AttPhyPNum As String, AttPhyCNumber, AltPhyLN As String, AltPhyFN
    As String
    Dim AltPhyAddress As String, AltPhyHPhone As String, AltPhyWPhone, AltPhyPNumber
    As String
    Dim AltPhyCNumber As String, PPodiatrist As String, PDentist As String,
    SQLQUERY As QueryDef, LifeLinePD As Database
    Set QueryDef
    rstAdmission.Open "Select * from LifeLinePD WHERE LastName = '123456789'",
    con, adOpenStatic
    With Admission
    SQLstr = "INSERT INTO LifeLinePD (LastName, FirstName, MiddleInitial,
    " & _
    "PPhoneNumber, Address, City, State, Zip) " & " values ('" & Last_Name
    & _
    "','" & First_Name & "','" & Middle_Initial & "','" & PhoneNum &
    "','" & _
    PAddress & "','" & RCity & "','" & RState & "','" & RZip & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD(BDay,BMonth,BYear,SocialSecurityNumber,
    " & _
    "MaritalStatus, ReligousStatus) " & " values ('" & RMonth & "','"
    & RDate & _
    "','" & RYear & "','" & SSNumber & "','" & MaritalStat & "','" &
    ReligousStat & "',"
    SQLstr = "INSERT INTO LifeLinePD (ChurchName,ChurchCity,FuneralHome,Race,
    " & _
    "AdvancedDirectives) " & " values ('" & Church_Name & "','" & CCity
    & "','" & RFuneralHome & _
    "','" & RRace & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD.LifeLinePD (AdmitDate,AdmitFrom,AdmitAddress,
    " & _
    "AdmitCity) " & " values ('" & ADate & "','" & AdmitText & "','"
    & AdmitAdressTxt & _
    "','" & AdmitCityTxt & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (AdmitState,MedicalRecordNum,HospitalPrefrence,
    " & _
    "VAClaaimNum,MedicadeNum) " & " values ('" & AdmitCombo & "','" &
    OMRecordNum & _
    "','" & RHospitalPref & "','" & RVAClaim & "','" & RMedicadeNo &
    "')"
    SQLstr = "INSERT INTO LifeLinePD (Private,Allergies) " & " values
    ('" & RPrivateI & "','" & AllergieList & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (LPaymentPerson,FPaymentPerson,PatientRelation,
    " & _
    "ECLastName,ECFirstName) " & " values ('" & PLastName & "','" & PFirstName
    & _
    "','" & PRelation & "','" & ELName & "','" & EFName & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (ECHCA,ECDRep,ECRelation,ECAddress
    " & _
    "ECHomePhone,ECWorkNumber) " & " values ('" & EHCA & "','" & EDREP
    & _
    "','" & ERelation & "','" & EAddress & "','" & EHPhone & "','" &
    EWPhone & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (ECPagerNumber,ECCellPhone,NextofKin1LN,NextofKin1FN
    " & _
    "NextofKin1HPA,NextofKin1DRep) " & " values ('" & ECPager & "','"
    & ECellPhone & _
    "','" & NOK1LName & "','" & NOK1FName & "','" & NOK1HCA & "','" &
    NOK1DREP & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (NextofKin1Relation,NextofKin1Address,NextofKin1HPhone,NextofKin1WPhone
    " & _
    "NextofKin1PNumber,NextofKin1CNumber) " & " values ('" & NOK1Rel
    & "','" & NOK1Address & _
    "','" & NOK1HPhone & "','" & NOK1WPhone & "','" & NOK1PNumber & "','"
    & NOK1Cell & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (NextofKin2LN,NextofKin2FN,NextofKin1HPA,NextofKin2DRep
    " & _
    "NextofKin2Relation,NextofKin2Address) " & " values ('" & NOK2Rel
    & "','" & NOK2Address & _
    "','" & NOK2HPhone & "','" & NOK2WPhone & "','" & NOK2PNumber & "','"
    & NOK2Cell & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (AttPhyciaianLN,AttPhyciaianFN,AttPhyciaianAddress,AttPhyciaianHPhone
    " & _
    "AttPhyciaianWPhone,AttPhyciaianPNumber) " & " values ('" & AttPhyLN
    & "','" & AttPhyFN & _
    "','" & AttPhyAddress & "','" & AttPhyHPhone & "','" & AttPhyWPhone
    & "','" & AttPhyPNumber & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (AttPhyciaianCNumber,AltPhyciaianLN,AltPhyciaianFN,AltPhyciaianAddress
    " & _
    "AltPhyciaianHPhone,AltPhycciaianCNumber) " & " values ('" & AttPhyCNumber
    & "','" & AltPhyLN & _
    "','" & AltPhyFN & "','" & AltPhyAddress & "','" & AltPhyHPhone &
    "','" & AltPhyWPhone & "')"
    SQLpro SQLstr, rstAdmission
    SQLstr = "INSERT INTO LifeLinePD (AltPhyciaianPNumber,AltPhycciaianCNumber,PodiatristName,DentistName)
    " & _
    " values ('" & AltPhyPNumber & "','" & AltPhyCNumber & "'," & PPodiatrist
    & "','" & PDentist & "')"
    SQLpro SQLstr, rstAdmission
    End With
    LifeLinePD.Execute SQLQUERY
    con.Close
    Set con = Nothing

    End Sub

    MODULE:

    Option Explicit
    Public Sub SQLpro(SQLstr As String, Record_Set As adodb.Recordset)
    Dim con As adodb.Connection
    Set con = New adodb.Connection

    con.Open "dsn=LifeLinePD"

    Dim SQLCmd As New adodb.Command
    Set SQLCmd = New adodb.Command 'initialize the command object
    With SQLCmd
    Set .ActiveConnection = con 'set the active connection
    .CommandType = adCmdText 'set command type as text (SQL)
    .CommandText = SQLstr 'set the command = the passed SQL string
    ADOCmd.Execute 'Execute the command
    End With
    con.Close
    Set con = Nothing
    Record_Set.Close
    Set Record_Set = Nothing
    'SQLCmd.Close
    'Set SQLCmd = Nothing
    End Sub

    The error that I am getting is...
    Compile error:
    user-defined type not defined

    It highlights this part of the Code in the Application
    SQLQUERY As QueryDef

    Someone told me I didn't need to Dim it, but VB disagrees.
    What can I do to fix the error and finaly get the Application to start saving
    data to my LifeLinePD Database.

    Any help would be appreciated. : )
    Thank You.

  2. #2
    Andrew Grillage Guest

    Re: Database problems

    You must use Dim, Private, Public or Static when declaring variables. There
    is no avoiding it.

    --
    Andrew Grillage
    http://vbdata.iwarp.com


    "Robert Rieth" <paparieth1@juno.com> wrote in message
    news:38f0f19e$1@news.devx.com...
    >
    > I have had a number of errors doing SQL programming with in my Visual

    Basic
    > Application. This is the latest one and it seems to have finaly stoped

    me.
    > If anyone could give me the solution as to what is wrong I would be very
    > greatful. Here is the Code to both the Application and Module that uses
    > the Database.
    > APPLICATION:
    >
    > Private Sub SaveData_Click()
    > Dim con As adodb.Connection
    > Set con = New adodb.Connection
    > con.Open "dsn=LifeLinePD"
    > Dim rstAdmission As adodb.Recordset
    > Set rstAdmission = New adodb.Recordset
    > Dim SQLstr As String
    > Dim Last_Name As String, First_Name As String, Middle_Initial As

    String,
    > PhoneNum As String
    > Dim PAddress As String, RCity As String, RState As String, RZip As

    Integer,
    > RMonth As String
    > Dim RDate As Integer, RYear As Integer, SSNumber As Integer,

    MaritalStat
    > As String
    > Dim ReligousStat As String, Church_Name As String, CCity As String,

    RFuneralHome
    > As String
    > Dim RRace As String, ADate As Date, AdmitText As String,

    AdmitAdressTxt
    > As String, AdmitCityTxt As String
    > Dim AdmitCombo As String, OMRecordNum As Integer, RHospitalPref As

    String,
    > RVAClaim As String
    > Dim RMedicadeNo As String, RPrivateI As String, AllergieList As

    ListBoxConstants
    > Dim PLastName As String, PFirstName As String, PRelation As String,

    ELName
    > As String
    > Dim EFName As String, EHCA As String, EDREP As String, ERelation As

    String
    > Dim EAddress As String, EHPhone As String, EWPhone As String, ECPager
    > As String
    > Dim ECellPhone As String, NOK1LName As String, NOK1FName As String
    > Dim NOK1HCA As String, NOK1DREP As String, NOK1Rel As String,

    NOK1Address
    > As String
    > Dim NOK1HPhone As String, NOK1WPhone As String, NOK1PNumber As String,
    > NOK1Cell As String
    > Dim NOK2LName As String, NOK2FName As String, NOK2Rel As String,

    NOK2Address
    > As String
    > Dim NOK2HPhone As String, NOK2WPhone As String, NOK2PNumber As String,
    > NOK2Cell As String
    > Dim AttPhyLN As String, AttPhyFN As String, AttPhyAddress,

    AttPhyHPhone,
    > AttPhyWPhone
    > Dim AttPhyPNum As String, AttPhyCNumber, AltPhyLN As String, AltPhyFN
    > As String
    > Dim AltPhyAddress As String, AltPhyHPhone As String, AltPhyWPhone,

    AltPhyPNumber
    > As String
    > Dim AltPhyCNumber As String, PPodiatrist As String, PDentist As

    String,
    > SQLQUERY As QueryDef, LifeLinePD As Database
    > Set QueryDef
    > rstAdmission.Open "Select * from LifeLinePD WHERE LastName =

    '123456789'",
    > con, adOpenStatic
    > With Admission
    > SQLstr = "INSERT INTO LifeLinePD (LastName, FirstName,

    MiddleInitial,
    > " & _
    > "PPhoneNumber, Address, City, State, Zip) " & " values ('" &

    Last_Name
    > & _
    > "','" & First_Name & "','" & Middle_Initial & "','" & PhoneNum &
    > "','" & _
    > PAddress & "','" & RCity & "','" & RState & "','" & RZip & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO

    LifeLinePD(BDay,BMonth,BYear,SocialSecurityNumber,
    > " & _
    > "MaritalStatus, ReligousStatus) " & " values ('" & RMonth & "','"
    > & RDate & _
    > "','" & RYear & "','" & SSNumber & "','" & MaritalStat & "','" &
    > ReligousStat & "',"
    > SQLstr = "INSERT INTO LifeLinePD

    (ChurchName,ChurchCity,FuneralHome,Race,
    > " & _
    > "AdvancedDirectives) " & " values ('" & Church_Name & "','" &

    CCity
    > & "','" & RFuneralHome & _
    > "','" & RRace & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD.LifeLinePD

    (AdmitDate,AdmitFrom,AdmitAddress,
    > " & _
    > "AdmitCity) " & " values ('" & ADate & "','" & AdmitText & "','"
    > & AdmitAdressTxt & _
    > "','" & AdmitCityTxt & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (AdmitState,MedicalRecordNum,HospitalPrefrence,
    > " & _
    > "VAClaaimNum,MedicadeNum) " & " values ('" & AdmitCombo & "','" &
    > OMRecordNum & _
    > "','" & RHospitalPref & "','" & RVAClaim & "','" & RMedicadeNo &
    > "')"
    > SQLstr = "INSERT INTO LifeLinePD (Private,Allergies) " & " values
    > ('" & RPrivateI & "','" & AllergieList & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (LPaymentPerson,FPaymentPerson,PatientRelation,
    > " & _
    > "ECLastName,ECFirstName) " & " values ('" & PLastName & "','" &

    PFirstName
    > & _
    > "','" & PRelation & "','" & ELName & "','" & EFName & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (ECHCA,ECDRep,ECRelation,ECAddress
    > " & _
    > "ECHomePhone,ECWorkNumber) " & " values ('" & EHCA & "','" & EDREP
    > & _
    > "','" & ERelation & "','" & EAddress & "','" & EHPhone & "','" &
    > EWPhone & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (ECPagerNumber,ECCellPhone,NextofKin1LN,NextofKin1FN
    > " & _
    > "NextofKin1HPA,NextofKin1DRep) " & " values ('" & ECPager & "','"
    > & ECellPhone & _
    > "','" & NOK1LName & "','" & NOK1FName & "','" & NOK1HCA & "','" &
    > NOK1DREP & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (NextofKin1Relation,NextofKin1Address,NextofKin1HPhone,NextofKin1WPhone
    > " & _
    > "NextofKin1PNumber,NextofKin1CNumber) " & " values ('" & NOK1Rel
    > & "','" & NOK1Address & _
    > "','" & NOK1HPhone & "','" & NOK1WPhone & "','" & NOK1PNumber &

    "','"
    > & NOK1Cell & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (NextofKin2LN,NextofKin2FN,NextofKin1HPA,NextofKin2DRep
    > " & _
    > "NextofKin2Relation,NextofKin2Address) " & " values ('" & NOK2Rel
    > & "','" & NOK2Address & _
    > "','" & NOK2HPhone & "','" & NOK2WPhone & "','" & NOK2PNumber &

    "','"
    > & NOK2Cell & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (AttPhyciaianLN,AttPhyciaianFN,AttPhyciaianAddress,AttPhyciaianHPhone
    > " & _
    > "AttPhyciaianWPhone,AttPhyciaianPNumber) " & " values ('" &

    AttPhyLN
    > & "','" & AttPhyFN & _
    > "','" & AttPhyAddress & "','" & AttPhyHPhone & "','" &

    AttPhyWPhone
    > & "','" & AttPhyPNumber & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (AttPhyciaianCNumber,AltPhyciaianLN,AltPhyciaianFN,AltPhyciaianAddress
    > " & _
    > "AltPhyciaianHPhone,AltPhycciaianCNumber) " & " values ('" &

    AttPhyCNumber
    > & "','" & AltPhyLN & _
    > "','" & AltPhyFN & "','" & AltPhyAddress & "','" & AltPhyHPhone &
    > "','" & AltPhyWPhone & "')"
    > SQLpro SQLstr, rstAdmission
    > SQLstr = "INSERT INTO LifeLinePD

    (AltPhyciaianPNumber,AltPhycciaianCNumber,PodiatristName,DentistName)
    > " & _
    > " values ('" & AltPhyPNumber & "','" & AltPhyCNumber & "'," &

    PPodiatrist
    > & "','" & PDentist & "')"
    > SQLpro SQLstr, rstAdmission
    > End With
    > LifeLinePD.Execute SQLQUERY
    > con.Close
    > Set con = Nothing
    >
    > End Sub
    >
    > MODULE:
    >
    > Option Explicit
    > Public Sub SQLpro(SQLstr As String, Record_Set As adodb.Recordset)
    > Dim con As adodb.Connection
    > Set con = New adodb.Connection
    >
    > con.Open "dsn=LifeLinePD"
    >
    > Dim SQLCmd As New adodb.Command
    > Set SQLCmd = New adodb.Command 'initialize the command object
    > With SQLCmd
    > Set .ActiveConnection = con 'set the active connection
    > CommandType = adCmdText 'set command type as text (SQL)
    > CommandText = SQLstr 'set the command = the passed SQL string
    > ADOCmd.Execute 'Execute the command
    > End With
    > con.Close
    > Set con = Nothing
    > Record_Set.Close
    > Set Record_Set = Nothing
    > 'SQLCmd.Close
    > 'Set SQLCmd = Nothing
    > End Sub
    >
    > The error that I am getting is...
    > Compile error:
    > user-defined type not defined
    >
    > It highlights this part of the Code in the Application
    > SQLQUERY As QueryDef
    >
    > Someone told me I didn't need to Dim it, but VB disagrees.
    > What can I do to fix the error and finaly get the Application to start

    saving
    > data to my LifeLinePD Database.
    >
    > Any help would be appreciated. : )
    > Thank You.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center