Solved

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

Posted on 2003-12-04
20
596 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Order table with macro 3 75
Not needed 13 55
index Out OF Range Exception error 4 29
Base1 Encode/Decode 3 31
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

17 Experts available now in Live!

Get 1:1 Help Now