vb 2005 insert into help

[TofiLuk]

Hi I have a problem with this code. I'm trying to save a values in the database.. When I click the save button the table is being updated and showing the new values I entered. But the problem is it is not actually saved in the database, when I re-run my program the new values are gone. Please help! Thanks!

Private Sub DataGridView1_Load()
        da = New SqlClient.SqlDataAdapter("SELECT * FROM tblEmployee", con)
        da.Fill(dt)
        DataGridView1.DataSource = dt
        DataGridView1.Refresh()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        da.InsertCommand = New SqlClient.SqlCommand("INSERT INTO tblEmployee([Last Name], [First Name], [Middle Initial], [Age], [Username], [Password])" & _
                                                    "VALUES('" & txtLastName.Text & "', '" & txtFirstName.Text & "', '" & txtMiddleInitial.Text & "', " & txtAge.Text & ", '" & txtUsername.Text & "', '" & txtPassword.Text & "')", con)
        da.InsertCommand.ExecuteNonQuery()
        da.Update(dt)
        dt.Clear()
        Call DataGridView1_Load()
        MsgBox("New user has been saved.", MsgBoxStyle.Information, "Sales and Inventory")
    End Sub

[kvprajapati]

Turn off Copy to Output Directory property of .mdf (database) file from the properties windows. Set Copy To Output Directory=Copy if newer.

[TofiLuk]

Hi adatapost. Thanks for the reply! Now it's saving even when I re-run my system, but when I open the table (tblEmployee) the data is not there and then when I re-run again the values are gone. Any suggestions or solutions?

Anyway I'm using VB 2005 Express.

[kvprajapati]

Read post #2. Set Copy To Output Directory=Copy if newer.

[TofiLuk]

@adatapost Yes, I've set Copy To Output Directory = Copy if newer.. that's why when I re-run my system, the data is saved.

But the next problem is when i open tblEmployee at the Database.mdf the data is not there.

Maybe I need to set/config something in my VB 2005 Express or what because when I use MS Acess as my database, it's saving..

[kvprajapati]

Please post connectionString used in your program here.

[TofiLuk]

Here it is..

con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

[kvprajapati]

Sorry! I missed second paragraph in post #5.

But the next problem is when i open tblEmployee at the Database.mdf the data is not there.

After setting Copy if newer, your program (code) is not updating the database (mdf) in your project folder which you are inspecting the value of tblEmployee, but your code updates a database file located under Bin\Debug folder.

[TofiLuk]

I see.. so what will I do to update my database (mdf) in my project? Please give me a hint on what code will i use or settings i need to change..

Thanks a lot adatapost

[kvprajapati]

>so what will I do to update my database (mdf) in my project?

Copying datafile at deployment folder is not an issue. Please take a look at these links:

1. http://cs.rthand.com/blogs/blog_with_righthand/archive/2005/11/04/166.aspx
2. http://msdn.microsoft.com/en-us/library/bb219481%28SQL.90%29.aspx
3. http://msdn.microsoft.com/en-us/library/ms233817.aspx

The connection string with |DataDirectory| will update database under Bin\Debug folder.

If you want to update database located in your project folder then you have to change connection string.

con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\folder\yourprojectolder\Database.mdf;Integrated Security=True;User Instance=True"

[TofiLuk]

Man thanks a lot you're a big help. It's working fine now! Is there any string that will work like App.Path in Visual Basic 6? So I can put my file in any folder I want.

[kvprajapati]

>Is there any string that will work like App.Path in Visual Basic 6?

Application.StartupPath property.

Dim CnStrstr As String = IO.Path.GetDirectoryName(IO.Path.GetDirectoryName(Application.StartupPath)) & "\file.mdf"

[TofiLuk]

I see.. Thanks again. I've learned a lot. More power to you and to www.daniweb.com :)