Not able to insert row in table. using *.sdf file

Question

2.33/5 (2 votes)

See more:

please help me with the following code

VB

Dim conn As SqlCeConnection = Nothing

     
      Try
          conn = New SqlCeConnection("Data Source = MyDb.sdf; Password =''")
          conn.Open()



          Dim cmd As SqlCeCommand = conn.CreateCommand()
          
          ''cmd.CommandText = "INSERT INTO Customer_Details ([Cust_Order], [Customer_Name]) Values('1', '" + txtCustomer.Text + "')"
          cmd.CommandText = "INSERT INTO Customer_Details (Cust_Order, Customer_Name) Values(1, '" + txtCustomer.Text + "')"
          cmd.ExecuteNonQuery()
         
          cmd.CommandText = "SELECT * FROM Customer_Details"
         
      Catch
          MessageBox.Show("Database not connected")
      End Try

I'm able to connect with database but not able to populate table with values.. Let me know where I am going wrong.Thank you in advance

Posted 8-Jan-14 0:07am

shwetap07

Add a Solution

Comments

Sergey Vaselenko 8-Jan-14 6:22am

You have no the execute command for the SELECT statement like cmd.ExecuteReader in the code sample.
Do you have it in your code?

shwetap07 8-Jan-14 6:56am

Yup.. I Tried.. but there is no effect... :(

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)

OriginalGriff · Accepted Answer · 2014-01-08T00:30:00

Solution 1

Exactly why that doesn't work is going to be down to you, for the most part.
The important thing is not to use anonymous catches - you are throwing away information you need in order to solve this.
Instead of writing:

VB

Catch
    MessageBox.Show("Database not connected")
End Try

Try this:

VB

Catch ex As Exception
    MessageBox.Show(String.Format("Unable to insert values:\n{0}", ex.ToString()))
End Try

Which will at least give you an overview of what is going wrong.

My guess? Either your table doesn't have those columns, or it has a value of 1, or it's an Identity field.

Posted 8-Jan-14 0:30am

OriginalGriff

Comments

shwetap07 8-Jan-14 6:51am

@OriginalGriff I tried as you said but catch block is not executing.
Try
conn = New SqlCeConnection("Data Source = MyDb.sdf; Password =''")
conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand()
MessageBox.Show("Command Created")
''cmd.CommandText = "INSERT INTO Customer_Details ([Cust_Order], [Customer_Name]) Values('1', '" + txtCustomer.Text + "')"
cmd.CommandText = "INSERT INTO Customer_Details (Cust_Order, Customer_Name) Values(1, '" + txtCustomer.Text + "')"
cmd.ExecuteNonQuery()
MessageBox.Show("Commmand Executed SuccessFully")
cmd.CommandText = "SELECT * FROM Customer_Details"
cmd.ExecuteReader()
'MessageBox.Show("Table Refreshed")
Catch ex As Exception
MessageBox.Show(String.Format("Unable to insert values:\n{0}", ex.ToString()))
End Try

OriginalGriff 8-Jan-14 7:08am

If the catch block isn't executing, then the problem is quite likely twofold: and you extra note kinda confirms that.
No catch means no error - the operation has succeeded - so the lack of updates has two reasons.
The first is that executing cmd.ExecuteReader doesn't do anything useful on it's own - it returns a SqlCeReader which can be used to look at the rows returned, but it doesn't change any data showing on your form. (You would need to use the reader it returned to do that).
The other is that it is quite likely that because you have added the file as an item, you are overwriting it into your debug folder every time. Try something for me: create a new folder of your root called "TestDB" and manually copy your database into that. Then change your connection string to explicitly refer to the newly copied file:
conn = New SqlCeConnection("Data Source = C:\MyDb.sdf; Password =''")
Now when you run your app, it will always be using the existing copy and updates your application makes should be preserved from run to run.

shwetap07 8-Jan-14 6:58am

Note: I have created database programmatically.... i.e via "Add New Items" in project.Is that a correct way ??

shwetap07 8-Jan-14 9:27am

@OriginalGriff thank you very much :) with your help was able to do.

OriginalGriff 8-Jan-14 9:33am

You're welcome!

shwetap07 8-Jan-14 9:43am

@OriginalGriff sorry to disturb you again... ;) I'm having problem with auto-increment of number with primary key. in sdf there is no mechanism of auto-increment of number,there is one data type call "rowversion" but this is not useful if i update the particular row.please help me with this... how can i increment field value on new insertion of record..

OriginalGriff 8-Jan-14 10:10am

Um. Yes you can - I just did! :laugh:
I did it in VS, (but it's as easy in SSMS) - just add your column, set "Allow Nulls" to "No" in the top pane, and set "Identity" to "True" in the lower one. Job done...

Though personally, I rarely use Identity columns: I prefer to use GUID IDs. Slightly more space, but can be assigned from the C# instead of the DB engine. I only really use Identity fields when I want a unique column identifier that I will probably never look at - log file entries and so forth. Orders, Users, Invoice lines etc. I will always use a GUID so that I know the value before I insert the record.

shwetap07 9-Jan-14 6:38am

@OriginalGriff "Identity" in the lower pane is disabled by default.... :( how do i enabled ??? please help.

OriginalGriff 9-Jan-14 7:16am

You can't enable it once you have data in the table, since you are giving control of that column to SqlCE it can't work round existing values - you need to delete the existing column and add a new one.

shwetap07 9-Jan-14 7:42am

@OriginalGriff thanks.. :) i made a silly mistake of datatype :p i.e. initially i assigned my "Cust_Order" column as "numeric" where it was not allowing me to set "identity" of column :P later on as u said to delete old and create new i did and now i came to known it was datatype which was creating problem.. Now have set that field as "int" datatype and now m able to set "identity" property as true :) thank you

OriginalGriff 9-Jan-14 7:50am

You're welcome!