Error writing updates back to database

K

KABOOM

Member
Joined
Jan 26, 2003
Messages
6
Im just a couple of hours into learning this VB.NET stuff and ive hit a bit of a snag. Im trying to store a new record back into my database. I know this is a pretty elementry question but I looked at my book and some online sites and I cant see what im doing thats any different. I am crapping out at the line:

dscmd.Update(ds, "Customers")

Below is my code, the above line can be found near the bottom:

Code: 
Imports System.Data
Imports System.Data.SqlClient


Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim strConnection As String = "Data Source=localhost;Initial Catalog=Northwind;User Id=someuser;Password=somepass; Trusted_Connection=yes"
    Dim cn As SqlConnection = New SqlConnection(strConnection)
    Dim strSelect As String = "SELECT ContactName, City FROM Customers"
    Dim dscmd As New SqlDataAdapter(strSelect, cn)
    Dim ds As New DataSet()
    Dim dt As DataTable

     SNIPPED OUT VB.NET GENERATED CODE 

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            cn.Open()
        Catch openException As SqlException
            MsgBox("Exception Caught: " & openException.Message)
        End Try

        Try
            dscmd.Fill(ds, "Customers")
        Catch fillException As System.Exception
            MsgBox("Exception Caught: " & fillException.Message)
        End Try

        cn.Close()
        dt = ds.Tables("Customers")
    End Sub

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        cn.Open()
        Dim newRow As DataRow = dt.NewRow()
        newRow("ContactName") = Me.txtName
        newRow("City") = Me.txtCity
        dt.Rows.Add(newRow)

        Try
            dscmd.Update(ds, "Customers")  PROBLEM LINE 
        Catch invalid As InvalidOperationException
            MsgBox("Exception Caught: " & invalid.Message)
        End Try
        cn.Close()
    End Sub
End Class
 
Last edited by a moderator:
When you say "crapped out," is that the text of the exception? Ive never had a MS thrown exception with that sort of message. More insight than "crapped out" would be helpful. Just based on what I see up there, I dont see you creating or setting the UpdateCommand of the dataadapter.

btw. I think naming the dataadapter cmd is quite confusing too.
 
Yes, when I say "crapped out", the error message was saying that I needed to throw the InvalidOperationException - so I did. Yah, no kidding the names are kinda confusing, you can blame OReilly books for that one.

In any case, I kinda got past my error and have a new one at the same place. Below is my NEW btnSubmit subroutine. I can an error stating:
"String or binary data would be truncated"

Any help would be much appreciated.

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim autogen As New SqlCommandBuilder(dscmd)
cn.Open()
Dim newRow As DataRow = dt.NewRow()
newRow("CustomerID") = Me.txtID
newRow("ContactName") = Me.txtName
newRow("City") = Me.txtCity
dt.Rows.Add(newRow)

Try
dscmd.Update(ds, "Customers")
Catch invalid As System.Data.SqlClient.SqlException
MsgBox("Caught Exception: " & invalid.Message)
End Try
cn.Close()
End Sub
 
The problem is when u assign the values to newrow:
you r trying to assign the textbox object and not the text itself
newRow("CustomerID") = Me.txtID

The correct syntax should be
newRow("CustomerID") = Me.txtID.text
 
Back
Top