edit a field in an SQL mobile database
Hi There,
I'm currently writing an application for a windows mobile 5 device which will store data in sql mobile 2005.
I would like to know which would be the most efficient way of writing the following example to work with the above. (up until now all the code i have written has been in vb6 and connecting to an access database)
Set db = DBEngine.OpenDatabase("location\database.mdb")
'
strSQL = "SELECT * FROM Floats WHERE AgentName = '" & lblAgentName.Caption & "' AND Status = 0 "
'
Set rs3 = db.OpenRecordset(strSQL)
If rs3.RecordCount > 0 Then
rs3.MoveLast
lngRecordCount3 = rs3.RecordCount
rs3.MoveFirst
Else
lngRecordCount3 = 0
End If
'
If lngRecordCount3 > 0 Then
For Counter = 1 To lngRecordCount3
With rs3
.Edit
.Fields("Status") = 1
.Update
.MoveNext
End With
Next
Else
MsgBox "No Outstanding Floats ERROR"
End If
[1135 byte] By [
dazjack1] at [2007-12-23]
Hi There,
I'm currently writing an application using visual studio 2005 for a windows mobile 5 device which will store data in sql mobile 2005.
if anyone could assist me or point me in the right direction as to how to edit a database directly (up until now all the code i have written has been in vb6 and connecting to an access database). below is an example of how i would connect and edit a record in an access database table using vb6 and i'm wanting to do the equivalent with the above application.
Set db = DBEngine.OpenDatabase("location\database.mdb")
'
strSQL = "SELECT * FROM Floats WHERE AgentName = '" & lblAgentName.Caption & "' AND Status = 0 "
'
Set rs3 = db.OpenRecordset(strSQL)
If rs3.RecordCount > 0 Then
rs3.MoveLast
lngRecordCount3 = rs3.RecordCount
rs3.MoveFirst
Else
lngRecordCount3 = 0
End If
'
If lngRecordCount3 > 0 Then
For Counter = 1 To lngRecordCount3
With rs3
.Edit
.Fields("Status") = 1
.Update
.MoveNext
End With
Next
Else
MsgBox "No Outstanding Floats ERROR"
End If
I have managed to figure out how to connect to the database using a dataadapter as follows but i'm not sure of the difference between dataadapters, datasets, datareaders etc. i've found various help files that show you how to insert records but cant seem to find any that just edit 1 field.
'
Dim strdb As String
Dim PocketConn As SqlCeConnection
Dim DataAdapter As SqlCeDataAdapter
Dim strCmd As SqlCeCommand
'
ds.Clear()
'
strdb = fcnFindDatabase("database.sdf")
'
PocketConn = New SqlCeConnection(String.Format("Data Source={0}", strdb))
'
strCmd = New SqlCeCommand("SELECT * FROM table WHERE Day = " & lngDayNo, PocketConn)
'
DataAdapter = New SqlCeDataAdapter()
DataAdapter.SelectCommand = strCmd
'
Try
PocketConn.Open()
' Catch the exception as e and pass it to the ShowErrors routine.
Catch err As SqlCeException
ShowErrors(err)
End Try
'
DataAdapter.Fill(ds, "Table")
'
PocketConn.Close()
'
dtgWalk.DataSource = ds.Tables("table")
dtgWalk.Refresh()
'
any assistance given would be appreciated.
The most efficient way (both on compact framework and MS Access database) will be executing an update query:
UPDATE Floats
SET Status = 1
WHERE AgentName = '" & lblAgentName.Caption & "' AND Status = 0
Dim conn as new SqlCeConnection(connString)
dim cmd as SqlCeCommand = conn.CreateCommand
cmd.CommandText = "UPDATE Floats SET Status = 1 WHERE AgentName = ? AND Status = 0"
cmd.Parameters.Add("@agentName", SqlDbType.NVarchar, 50).Value = lblAgentName.Caption
cmd.ExecuteNonQuery()
thank you for your help! and sorry about the merge, clicked the post button when i was previewing it!
Is there an alternative for editing more than 1 field at once. Eg. an address change would require at least 5 fields to be edited.
based on the above code and assuming that you can add more than 1 parameter the same as you would a select string i'm guessing it is possible to do it this way but it would make it very long and complex.
Again thank you for your assistance , it is appreciated, and i am more than happy to learn via help files (rather than what is sometimes seen as taking the easy route) but havent yet found the section that deals with this problem.
