problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

Problem with parameterized query.

I'm trying to use a parameterized query where the parameter is the table I'm querying from (i.e. "select * from @.datatable). Whenever I try to execute the report, I get an error stating that I "... must declare the table variable @.datatable". I think that's because my sql is not legal -- I haven't defined the variable to be an acutal table structure. Is that correct?

If so is there any other way to select from a table name that is dynamic using the paramteter structure that RDL supports? I have been able to do what I want by making the value of the commandtext element an expression that evaluates to the proper sql. Parameters help cut down on possible malicious SQL and I'm hoping to be able to make use of the supported parameter system in RS if possible.

Any help is appreciated.

You can't substitute a table name using standard parameterization. Basically, you would either need to write some sort of stored proc that constructed the SQL and used an EXEC statement or you can use an expression string for the query.

="SELECT * FROM [" & Parameters!TableName.Value & "]"

You need to be careful here about SQL injection. If someone passes in "FOO GO DROP DATABASE XXX" as the parameter, you are in for trouble. You would want to write some sort of function to vaildate the input.