Converting a field in Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Converting a field in Access

  1. #1
    Join Date
    Feb 2005
    Posts
    34

    Converting a field in Access

    Hi everyone,

    I was wondering what changes I would have to make in my code to make the program I have work the same as it does now. Currently, the key field, NetworkID is a number field, however, I have discovered that this will not work for the kind of work I need to do. Therefore I need to convert the field to text and I will have to make some changes in the ASP code to make it work right. Any help anyone can give would be greatly appreciated. Thanks in advance.

    - Joe

    Code:


    Sub BtnSendClicked(Sender as Object, e as EventArgs)

    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.MapPath("CPP_v6.mdb") & ";"
    Dim MyConn as New OleDBConnection (strConn)
    Dim MySQL As String

    Dim intCount as Integer
    MYSQL = "SELECT COUNT(*) FROM Eligible WHERE StudentID=" & StudentID.Text
    Dim cmd as New OleDBCommand (MySQL, MyConn)
    MyConn.Open ()
    intCount = cmd.ExecuteScalar()
    MyConn.Close ()
    Response.write("<h2>The value for intCount is " & intCount & ". Which means the record exists</h2>")

    If NOT intCount > 0 Then

    Response.Redirect("ParkingLotteryFailurePage.html")

    Else

    MySQL = "INSERT INTO LotteryEntry (networkID, studentID, firstname, lastname, dayphone, eveningphone, cellphone, address1, address2, aptnumber, city, state, zip, email, handicap) VALUES ('" & networkID.Text & "','" & studentID.Text & "','" & firstname.Text & "','" & lastname.Text & "','" & dayphone.text & "','" & eveningphone.text & "','" & cellphone.text & "','" & address1.text & "','" & address2.text & "','" & aptnumber.text & "','" & city.text & "','" & state.selectedItem.value & "','" & zip.text & "','" & email.text & "','" & handicap.selectedItem.value & "')"

    cmd = New OleDBCommand (MySQL, MyConn)
    MyConn.Open ()
    intCount = cmd.ExecuteScalar()
    MyConn.Close ()
    Response.Redirect("ParkingLotterySuccessPage.html")
    End If

    End Sub

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    According to your code, NetworkID is already a text field: You're assigning the value of networkID.Text, and surrounding it with single quotes, which won't work for a numeric field.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Mar 2005
    Posts
    71
    The following two statements have contradiction

    SELECT COUNT(*) FROM Eligible WHERE StudentID=" & StudentID.Text (without single quote, i.e. treat as number)

    INSERT INTO LotteryEntry (..., studentID, ...) VALUES (...,'" & studentID.Text & "',...) (with single quote, i.e. treat as string)

    Either one is wrong.

  4. #4
    Join Date
    Feb 2005
    Posts
    34
    I made changes to the line SELECT COUNT as oupoi suggested. The line now looks as follows:

    MYSQL = "SELECT COUNT(*) FROM Eligible WHERE StudentID= '& StudentID.Text'"

    However, I still cannot get the database to take the entry from that field. Did I make the changes incorrectly, or did I chaneg the wrong line? Please let me know. Thanks.

  5. #5
    Join Date
    Dec 2003
    Posts
    2,750
    Quote Originally Posted by jbricher
    MYSQL = "SELECT COUNT(*) FROM Eligible WHERE StudentID= '& StudentID.Text'"
    Is the above a typo? Shouldn't it be:

    MYSQL = "SELECT COUNT(*) FROM Eligible WHERE StudentID= '" & StudentID.Text & "'"

    I'm assuming that StudentID is a Text field in the Table of your database.

    If you're encountering a error please indicate what it is. If there is no error but you're not getting the expected results please indicate what those are.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  6. #6
    Join Date
    Feb 2005
    Posts
    34
    You were absolutely right, thanks for your good eye. I appreciate it.

    - Joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center