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

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
khankejanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ralf KlattEmployee in Civil ServiceCommented:
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
Ralf KlattEmployee in Civil ServiceCommented:
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
khankejanAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

khankejanAuthor Commented:
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
Ralf KlattEmployee in Civil ServiceCommented:
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
Ralf KlattEmployee in Civil ServiceCommented:
what I mean is, would you like to fill more text fields with this function?
0
Ralf KlattEmployee in Civil ServiceCommented:
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
khankejanAuthor Commented:
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
khankejanAuthor Commented:
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
Ralf KlattEmployee in Civil ServiceCommented:
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
khankejanAuthor Commented:
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
khankejanAuthor Commented:
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
khankejanAuthor Commented:
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
Ralf KlattEmployee in Civil ServiceCommented:
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
Ralf KlattEmployee in Civil ServiceCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
khankejanAuthor Commented:
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
Ralf KlattEmployee in Civil ServiceCommented:
Hi,

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


Best regards, Raisor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.