Problem updating Database

[Kristofferson]

Alright, so my issue is that I have a staff profile. When you press the button it brings you to the Edit Profile page with text boxes with the original information in them so they can be edited. Now I ask for the edited information to be updated when the update button is clicked but it won't update. I know that I need to call new versions of the information from the text boxes but I'm not exactly sure how to go about this. I have use the "Request.Item" elsewhere in my code but you usually have to define a "Server.MapPath" which I'm not sure I can do in the confines of the update statement. Any help, hints, or ideas would be appreciated.

Also this is my first thread so if I do something wrong with the code tags please forgive me, I will learn quickly.

VB Code for the entire Staff Member Edit Profile Page below:

Dim MyCmd As String
    
    Sub Page_Load(ByVal s As Object, ByVal e As EventArgs)
    
        If Not Page.IsPostBack Then
          
        End If
    
        ' Create our Variables

        Dim MyConn As OleDbConnection
        Dim MyDA As OleDbDataAdapter
    
        Dim DS As DataSet
        Dim dvwEmployees As DataView
    
        ' Set up the DataView cache

        dvwEmployees = Cache("Employee")
        If dvwEmployees Is Nothing Then
            MyConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Christopher Carr\Documents\Visual Studio 2008\WebSites\MYTPSS new\tpsDatabase.accdb; Persist Security Info=False;")
            MyDA = New OleDbDataAdapter("Select * From Employee Where Employee_ID = " & Session.Item("userName") & " ", MyConn)
    
            DS = New DataSet()
            MyDA.Fill(DS, "Employee")
    
            dvwEmployees = DS.Tables("Employee").DefaultView()
            Cache("Employee") = dvwEmployees
        End If

        ' Display the information using labels
        
        lblWelcomeFName.Text = dvwEmployees(0).Row("First_Name")
        lblWelcomeLName.Text = dvwEmployees(0).Row("Last_Name")
        txtStaffID.Text = dvwEmployees(0).Row("Employee_ID")
        txtFirstName.Text = dvwEmployees(0).Row("First_Name")
        txtLastName.Text = dvwEmployees(0).Row("Last_Name")
        txtPhone.Text = dvwEmployees(0).Row("Phone")
        txtEmail.Text = dvwEmployees(0).Row("E-mail")
        txtAddress.Text = dvwEmployees(0).Row("Address")
        txtAvailability.Text = dvwEmployees(0).Row("Availability")
        
    End Sub

    Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim myTransaction As System.Data.OleDb.OleDbTransaction

        Try

            ' Declares connection and variables and opens connection to database
            Dim conn As New OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\Users\Christopher Carr\Documents\Visual Studio 2008\WebSites\MYTPSS new\tpsDatabase.accdb")
            conn.Open()
            Dim command As OleDbCommand = conn.CreateCommand()
            Dim strSQL As String

                        
            ' Adds and New Transaction
            myTransaction = conn.BeginTransaction

            ' Sets what strSQL display value is
            strSQL = "Update [Employee] Set First_Name = " & txtFirstName.Text & ", Last_Name = " & txtLastName.Text & ", Phone = " & txtPhone.Text & ", E-mail = " & txtEmail.Text & _
            ", Address = " & txtAddress.Text & ", Availability = " & txtAvailability.Text & " Where Employee_ID = " & txtStaffID.Text & ""

            ' Gets a value indicating how the CommandText property is to be interpreted and CommandText is set equal to the strSQL value
            command.CommandType = CommandType.Text
            command.CommandText = strSQL

            ' Starts the transaction
            command.Transaction = myTransaction

            ' Performs the cataloging operations for update, insert and delete statements
            command.ExecuteNonQuery()

            ' Commits or saves transaction to the database
            myTransaction.Commit()

            ' Closes the connection to the database
            conn.Close()

            lblError.Text = "Your profile was successfully updated."
            
            
        Catch ex As Exception

           
            lblError.Text = "Your profile was unable to be updated."
            
        End Try

    End Sub

[GeekByChoiCe]

do you get an error or its just not updating?

[Kristofferson]

It just not updating no error. The label message in the Catch ex As Exception comes up.

[GeekByChoiCe]

change the
lblError.Text = "Your profile was unable to be updated."
to
lblError.Text = ex.message

then you see the error.

[Kristofferson]

