How Do I Update Existing Sql Ce Records

Question

0.00/5 (No votes)

See more:

Hie
My name is Jan Makopa.
I am working torwards a project to read and display information from MODBUS device. I want to create an SQL CE database in vb.net programmatically. The database will contain custimized fields. So far i have achieved the following:
- Creating SQLCE Programatically
- Creating Tables and Columns
- Deleting Records
-Refreshing Database

Problem:
I am failing to pass a quiry to SQL to edit existing record
The source code i currently have for UPDATING is as follows:
***************************************************************
Dim Description As String = "Hello Code Project"

Dim Row() As Data.DataRow
Row = ds.Tables("Modbus").Select("CoilID = 54")
Row(0)("Register") = Description
cmd.ExecuteNonQuery()
****************************************************************

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Modbus is the name of the Table
CoilID is the name of the KeyID column
Registr is the name of the column where i want to make changes
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That i just thought i could clarify so that it makes it easy for someone who is willing to
assist me.

************************************************************
when i try to update it gives the following error is generated:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Additional information: ExecuteNonQuery: Connection property has not been initialized.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Problem is i dont know what connection property has not been "initialized" in the quiery

I was kindly requesting you to assist me with the problem that i am facing. Please a sample of updating a record is good enough. I have exhausted a number of links from our beloved search engine "Google" and that is why i came to this forum to seek for your assistance.

Thank you in advance gentleman

Posted 19-Sep-14 10:09am

PCOMM Engineering

Add a Solution

Comments

[no name] 19-Sep-14 16:22pm

You need to create a connection to your database using an instance of SqlCeConnection. See http://arcanecode.com/2007/04/13/sql-server-compact-edition-with-c-and-vbnet/

Abdul Samad KP 19-Sep-14 17:23pm

Have you assigned a connection to the command?
like
cmd.Connection=yourconnection

PCOMM Engineering 20-Sep-14 14:05pm

Hie
I have managed to extract certain information from the link you gave me and it worked. I just created a sample database and i put the following update query:
Try
Dim Description As String = TextBox4.Text

Dim Row() As Data.DataRow
Row = ds.Tables("Persons").Select("PersonsID = 54")
Row(0)("Name") = Description

Dim q As New SqlCeCommand("UPDATE Persons SET Name=@Name,Phone=@Phone where PersonsID = 54", conn)
For i = 0 To ds.Tables("Persons").Rows.Count - 1

q.Parameters.Add(New SqlCeParameter("@Name", ds.Tables("Persons").Rows(i)("Name")))
q.Parameters.Add(New SqlCeParameter("@Phone", ds.Tables("Persons").Rows(i)("Phone")))

q.ExecuteNonQuery()
Next

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
At last, after two days of scratching my head. It really worked fine, thanks to you man.

PCOMM Engineering 20-Sep-14 14:06pm

I hope our thread is going to help other people facing the same problem

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PCOMM Engineering · Answer 1 · 2014-09-20T08:04:00

Hie
I have managed to extract certain information from the link you gave me and it worked. I just created a sample database and i put the following update query:
Try
Dim Description As String = TextBox4.Text

Dim Row() As Data.DataRow
Row = ds.Tables("Persons").Select("PersonsID = 54")
Row(0)("Name") = Description

Dim q As New SqlCeCommand("UPDATE Persons SET Name=@Name,Phone=@Phone where PersonsID = 54", conn)
For i = 0 To ds.Tables("Persons").Rows.Count - 1

q.Parameters.Add(New SqlCeParameter("@Name", ds.Tables("Persons").Rows(i)("Name")))
q.Parameters.Add(New SqlCeParameter("@Phone", ds.Tables("Persons").Rows(i)("Phone")))

q.ExecuteNonQuery()
Next

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
At last, after two days of scratching my head. It really worked fine, thanks to you man.