在Pocket PC上使用VB.NET和SQLCE开发数据库程序的问题???

[xiaoyupzy]

在PPC 上开发一个数据库程序,使用的软硬件环境是:
Pocket PC (PPC 2003 系统)
SQL CE
Visual Basic.NET in VS 2003

问题是:现在有一个数据库Abpm.sdf, 其中有一个数据表 Paraset, 想要修改数据表Paraset中的记录
(Paraset中只有一条记录), 想给他的各个字段赋予新值, 使用下面的代码,但是总是有异常错误,望高人指点,谢谢!


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

Handles Button1.Click

Dim sqlDS As DataSet
Dim sqlDA As SqlCeDataAdapter

Dim myConnString As String = "Data Source=\My Documents\Abpm.sdf"
Dim myConnection As New SqlCeConnection(myConnString)
myConnection.Open()


sqlDA = New SqlCeDataAdapter("SELECT * FROM Paraset", myConnection)

sqlDA.Fill(sqlDS, "Paraset")

sqlDS.Tables("Paraset").Rows(0).Item(0) = 1


sqlDA.Update(sqlDS, "Paraset")

End Sub

[bitsbird]

把错误贴出来

[xiaoyupzy]

错误信息是:

一个托管的ArgumentException发生在Application::Run+0xf

Application::Run+0xf
Form1::Main+0xd

"确定"将终止

[mylovezcp]

如果你的数据库是在PC机上，使用RDA或者合并复制的方式建立表的话，在SQL SERVER的企业管理器中对表进行操作，建立主键，如果在POCKET PC上创建，使用DDL，如：
create table 表名
(
字段1 numeric(15) not null,
字段2 varchar(20) not null,
constraint PK_SSXX primary key (字段1)
)
go

[xiaoyupzy]

应该不是主键的事,我试了,用一个设置了主键的数据表来试还是同样的问题.
我是用代码由PPC自己生成数据库,建库的代码如下:

Sub createAbpmDB()
' if database does not exist, create one
If Not File.Exists("\My Documents\Abpm.sdf") Then
Dim connStr As String = "Data Source = \My Documents\Abpm.sdf"

Dim engine As New SqlCeEngine(connStr)
engine.CreateDatabase()
engine.Dispose()


Dim conn As SqlCeConnection = Nothing

