Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unanswered: Update a field in an SQL database from VB6?

    Hi All,

    I have a number of Crystal Reports where I use VB6 to pass parameters to them and to create executables. These all work fine.

    Now I need to update a field on a table to show that the report has been printed so that it is only printed once.

    This is the VB code to access the database - Can anyone show me what I need to do to open the table (oeim) and update the field (oeim_printed) for the parameter that is being passed to VB?

    Dim Report As New CrystalReport1

    Private Sub Form_Load()
    Screen.MousePointer = vbHourglass
    Report.Database.LogOnServer "p2sodbc.dll", "server1", "MAX112live", "maxmast", "mcs000"
    Report.ParameterFields(1).AddCurrentValue (Command)
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
    Screen.MousePointer = vbDefault

    End Sub

    Many thanks....

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Code:
    Dim Report As New CrystalReport1
    
    Private Sub Form_Load()
    Screen.MousePointer = vbHourglass
    Report.Database.LogOnServer "p2sodbc.dll", "server1", "MAX112live", "maxmast", "mcs000"
    Report.ParameterFields(1).AddCurrentValue (Command)
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
    Screen.MousePointer = vbDefault
    
    Dim CN as Adodb.Connection
    set CN = New Adodb.Connection
    CN.Open "YourConnectionString"
    
    CN.Execute "Update oeim set oeim_printed = 'Your Parameter Data' Where YourReportName = 'ReportName'"
    
    CN.Close
    Set CN = Nothing
    
    End Sub
    Your app probably already has a connection open, so you could use it instead.

    If you don't specify which record is to be update, they all will, which is why you need to specify the recurd using the SQL WHERE clause.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Aug 2004
    Posts
    15
    Hi Loquin,

    Thanks for the help but when I try to compile/run the code it errors on the line:

    Dim CN as Adodb.Connection - with a "User defined type not defined" error.

    also where you have the following line:

    CN.Open "YourConnectionString"

    ... should the "YourConnectionString" be replaced with

    "p2sodbc.dll", "server1", "MAX112live", "maxmast", "mcs000"

    As you might have guessed I am not a programmer so excuse me if the questions are a bit noddy.

    Thanks again

    nomis

  4. #4
    Join Date
    Aug 2004
    Posts
    15
    Thanks to the link from Loquin I found the "Top Ten Tutorials" and the "Beginners Guide to ADO" which solved my first problem which was to add the "Microsoft ActiveX Data Objects 2.x Library" from the project menu.

    However I am still struggling with the connection string.

    CN.ConnectionString = "p2sodbc.dll & max & MAX112albr & maxmast & mcs123"
    CN.Open

    Is what I have at the moment but this returns an error of 'Data source name too long" on the CN.Open line.

    Any hints, shoves in the right direction or explanations would be gratefully accepted. ;-)

    Nomis

  5. #5
    Join Date
    Aug 2004
    Posts
    15
    Problem solved....

    Dim CN As ADODB.Connection
    Set CN = New ADODB.Connection
    Set cnMyConnection = New ADODB.Connection
    CN.Open "DSN=albr;" & "Uid=maxmast;" & "Pwd=mcs123"

    CN.Execute etc.....

    I found a link to connection strings from the extreme visual basic site and by giving all the potential connection types I was finally able to make sense of where they all should be used.

    this is the link if anyone is interested:
    http://www.able-consulting.com./ADO_Conn.htm

    Again thanks to Loquin for pointing me in the right direction.

  6. #6
    Join Date
    Sep 2004
    Posts
    1
    [QUOTE=nomis]Thanks to the link from Loquin I found the "Top Ten Tutorials" and the "Beginners Guide to ADO" which solved my first problem which was to add the "Microsoft ActiveX Data Objects 2.x Library" from the project menu.

    However I am still struggling with the connection string.

    CN.ConnectionString = "p2sodbc.dll & max & MAX112albr & maxmast & mcs123"
    CN.Open

    Is what I have at the moment but this returns an error of 'Data source name too long" on the CN.Open line.

    Any hints, shoves in the right direction or explanations would be gratefully accepted. ;-)

    Nomis

    Ok the best way to set up your connection string is to place an ADO data control on your form and go through the connection string settings in propertys then simply copy and paste into your code! this way is very quick and is common practicematt_carter_25

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •