Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    14

    want to save Alpha-numeric employee ID in database

    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 a vb 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....

    Is there any wrong with my sql string that i had used in the vb6 application?

    thanks for any urgent help... as my class assignment closing date is ahead.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you are using double quotes (or no quotes) instead of single quotes.... change to single quotes and all should be right witht he world.

  3. #3
    Join Date
    Oct 2003
    Posts
    14

    Re: want to save Alpha-numeric employee ID in database

    Originally posted by ishrat khan
    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 a vb 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....

    Is there any wrong with my sql string that i had used in the vb6 application? the string is ....
    strsql = "INSERT INTO Employee(EmpID, Name) VALUES("
    strsql = strsql & Me.txtEmpID.Text & "','"
    strsql = strsql & Me.txtName.Text & "')"
    conn.Execute strsql

    thanks for any urgent help... as my class assignment closing date is ahead.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Is there any wrong with my sql string that i had used in the vb6 application? the string is ....

    strsql = "INSERT INTO Employee(EmpID, Name) VALUES("


    Should be....

    strsql = "INSERT INTO Employee(EmpID, Name) VALUES('"

    you are missing the first single quote in your post.

  5. #5
    Join Date
    Oct 2003
    Posts
    14

    thanks but another realted problem

    I have placed the single quote and its works.... but the second part of the code that is an update statement doesnt work....
    what i want to do is that when i update the salary of an employee a copy of the change is be saved in the SalMonth table as well. But i am facing problem in updating the employee s salary filed by using the following string.(*)


    strsql = "INSERT INTO SalChange (EmpID, Salary, ChDate) VALUES('"
    strsql = strsql & Me.txtEmpID.Text & "','"
    strsql = strsql & Me.txtSalary.Text & "','"
    strsql = strsql & Date & "')"
    conn.Execute strsql


    (*) strsql = "UPDATE Employee SET SalMonth = '" & txtSalary.Text & "' where EmpID = " & txtEmpID.Text
    conn.Execute strsql

    the error i got is...
    err.number : -2147217913
    Description : Syntax error converting the varchar value ' A-11 ' to a column of datatype int.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you have exactly the same problem....

    this

    Code:
    strsql = "UPDATE Employee SET SalMonth = '" & txtSalary.Text & "' where EmpID = " & txtEmpID.Text
    should be

    Code:
    strsql = "UPDATE Employee SET SalMonth = '" & txtSalary.Text & "' where EmpID = '" & txtEmpID.Text & "'"
    (note the side quotes around the EmpID)

    If you are building and sql statement with string values you need to wrap them in single quotes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •