locked
Delete Statement Variables RRS feed

  • Question

  • Hi,

    I have been attempting to use a variable set in a text box to refine the criteria within the where clause. I have declared the @CustNo Variable within the delete statement and set the text box value to a String called CustNo.

     

     

    How do i set the @CustNo var to the value held within the String CustNo.

     

     

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

    Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=ITDEPT004\SQLEXPRESS;Initial Catalog=AuroraExtracts;Integrated Security=True")

    Dim cmd As New System.Data.SqlClient.SqlCommand

    Dim CustNo As String

    CustNo = TextBox1.Text

    cmd.CommandType = System.Data.CommandType.Text

    cmd.CommandText = " declare @CustNo varchar(8) DELETE FROM CustomerModels WHERE (Customer = @CustNo) AND (ItemCode = 'BK3552-51P')"

    cmd.Connection = sqlConnection1

    sqlConnection1.Open()

    cmd.ExecuteNonQuery()

    sqlConnection1.Close()

    End Sub

     

    Thanks david

    Wednesday, August 29, 2007 10:32 AM

All replies

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

    Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=ITDEPT004\SQLEXPRESS;Initial Catalog=AuroraExtracts;Integrated Security=True")

    Dim cmd As New System.Data.SqlClient.SqlCommand

    Dim CustNo As String

    CustNo = TextBox1.Text

    cmd.CommandType = System.Data.CommandType.Text

    cmd.CommandText = "DELETE FROM CustomerModels WHERE  Customer = " & " ' " & CustNo & " ' " & " AND (ItemCode = 'BK3552-51P')"

    cmd.Connection = sqlConnection1

    sqlConnection1.Open()

    cmd.ExecuteNonQuery()

    sqlConnection1.Close()

    End Sub

    I didnt tried  Plz Check Out. If u get any error plz post that.


    Thank u


    Baba

    Wednesday, August 29, 2007 10:57 AM
  • Hi,

     

    Thanks for that, however it doesnt seem to delete the value from the database. Can you see any other problems?

     

    Thanks david

     

    Wednesday, August 29, 2007 11:15 AM
  • This should work. I wrote my changes in C# but it should be the same in VB.Net.

     

    Code Snippet

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

    Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=ITDEPT004\SQLEXPRESS;Initial Catalog=AuroraExtracts;Integrated Security=True")

    Dim cmd As New System.Data.SqlClient.SqlCommand

    Dim CustNo As String

    CustNo = TextBox1.Text

    cmd.CommandType = System.Data.CommandType.Text

    cmd.CommandText = "DELETE FROM CustomerModels WHERE (Customer = @CustNo) AND (ItemCode = @ItemCode)"

    cmd.Parameters.AddWithValue("@CustNo", custNo)

    cmd.Parameters.AddWithValue("@ItemCode", "BK3552-51P")

    cmd.Connection = sqlConnection1

    sqlConnection1.Open()

    cmd.ExecuteNonQuery()

    sqlConnection1.Close()

    End Sub

     

     

    Note that I've used the custNo variable. You should set this to the CustNo you're looking for.

     

    Sean

    Wednesday, August 29, 2007 12:35 PM
  • Hi Sean,

     

    Cheers for that works great.

     

    Just a second question if you know how... Ive got a details view, i need it so that when the page loads the details view doesnt, then teh ability to select a button that will display the details view.

     

    Any ideas? Thanks David
    Wednesday, August 29, 2007 12:49 PM
  • Hi David; glad that worked.

     

    It's probably best to post your new question as a new thread in the appropriate forum, as it's a UI rather than data access question. You'll also need to say whether it's a WinForm or WebForm app.

     

    Briefly, if it's a WinForm app then you don't retrieve the data until they click the button. Then the controls bound to your data source will be blank until they click the button. Alternatively if for some reason you need to load the data when the form loads you can delay binding the controls to the data source until the user clicks the button. The best way to do this is to bind the controls to a BindingSource when the form loads, but don't set the BindingSource's DataSource property until the user clicks the button.

     

    If it's a WebForm app then you're probably looking at refreshing the page when the user clicks the button. You can speed this up and make it look better by using Ajax, which is very simple to use for this kind of thing. Ajax will cause the browser to only refresh the Details part of the web page.

     

    Sean

     

    Wednesday, August 29, 2007 2:21 PM