It comes up with "Syntax error in UPDATE statement." And when I put a breakpoint on my Update statement all the values aren't the new values but the old ones. I keep looking at the update statement but I don't see anything wrong. Of course I have inexperienced eyes, only been coding a total of like 16 to 20 weeks over the past year.

[TomW]

Each of the string/text field values should be surrounded by single quotes in your update statement. However I would suggest changing this concatenated string to use parameters instead;. it will take care of not having to surrond the values in quotes and the database wont have to parse your statement multiple times times to format it for missing parameters.

[babbu]

i think there are extra inverted commas at the end of the update statement

[TomW]

Comma's look fine although the double set of quotes at the end of the where clause is not needed (it shouldnt hurt anything). Also in your catch block you should include a myTransaction.Rollback

[Kristofferson]

Alright I added the myTransaction.Rollback which was giving me errors but works now for some reason.

I have not used parameters at all before, so can you point me to a good resource to learn how to setup and use parameters. I have already attempted to create my parameters which seems straight forward but how do I actually implement them in the Update Statement?

I haven't said it yet but thanks for the help so far.

[TomW]

Parameters are pretty easy, I dont know why they dont push it more then the concatenated strings. Below is a quick example and here is a link for some more detailed info Configuring Parameters and Parameter Data Types (ADO.NET)

strSQL = "Update [Employee] Set First_Name = @FName And Last_Name = @LName  Where Employee_ID =  @Id"

command.Transaction = myTransaction       
command.CommandType = CommandType.Text            
command.CommandText = strSQL

Command.Parameters.AddWithValue("@FName", txtFName.Text)
Command.Parameters.AddWithValue("@LName", txtLName.Text)
Command.Parameters.AddWithValue("@Id", cint(txtStaffId.Text))

command.ExecuteNonQuery()

[Kristofferson]

I like the ease of the parameters I will try to implement it more when I code, but I still get the same error when I run it. "Syntax error in the UPDATE statement." Could it be a problem with the parameters in this case or is there still a problem with the update statement only.

[kvprajapati]

You have to turn on "write" permission to the Users group for the database file.

[Kristofferson]

Well what I did earlier is set my UPDATE statement to

Update Employee Set Phone = ""

Just to see if the the update statment syntax was correct. It worked it effectively erased the entire Phone column. Wouldn't that mean that I can write to the database?

[kvprajapati]

Can I see the update statement you wrote?

[TomW]

Relooking at my example, my syntax was incorrect and I hope you caught this mistake. I didnt use a comma between each of the columns & values and instead used the key word "And". Thats what I get for attempting to code in this webpage textbox... lol

strSQL = "Update [Employee] Set First_Name = @FName, Last_Name = @LName  Where Employee_ID =  @Id"

Parameters are pretty easy, I dont know why they dont push it more then the concatenated strings. Below is a quick example and here is a link for some more detailed info Configuring Parameters and Parameter Data Types (ADO.NET)

strSQL = "Update [Employee] Set First_Name = @FName And Last_Name = @LName  Where Employee_ID =  @Id"

command.Transaction = myTransaction       
command.CommandType = CommandType.Text            
command.CommandText = strSQL

Command.Parameters.AddWithValue("@FName", txtFName.Text)
Command.Parameters.AddWithValue("@LName", txtLName.Text)
Command.Parameters.AddWithValue("@Id", cint(txtStaffId.Text))

command.ExecuteNonQuery()

[sknake]

Well what I did earlier is set my UPDATE statement to

Update Employee Set Phone = ""

Just to see if the the update statment syntax was correct. It worked it effectively erased the entire Phone column. Wouldn't that mean that I can write to the database?

Yes that does indicate you can write to the database. Follow the suggestion TomW posted. Always use parameterized SQL. Dynamically build parameterized SQL statements if you need to for search screens or reports but never put user input in your raw TSQL command text.

[Kristofferson]

