trouble with inserting data into db table with VB 2008

[roller828]

I'm trying to insert Username, Password, and Account Balance data into my SQL Database. The code debugs correctly, but when I run the program, I'm not able to use the information I registered with to login with. (I have a separate form that lets me login with a username and password, and that works fine, so it has to be something to do with my register form.) Was this enough information?

Imports System.Data.SqlClient
Public Class register
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Then
            MsgBox("Please enter text into *all* text boxes.")
        Else
            Dim conn As New SqlConnection
            Dim comm As New SqlCommand
            Dim adapter As New SqlDataAdapter
            Dim ds As New DataSet

            conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            comm.CommandText = "INSERT INTO users (username,password,balance) VALUES (" & TextBox1.Text & "," & TextBox2.Text & "," & TextBox3.Text & ")"
            conn.Open()
            MsgBox("Registered!")
        End If
        Me.Close()
    End Sub
End Class

[jmcilhinney]

Follow the first link in my signature and read about managing local data files. That will, I think, cover your confusion. If not then post back with any further queries you may have.

[roller828]

Hey, thank you for helping me out. I went to the msdn link in your sig., but i didn't see any help in there. I messed with the "Copy to Output Dir." thing, but it's at its default state right now, which is the correct one, from what I read (Copy always). I'm using the same connection string in my register form, as I am in my login form:

conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

However, I still didn't fully understand what it was talking about in that link about how there are 2 different database "files"? But, I changed the AttachDbFilename parameter in the connection string to the directory the database was in inside both the register and login form... but still no luck.

[jmcilhinney]

Here's what happens. When you add a database to your project it creates an MDF file, which is a SQL Server database, and adds it to your project source folder, along with your VB code files, etc. That is the database that you should build the schema in and add any default data because that is your template.

Now, when you build your project all your source files are processed and the result of that processing is placed in the output folder. It's that output that you run in the debugger. If your database is set to copy to the output folder then, as you'd expect, a copy is created in the output folder. This is desirable because working with a copy means that your template stays clean.

Now, consider what happens if you have it set to copy always. You run the project in the debugger and insert some data. You then exit the app and run it again. The database will copy again, thus it will overwrite the old copy you added the data to, thus your test data goes bye-bye. That's why copy if newer is better. Your test data will survive across multiple sessions and will only be lost when you either make changes to the source database, thus making it newer, or set it to copy always to force a refresh.

Note that the data you add during testing is added to the copy of the database that's in the Debug folder. When you build a Release version to deploy, a whole new copy of the source database is created and placed in the Release folder. As I said earlier, if you have any default data you want in every database all the time then that needs to be put into your original source database, which you would do through the IDE, not through your app.

[roller828]

hmm, okay. That made more sense that time. But I still can't get it to load the users I entered from my register from. I changed the connection string to the correct directory (the debug dir.), and I set the database to "Copy if Newer". Any other ideas? I have teamviewer if anyone would like to take a look.

[jmcilhinney]

Looking more closely at your code from post #1, you're creating the SQL code to insert a record but you're not executing it. Follow the Database FAQ link in my signature and follow some of the links in the VB.NET section. The second one is one of mine and provides code examples for common scenarios, including inserting a single record.