Try
conn = New SqlCeConnection(connStr)
conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = "CREATE TABLE Patient(Pat_ID nvarchar(20) Primary Key NOT NULL, Case_Num nvarchar(20)," _
& " Department nvarchar(20), RoomNum nvarchar(10), Bed nvarchar(10), Name nvarchar(20), Sex nvarchar(6), Birthday datetime, Age int," _
& " Height int, Weight int, Med_Name nvarchar(20), Smoke nvarchar(6), State nvarchar(20)," _
& " City nvarchar(20), Zip nvarchar(10), Address nvarchar(40), Diagnosis nvarchar(200)," _
& " Phone nvarchar(30), HandPhone nvarchar(30), Email nvarchar(40), Medication1 nvarchar(20)," _
& " Medication2 nvarchar(20), Medication3 nvarchar(20), Medication4 nvarchar(20)," _
& " Medication5 nvarchar(20), Medication6 nvarchar(20), Medication7 nvarchar(20)," _
& " RefSys int, RefDia int, RefDate datetime, Other nvarchar(30) )"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE Dataset(Counter int, Pat_ID nvarchar(20),Orders int, StartDate datetime, DayStartTime datetime, DayInterval int,NightStaTim datetime, NightInterv int, TotalCount int, ErrorCount int,Remark nvarchar(20) )"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE Measure(Pat_ID nvarchar(20), Counter int, MeasureNo int, Sys int, Sys_kpa Float," _
& " Dia int, Dia_kpa Float, Map int, Map_kpa Float, Hr int, Date datetime," _
& " Time datetime, Status nvarchar(2), Comment nvarchar(20) )"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE Paraset(SysHigh int, SysLow int, DiaHigh int, DiaLow int, NormSysDay int, NormSysNight int, " _
& " NormDiaDay int, NormDiaNight int, DayStart datetime, DayEnd datetime, " _
& " NightStart datetime, NightEnd datetime, HospitalName nvarchar(40), Header nvarchar(40),Footer nvarchar(40) )"
cmd.ExecuteNonQuery()


' cmd.CommandText = "CREATE TABLE Para(SysHigh int, SysLow int, DiaHigh int, DiaLow int, NormSysDay int, NormSysNight int, " _
' & " NormDiaDay int, NormDiaNight int, DayStart datetime, DayEnd datetime, " _
' & " NightStart datetime, NightEnd datetime, HospitalName nvarchar(40), Header nvarchar(40),Footer nvarchar(40) )"
'cmd.ExecuteNonQuery()


cmd.CommandText = "CREATE TABLE Tempmeas(Pat_ID nvarchar(20), Counter int, StartDate datetime, TotalCount int, ErrorCount int)"
cmd.ExecuteNonQuery()


cmd.CommandText = "CREATE TABLE Temp_data1(Pat_ID nvarchar(20), Counter int, MeasureNo int, Sys int, Sys_kpa Float," _
& " Dia int, Dia_kpa Float, Map int, Map_kpa Float, Hr int, Date datetime," _
& " Time datetime, Status nvarchar(2), Comment nvarchar(20) )"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE Temp_data2(Pat_ID nvarchar(20), Counter int, MeasureNo int, Sys int, Sys_kpa Float," _
& " Dia int, Dia_kpa Float, Map int, Map_kpa Float, Hr int, Date datetime," _
& " Time datetime, Status nvarchar(2), Comment nvarchar(20) )"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE Deleted(Pat_ID nvarchar(20), Counter int, MeasureNo int, Sys int, Sys_kpa Float," _
& " Dia int, Dia_kpa Float, Map int, Map_kpa Float, Hr int, Date datetime," _
& " Time datetime, Status nvarchar(2), Comment nvarchar(20) )"
cmd.ExecuteNonQuery()

cmd.CommandText = "CREATE TABLE Temp(Pat_ID nvarchar(20), Counter int, MeasureNo int, Sys int, Sys_kpa Float," _
& " Dia int, Dia_kpa Float, Map int, Map_kpa Float, Hr int, Date datetime," _
& " Time datetime, Status nvarchar(2), Comment nvarchar(20) )"
cmd.ExecuteNonQuery()
Catch
Finally
conn.Close()
End Try

End If
End Sub

[xiaoyupzy]

大家可以在上面的代码中看到Paraset这个数据表, 我现在的目的就是修改这个表中某个字段或某几个字段的值, 可是我发现修改完了用sqlDA.Update(sqlDS, "Paraset") 语句就出错, 一运行到这句就出错, 可是不用这句, 修改的数据又没法重新写回到 Paraset表中.

[mylovezcp]

定义这个Dim sqlDA As SqlCeDataAdapter后，需要定义它的insertcommand,deletecommand,updatecommand和selectcommand四个对象，需要注意的是，必须要定义主键作为你更新的参数。

[mylovezcp]

代码参考
Public Shared Function CreateCustomerAdapter(conn As SqlConnection) As SqlDataAdapter

Dim da As SqlDataAdapter = New SqlDataAdapter()
Dim cmd As SqlCommand
Dim parm As SqlParameter

' Create the SelectCommand.

cmd = New SqlCommand("SELECT * FROM Customers " & _
"WHERE Country = @Country AND City = @City", conn)

cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15)
cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15)

da.SelectCommand = cmd

' Create the UpdateCommand.

cmd = New SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
"WHERE CustomerID = @oldCustomerID", conn)

cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")

parm = cmd.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original

da.UpdateCommand = cmd

Return da
End Function

[xiaoyupzy]

按照你说的,我把代码改成这样, 数据表Patient中主键是 Pat_ID 字段.

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim da As SqlCeDataAdapter = New SqlCeDataAdapter
Dim cmd As SqlCeCommand
Dim parm As SqlCeParameter

Dim strConnection As String = "Data Source=\My Documents\Abpm.sdf"
Dim conn As SqlCeConnection = New SqlCeConnection(strConnection)

