Simple SQL Database Issues!!!

[soulcode]

Hi Guys,

I've been working the this problem for two days now and I am starting to get really frustrated!! All I need to do is to be able to add a new row into a database table with some new data in it! But even the following example code does not work for me...

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click
        MsgBox("Attempting to update a DataTable that is Nothing...")
        Dim cn As New SqlConnection("Server=vcomp;" & _
                                    "integrated security=true;database=northwind")
        Dim da As New SqlDataAdapter("Select * From Customers", cn)
        Dim cb As New SqlCommandBuilder(da)
        Dim ds As New DataSet
        Dim dr As DataRow

        da.Fill(ds, "Customers")

        dr = ds.Tables("Customers").NewRow

        ds.Tables(0).Rows(0)!ContactName = "New_Contact"
        ds.Tables(0).Rows.Add(dr)

        da.Update(ds, "Customers")

        cn.Close()
        cn = Nothing
    End Sub

This example is from the Microsoft Knowledgebase with some lines added in to insert a row and add 'New_Contact' in the ContactName field.

I get the following error on the da.Update(ds, "Customers") line...

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

What is wrong?? What am I missing?? Please help me

[robertx]

I haven't tried that method of inserting rows but give this a try:

I am assuming that the Customers table has 2 fields - CustomerID (identity field) and ContactName.

Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection("Server=vcomp;" & _
                                    "integrated security=true;database=northwind")
 
SQL = "INSERT INTO Customers(ContactName) VALUES('New_Contact')"
 
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(SQL)
 
myConnection.Open()
 
myCommand.Connection = myConnection
 
myCommand.ExecuteNonQuery()

[alex_read]

Here's how I'd tackle a database insert with ADO.Net:

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim cnnADONet As SqlClient.SqlConnection = New SqlClient.SqlConnection
        Dim cmdSQL As SqlClient.SqlCommand = new SqlClient.SqlCommand 
 
    MsgBox("Attempting to update a DataTable that is Nothing...")
 
    cnnADONet.ConnectionString = ("Server=vcomp;" & _
        "integrated security=true;database=northwind")
    cnnADONet.Open()
 
    If (cnnADONet.State = ConnectionState.Open) then
        cmdSQL.Connection = cnnADONet
        cmdSQL.CommandText = "INSERT INTO Customers(ContactName)" & _
        "VALUES('New_Contact')"
        cmdSQL.CommandType = CommandType.Text 
 
        Msgbox (cmdSQL.ExecuteNonQuery & " rows affected")
    End If
 
    cmdSQL.Dispose 
        cmdSQL = nothing 
 
    cnnADONet.Close 
    cnnADONet.Dispose 
    cnnADONet = nothing 
 
    MsgBox("Finished")
End Sub

[alex_read]

Damn, beat me to it

[soulcode]

Well, with all your help and alot of searching on the internet, I came to the solution. In the end I modified the following code from this link:: http://msdn.microsoft.com/library/de...singadonet.asp

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oAdapter As SqlClient.SqlDataAdapter
        Dim oBuild As SqlClient.SqlCommandBuilder
        Dim sqlConn As New SqlConnection
        Dim oDR As DataRow
        Dim strSQL As String
        Dim moDS = New DataSet

        sqlConn = New SqlConnection("Data Source = LocalHost; Initial Catalog = vTest; User Id=sa")
        sqlConn.Open()
        oAdapter = New SqlDataAdapter("SELECT * FROM Customer", sqlConn)
        oAdapter.Fill(moDS, "Customer")

        oDR = moDS.Tables("Customer").NewRow()
        oDR.BeginEdit()

        oDR("CustID") = "TestID"
        oDR("CustName") = "TestCustName"

        oDR.EndEdit()
        moDS.Tables("Customer").Rows.Add(oDR)


        strSQL = "SELECT * FROM Customer "
        oAdapter = New SqlClient.SqlDataAdapter(strSQL, sqlConn)
        oBuild = New SqlClient.SqlCommandBuilder(oAdapter)

        oAdapter.InsertCommand = oBuild.GetInsertCommand()

        oAdapter.Update(moDS, "Customer")
        moDS.AcceptChanges()

    End Sub

I didnt expect that something as simple as adding a new row to a database could take me so long to find a solution for!!!

But, hope the above example helps the next guy thats has a similar problem to solve.

Cheers guys.


.