Converting a field in Access
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.
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)
intCount = cmd.ExecuteScalar()
Response.write("<h2>The value for intCount is " & intCount & ". Which means the record exists</h2>")
If NOT intCount > 0 Then
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)
intCount = cmd.ExecuteScalar()
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.
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
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.
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.
Is the above a typo? Shouldn't it be:
Originally Posted by jbricher
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.
Microsoft MVP (Visual Basic)
You were absolutely right, thanks for your good eye. I appreciate it.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL