VB.NET updating SQL Database Command

Question

I am building an asp.net web application using vb and .net 2.0. I have a class that handles an object and all its attributes. This class correctly handles reading, creating, and deleting from the database. However, it will not update. I took the update script directly from the Microsoft SQL database, but I cannot figure it out. Here is my SQL command.

saveCommand = "UPDATE [Database].[Table] SET [name] = @name,[content] = @content,[date_updated] = @dateupdated WHERE ref = @ref"

This is then being put into...

Dim setCmd As New SqlCommand(saveCommand, sqlConn)

And then parameter values are put into it. It is weird because the "saveCommand" is determined on whether the item already exists (updates) or doesn't (switches to "INSERT INTO" command WHICH WORKS)

More Code

Public Class MyClass()
    #Region Properties
        Public name As String = ""
        Public content As String = ""
        Public date_updated As DateTime = DateTime.Now
    #End Region

    Public Sub Save()
    Dim sqlConn As New SqlConnection(sqlConnString)

    sqlConn.Open()

    Dim saveCommand As String
    If Me.Exists Then
        saveCommand = "UPDATE [Database].[Table] SET [name] = @name,[content] = @content,[date_updated] = @date_updated WHERE ref = @ref"
    Else
        saveCommand = "INSERT INTO [Database].[Table] ([name],[content],[date_updated],[ref]) VALUES(@name,@content,@date_updated,@ref)"
    End If

    Dim setCmd As New SqlCommand(saveCommand, sqlConn)
    setCmd.Parameters.Add("@name", Data.SqlDbType.VarChar).Value = Me.name
    setCmd.Parameters.Add("@content", Data.SqlDbType.VarChar).Value = Me.content
    setCmd.Parameters.Add("@date_updated", Data.SqlDbType.DateTime).Value = Me.date_updated

    If Me.Exists Then
        setCmd.Parameters.Add("@ref", Data.SqlDbType.Int).Value = Me.ID
    Else
        Dim countCmd As New SqlCommand("SELECT COUNT(*) FROM [DWM-DataSQL].[dbo].[biglots]", sqlConn)
        Me.ID = countCmd.ExecuteScalar() + 1
        setCmd.Parameters.Add("@ref", Data.SqlDbType.Int).Value = Me.ID
        Me.Exists = True
    End If

    setCmd.ExecuteNonQuery()
    sqlConn.Close()
    sqlConn.Dispose()
End Sub

Public Sub New()
End Sub

Public Sub New(ByVal num As Integer)
    Me.ID = num
End Sub

End Class

This is being called upon by

myObj = New BLL.MyClass(ref)

'When form is submitted
Public Sub submitForm(ByVal sender As Object, ByVal e As System.EventArgs)
   myObj.name = tbName.Text
   myObj.content = tbcontent.Text
   myObj.Save()
End Sub

Please post all the code relative to your operation. When you say it doesn't update it means that the record is not changed or that you get an error message? — Steve
– Steve, Commented Jun 26, 2014 at 14:03
I've just realized the only thing that successfully updates is the [date_updated] field — nwestfall
– nwestfall, Commented Jun 26, 2014 at 14:04
So actually the update works. Then you should post the code where you assign the values to the parameters. And have a look at the part where you got the values from. I guess it's a bug in your ASP.NET code. — Tim Schmelter
– Tim Schmelter, Commented Jun 26, 2014 at 14:09
@TimSchmelter I understand that but I don't know why my other values aren't going in. The subitForm() sub is the same one that I use to create a new row and it takes those values fine. Do I have to assign them another way? — nwestfall
– nwestfall, Commented Jun 26, 2014 at 14:37

nwestfall · Accepted Answer · 2014-06-26 15:39:01Z

1

I figured it out. It was somehow calling upon the read() function RIGHT before the save() function so it replaced all the values. Thanks Anyway

answered Jun 26, 2014 at 15:39

nwestfall

2192 gold badges5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.NET updating SQL Database Command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related