cmd = New SqlCeCommand("UPDATE Patient SET Pat_ID = @Pat_ID " & _
"WHERE Pat_ID = @oldpatID", conn)

cmd.Parameters.Add("@Pat_ID", SqlDbType.NChar, 20, "Pat_ID")

parm = cmd.Parameters.Add("@oldpatID", SqlDbType.NChar, 20, "Pat_ID")
parm.SourceVersion = DataRowVersion.Original

da.UpdateCommand = cmd

写到这,下面应该怎么写, 比如说我要把Pat_ID 字段值为'123'的记录修改为'456',要怎么写?
怎么把@Pat_ID 赋值为'456', @oldpatID赋值为为'123' ?
最后如何执行这SQL语句?

[xiaoyupzy]

Dim da As SqlCeDataAdapter = New SqlCeDataAdapter
Dim cmd As SqlCeCommand

Dim strConnection As String = "Data Source=\My Documents\Abpm.sdf"
Dim conn As SqlCeConnection = New SqlCeConnection(strConnection)

conn.Open()

' Create the UpdateCommand.


cmd = New SqlCeCommand("UPDATE Patient SET Pat_ID = @Pat_ID " & _
"WHERE Pat_ID = '0123'", conn)

Dim parm As New SqlCeParameter("@Pat_ID", SqlDbType.NChar, 20, "Pat_ID")
parm.Value = "888"
parm.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(parm)


cmd.ExecuteNonQuery()

conn.Close()

End Sub

还是一样不行, 不是说这种错误:
一个托管的ArgumentException发生在Application::Run+0xf

Application::Run+0xf
Form1::Main+0xd

"确定"将终止

就是说这种:
一个托管的SqlCeException发生在Application::Run+0xf

Application::Run+0xf
Form1::Main+0xd

"确定"将终止

[mylovezcp]

Dim strConnection As String = "Data Source=\My Documents\Abpm.sdf"
Dim conn As SqlCeConnection = New SqlCeConnection(strConnection)

conn.Open()

Dim da As SqlCeDataAdapter = New SqlCeDataAdapter("select *from Patient ")
Dim cmd As SqlCeCommand
dim dt as new dataset

cmd = New SqlCeCommand("UPDATE Patient SET Pat_ID = @Pat_ID " & _
"WHERE Pat_ID =@Pat_ID", conn)
cmd.SqlCeParameter("@Pat_ID", SqlDbType.NChar, 20, "Pat_ID")
da.UpdateCommand = cmd

da.fill(dt)


dt.Tables("Patient").rows(DataGrid1.CurrentRowIndex).Item("Pat_ID") = "123"

Try
SqlDataAdapter1.Update(DataSet11)
Catch ex As Exception
MsgBox(ex.Source + " " + ex.Message)
End Try

conn.Close()

[mylovezcp]

Dim strConnection As String = "Data Source=\My Documents\Abpm.sdf"
Dim conn As SqlCeConnection = New SqlCeConnection(strConnection)

conn.Open()

Dim da As SqlCeDataAdapter = New SqlCeDataAdapter("select *from Patient ")
Dim cmd As SqlCeCommand
dim dt as new dataset

cmd = New SqlCeCommand("UPDATE Patient SET Pat_ID = @Pat_ID " & _
"WHERE Pat_ID =@Pat_ID", conn)
cmd.SqlCeParameter("@Pat_ID", SqlDbType.NChar, 20, "Pat_ID")
da.UpdateCommand = cmd

da.fill(dt)


dt.Tables("Patient").rows(DataGrid1.CurrentRowIndex).Item("Pat_ID") = "123"

Try
da.Update(dt)
Catch ex As Exception
MsgBox(ex.Source + " " + ex.Message)
End Try

conn.Close()

[xiaoyupzy]

还是不行,天啊!

[xiaoyupzy]

高分求解, 分不够再加!

[haoztao]

这方面没有弄过，帮顶！

[mylovezcp]

你的表没有定义主键吧，你定义主键后试试。

[xiaoyupzy]

怎么定义主键? 能不能说详细点?

[xiaoyupzy]

mylovezcp(笑傲江湖), 帮帮俺吧!

[xiaoyupzy]

问题总算解决了,结帖