Solved

automatiaclly fetch the salary form a database against the empID given.

Posted on 2003-12-04
20
601 Views
Last Modified: 2010-04-17
I am working on an assignment. using vb6 and sql server2000.
The problem is.... On the employees form of the application... i have fields like EmpID.. Salary.. etc.
What i need is that when i write an empID in the "txtEmpID" textbox, on its lost focus event a code should execute and populates the Salary textbox with that particular employees salary. Getting the salary form the employees table against the EmpID provided.

on calculating its overtime payment I again submit the form to the database. But this time salary field will not be added to the database.

I hope u understand whats my problem.
thanks in advance... waithing for reply.
regards
0
Comment
Question by:khankejan
  • 9
  • 8
20 Comments
 
LVL 15

Expert Comment

by:Raisor
ID: 9875923
Hi,

This is what belongs to your VB6 form:

Private Sub txtEmpID_LostFocus()
    If txtEmpID.Text <> "" And IsNumeric(txtEmpID.Text) Then
        Dim rs As ADODB.Recordset, ConString As String, sqlString As String
        Set rs = New ADODB.Recordset
        ConString = "driver={SQL Server};" & "server=" & theServer & ";uid=yoursystemaccount;pwd=yourpassword;database=yourdatabase"
        sqlString = "EXEC sp_GetEmployeesSalary " & txtEmpID.Text
        rs.Open sqlString, ConString, adOpenKeyset, adLockOptimistic
        If Not CLng(rs.Fields(0)) = -1 Then
            txtSalary.Text = rs.Fields(0)
        Else
            MsgBox "Employee doesn't exist!"
        End If
        rs.Close
        Set rs = Nothing
    End If
End Sub


When you fetch the data using a stored procedure, make sure you return the value -1 if no employee is found in the table.

Please let me know if you don't know about writing a stored procedure!


Best regards, Raisor
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9875947
Hi,

Sorry, the ConString should be like this:

ConString = "driver={SQL Server};server=yourserver;uid=yoursystemaccount;pwd=yourpassword;database=yourdatabase"


I'm working with variables and I've sorted the lines of code out of one of my applications ;-))


Best regards, Raisor
0
 

Author Comment

by:khankejan
ID: 9880547
Thanks Raisor,

You are right, I had some verbal knowledge of stored procedures. But never wrote even a single "sp" . You can say its my beginning. And really appreciate if u help me out in writing a stored procedure.
The table name is Employee and the field names are
EmpID, Name, Designation, SalMonth (salary field).

Regards.
khankejan
0
 

Author Comment

by:khankejan
ID: 9880552
Thanks Raisor,

You are right, I had some verbal knowledge of stored procedures. But never wrote even a single "sp" . You can say its my beginning. And really appreciate if u help me out in writing a stored procedure.
The table name is Employee and the field names are
EmpID, Name, Designation, SalMonth (salary field).

Regards.
khankejan
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9880851
Hi,

Do you need all four fields for this or is it enaugh to use EmpID as query input and SalMonth as query output?


Best regards, Raisor
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9880854
what I mean is, would you like to fill more text fields with this function?
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9880962
Hi,

Here's the stored procedure, it gives you the salary, employee name and designation in return for an employee ID -> to execute it just -> open the query manager -> select the database where the table Employee resides -> paste the code -> and run the execute command.

create procedure sp_GetEmployeesSalary
(
      @empID int
)
as
declare @chkEmp int
select
      @chkEmp = count(EmpID)
from
      Employee
where
      EmpID = @empID
if @chkEmp = 0
      begin
            select -1
      end
if @chkEmp > 0
      begin
            select
                  SalMonth,
                  Name,
                  Designation
            from
                  employee
            where
                  EmpID = @empID
      end


The modified function for VB looks like this:

Private Sub txtEmpID_LostFocus()
    If txtEmpID.Text <> "" And IsNumeric(txtEmpID.Text) Then
        Dim rs As ADODB.Recordset, ConString As String, sqlString As String
        Set rs = New ADODB.Recordset
        ConString = "driver={SQL Server};" & "server=YourServer;uid=YourUser;pwd=YourPW;database=YourDB"
        sqlString = "EXEC sp_GetEmployeesSalary " & txtEmpID.Text
        rs.Open sqlString, ConString, adOpenKeyset, adLockOptimistic
        If Not CLng(rs.Fields(0)) = -1 Then
            txtSalary.Text = rs.Fields(0)
            txtEmpName.Text = rs.Fields(1)
            txtEmpDesig.Text = rs.Fields(2)
        Else
            MsgBox "Employee doesn't exist!"
        End If
        rs.Close
        Set rs = Nothing
    End If
End Sub



Best regards, Raisor
0
 

Author Comment

by:khankejan
ID: 9901851
Hi... Raisor, thankyou for ur help.... its working as i had made some changes because i need only the salary to be fetched.... But there is another problem in the way.....
The problem is....I want to store Alpha-numeric Employee ID's in the database. When i stroe a numeric Employee ID like 001, 030 things went ok.... But when i try to enter Employee ID like " aa11 ", it is not saved to the database. And an error occurs.
The error number is : -2147217900
Description : The name " aa11 " in not permitted in this context. Only constants,        experssions or variables allowed here. Column names are not permitted.
One thing more....  when i directly put some alpha-numeric ID in the data base .. there is no problem... But when i try to put with the vb6 interface forms the above mentioned error occurs....

I will be greatful if u help me out....
regards.
khankejan.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:khankejan
ID: 9933771
I have an alpha-numeric employee ID, in the format &#8220; A-001 &#8221; When the  txtEmpID lost focus event is triggered. I receive an error, that is.
Error Number: -2147217900      
Description : Line 1= Incorrect syntax near &#8216;-&#8216;.

