• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Verify and/or Recommend Code to Insert and Select Data from SQL Database

I've attached a document that has some code that I've modified and would like to use in a worksheet, which both Inserts and then Selects Data to be imported into Excel.  The queries and connection strings work fine.  We are currently using them within SSMS and the data is published to a grid within SSMS.  

Normally, we would just copy and paste the data, but lately there seems to be a memory problem within SSMS that prevents us from being able to do this all at once.

I would like for an EE who knows SQL Data to look over the code, and/or make recommendations on a best process/practice for doing this, if it seems there may be issues.
INSERT-and-SELECT-Data-from--SQL.docx
0
Cook09
Asked:
Cook09
  • 10
  • 6
1 Solution
 
RyanProject Engineer, ElectricalCommented:
Are you closing and releasing your cmd and con objects from the first INSERT functions?

The SQL itself is pretty a pretty simple Select * FROM table.  The only SQL change you could do here is select less columns, or less rows, but it looks like you're using everything, so nothing to do there.

I personally don't like to use late binding if I don't have to, but I don't think that causes any issues, just less error checking by the IDE/compiler.

I'm not understanding what the problem is, and I don't see anything obviously wrong with the code.
0
 
Cook09Author Commented:
Mr. Bullwinkle,

I don't have have to release the cmd and con objects, that was one area that I need to look at. Where would be the best place to comment out the second cmd and con?

I think I need to set a Target.Row and Target.Column, which I guess would be 1,1.  It seems that the data is is added row by row, is there a faster way to paste the entire data array in one shot?

The SELECT is as it is, they want all the columns and rows that will be a part of the Insert statement.  Does target.worksheet also assume that it could be the active worksheet, or does that need to be specified?

I can set the statements for an early bind, as mentioned previously, I modified a couple of other snippets.

Regards,
Ron
0
 
RyanProject Engineer, ElectricalCommented:
CopyFromRecordset is as quick as it gets, as far as I know, though Excel will likely display seemingly row by row as it trys to refresh often in the middle of the process.

You would get some performance improvement by turning off screen update, so it doesn't waste time refreshing/calulating mid process.

Application.ScreenUpdating=false
...CopyFromRecordet
Application.ScreenUpdating=true

To release cmd and con once done using it...  Ideally you open and close connections as soon as possible.
cmd.Close
set cmd=nothing
con.close
set con=nothing
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Cook09Author Commented:
Could you help me with a syntax issue?  When I try to set:
query = Select * from..... it turns red and lets me know that I have syntax issue.  How should that be written?
0
 
Cook09Author Commented:
I think I may have figured it out...placing "  " quotes around the statement.  Would that be correct?
0
 
RyanProject Engineer, ElectricalCommented:
yes, everything after the = is a string, and should be surrounded by double quotes
0
 
Cook09Author Commented:
Now the fun starts:
As I put in the Insert Statements, all kinds of syntax errors arise?
When it starts with a (
                                 USI,
                                   Lo_Cond_ID,
                                    etc,  
                                          )
how should that be bracketed...each one, but then I get an expected line number error.
or
Group by COQA.USI,   --- it's looking for something like Expected End of Statement
I guess I don't know the syntax rules from Excel to SQL.

Ron
0
 
RyanProject Engineer, ElectricalCommented:
Why don't you just include the whole code rather than having separate questions for every line?  You can make the entire query 1 line. SQL doesn't care about white space (excess spaces, tabs, enter)
0
 
Cook09Author Commented:
You're right, and the Insert Statement was a little ambitious...it is very long and complex.  As that seemed to function correctly, the real need was in the SELECT statement from Excel to SQL.  Based upon your suggestions, I narrowed it down to two sections.
1. A Sub that defines what is needed in the Function
2. The Function which does the heavy lifting.

The trimmed down code is:

Sub GetSQLData()
Dim query As String
Dim conString As String
Dim con As Object
Dim cmd As Object
Dim intReturn As Integer

   Const conString = "driver={sql server}; Server=SQL Server,xxxx;database=db1;uid=user1;pwd=password1;"
   Set rng = ActiveSheet.Range("A1")
   query = "SELECT * from Database_Table_RAW_DATA"

          'Call to Function
    Call ImportSQLtoRange(conString, query, rng)
          'Function Return
     If ImportSQLtoRange = 0 Then
        Exit Sub
       Else: MsgBox "Failed to Import SQL Data"
    End If
   Application.ScreenUpdating = True
End Sub

Open in new window


Function ImportSQLtoRange(ByVal conString As String, ByVal query As String, _
    ByVal target As Range) As Integer
  Dim con As Object, cmd As Object, rst As Object
  Dim ws As Worksheet
  Dim col As Integer
  Dim intReturn As Integer
  
Application.ScreenUpdating = False
On Error Resume Next

  Set ws = target.Worksheet
  Set con = CreateObject("ADODB.Connection")
  Set cmd = CreateObject("ADODB.Command")
   con.ConnectionString = conString
   con.Open
   cmd.ActiveConnection = con

   cmd.CommandText = query
   cmd.CommandType = 1         ' adCmdText
        
  Set rst = cmd.Execute
    If rst Is Nothing Then
     con.Close
     Set con = Nothing
     cmd.Close
     Set cmd = Nothing
    ImportSQLtoRange = 1
   intReturn = ImportSQLtoRange
    Exit Function
   End If

    ' Data from Recordset
    ws.Cells(target.Row + 1, target.Column).CopyFromRecordset rst
    
    cmd.Close
    Set cmd = Nothing
    rst.Close
    Set rst = Nothing
    con.Close
    Set con = Nothing
     
   ImportSQLtoRange = 0

End Function

Open in new window

Would appreciate your comments and insight.
Ron
0
 
Cook09Author Commented:
While I have to go for the evening, the issue that I found in running it, was that no data was returned.  When I ran just the SELECT from within SSMS, then I started to have data returned. There were no errors from the code itself.

Best Regards,
Ron
0
 
Cook09Author Commented:
I think I may have found part of the issue which I'll investigate tomorrow.  While the conString does work, it was for another Server and Database.  I'll change it tomorrow and let you know.

Ron
0
 
RyanProject Engineer, ElectricalCommented:
Connecting to the proper database does help!

I'm not sure about that CommandType = 1.  From what I'm seeing, it should be a 2, xlCmdSQL.

I'm not seeing anything obvious that wouldn't work.  There a lot of coding  I would reconsider, but it just needs to work.
0
 
Cook09Author Commented:
Mr. Bullwinkle,
Attached is a spreadsheet that I was able to get to work properly; attach to the server's database, pull the data in through a query, and then format it per the user requirements.

The one issue I have is with column S.  The Cells do not have a value in them, as a number of other cells.  The other cells which don't have a value in them, have NULL placed in their cell. Plus, there are some other requirements that I have working, except Column S.  For whatever reason, the code will not put a NULL value in its cells.  Would you by chance know why?
ImportSQLData.xlsm
0
 
Cook09Author Commented:
I had to write a routine just to take care of the Cells in Column S...Special Cells.

The biggest I'm facing now is trying to bring back the Headers.  The data seems easy enough now, but I haven't a way to bring back the headers.  Would you know how that is done?
0
 
RyanProject Engineer, ElectricalCommented:
To get the headers, after you copy the recordset,

    i = 0
    For Each Field In rs.Fields
        wks.Cells(1, i) = (Field.Name)
        i = i + 1
    Next

Open in new window


As for your problem with S column, it's going to put int whatever the query returns, whether its NULLs, 0s, or blank (empty string).
0
 
Cook09Author Commented:
Thanks, I appreciate your patience and help with the Table.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now