PDA

View Full Version : I can't seem to get Recordsets loaded with data from SQL Server 2008 Express...



deyken
08-08-2011, 07:56 AM
Hi Guys,

I have been programming between Excel and a SQL Server 2008 Express Database for a while now, but today is just one of THOSE days. Below is a copy of my Code. I can't seem to get records back from the Database with even simple SQL Queries! Can anyone please check my code, I am probably missing something silly.

Appreciate it a lot!!

Sub LoadPOInfo()
' Load the PO from the Database
Dim conn As New ADODB.Connection
Dim POMaster As New ADODB.Recordset
Dim PODetail As New ADODB.Recordset
Dim PONumber As String
Dim i As Integer
Dim RowStart As Integer

conn.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING004\SQLEXPRESS;Database=Brolaz"
POMaster.Open "SELECT * FROM dbo.tblPO_MASTER WHERE tblPO_MASTER.PO_NUMBER = '" & Sheet1.Cells(11, "E") & "'", conn, adOpenDynamic, adLockOptimistic

If Sheet1.Cells(11, "E") = "" Then
MsgBox "You must enter a Purchase Order Number to continue!", vbCritical
Sheet1.Cells(11, "L") = ""
Else
PONumber = Sheet1.Cells(11, "E")
' begin with the load function here
Sheet1.Cells(11, "L") = "Loaded!"

' Now put the detail into the rows of the GR Note
Range("A22", "N43").ClearContents
PODetail.Open "SELECT * FROM dbo.tblPO_DETAIL WHERE tblPO_DETAIL.PO_NUMBER = '" & PONumber & "'", conn, adOpenDynamic, adLockOptimistic

If PODetail.RecordCount <= 0 Then
MsgBox "No Items were loaded for Purchase Order: " & PONumber, vbCritical
Else
RowStart = 22 ' hardcoded for now
MsgBox PODetail.RecordCount & " Items found!", vbInformation

For i = 0 To PODetail.RecordCount - 1
With Sheet1
.Cells(RowStart + i, "A") = PODetail.Fields("ENRTY_NO").Value
.Cells(RowStart + i, "B") = PODetail.Fields("DESCRIPTION").Value
.Cells(RowStart + i, "K") = PODetail.Fields("QUANTITY").Value
End With
Next i
End If
End If
conn.Close
End Sub

Aflatoon
08-08-2011, 08:13 AM
Is your PO_NUMBER field in the database actually text or numeric? Do queries without WHERE clauses work?

deyken
08-08-2011, 10:46 AM
Hi Aflatoon,

No. Not even straight Select * from <> seem to work.I test all my queries directly on SQL Server's Management Studio, where they work perfectly... Weird, hey?

What do you think it can be?

deyken
08-08-2011, 10:50 AM
BTW, PO_NUMBER is a string, it looks like this: 'BSAT60000'. I extract the '60000' and increment it by +1 for each next record. This works fine - as I test it on SQL Server - but now, no records in my ADODB.Recordset...

Kenneth Hobs
08-08-2011, 10:57 AM
I guess that you have the references set or you would have told us about an error.

Did you run this from a Module?

deyken
08-08-2011, 11:04 AM
Hi Kenneth,

I have the ADO Libraries rferenced, yes. As you can see in my code, I declare and initialize each ADODB object - as I have always done - , being the connection and recordsets. When I try to call on a Recorset's RecordCount property, it returns -1, although the recordset is supposed to deliver a result set of of 6 records. It does this directly in SQL Server in a query window - where I copie and pasted my Recordset' command to test it - yet, nothing in Excel.

Any ideas?

Kenneth Hobs
08-08-2011, 11:20 AM
Is LoadPOInfo in a Module?

While not SQLServer, this is code that I have used. Note how I gracefully exit. Maybe you should close Excel and try again to clear any remaining objects from memory.

Sub ADO()
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer, Row As Integer, s As String

'On Error GoTo EndNow
' Set window and calc off to speed updates
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn

' Database information
'DBFullName = "C:\myfiles\vbabook\Names.mdb"
DBFullName = "u:\Material\ADO\NWind.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
' Next two lines critical to work in QPro properly. Excel does not need them.
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
' Filter
Src = "SELECT * FROM Orders "
'Src = Src & "and CategoryID = 30"
Recordset.Open Source:=Src, ActiveConnection:=Connection

' Cells.Clear 'Used in Excel to clear a sheet
' Write the field names
'For Col = 0 To .Fields.Count - 1
'Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name 'Excel method
'Next

If .RecordCount < 1 Then GoTo EndNow 'Query found no matching records
' Write the recordset by Excel method
'Range("A1").Offset(1, 0).CopyFromRecordset Recordset
'Add a new record (not pushed to the database until Update)
MsgBox CStr(.RecordCount), vbInformation, "#Records"
.AddNew
Recordset("ShipName") = [Name!A2]
Recordset("ShipAddress") = [Address!B6]
Recordset("ShipCity") = Worksheets("City").Range("C3")
.Update
MsgBox CStr(.RecordCount), vbInformation, "#Records"

End With

EndNow:
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
' Reset window and calculation
SpeedOff
End Sub

deyken
08-08-2011, 12:57 PM
Hi Kenneth,

Thanks for the feedback. It really helps to connect with true experts.

I admit my code is rather rough, considering I rambled it down in minutes. I put virtually no thought into it. One gets used being able to both declare AND initialize a varibale in a single line, which then often leads to the forgetting of the memory consumed by its construction. I am experiencing a lot of this with VB.net and ASP.net development also. So far I know, only VB and C++/C# offer that convenience.

I certainly did not consider clearing the memory. I shall definitely give that a try. Your graceful exit is indeed such, I will follow suit.

Thanks again!

Aflatoon
08-08-2011, 01:16 PM
It seems I did not read your code closely enough. You should test for a recordcount to determine if records are returned since certain cursor types will return -1 rather than a true count. Better to test for EOF or BOF (if either are true, you have no records).
Also note that you are not actually looping through your recordset at the end, so you will merely return the same values over and over again.