Solved

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

Posted on 2003-12-04
20
606 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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
 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupSum6 challenge 6 85
WMI, model #, retrieving information 10 137
parse convert xml feed to text (python) 2 81
Apps blocked by Java 9 79
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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 …

815 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

8 Experts available now in Live!

Get 1:1 Help Now