Forum Home
Press F1
 
Thread ID: 45949 2004-06-08 05:23:00 ASP .net DB problem george12 (7) Press F1
Post ID Timestamp Content User
242730 2004-06-08 05:23:00 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? 		george12 (7)
242731 2004-06-08 06:01:00 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 		odyssey (4613)
242732 2004-06-08 06:10:00 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? 		antmannz (28)
242733 2004-06-08 06:20:00 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. 		george12 (7)
1