Converting a field in Access

[jbricher]

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

[Phil Weber]

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.

[oupoi]

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.

[jbricher]

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.

[pclement]

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.

[jbricher]

You were absolutely right, thanks for your good eye. I appreciate it.

- Joe