Sunday, May 9, 2010

UPDATE VALUES IN SQL, ASP(VB)

12 comments:

  1. I having a problem about updating the Sql Database through asp.net website. When I build it, it doesn't show any error. But, When I try to update from the website, it shows error on this line... "Cmd.ExecuteNonQuery() " And the error messege is "Incorrect syntex near '('. 'Delivery' is not recognized SAT option. Please help. The code is following...

    Imports System.Data.SqlClient

    Partial Class Minnesota

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub

    Dim con As SqlConnection

    Dim cmd As SqlCommand

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

    con = New SqlConnection("Data Source=STG-PC;Initial Catalog=Card distribution locations;Integrated Security=True")

    con.Open()

    cmd = New SqlCommand("UPDATE Minnesota(Hospitals) Set Delivery Report=" + TextBox3.Text + " where Type=" + TextBox2.Text + "", con)

    cmd.ExecuteNonQuery()

    Button3.Text = " Successfully Updated"

    TextBox2.Text =""

    TextBox3.Text =""

    con.Close()

    End Sub

    End Class

    ReplyDelete
    Replies
    1. UPDATE Minnesota(Hospitals) Set Delivery Report='" + TextBox3.Text + "' where Type='" + TextBox2.Text + "'",


      Try this...
      Is delivery report is two word.

      Delete
  2. Yes...It is two words...I was wondering if this is the reason...also all my database files name are 2 words.
    And I am using SQL server 2008 version. I don't know if that is matter...

    ReplyDelete
  3. Its the same type of error ...
    "Incorrect Syntax near 'Report'."
    Than I make 'DeliveryReport' one word and now its showing another error that,
    "Invalid object name 'Minnesota(Hospitals)'. "

    ReplyDelete
  4. Thats not a problem here.

    Now i understand... Try this way

    update [Minnesota (Hospitals)] Set [Delivery Report]

    If you have space or some characters in your column names or table names use this notation. If your column is varchar use ' before and after value.

    let me know if you still get error.

    ReplyDelete
  5. Thanks for the fast reply. It Worked...Thank You So Much!

    Your videos are rocking....!

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I have a questions...
    If I want to search multiple tables( 3 - 7 tables) to find a specific row or column value with the same search box also update values with the same update box like the way you did in this video, should I use the same code for all the tables ? or I have to do the coding for each table separately?
    For example: I have to deliver goods in a state in 5 cities. Each city has a list of addresses. Can I use just one search box to find a specific address information from five table? or I have to use separate search box for each city.
    Also to update the information on that specific address, do I have to make 5 update box ?


    ReplyDelete
  8. You can use same sql command to update all table at once.

    ReplyDelete
  9. I don't know how to do that, I have tried couple ways, but it didn't work...I m writing it...please correct me...
    These are the 4 table I need to put on same page...
    Minnesota(Universities)
    Minnesota(Hospitals)
    Minnesota(Nursing Home Assistant Living)
    Minnesota(Temp Companies)

    Now I have tried these ways---

    "UPDATE [Minnesota(Hospitals), Minnesota(Nursing Home Assistant Living), Minnesota(Temp Companies), Minnesota(Universities)] SET [Delivery Report]='" + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'", con

    or
    "UPDATE [Minnesota(Hospitals)], [Minnesota(Nursing Home Assistant Living)], [Minnesota(Temp Companies)], [Minnesota(Universities)] SET [Delivery Report]='" + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'", con

    or
    "UPDATE [Minnesota(Nursing Home Assistant Living)] SET [Delivery Report]='" + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'", con
    "UPDATE [Minnesota(Hospitals)] SET [Delivery Report]='" + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'", con
    "UPDATE [Minnesota(Temp Companies)] SET [Delivery Report]='" + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'", con
    "UPDATE [Minnesota(Universities)] SET [Delivery Report]='" + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'", con

    All of them have the same error....incorrect syntax error.

    ReplyDelete
  10. Try this....

    SqlCommand cmd = new SqlCommand("UPDATE [Minnesota(Nursing Home Assistant Living)] SET [Delivery Report]='"
    + TextBox3.Text + "' WHERE Type='" + TextBox2.Text + "'"
    + " UPDATE [Minnesota(Hospitals)] SET [Delivery Report]='" + TextBox3.Text
    + "' WHERE Type='" + TextBox2.Text + "'"
    + " UPDATE [Minnesota(Temp Companies)] SET [Delivery Report]='" + TextBox3.Text
    + "' WHERE Type='" + TextBox2.Text + "'"
    + " UPDATE [Minnesota(Universities)] SET [Delivery Report]='" + TextBox3.Text
    + "' WHERE Type='" + TextBox2.Text + "'", con);

    ReplyDelete