When the EmpID format is &#8220;A001&#8221;
Error Number: -2147217887      
Description : Multiple- step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Can anyone adjust the problem&#8230;.. the code is as under&#8230; thanks

The coding to call a stored procedure is as follows&#8230;.
If txtEmpID.Text <> "" And IsNumeric(txtEmpID.Text) Then
        Dim rs As ADODB.Recordset, ConString As String, sqlString As String
        Set rs = New ADODB.Recordset
        ConString = "Provider=SQLOLEDB.1;Data Source=mohdkashif;Initial Catalog= timesheet;" & _
                    "Persist Security Info=False;integrated security=SSPI"
        sqlString = "EXEC sp_GetEmployeesSalary " & txtEmpID.Text
        rs.Open sqlString, ConString, adOpenKeyset, adLockOptimistic
        If Not CLng(rs.Fields(0)) = -1 Then
            txtSalary.Text = rs.Fields(0)
        Else
            MsgBox "Employee doesn't exist!"
        End If
        rs.Close
        Set rs = Nothing
    End If


Code of stored procedure is.

CREATE procedure sp_GetEmployeesSalary
(
     @empID char
)
as
declare @chkEmp char
select
     @chkEmp = count(EmpID)
from
     Employee
where
     EmpID = @empID
if @chkEmp = 0
     begin
          select -1
     end
if @chkEmp > 0
     begin
          select
               SalMonth
          from
               employee
          where
               EmpID = @empID
     end
GO
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9934554
Hi,

The value you have stored up to now is only numeric ... as this is about an ID if one of your tables in one of your databases ...

... now you're trying to match pure numeric values (001, 030) with alphanumeric values (" aa11") -> that works only if you'd take "aa" off before matching and just compare "11" in first match (against your database) and on application level ad a secondary match for "aa" ...


Best regards, Raisor
0
 

Author Comment

by:khankejan
ID: 9947654
Hi Raisor.
the problem is not that i am matching alpha-numeric VS numeric. The thing is i have changed the format of EmpID's. In Data base the ID are stored as alpha-numeric. And i want to fetch the records by providing an alpha-numeric ID.
i changed ur code a bit at line:
sqlString = "EXEC sp_GetEmployeesSalary '" & txtEmpID.Text & "'"

Now what is hapening is that.... the code executes ok. but what ever ID i write the message appears:
MsgBox "Employee doesn't exist!"

I need ur help in this regard.
       
0
 

Author Comment

by:khankejan
ID: 9947662
Hi Raisor.
the problem is not that i am matching alpha-numeric VS numeric. The thing is i have changed the format of EmpID's. In Data base the ID are stored as alpha-numeric. And i want to fetch the records by providing an alpha-numeric ID.
i changed ur code a bit at line:
sqlString = "EXEC sp_GetEmployeesSalary '" & txtEmpID.Text & "'"

Now what is hapening is that.... the code executes ok. but what ever ID i write the message appears:
MsgBox "Employee doesn't exist!"

I need ur help in this regard.
       
0
 

Author Comment

by:khankejan
ID: 9947666
Hi Raisor.
the problem is not that i am matching alpha-numeric VS numeric. The thing is i have changed the format of EmpID's. In Data base the ID are stored as alpha-numeric. And i want to fetch the records by providing an alpha-numeric ID.
i changed ur code a bit at line:
sqlString = "EXEC sp_GetEmployeesSalary '" & txtEmpID.Text & "'"

Now what is hapening is that.... the code executes ok. but what ever ID i write the message appears:
MsgBox "Employee doesn't exist!"

I need ur help in this regard.
       
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9947749
Hi,

You might try it with a "trim" statement in the sp -> unfortunately I'm out for a meeting right now -> I'll get back to you on that when I return!


Best regards, Raisor
0
 
LVL 15

Accepted Solution

by:
Raisor earned 75 total points
ID: 9948971
Hi,

It should even work without using the "trim" statement:

The table looks modified like this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee] (
      [tabEntID] [int] IDENTITY (1, 1) NOT NULL ,
      [EmpID] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
      [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SalMonth] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO


The stored procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER  procedure sp_GetEmployeesSalary
(
      @empID char(10)
)
as
declare @chkEmp int
select
      @chkEmp = count(tabEntID)
from
      Employee
where
      EmpID = @empID
if @chkEmp = 0
      begin
            select -1
      end
if @chkEmp > 0
      begin
            select
                  SalMonth
            from
                  employee
            where
                  EmpID = @empID
      end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


This is set to be EmpID as char(10) format -> Personally I prefer working with nchar or nvarchar formats as strings are not filled up with blanks. You may try which format fits the best for you.


Best regards, Raisor
0
 

Author Comment

by:khankejan
ID: 9955948
Thankyou Raisor !
Form ur above mentioned code i changed two things and it worked.
First i changed the following line:
@empID char(6)
Initially i wrote it as " @empID char "

the second change i made is on line:
declare @chkEmp int
Initially what i had written was " declare @chkEmp char "

what i think is that the second change did the trick.

rest of the code is the same old one.
I appreciate ur interest and help. Hope to have a good friendship in future. Our field is the same but the difference is that i am at the early stages of experience (less than 6 month, working alone without any senoir available to guide)

best regards.
khankejan.

0
 
LVL 15

Expert Comment

by:Raisor
ID: 9962520
Hi,

Thanks for your feedback! I'm glad that it works now.


Best regards, Raisor
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Adobe Customization Wizard XI issues 26 212
array220 challenge 8 57
Hide vba in gp 7 83
c++ reading data from file into two dimensional array 3 96
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This is an explanation of a simple data model to help parse a JSON feed
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now