SQL Database Connection not open?

Well here's a summary...
I've been trying now forever trying to get my VB to connect and insert a new record into an SQL database. I created a fresh VB form and added a SQL database named customers. Created a table named address which contained firstname,lastname,address,city,state,zip. I then created a data source using the wizard. All this i guess is irrelevant as i can't get even a basic SQL string to insert a new record. Here's the SQL string that i KNOW works because I executed it into the SQL pane:
INSERT address (FirstName, LastName, Address) VALUES ('MyFirst', 'MyLast', 'MyAddress')

That edits the database exactly as expected. I've tried several different methods...So here's the vb coding i've tried
inside of a button.click sub:
==============START VB============
        'first method --all manual
        Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\customers.mdf;Integrated Security=True;User Instance=True")
        Dim command1 As New System.Data.SqlClient.SqlCommand

        command1.CommandType = System.Data.CommandType.Text
        command1.CommandText = "INSERT address (FirstName, LastName, Address) VALUES ('myfirst', 'mylast', 'addresss')"
        command1.Connection = sqlConnection

        sqlConnection.Open()
        command1.ExecuteNonQuery()
        sqlConnection.Close()

        'second method --built in (automatically connects to database)
        AddressTableAdapter.Insert("first", "last", "addy", "cityy", "NNY", "2222")

        'third method --another way in the tutorial
        Dim newTableAdapter As New customersDataSetTableAdapters.addressTableAdapter

        newTableAdapter.Insert("myfirst", "mylast", "addy", "city1", "NY", "1333")

==============END VB=============

let me know what i'm doing wrong. I can pull data from the database and update records.. but i cannot insert a new record..... HELP!!!!

thanks

wade

Answer this question

SQL Database Connection not open?

RandyTheWebGuy

hi,

you said you have a dataset in your form that retrieve the data from your database why don't you add the new row to your dataset and call the table adapter to update the database its easier

Dim NewRow As Mydataset.MytableRow

NewRow = MyDataSetInstance.MyTAble.NewMytableRow

Newrow.MyFirstfield = Textbox1.text

'add the new values from your controls to the dataset

MyDatasetInstance.Mytable.AddMytableRow(NewRow)

'let the Table Adapter update the database

Me.MytableTableAdapter.Update(NewRow)

notice that MyDataset is the name of hte dataset in your solution explorer Mydatasetinstance is the name of the instance that exist in your form

i don't know much about SqlServer but i guess your insert statment is wrong in command text it should be like that also you creat a new instance of your tableadapter i don't know why you can use the existing table adapter

command1.CommandText = "Insert into Address (

hope it helps
Dlimanov

hi,

ok , i guess something wrong

first run your program and add some records then close it

2) open your project folder by your windows explorer, in your folder there are a debug folder open it , open bin folder copy the instance of your database in bin folder, then go back to your project folder and replace the database there by the copy that you have

3) open the database by vbexpress i guess you will find the changes

actualy you have 2 databases not one , the original one that in your folder and a copy that you test by it in your debug/bin folder your origional database every time affect the instance, but your instance will not affect back your database

Hope it will help
Lyric8
I like the idea that you're going with that method makes complete sense... but it didn't work... well kind of.... here's what i did:

=============BEGIN VB================
   Private Sub SaveCustomerButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveCustomerButton.Click
        Dim NewRow As customersDataSet.addressRow
        NewRow = CustomersDataSet.address.NewaddressRow
        NewRow.FirstName = FirstNameTextBox.Text
        NewRow.LastName = LastNameTextBox.Text
        NewRow.Address = AddressTextBox.Text
        NewRow.City = CityTextBox.Text
        NewRow.State = StateTextBox.Text
        NewRow.Zip = ZipTextBox.Text
        Me.AddressTableAdapter.Update(NewRow)
        Dim testString As String
        testString = NewRow.FirstName.ToString & NewRow.LastName.ToString & NewRow.Address.ToString & NewRow.City.ToString & NewRow.State.ToString & NewRow.Zip.ToString
        MsgBox(testString)
        Me.Close()
    End Sub

==================END VB=================
So using the msgbox... I test and ALL the input DOES go assigned into the "NewRow". The database still does not update when i compile it. Am i missing something still You referred to the "instance" I'm not sure exactly what you mean..... am i supposed to declare something or some how initiate an instance of the customersdataset Thanks in advance...

-wade-
Shiqin Xing

Could you be experiencing:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=174628&SiteID=1

Ed
SQL Database Connection not open?

Answer this question