Data Environment help please - Visual Basic 4 / 5 / 6

16 New Member

i need to view salary payslip (data report) based on that particular month and year.

in Data Environment sql :

SELECT[emp_id], [emp_name], [emp_position], [emp_bs], [month_paid], [year_paid], [total_addition], [gross_pay], [total_deduction], [total_pay] FROM salary WHERE month_paid = ? and year_paid = ?

parameter is monthler

parameter is yearler

in vb code:

Expand|Select|Wrap|Line Numbers

  
Dim intMonth As Integer

Dim intYear As Integer
 
' is a combo box that select month
 
If cboMonth.ListIndex = 0 Then

    intMonth = 1

ElseIf cboMonth.ListIndex = 1 Then

    intMonth = 2

ElseIf cboMonth.ListIndex = 2 Then

    intMonth = 3

ElseIf cboMonth.ListIndex = 3 Then

    intMonth = 4

ElseIf cboMonth.ListIndex = 4 Then

    intMonth = 5

ElseIf cboMonth.ListIndex = 5 Then

    intMonth = 6

ElseIf cboMonth.ListIndex = 6 Then

    intMonth = 7

ElseIf cboMonth.ListIndex = 7 Then

    intMonth = 8

ElseIf cboMonth.ListIndex = 8 Then

    intMonth = 9

ElseIf cboMonth.ListIndex = 9 Then

    intMonth = 10

ElseIf cboMonth.ListIndex = 10 Then

    intMonth = 11

ElseIf cboMonth.ListIndex = 11 Then

    intMonth = 12

End If
 
 is a combo box that select year
 
If cboYear.ListIndex = 0 Then

    intYear = 2007

ElseIf cboYear.ListIndex = 1 Then

    intYear = 2008

ElseIf cboYear.ListIndex = 2 Then

    intYear = 2009

ElseIf cboYear.ListIndex = 3 Then

    intYear = 2010

ElseIf cboYear.ListIndex = 4 Then

    intYear = 2011

End If
 
' how should i pass a value to a Data Environment sql query ?

or got other way to do ?

' i need view that month and year that i have selected. 

'P_Payslip.Show

Apr 3 '07 #1

Subscribe Reply

2993

Dököll

2,364

Recognized Expert Top Contributor

i need to view salary payslip (data report) based on that particular month and year.

in Data Environment sql :

SELECT[emp_id], [emp_name], [emp_position], [emp_bs], [month_paid], [year_paid], [total_addition], [gross_pay], [total_deduction], [total_pay] FROM salary WHERE month_paid = ? and year_paid = ?

parameter is monthler

parameter is yearler

in vb code:

Expand|Select|Wrap|Line Numbers

Dim intMonth As Integer

Dim intYear As Integer

' is a combo box that select month

If cboMonth.ListIndex = 0 Then

    intMonth = 1

ElseIf cboMonth.ListIndex = 1 Then

    intMonth = 2

ElseIf cboMonth.ListIndex = 2 Then

    intMonth = 3

ElseIf cboMonth.ListIndex = 3 Then

    intMonth = 4

ElseIf cboMonth.ListIndex = 4 Then

    intMonth = 5

ElseIf cboMonth.ListIndex = 5 Then

    intMonth = 6

ElseIf cboMonth.ListIndex = 6 Then

    intMonth = 7

ElseIf cboMonth.ListIndex = 7 Then

    intMonth = 8

ElseIf cboMonth.ListIndex = 8 Then

    intMonth = 9

ElseIf cboMonth.ListIndex = 9 Then

    intMonth = 10

ElseIf cboMonth.ListIndex = 10 Then

    intMonth = 11

ElseIf cboMonth.ListIndex = 11 Then

    intMonth = 12

End If

is a combo box that select year

If cboYear.ListIndex = 0 Then

    intYear = 2007

ElseIf cboYear.ListIndex = 1 Then

    intYear = 2008

ElseIf cboYear.ListIndex = 2 Then

    intYear = 2009

ElseIf cboYear.ListIndex = 3 Then

    intYear = 2010

ElseIf cboYear.ListIndex = 4 Then

    intYear = 2011

End If

' how should i pass a value to a Data Environment sql query ?

or got other way to do ?

