Good day,
I'm currently trying to print the data in my database using Visual Basic but I have a little problem with the codes, need your help about figuring out what I might be missing. I keep on getting the same error Run-time error '-2147217900 (80040e14)' - Dim adoHistoryPrint As New ADODB.Connection
-
Dim recHistoryPrint As New ADODB.Recordset
-
Dim cmdHistoryPrint As New ADODB.Command
-
Dim rsHistoryPrint As New ADODB.Recordset
-
-
-
Private Sub cmdPrintReceivingHistory_Click()
-
Dim i As Integer
-
-
adoHistoryPrint.Open "Provider=SQLOLEDB.1;Password=Pword;Persist Security Info=True;User ID=User;Initial Catalog=DBase;Data Source=DATABASE"
-
-
With cmdHistoryPrint
-
.ActiveConnection = adoHistoryPrint
-
.CommandType = adCmdText
-
.CommandText = " SHAPE {" & adoReceivingHistory.RecordSource & "} AS Command1 COMPUTE Command1 BY 'ItemType'"
-
.Execute
-
End With
-
-
With recHistoryPrint
-
.ActiveConnection = adoHistoryPrint
-
.CursorLocation = adUseClient
-
.Open cmdHistoryPrint
-
End With
-
-
With rptReceivingHistory
-
Set .DataSource = Nothing
-
.DataMember = ""
-
Set .DataSource = rsHistoryPrint
-
-
With .Sections("ItemType").Controls
-
For i = 1 To .Count
-
If TypeOf .Item(i) Is RptTextBox Then
-
.Item(i).DataMember = ""
-
.Item(i).DataField = "ItemType"
-
End If
-
Next i
-
End With
-
-
With .Sections("ReceivingDetails").Controls
-
For i = 1 To .Count
-
If TypeOf .Item(i) Is RptTextBox Then
-
.Item(i).DataMember = ""
-
End If
-
Next i
-
End With
-
-
.Show
-
-
End With
-
-
End Sub
9 3972
What version of VB, and where does the error occur?
I'm using Visual Basic 6 the database is SQL Server 2005
I'm using Visual Basic 6 the database is SQL Server 2005
By "where" I meant "at what line in the code".
sorry i forgot to paste the code :D - With cmdHistoryPrint
-
.ActiveConnection = adoHistoryPrint
-
.CommandType = adCmdText
-
.CommandText = " SHAPE {" & adoReceivingHistory.RecordSource & "} AS Command1 COMPUTE Command1 BY 'ItemType'"
-
.Execute
I've never heard of a SHAPE command before, so don't know whether I'm likely to be any help with this. I did wonder when reading it, whether perhaps ItemType should not have quotes around it, in line 4.
Presumably it was the Execute which returned the error?
the Shape command is used for grouped reports. I'm also new with this command. As I have done some research regarding the error. It might have caused either by my
.CommandText = "SHAPE {SELECT * FROM ReceivingHistory} AS ReceivingDetails COMPUTE ReceivingDetails BY ItemType"
or by
adoHistoryPrint.Open "Provider=SQLOLEDB.1;Password=Pword;Persist Security Info=True;User ID=User;Initial Catalog=DBase;Data Source=DATABASE"
I have tried doing the same thing with Just 1 Form, 1 Command Button, and 1 Data Report with these codes using NWIND.MDB -
Dim cn As New ADODB.Connection
-
Dim rs As New ADODB.Recordset
-
Dim cmd As New ADODB.Command
-
Dim rs1 As New ADODB.Recordset
-
Private Sub Command1_Click()
-
Dim q As Integer
-
Dim intCtrl As Integer
-
Dim x As Integer
-
Dim z As Integer
-
x = 0
-
q = 0
-
z = 0
-
-
With DataReport1
-
.Hide
-
Set .DataSource = rs
-
.DataMember = ""
-
-
With .Sections("section4").Controls
-
For intCtrl = 1 To .Count
-
If TypeOf .Item(intCtrl) Is RptLabel Then
-
.Item(intCtrl).Caption = "City" & " :"
-
q = q + 1
-
End If
-
If TypeOf .Item(intCtrl) Is RptTextBox Then
-
.Item(intCtrl).DataMember = ""
-
.Item(intCtrl).DataField = "City"
-
End If
-
Next
-
End With
-
-
q = 0
-
With .Sections("Section1").Controls
-
For intCtrl = 1 To .Count
-
If TypeOf .Item(intCtrl) Is RptLabel Then
-
.Item(intCtrl).Caption = rs1.Fields(q).Name & " :"
-
q = q + 1
-
End If
-
If TypeOf .Item(intCtrl) Is RptTextBox Then
-
.Item(intCtrl).DataMember = "Command1"
-
.Item(intCtrl).DataField = rs1(z).Name
-
z = z + 1
-
End If
-
Next intCtrl
-
End With
-
.Refresh
-
.Show
-
End With
-
End Sub
-
-
Private Sub Form_Load()
-
-
Command1.Caption = "Show Report"
-
-
cn.Open "Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0;" & _
-
"Data Source=D:\DSVFiles\Visual Studio\VB98\Nwind.mdb;"
-
-
With cmd
-
.ActiveConnection = cn
-
.CommandType = adCmdText
-
.CommandText = " SHAPE {SELECT FirstName,Lastname,City FROM `Employees`} AS Command1 COMPUTE Command1 BY 'City'"
-
.Execute
-
End With
-
-
With rs
-
.ActiveConnection = cn
-
.CursorLocation = adUseClient
-
.Open cmd
-
End With
-
Set rs1 = rs(0).Value
-
-
End Sub
This codes work fine. But when I start using SQL Database I get Run-time error '-2147217900 (80040e14)'. I'm currently trying to find some other ways to print Grouped reports, hope you can give me some ideas :)
TIA
I've never heard of a SHAPE command before, so don't know whether I'm likely to be any help with this. I did wonder when reading it, whether perhaps ItemType should not have quotes around it, in line 4.
Presumably it was the Execute which returned the error?
I have tried removing the quotes around the ItemType, it still doesnt work.
Try using the codes that I have provided above using 1 form, 1 command button, 1 data report.
remove the Report header of the data report, add a group header and footer. at the group header add a rptLabel and a rptText.
on the details section add 2 rptLabel and 2 rptText.
just change the path of the database for your northwind database location.
I think I have solved my problem ^_^
I just have to modify the command of the hierarchicy of the data report. - Option Explicit
-
-
Const preSHAPE = "SHAPE {"
-
Const SQL = "SELECT dbo.ReceivingHeader.RRNo, dbo.ReceivingHistory.DateReceived, dbo.ReceivingHistory.ReceivedFrom, dbo.ReceivingHistory.Address, dbo.ReceivingHistory.ReceivingType, dbo.ReceivingHistory.Amount, dbo.ReceivingHistory.ItemType FROM dbo.ReceivingHeader INNER JOIN dbo.ReceivingHistory ON dbo.ReceivingHeader.RRNo = dbo.ReceivingHistory.RRNo "
-
Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'ReceivingType') AS Company BY 'ItemType'"
-
-
-
Private Sub Command1_Click()
-
With DataEnvironment1
-
If .rsItemTypes.State Then .rsItemTypes.Close
-
.Commands!ItemTypes.CommandText = preSHAPE & SQL & " where ReceivingHistory.ItemType = 'CHEMICAL'" & postSHAPE
-
.ItemTypes
-
End With
-
DataReport1.Refresh
-
If DataReport1.Visible = False Then DataReport1.Show
-
End Sub
^_^
I think I have solved my problem ^_^
I just have to modify the command of the hierarchicy of the data report.
Option Explicit
Const preSHAPE = "SHAPE {"
Const SQL = "SELECT dbo.ReceivingHeader.RRNo, dbo.ReceivingHistory.DateReceived, dbo.ReceivingHistory.ReceivedFrom, dbo.ReceivingHistory.Address, dbo.ReceivingHistory.ReceivingType, dbo.ReceivingHistory.Amount, dbo.ReceivingHistory.ItemType FROM dbo.ReceivingHeader INNER JOIN dbo.ReceivingHistory ON dbo.ReceivingHeader.RRNo = dbo.ReceivingHistory.RRNo "
Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'ReceivingType') AS Company BY 'ItemType'"
Private Sub Command1_Click()
With DataEnvironment1
If .rsItemTypes.State Then .rsItemTypes.Close
.Commands!ItemTypes.CommandText = preSHAPE & SQL & " where ReceivingHistory.ItemType = 'CHEMICAL'" & postSHAPE
.ItemTypes
End With
DataReport1.Refresh
If DataReport1.Visible = False Then DataReport1.Show
End Sub
^_^
I don’t know for what purpose you are using, but we can use - Dim adoHistoryPrint As New ADODB.Connection
-
Dim recHistoryPrint As New ADODB.Recordset
-
-
Set recHistoryPrint = adoHistoryPrint.Execute strSqlQuery
-
‘strSqlQuery should be Select query to fill the record set.
If you want the same structure, then you have to explain this line. - " SHAPE {" & adoReceivingHistory.RecordSource & "} AS Command1 COMPUTE Command1 BY 'ItemType'"
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bob Bamberg |
last post by:
Hello All,
I have been trying without luck to get some information on debugging
the Runtime Error R6025 - Pure Virtual Function Call. I am working in
C++ and have only one class that is derived...
|
by: Nachi |
last post by:
I am getting above error,
could pls anyone help me........?
Soruce Code :
Sub updateOfferCategoryAmounts(ByVal Transid As Long)
Dim spComm As Command
Set spComm = New Command
Dim intCatID...
|
by: neutralm |
last post by:
Hi,
I am getting the following error:
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either...
|
by: g_man |
last post by:
I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful...
|
by: renukayuvaraj |
last post by:
When i run the report its showing runtime error in this line as.... (Syntax error -
missing operator in query expression)
DataEnvironment1.rsCommand1.Open ("select * from GRNTrans where SUPPLIER...
|
by: Gorkamail2 |
last post by:
Hi,
I'm getting the following error when a program generates a query to a AS400 database.
Product=ZWWVI-AUNO Platform=Other Table=IIM
Error -2147217900 SQL0104 - Token 99 was not valid. Valid...
|
by: ALaurie10 |
last post by:
Hi
I am trying to write a select query that prompts a user for an application number that will call a recordset from an already existing table that will be used to export XML file data that will...
|
by: Jim Armstrong |
last post by:
Hello all -
This is driving me crazy. I have a table called tblClients - very
simple, has the following fields:
taxID (PK)
ClientName
SalesName
The main form of my application allows a...
|
by: rahul more |
last post by:
Hiiii I am making my project in VB6.0 and my back end is Access 2003. I hav one problem.....
When I going to save data into database, at at cmdSave_Click event, VB giving me error(Error mentioned...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |