Adding Record to table

[yaosman]

Hi Folks, I need quick help. I was access 2003 (OLeDB) as a database and all was fine. Now I have decided to use sql 2005 and that is where the problem is. I am listing the code below;

General declaration I have this;

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

At form load I have this code

 con.ConnectionString = "Data Source=HEADIT\SQLLOCAL;Initial Catalog=Address;Persist Security Info=True;User ID=sa;Password=logs1lumber"
        con.Open()
        Sql = "SELECT * FROM tblContacts"
        da = New SqlDataAdapter(sql, con)
        da.Fill(ds, "AddressBookDA")
        con.Close()

To add the record the code is as shown

            Dim dsNewRow As DataRow
            dsNewRow = ds.Tables("AddressBookDA").NewRow()
            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Surname") = txtSurname.Text
            dsNewRow.Item("Address1") = txtAddress1.Text
            ds.Tables("AddressBookDA").Rows.Add(dsNewRow)
            da.Update(ds, "AddressBookDA")
            MsgBox("New Record added to the Database")

As I said it used to work fine ith access but it doesn't work with sql server 2005. It gives the error message;

Update requires a valid InsertCommand when passed DataRow collection with new rows.

da.update is highlighted.

 

[daniel02_0403]

I'm not so sure but just try to look at this url for connection strings

ConnectionStrings.com - How to connect to SQL Server 2005

 

[cjard]

Your data apadter has no SqlCommand (containing an INSERT sql) in the .InsertCommand property.

Youre doing your data access in a bad way. Read the DW2 link in my signature, section "Creating a Simple Data App"

 

[cjard]

I'm not so sure but just try to look at this url for connection strings

Won't help. He hasnt used a commandbuilder to generate a valid insert command, nor has he written one. Nothing to do with the connstr I'm afraid. If you ever see anyone posting button click code that contains SQL in a string, refer them to DW2 - we need to educate the world that there are better ways of doing data access than following some ****e old tutorial from the '90s that describes a data access method that was out of date by the end of the '80s

 

[yaosman]

Thanks, I was trying to avoid the insert command,
When I addeded this snippet
adapter.InsertCommand = New SqlCommand("Insert into dbo.Categories (CategoryID,CategoryName) VALUES ('" & Me.txtCategoryID.Text & "','" & Me.txtCategoryName.Text & "')", _
it worked perfectly.

Thanks

 

[cjard]

"it worked perfectly"

Oh, if only you knew how far from "perfect" your definition of "perfectly" is..


"A man maie well bring a horse to the water, But he can not make him drinke without he will." - John Heywood, 1546