So here is my code as it stands now. The update statement is setup according to the way TomW recommended. But I still get the error "Syntax error in the UPDATE statement."

Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim myTransaction As System.Data.OleDb.OleDbTransaction

        Try

            ' Declares connection and variables and opens connection to database
            Dim conn As New OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\Users\Christopher Carr\Documents\Visual Studio 2008\WebSites\MYTPSS new\App_Data\tpsDatabase.accdb")
            conn.Open()
            Dim command As OleDbCommand = conn.CreateCommand()
            Dim strSQL As String
            
            ' Adds and New Transaction
            myTransaction = conn.BeginTransaction

            ' Sets what strSQL display value is
            strSQL = "Update Employee Set First_Name = @FirstName, Last_Name = @LastName, Phone = @Phone, E-mail = @Email, Address = @Address, Availability = @Availability Where Employee_ID = @EmployeeID"
            
            ' Gets a value indicating how the CommandText property is to be interpreted and CommandText is set equal to the strSQL value
            command.CommandType = CommandType.Text
            command.CommandText = strSQL

            ' Starts the transaction
            command.Transaction = myTransaction
            
            command.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
            command.Parameters.AddWithValue("@LastName", txtLastName.Text)
            command.Parameters.AddWithValue("@Phone", txtPhone.Text)
            command.Parameters.AddWithValue("@Email", txtEmail.Text)
            command.Parameters.AddWithValue("@Address", txtAddress.Text)
            command.Parameters.AddWithValue("@Availability", txtAvailability.Text)
            command.Parameters.AddWithValue("@EmployeeID", CInt(txtStaffID.Text))

            ' Performs the cataloging operations for update, insert and delete statements
            command.ExecuteNonQuery()

            ' Commits or saves transaction to the database
            myTransaction.Commit()

            ' Closes the connection to the database
            conn.Close()

            lblError.Text = "Your profile was successfully updated."
            
                       
        
        Catch ex As Exception

           
            lblError.Text = ex.Message
            myTransaction.Rollback()
            
        End Try

    End Sub

[TomW]

Try taking out the conn.createcommand

Dim command As OleDbCommand = new oledbcommand
command.Connection = conn

[sknake]

Access gives weird error messages if you type the wrong column name. Move your query over to the MS Access designer and see if you can run it with the substituted parameter values as a test. I'm almost certain you get a syntax error instead of a "column does not exist" error in updates with access.

[TomW]

Access gives weird error messages if you type the wrong column name. Move your query over to the MS Access designer and see if you can run it with the substituted parameter values as a test. I'm almost certain you get a syntax error instead of a "column does not exist" error in updates with access.

I'm kinda wondering about the error msg myself. Technically it shouldnt even know its an Update Statement, its not assigned to an update command of a data adapter.

[kvprajapati]

Change SQL Text,

sql="Update Employee Set First_Name = @FirstName, Last_Name = @LastName, Phone = @Phone, [E-mail] = @Email, Address = @Address, Availability = @Availability Where Employee_ID = @EmployeeID"

[Kristofferson]

adatapost.... That seemed to get it to run the code without an exception error, so it displays "Your profile was successfully updated!" The problem is that it is not updating the values in the database.

[TomW]

ahhh keyword, good catch...

As far as the missing data, make sure your not using two different versions of access, one that is your original and another that is just a copy of the original in your project.

[Kristofferson]

I just wanted to say the way you guys told me to setup the UPDATE statement with the parameters was correct the problem was is that I wasn't requesting the new values from the text box so I did so and it works now.

Thank you so much for all the help.

[musaprodhan]

Alright I added the myTransaction.Rollback which was giving me errors but works now for some reason.

I have not used parameters at all before, so can you point me to a good resource to learn how to setup and use parameters. I have already attempted to create my parameters which seems straight forward but how do I actually implement them in the Update Statement?

I haven't said it yet but thanks for the help so far.

I can not edit DataGridView1. How can i Edit DataGridView1.plz send me now.
Thanks
Musa Prodhan

[cbadchris]

This code does not produce errors but it does not execute any update.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        con.ConnectionString = "Data Source=XXXXXX;Initial Catalog=XXXXX;Persist Security Info=True;User ID=XXXX;Password=XXXX"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Update services set service_name = @sName, cost = @sCost, service_desc = @sDesc, cat_rel = @sCatRel Where service_ID = @sID"
        'Response.Write(cmd.CommandText)
        cmd.Parameters.Add(New SqlParameter("@sName", SqlDbType.VarChar, 50))
        cmd.Parameters("@sName").Value = txtSername.Text
        cmd.Parameters.Add(New SqlParameter("@sCost", SqlDbType.VarChar, 50))
        cmd.Parameters("@sCost").Value = txtCost.Text
        cmd.Parameters.Add(New SqlParameter("@sDesc", SqlDbType.Text))
        cmd.Parameters("@sDesc").Value = ServDescr.Value
        cmd.Parameters.Add(New SqlParameter("@sCatRel", SqlDbType.Int))
        cmd.Parameters("@sCatRel").Value = CatRel.SelectedValue
        cmd.Parameters.Add(New SqlParameter("@sID", SqlDbType.Int))
        cmd.Parameters("@sID").Value = Request.QueryString("id")
        cmd.ExecuteNonQuery()
        Updated.Text = "Your service data was updated successfully!"

    End Sub

