11 Replies - 3212 Views - Last Post: 05 July 2011 - 01:39 AM Rate Topic: -----

#1 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Combine update and insert function

Posted 04 July 2011 - 10:56 PM

Hello there...

This is related to "Next form won't display" topic. What I want is instead of calling other form I'll just want to combine the two functions into one button.

Condition is: If database is empty then insert function execute but if database is not empty then update data. I have codes below but it gave me error saying
"The connection was not closed. The connection's current state is open."





   strSQL = "SELECT * from tblbfy WHERE id= '1'"

        Dim cmd2 As New SqlCommand(strSQL, con)
        cmd2.Parameters.AddWithValue("@bf", Textbox1.Text)
        con.Open()


        Dim objReader As SqlDataReader = cmd2.ExecuteReader()


        If objReader.Read = True Then


            Me.Textbox1.Text = Trim(objReader("bf"))

            con.Open()
            strSQL = "UPDATE tblbfy SET " & _
            "bf = '" & Textbox1.Text & "'WHERE id = '1'"
            MsgBox("Data Updated Successfully !", MsgBoxStyle.Information, "Update")
            cmd2.ExecuteNonQuery()



        Else



            strSQL = "Insert into tblbfy (bf) Values (@bf) "

            cmd2.Parameters.AddWithValue("@bf", Textbox1.Text)
            con.Open()' --------------------------------------- At this line point out error

            cmd2.ExecuteNonQuery()

            MsgBox("Initial Amount saved Successfully !", MsgBoxStyle.Information, "Save")


        End If


        objReader.Close()
        con.Close()
        cmd2.Dispose()


Please help me on how to combine my codes correctly.. Thank you...

This post has been edited by Elda: 04 July 2011 - 11:32 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Combine update and insert function

#2 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1820
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Combine update and insert function

Posted 04 July 2011 - 11:04 PM

You are opening connection twice while the previous connection is still open, so either close the previous connection before opening the next connection or remove the second connection
Was This Post Helpful? 0
  • +
  • -

#3 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: Combine update and insert function

Posted 04 July 2011 - 11:19 PM

View Postsmohd, on 05 July 2011 - 02:04 PM, said:

You are opening connection twice while the previous connection is still open, so either close the previous connection before opening the next connection or remove the second connection


I removed the second connection and the second one
cmd2.ExecuteReader() 
but it won't update data. It would just simply insert data.
Was This Post Helpful? 0
  • +
  • -

#4 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1820
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Combine update and insert function

Posted 04 July 2011 - 11:30 PM

look at your code, first you insert, then you test in if you update but in else you repeat the same you did before? Is this what you mean?
Was This Post Helpful? 1
  • +
  • -

#5 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Combine update and insert function

Posted 04 July 2011 - 11:43 PM

I changed my code a bit.

At first I am checking if record available in table using select command if available then I am using update query else insert query

        Dim con As SqlConnection = Nothing
        Dim sqlCommand As SqlCommand = Nothing
        Dim adapter As SqlDataAdapter = Nothing
        Dim table As DataTable = Nothing
        Dim strQuery As String = Nothing
        Dim strMessage As String = Nothing

        con = New SqlConnection("Your Connection String")
        con.Open()

        sqlCommand = New SqlCommand("SELECT 1 FROM tablename WHERE bf=@bf", con)
        sqlCommand.Parameters.AddWithValue("@bf", Textbox1.Text)
        adapter = New SqlDataAdapter(sqlCommand)
        adapter.Fill(table)

        If (Not table Is Nothing And table.Rows.Count > 0) Then
            strQuery = "INSERT INTO INTO tblbfy (bf) Values (@bf)"
            strMessage = "Data Inserted Successfully..."
        Else
            strQuery = "UPDATE tblbfy SET bf =@pf 'WHERE id = '1'"
            strMessage = "Data Updated Successfully..."
        End If

        sqlCommand = New SqlCommand(strQuery, con)
        sqlCommand.Parameters.AddWithValue("@pf", Textbox1.Text)
        sqlCommand.ExecuteNonQuery()
        MessageBox.Show(strMessage)

        con.Close()



If (Not table Is Nothing And table.Rows.Count > 0) Then At this line I am checking if records is available then use update query else insert query.