' i need view that month and year that i have selected.

'P_Payslip.Show

Just posted the below, gillian3114, greetings...

Give it a whirl. I also posted another that deals with Data Environment. Will find it for you:

http://www.google.com/search?q=Data.. .ie7&rlz=1I7SUN A

+++++++++++++++ +++++++

Please search a bit deeper here if this is no help. I added "Data Report without Data Environment " to Google to fetch info included there.

Good luck with the project!

Dököll

Apr 3 '07 #2

cmrhema

375

Contributor

i need to view salary payslip (data report) based on that particular month and year.

in Data Environment sql :

SELECT[emp_id], [emp_name], [emp_position], [emp_bs], [month_paid], [year_paid], [total_addition], [gross_pay], [total_deduction], [total_pay] FROM salary WHERE month_paid = ? and year_paid = ?

parameter is monthler

parameter is yearler

in vb code:

Expand|Select|Wrap|Line Numbers

Dim intMonth As Integer

Dim intYear As Integer

' is a combo box that select month

If cboMonth.ListIndex = 0 Then

    intMonth = 1

ElseIf cboMonth.ListIndex = 1 Then

    intMonth = 2

ElseIf cboMonth.ListIndex = 2 Then

    intMonth = 3

ElseIf cboMonth.ListIndex = 3 Then

    intMonth = 4

ElseIf cboMonth.ListIndex = 4 Then

    intMonth = 5

ElseIf cboMonth.ListIndex = 5 Then

    intMonth = 6

ElseIf cboMonth.ListIndex = 6 Then

    intMonth = 7

ElseIf cboMonth.ListIndex = 7 Then

    intMonth = 8

ElseIf cboMonth.ListIndex = 8 Then

    intMonth = 9

ElseIf cboMonth.ListIndex = 9 Then

    intMonth = 10

ElseIf cboMonth.ListIndex = 10 Then

    intMonth = 11

ElseIf cboMonth.ListIndex = 11 Then

    intMonth = 12

End If

is a combo box that select year

If cboYear.ListIndex = 0 Then

    intYear = 2007

ElseIf cboYear.ListIndex = 1 Then

    intYear = 2008

ElseIf cboYear.ListIndex = 2 Then

    intYear = 2009

ElseIf cboYear.ListIndex = 3 Then

    intYear = 2010

ElseIf cboYear.ListIndex = 4 Then

    intYear = 2011

End If

' how should i pass a value to a Data Environment sql query ?

or got other way to do ?

' i need view that month and year that i have selected.

'P_Payslip.Show

I have used datareports BUT WITHOUT data environment, and I pass the query in form code itself
You must be having a control where you will be prompting the user to print report
Try out the below code in that control

Dim db_file As String
Dim Mycon1 As New ADODB.Connectio n
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "emp.mdb"
' Open a connection.
Mycon1.Connecti onString = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Me.Refresh
Mycon1.Open
Set m1 = Mycon1.Execute( "SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction , total_pay FROM salary WHERE month_paid = " & intMonth & " and year_paid =" & intYear & "", , adCmdText)
' Connect the Recordset to the DataReport.
Set DataReport1.Dat aSource = m1
DataReport1.Win dowState = vbMaximized
DataReport1.Sho w vbModal
DataReport1.Cap tion = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If

This code works for me
Good Luck

Apr 3 '07 #3

Dököll

2,364

Recognized Expert Top Contributor

I have used datareports BUT WITHOUT data environment, and I pass the query in form code itself
You must be having a control where you will be prompting the user to print report
Try out the below code in that control

Dim db_file As String
Dim Mycon1 As New ADODB.Connectio n
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "emp.mdb"
' Open a connection.
Mycon1.Connecti onString = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Me.Refresh
Mycon1.Open
Set m1 = Mycon1.Execute( "SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction , total_pay FROM salary WHERE month_paid = " & intMonth & " and year_paid =" & intYear & "", , adCmdText)
' Connect the Recordset to the DataReport.
Set DataReport1.Dat aSource = m1
DataReport1.Win dowState = vbMaximized
DataReport1.Sho w vbModal
DataReport1.Cap tion = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If

This code works for me
Good Luck

Good job, cmrhema!