Any ideas?

Thanks

[TomW]

This code does not produce errors but it does not execute any update.

Chris are you checking the actual database or just your in-memory dataset. Since the coding is directly updating the database with values from textboxes rather then first adding a new datarow to the dataset/datatable, you will not see the changes in the dataset/datatable until the next time you perform a fill operation.

[cbadchris]

So below I am populating the form with the original data on the load
event then updating the database and the form is reloading on postback.

I just thought to myself that maybe the problem is with the load event. Maybe I am updating the DB with the original data and that is why I am not getting errors yet noithing is being updated?

Imports System.Data
Imports System.Data.SqlClient

Partial Class admin_service_edit
    Inherits System.Web.UI.Page
    'write database data to form on initial load
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim con As String = "Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx"
        Dim cmd As New SqlCommand
        Dim pId As Integer
        pId = Request.QueryString("id")
        cmd.CommandText = "Select * from services where service_ID = '" & pId & "'"
        cmd.Connection = New SqlConnection(con)
        cmd.Connection.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        While dr.Read()
            txtSername.Text = dr("service_name")
            txtCost.Text = dr("cost")
            ServDescr.Value = dr("service_desc")
            CatRel.SelectedValue = dr("cat_rel")
        End While
        cmd.Connection.Close()
    End Sub
    'on button click update the database record
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim mycon As New SqlConnection
        Dim mycommand As New SqlCommand
        mycon.ConnectionString = "Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Passwordxxx"
        mycon.Open()
        mycommand.Connection = mycon
        Try
            mycommand.CommandText = "Update services set service_name = @sName, cost = @sCost, service_desc = @sDesc, cat_rel = @sCatRel Where service_ID = @sID"
            'Response.Write(mycommand.CommandText)
            mycommand.Parameters.AddWithValue("@sName", txtSername.Text)
            mycommand.Parameters.AddWithValue("@sCost", txtCost.Text)
            mycommand.Parameters.AddWithValue("@sDesc", ServDescr.Value)
            mycommand.Parameters.AddWithValue("@sCatRel", CatRel.SelectedValue)
            mycommand.Parameters.AddWithValue("@sID", Request.QueryString("id"))
            'mycommand.Parameters.Add(New SqlParameter("@sName", SqlDbType.VarChar, 50))
            'mycommand.Parameters("@sName").Value = txtSername.Text
            'mycommand.Parameters.Add(New SqlParameter("@sCost", SqlDbType.VarChar, 50))
            'mycommand.Parameters("@sCost").Value = txtCost.Text
            'mycommand.Parameters.Add(New SqlParameter("@sDesc", SqlDbType.Text))
            'mycommand.Parameters("@sDesc").Value = ServDescr.Value
            'mycommand.Parameters.Add(New SqlParameter("@sCatRel", SqlDbType.Int))
            'mycommand.Parameters("@sCatRel").Value = CatRel.SelectedValue
            'mycommand.Parameters.Add(New SqlParameter("@sID", SqlDbType.Int))
            'mycommand.Parameters("@sID").Value = Request.QueryString("id")
            mycommand.ExecuteNonQuery()
            Updated.Text = "Your service data was updated successfully!"
        Catch ex As Exception
            Updated.Text = ex.Message
            MsgBox(ex.Message)
        End Try
        mycommand.Connection.Close()
    End Sub
End Class

[TomW]

Again I would ask are you sure the record is not being updated in the actual database and your just not seeing it on the front end? I dont see where its doing a refill of the new data except in the page load. Also have you stepped thru the code to see the exact values that are in the parameter variables at the time it is executed; it might be something unexpected?

I would change your fill in the page load, to fill a form/global scope level dataset rather then using a reader to store the values in your controls. Then in your update button, I would make the update to the existing record in your dataset. Then call the update method of the dataadapter to update the database on the entire dataset.