Hope this help.

This post has been edited by noorahmad: 04 July 2011 - 11:44 PM

Was This Post Helpful? 3
  • +
  • -

#6 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: Combine update and insert function

Posted 04 July 2011 - 11:51 PM

Ok. I'll check this out.. I give feedback after I test your codes..

Thank you both.. :)
Was This Post Helpful? 0
  • +
  • -

#7 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Combine update and insert function

Posted 04 July 2011 - 11:57 PM

If this is MS SQL Server, you can do this in one t-sql statement using the MERGE Statement. I use it quite often for this exact task. I believe that the merge statement is also available for Oracle as well...
Was This Post Helpful? 0
  • +
  • -

#8 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: Combine update and insert function

Posted 05 July 2011 - 12:18 AM

This is what I have done...

con = New SqlConnection("Data Source=ISD-ELDA;Initial Catalog=t-cashbook;Integrated Security=True")
        con.Open()

        sqlCommand = New SqlCommand("SELECT 1 FROM tblbfy WHERE bf=@bf", con)
        sqlCommand.Parameters.AddWithValue("@bf", Textbox1.Text)
        adapter = New SqlDataAdapter(sqlCommand)


        adapter.Fill(ds, "CB")
        table = ds.Tables(0)



        If (Not table Is Nothing And table.Rows.Count > 0) Then
            strQuery = "INSERT INTO tblbfy (bf) Values (@bf)"
            strMessage = "Data Inserted Successfully..."


        Else



            strQuery = "UPDATE tblbfy SET " & _
            "bf = '" & Textbox1.Text & "'WHERE id = '1'"
            strMessage = "Data Updated Successfully..."
        End If


        sqlCommand = New SqlCommand(strQuery, con)

        sqlCommand.Parameters.AddWithValue("@bf", Textbox1.Text)
        sqlCommand.ExecuteNonQuery()
        MessageBox.Show(strMessage)

        con.Close()

    



It perfectly update data but when database is empty, No insertion occur.... What's wrong please?

This post has been edited by Elda: 05 July 2011 - 12:35 AM

Was This Post Helpful? 0
  • +
  • -

#9 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: Combine update and insert function

Posted 05 July 2011 - 12:53 AM

Got it works... It should be like this:

 con = New SqlConnection("Data Source=ISD-ELDA;Initial Catalog=t-cashbook;Integrated Security=True")
        con.Open()

        sqlCommand = New SqlCommand("SELECT 1 FROM tblbfy WHERE bf=@bf", con)
        sqlCommand.Parameters.AddWithValue("@bf", Textbox1.Text)
        adapter = New SqlDataAdapter(sqlCommand)


        adapter.Fill(ds, "CB")
        table = ds.Tables(0)



        If (Not table Is Nothing And table.Rows.Count > 0) Then
           
            strQuery = "UPDATE tblbfy SET " & _
           "bf = '" & Textbox1.Text & "'WHERE id = '1'"
            strMessage = "Data Updated Successfully..."


        Else

            strQuery = "INSERT INTO tblbfy (bf) Values (@bf)"
            strMessage = "Data Inserted Successfully..."

           
        End If


        sqlCommand = New SqlCommand(strQuery, con)

        sqlCommand.Parameters.AddWithValue("@bf", Textbox1.Text)
        sqlCommand.ExecuteNonQuery()
        MessageBox.Show(strMessage)

        con.Close()





Insert function should come first before update... :)

@noohrahmad: Thanks Sir.. I haven't pay attention to your hint. Now I know what you explained first.. :)


Thanks guys....:)
Was This Post Helpful? 0
  • +
  • -

#10 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1820
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Combine update and insert function

Posted 05 July 2011 - 01:14 AM

Glad you get it working :)
And thanks to Noor Ahmad for simplifying the work :)
Was This Post Helpful? 1
  • +
  • -

#11 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: Combine update and insert function

Posted 05 July 2011 - 01:26 AM

Yeah.. He simplified my work.. :)

Thank you.... :)
Was This Post Helpful? 0
  • +
  • -

#12 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Combine update and insert function

Posted 05 July 2011 - 01:39 AM

Glad I helped you.

Thank you smohd.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1