Here is the link I was looking for, gillian3114. You can query your database and furnish a report:

http://www.dreamincode.net/forums/showtopic13544.htm

Give above time to load, snapshots included. Continue asking questions, we'll ge there...

Take cmrhema also, see how that works for you.

Apr 3 '07 #4

vijaydiwakar

579

Contributor

I have used datareports BUT WITHOUT data environment, and I pass the query in form code itself
You must be having a control where you will be prompting the user to print report
Try out the below code in that control

Dim db_file As String
Dim Mycon1 As New ADODB.Connectio n
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "emp.mdb"
' Open a connection.
Mycon1.Connecti onString = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Me.Refresh
Mycon1.Open
Set m1 = Mycon1.Execute( "SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction , total_pay FROM salary WHERE month_paid = " & intMonth & " and year_paid =" & intYear & "", , adCmdText)
' Connect the Recordset to the DataReport.
Set DataReport1.Dat aSource = m1
DataReport1.Win dowState = vbMaximized
DataReport1.Sho w vbModal
DataReport1.Cap tion = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If

This code works for me
Good Luck

Nice answer so far
keep it up

Apr 3 '07 #5

gillian3114

New Member

Error: Object Requiered.

Expand|Select|Wrap|Line Numbers

  
Dim intMonth As Integer

Dim intYear As Integer
 
If cboMonth.ListIndex = 0 Then

    intMonth = 1

ElseIf cboMonth.ListIndex = 1 Then

    intMonth = 2

ElseIf cboMonth.ListIndex = 2 Then

    intMonth = 3

ElseIf cboMonth.ListIndex = 3 Then

    intMonth = 4

ElseIf cboMonth.ListIndex = 4 Then

    intMonth = 5

ElseIf cboMonth.ListIndex = 5 Then

    intMonth = 6

ElseIf cboMonth.ListIndex = 6 Then

    intMonth = 7

ElseIf cboMonth.ListIndex = 7 Then

    intMonth = 8

ElseIf cboMonth.ListIndex = 8 Then

    intMonth = 9

ElseIf cboMonth.ListIndex = 9 Then

    intMonth = 10

ElseIf cboMonth.ListIndex = 10 Then

    intMonth = 11

ElseIf cboMonth.ListIndex = 11 Then

    intMonth = 12

End If
 
If cboYear.ListIndex = 0 Then

    intYear = 2007

ElseIf cboYear.ListIndex = 1 Then

    intYear = 2008

ElseIf cboYear.ListIndex = 2 Then

    intYear = 2009

ElseIf cboYear.ListIndex = 3 Then

    intYear = 2010

ElseIf cboYear.ListIndex = 4 Then

    intYear = 2011

End If
 
Dim Mycon1 As New ADODB.Connection

Dim m1 As New ADODB.Recordset

Dim strCNString As String
 
strCNString = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"

Mycon1.Provider = "Microsoft Jet 4.0 OLE DB Provider"

Mycon1.ConnectionString = strCNString

Mycon1.Open
 
    Set m1 = Mycon1.Execute("SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction, total_pay FROM salary ", , adCmdText)

' Connect the Recordset to the DataReport.
 
    Set DataReport1.DataSource = m1 ' error: object required
 
    DataReport1.WindowState = vbMaximized

    DataReport1.Show vbModal

    DataReport1.Caption = "Invoice"

    m1.Close

    Mycon1.Close

    Mycon1.Open

    Set Mycon1 = Nothing

Apr 3 '07 #6

vijaydiwakar

579

Contributor

Error: Object Requiered.

[code]

pls sho the place where u got the error

Apr 3 '07 #7

gillian3114

New Member

error occur at:

Set DataReport1.Dat aSource = m1

Apr 3 '07 #8

cmrhema

375

Contributor

error occur at:

Set DataReport1.Dat aSource = m1

Sorry Gillian for the trouble
Fogot to tell you that one should Declare m1 intially
Dim m1 As New ADODB.Recordset

Apr 4 '07 #9

Arvindkumar

New Member

Hello All

I have the similar problem in data environment. Though the code works well to set the data source with report.

How can I show the data in Reports. Say I have text1 as a text box in report and I want to display the data in the textbox.

