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
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