Solved

Exporting Data from SQL Server

Posted on 2002-03-22
5
135 Views
Last Modified: 2010-05-02
I have a table in Microsoft SQL server with the following fields...first ; last ; middle ; ss# ; address ; phone ..etc..

I have a form with text boxes where you either enter the ss# or the first;last;middle name.  I need to know how I would tell the SQL server to take either the records for the ss# or the name that I entered and export it into a text delimited format?   I want all the fields for that person....could someone give me an example?  

Thanks,

Zippy21
0
Comment
Question by:zippy21
[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
  • 2
5 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 300 total points
ID: 6889506
if you have an ADO recordset containing the data you want to be copied to a CSV file, just pass it to this function:

Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal pstrFileName As String)
Dim intFileNumber As Integer
Dim lngLoop As Long
Dim strX As String

    If Len(Trim$(pstrFileName)) = 0 Then pstrFileName = App.Path & "\zzRst.csv"
   
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFileName For Output As #intFileNumber
    'Write data to file
    With prstData
        strX = ""
        For lngLoop = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop).Name & ", "
        Next lngLoop
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & ", "
            Next lngLoop
            Print #intFileNumber, strX
            .MoveNext
        Loop
    End With
    'Close
    Close #intFileNumber
End Sub
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6889560
You'll need to have a Reference set to the Microsoft ActiveX Data Objects Library.  This will show you how to format the SQL as well as actually print the file...


Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

Private Sub Command1_Click()
Dim sSQL As String
Dim j As Long
Dim fHandle As Long
Dim sTemp As String
Dim sSepValue As String

'    sSepValue = ","
    sSepValue = vbTab
   

    sSQL = "SELECT * FROM user_base WHERE USER_NO = USER_NO"
    If txtSSN.Text <> "" Then
        sSQL = sSQL & " AND USER_SSN = '" & txtSSN.Text & "'"
    End If
    If txtFirst.Text <> "" Then
        sSQL = sSQL & " AND USER_FIRSTNAME = '" & txtFirst.Text & "'"
    End If
    If txtMiddle.Text <> "" Then
        sSQL = sSQL & " AND USER_MIDDLENAME = '" & txtMiddle.Text & "'"
    End If
    If txtLast.Text <> "" Then
        sSQL = sSQL & " AND USER_LASTNAME = '" & txtLast.Text & "'"
    End If

    Set RS = New ADODB.Recordset
    RS.LockType = adLockBatchOptimistic
    RS.CursorType = adOpenStatic
    RS.ActiveConnection = CN
    RS.Source = sSQL
    RS.Open
    If Not RS.State = adStateOpen Then
        MsgBox "Recordset Open Failed"
        Exit Sub
    End If

    fHandle = FreeFile
   
    Open "H:\Code\Temp.csv" For Output As fHandle

    While Not RS.EOF
        sTemp = ""
        For j = 0 To RS.Fields.Count - 1
            sTemp = sTemp & RS.Fields(j).Value & sSepValue
        Next j
        If Len(sTemp) > 0 Then
            sTemp = Left(sTemp, Len(sTemp) - 1)
            Print #fHandle, sTemp
        End If
        RS.MoveNext
    Wend
   
    RS.Close
   
    Close fHandle
    MsgBox "File saved to H:\Code\Temp.csv"
   
    Me.Cls
    fHandle = FreeFile
    Open "H:\Code\Temp.csv" For Input As fHandle
    While Not EOF(fHandle)
        Line Input #fHandle, sTemp
        Me.Print sTemp
    Wend
   
    Close fHandle
   
End Sub

Private Sub Form_Load()

Set CN = New ADODB.Connection
CN.CursorLocation = adUseClient
CN.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;DRIVER=SQL Server;SERVER=myserver;DATABASE=mydb;Trusted_Connection=Yes;Initial Catalog=mydb"
CN.Open
If Not CN.State = adStateOpen Then
    MsgBox "Connection Failed"
    Exit Sub
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)

CN.Close
Set CN = Nothing


End Sub
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6889572
By the way, in the Select statement, I have "WHERE USER_NO = USER_NO"

The reason I did this was because I may have zero to four fields to search on, and I want to simplify the code for appending the "AND" statements.  So, I need something that I can always code as the first "WHERE" clause, something that will always be true.  So, I pick the primary key field, and say that it equals itself.  This will always be true.  Then, I can just append an "AND" for any criteria that is supplied.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7060595
This question appears to be abandoned. A question regarding it will be left in the Community Support
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to accept the comment
of <emoreau>.

The link to the Community Support area is:
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7065686
Thanks, emoreau  for your help here.

I have finalized this question, and will monitor it for comments.

Moondancer - EE Moderator
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 23 hours left to enroll

627 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