Also I want to display all the records that matching the criteria :
say
select * from emp where sal >5000

Please help I am in trouble

Thanks

May 12 '07 #10

Similar topics

3414

Error in dynamic sql: Data type mismatch in criteria

by: Jack | last post by:

Hi, I am trying to test a sql statement in Access which gives me the error as stated in the heading. The sql statement is built as a part of asp login verification, where the userid and password are input in login screen. The password in the database is a number field. I am writing the dynamic sql statement as follows below. I believe I am going wrong in the password section of the code. I appreciate any help. Thanks. Regards.

ASP / Active Server Pages

1771

Deploying Data Access Pages (DAPs) via RDS in 3-tier environment

by: Bob Alston | last post by:

Anyone out there successfully deployed Data Access Pages, on a server they do not control, using RDS in a 3-tier environment? If so I would like to hear about your success. From my reading, RDS is not widely available on commercial servers due to security issues advised by Microsoft (see prior posts). I have done it successfully in a two tier environment - which (according to Microsoft) is where the page is on a server, but the access...

Microsoft Access / VBA

1774

Static shared data connection provider?

by: Jon Davis | last post by:

I like the drag-and-drop accessibility of dragging a table to a Web Forms designer and seeing a SqlDataAdapter automatically created for me.. being able to create a DataSet from that is fun and exciting, because now with this schema based data outline Visual Studio provides me with a typed class for managing data complete with Intellisense menus exposing my own field names as C# object properties ... cool .. Anyway, I have a problem. I...

ASP.NET

1922

Confused about project references / data access application block

by: EO | last post by:

I am trying to use the MSFT data access application block on 3 machines. Machine 1: Sandbox environment; I installed the application block with the msi. The Sqlhelper class compiles & runs great! (Also the SqlhelperParameterCache class; I'll refer to them collectively as the SqlHelper class.) I'm using unmodified application block code in all cases. Machine 2: Development environment, different project

ASP.NET

3267

'Unable to validate data' - Why is Viewstate corrupt?

by: sjl | last post by:

I've got an .aspx webform for searching my database. It basically takes user input and passes it as a parm into a stored proc to search a table. The results are returned in a SQLDataReader and bound to a datagrid on the same page. It works 90% of the time as expected. However, for some unknown reason, I randomly get the error below. The HttpException says there is an 'Invalid_Viewstate'. Ok, but why? I'm posting back to the same page. If...

ASP.NET

1659

background data retrieval

by: Doug Bell | last post by:

Hi, I have an application that has a "Data Access Class" and "User Interface Class". It is for receiving Purchase Order data from one system and pushing processed transactions to another system. The system generally works quite well. Currently the User interface calls for a refresh of data every 15 minutes (selectable) and the Data Access Class connects to the DB and retrieves the

Visual Basic .NET

15712

Async socket connection ends up in Unable to read data from....

by: Arno | last post by:

reposted with the right microsoft managed newsgroup ID: Sorry for the inconvinience Hi, I've written a class for client-socket connection, but I get a lot of times the error message "Unable to read data from the transport connection" when restart reading the stream with socket.BeginRead in the Sub SocketIncomingMsg. I'm debugging now for weeks, but I can't detect where it's

Visual Basic .NET

1199

Can't see output with Data Environment

by: kowndinya | last post by:

I have created a small program with Data Environment. database is MS Access. It is working fine on my computer where VB 6 was Installed. I want to use this program on different machines where there is no Visual basic. I am not able to see the data in the form . All fields are empty. Created my connection/ command using Data Environment. droped these data command on to main form (i have only one form). Data entry form was created...

Visual Basic 4 / 5 / 6

2047

Overlapping of data in xsl

by: manugm1987 | last post by:

<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> <style type="text/css"><!-- span.cls_002{font-family:Times,serif;font-size:12px;color:rgb(0,0,0);font-weight:normal;font-style:normal} div.cls_002{font-family:Times,serif;font-size:12px;color:rgb(0,0,0);font-weight:normal;font-style:normal} span.cls_004{font-family:Times,serif;font-size:11px;color:rgb(0,0,0);font-weight:normal;font-style:normal}

XML

8202

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8707

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8641

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8510

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6125

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5575

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4093

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2628

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1512

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General