PDA

View Full Version : ASP .net DB problem



george12
08-06-2004, 05:23 PM
I am trying to create a function to write a single value to a field in a table of a database (users.mdb), so that I can execute commands such as:

SaveDataField("John","Email","john@jgih.com")

When I run this script, there are no errors, but the database does not change. Here's my code:

Public Function SaveDataField(UserName,Field,Value)
Dim MyConn as OleDbConnection
Dim ConnStr as String
ConnStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\db\Users.mdb"
MyConn= new OleDbConnection(ConnStr)
MyConn.Open
Dim queryString As String = "UPDATE Users SET '" & Field & "' = '" & Value & "' WHERE UserName = '" & UserName & "'"
response.write(querystring)
Dim dbCommand As OleDbCommand = New OleDbCommand(querystring,MyConn)
MyConn.Close
End Function

If it helps, I run the ASP.net framework, latest version. The function I use to read a value is:


Public Function GetUserData(UserName,Field) As String
Dim MyConn as OleDbConnection
Dim ConnStr as String
Dim ResultVal as String
ConnStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\db\Users.mdb"
MyConn= new OleDbConnection(ConnStr)
MyConn.Open
Dim queryString As String = "SELECT [Users].[" & Field & "] FROM [Users] WHERE ([Users].[UserName] = '" & UserName & "')"
Dim dbCommand As OleDbCommand = New OleDbCommand(querystring,MyConn)
ResultVal = dbCommand.ExecuteScalar()
MyConn.Close
Return ResultVal
End Function

I would write, say,
Response.Write(GetUserData("John","Email")) - this works fine.

Can anyone tell me why this happens, and how to fix it?

odyssey
08-06-2004, 06:01 PM
Could it be that you have to run the dbCommand.ExecuteScalar() or a similar execute function like you have in your GetUserData function?

Cheers
Dave

antmannz
08-06-2004, 06:10 PM
I'm not too up on .Net but anyway:

1. Check that your UserName variable exists in the database. Nothing will happen if:
"UPDATE Users SET Sex = 'Male' WHERE UserName = 'John'"
and John can't be found in the UserName field.

2. It looks to me as though your update string has apostropohes (') around the Field name. Field names should not be passed to the database as strings.

3. Lastly, as I said, .Net wasn't my forte; but I take it that the "Dim dbCommand ......." doesn't need a dbCommand.Execute statement after it?

george12
08-06-2004, 06:20 PM
I have removed the apostrophes around the field name, and I do have the data to update, but I think you're right about the Execute thing. I am checking up on this now.

Thanks for the help.