none
Basic Database Tasks RRS feed

  • Question

  • Hi,

     

    I'm trying to do some basic database tasks in VS 2008 Express Edition (Visual Basic), but am finding them quite difficult. One such task is to simply INSERT new data into a database. Here's what I've done (I've gone through each element so that maybe someone can see where I've slipped up)...

     

    1. Created a new project.

    File > New Project > Windows Forms Application > Name: test

     

    2. With Form1.vb open, I add a textbox and a button to the form.

     

    3. I now move over to the Solution Explorer window, right-click on test project name and add a database.

    Add > New Item > Local Database > Name: testDB.sdf

     

    4. The "Data Source Configuration Wizard" opens I leave the dataset name as is (testDBDataSet) and click Finish.

     

    5. I then open the "Database Explorer" window open up the testDB.spf branch and right click on the "Tables" folder and select "Create Table".

     

    6. In the "New Table" window I enter the following:

    Name: AAA

    1st Column: (Name) id (Data Type) int (Length) 4 (Allow Nulls) No (Unique) Yes (Primary Key) Yes (Identity) True (IdentityIncrement) 1 (IdentitySeed) 1

    2nd Column: (Name) name (Data Type) nvarchar (Length) 100 (Allow Nulls) No (Unique) Yes (Primary Key) No

     

    7. I then go to over to the "Data Sources" window, right-click on testDBDataSet and select "Configure Dataset With Wizard..." I ensure that the Tables box is selected and click finish.

     

    8. I then open the newly inserted AAA table, and select name and drag it over to the textbox1 element on Form1. As a result of this the following is displayed down the bottom: TestDBDataSet, AAABindingSource, AAATableAdapter, TableAdapterManager

     

    9. Okay, from here, I double click on the button1 element which takes me to the code section of Form1. I enter the following code in the Button1_Click event:

     

    Me.AAATableAdapter.Insert(Me.TextBox1.Text)

     

    I then save all this (as test), and run. The form then pops up, I type in some gibberish (aaaa) in the textbox and click the button. Now from what I've written I would've thought that this would've added my textbox gibberish into the testDB.sdf database.

     

    10. So I close down the form, go to the database explorer, right-click on the AAA table and select "Show Table Data" and it shows nothing!?

     

    I have also tried using the following code at step 9:

     

    Dim newRow As testDBDataSet.AAARow

    newRow = Me.TestDBDataSet.AAA.NewAAARow

    newRow.name = "hello"

    Me.TestDBDataSet.AAA.Rows.Add(newRow)

    Me.AAATableAdapter.Update(Me.TestDBDataSet.AAA)

     

    But to no avail, or using the following code (again at step 9)...

     

    Dim newTA As New testDBDataSetTableAdapters.AAATableAdapter

    newTA.Insert(Me.TextBox1.Text)

     

    Now I will note that when I click the button twice it will throw an error stating that the database cannot allow the gibberish in the textbox as it already exists (provided the gibberish in the textbox has stayed the same upon both clicks of the button).

     

    I know I'm missing something quite obvious and simple, but I can't for the life of me figure out what it is.

     

    Please help!

     

    Thank you,

    Ryan 

    Wednesday, December 5, 2007 10:59 AM

Answers

  •  

    I ended up using the following to get my basic form to insert data into the SQL CE  database (test). In case anyone else has troubles here's what I did...

     

    1. Clicked on testDB.sdf file in Solution Explorer window, and changed the "Copy to Output Directory" to "Do not copy"

     

    2. Changed the Button1_Click event to only read the following...

     

    Dim conn As New System.Data.SqlServerCe.SqlCeConnection("Data Source=C:\Documents and Settings\ABC\My Documents\Visual Studio 2008\Projects\test\test\testDB.sdf;Persist Security Info=False;")

    Dim cmd As New System.Data.SqlServerCe.SqlCeCommand

    cmd.CommandText = "INSERT AAA (name) VALUES ('" & Me.TextBox1.Text & "')"

    cmd.Connection = conn

    Try

    conn.Open()

    cmd.ExecuteNonQuery()

    conn.Close()

    Catch ex As Exception

    If ex.Message.Contains("duplicate") Then

    MsgBox("Duplicate enter please try something different.")

    Me.TextBox1.Text = ""

    Else

    MsgBox(ex.ToString)

    End If

    End Try

     

    3. Upon saving and running the script I was able to enter stuff into the textbox, click on the button and after closing the program open up the "Database Explorer" view the table data and see my data added!!

     

    Thanks to all who helped.

     

    Ryan

     

     

    Friday, December 7, 2007 6:24 AM

All replies

  • (testDB.sdf)

     

    That is the data file for SQL Server CE not the regular SQL Server which uses MDF(Microsoft data File) and it is best practice to create your database in SQL Server cleanup connection by using the connection property at the top of Visual Studio to test your connection before using the database for development.

    Wednesday, December 5, 2007 8:13 PM
  • When you're working with a local database (with SQL Server Express or, I assume, Compact), VS's default build behavior is to copy the database to the output directory.  It's very likely that your program is in fact updating the database, but because you're looking at your development copy of the database instead of the copy in the output directory, you're not seeing then changes.  See the FAQ topic at the very top of this forum's posts.

    Wednesday, December 5, 2007 8:15 PM
  • Hi Robert,

     

    Thank you for your help.

     

    I read the above FAQ post, and changed the database setting to "Do Not Copy", however, whenever I run the form I receive an error message stating that the database cannot be found. It details the location of the database, and when I physically open up explorer and navigate to the same location the file IS there!?

     

    Any ideas on why it is doing this? Do I need to upgrade to an MDF database?

     

    Thanks,

    Ryan

     

    Thursday, December 6, 2007 1:31 AM
  •  

    I ended up using the following to get my basic form to insert data into the SQL CE  database (test). In case anyone else has troubles here's what I did...

     

    1. Clicked on testDB.sdf file in Solution Explorer window, and changed the "Copy to Output Directory" to "Do not copy"

     

    2. Changed the Button1_Click event to only read the following...

     

    Dim conn As New System.Data.SqlServerCe.SqlCeConnection("Data Source=C:\Documents and Settings\ABC\My Documents\Visual Studio 2008\Projects\test\test\testDB.sdf;Persist Security Info=False;")

    Dim cmd As New System.Data.SqlServerCe.SqlCeCommand

    cmd.CommandText = "INSERT AAA (name) VALUES ('" & Me.TextBox1.Text & "')"

    cmd.Connection = conn

    Try

    conn.Open()

    cmd.ExecuteNonQuery()

    conn.Close()

    Catch ex As Exception

    If ex.Message.Contains("duplicate") Then

    MsgBox("Duplicate enter please try something different.")

    Me.TextBox1.Text = ""

    Else

    MsgBox(ex.ToString)

    End If

    End Try

     

    3. Upon saving and running the script I was able to enter stuff into the textbox, click on the button and after closing the program open up the "Database Explorer" view the table data and see my data added!!

     

    Thanks to all who helped.

     

    Ryan

     

     

    Friday, December 7, 2007 6:24 AM