Dim dr As New System.Windows.Forms.DataGridViewRow For Each dr In Me.dgvOrders.Rows Dim pno As String = dr.Cells(0).Value Dim price As String = dr.Cells(5).Value Dim qty As String = dr.Cells(6).Value Dim objCon As New dbConnect objCon.con.Open() Dim cmd As New OleDbCommand("Select * from SalesDetail", objCon.con) Dim da As New OleDbDataAdapter(cmd) Dim dreader As OleDbDataReader = cmd.ExecuteReader While dreader.Read If txtSalesID.Text = dreader(0).ToString And pno = dreader(1).ToString Then Dim cmd1 As New OleDbCommand("UPDATE SalesDetail set [email protected],[email protected] where [email protected] and [email protected]", objCon.con) cmd1.Parameters.AddWithValue("@price", price) cmd1.Parameters.AddWithValue("@qty", qty) cmd1.Parameters.AddWithValue("@salesid", txtSalesID.Text) cmd1.Parameters.AddWithValue("@pno", pno) cmd1.ExecuteNonQuery() Else Dim cmd2 As New OleDbCommand("INSERT INTO SalesDetail values(@salesid,@pno,@price,@qty)", objCon.con) cmd2.Parameters.AddWithValue("@salesid", txtSalesID.Text) cmd2.Parameters.AddWithValue("@pno", pno) cmd2.Parameters.AddWithValue("@price", price) cmd2.Parameters.AddWithValue("@qty", qty) cmd2.ExecuteNonQuery() End If End While dreader.Close() objCon.con.Close() Next
4 Replies - 24449 Views - Last Post: 25 August 2013 - 07:44 AM
#1
Check if record already exist, if not Insert new record.
Posted 25 August 2013 - 05:40 AM
My problem is a Button that will loop through a Datagridview and check if row already exist in the Database it will just Update, If not it will insert another that row. Help me please,Thanks.
Replies To: Check if record already exist, if not Insert new record.
#2
Re: Check if record already exist, if not Insert new record.
Posted 25 August 2013 - 07:07 AM
Does your code work? If not, what does it do instead? What errors do you receive and what line do the errors refer to (in your posted code)?
#3
Re: Check if record already exist, if not Insert new record.
Posted 25 August 2013 - 07:16 AM
However, your code is very labour intensive, and you would need to loop through all the table-rows each time before you could determine that you need to insert a record. (Once you've updated a record you should also break out of the inner loop.)
Study the SQL WHERE clause so that you could just check whether a record exists before deciding to UPDATE or INSERT.
Study the SQL WHERE clause so that you could just check whether a record exists before deciding to UPDATE or INSERT.
This post has been edited by andrewsw: 25 August 2013 - 07:19 AM
#4
Re: Check if record already exist, if not Insert new record.
Posted 25 August 2013 - 07:34 AM
I solved my problem. I used dataadapter and datatable instead of datareader. Thanks BTW.
This is my code.
This is my code.
Dim dr As New System.Windows.Forms.DataGridViewRow For Each dr In Me.dgvOrders.Rows Dim pno As String = dr.Cells(0).Value Dim price As String = dr.Cells(5).Value Dim qty As String = dr.Cells(6).Value Dim objCon As New dbConnect objCon.con.Open() Dim cmd As New OleDbCommand("Select * from SalesDetail where [email protected] and [email protected]", objCon.con) cmd.Parameters.AddWithValue("@salesid", txtSalesID.Text) cmd.Parameters.AddWithValue("@pno", pno) Dim da As New OleDbDataAdapter(cmd) Dim dt As New DataTable da.Fill(dt) If dt.Rows.Count = 0 Then Dim cmd2 As New OleDbCommand("INSERT INTO SalesDetail values(@salesid,@pno,@price,@qty)", objCon.con) cmd2.Parameters.AddWithValue("@salesid", txtSalesID.Text) cmd2.Parameters.AddWithValue("@pno", pno) cmd2.Parameters.AddWithValue("@price", price) cmd2.Parameters.AddWithValue("@qty", qty) cmd2.ExecuteNonQuery() Else Dim cmd1 As New OleDbCommand("UPDATE SalesDetail set [email protected],[email protected] where [email protected] and [email protected]", objCon.con) cmd1.Parameters.AddWithValue("@price", price) cmd1.Parameters.AddWithValue("@qty", qty) cmd1.Parameters.AddWithValue("@salesid", txtSalesID.Text) cmd1.Parameters.AddWithValue("@pno", pno) cmd1.ExecuteNonQuery() End If objCon.con.Close() Next
This post has been edited by andrewsw: 25 August 2013 - 07:44 AM
Reason for edit:: Removed duplication
#5
Re: Check if record already exist, if not Insert new record.
Posted 25 August 2013 - 07:44 AM
Erm, you solved it by using the WHERE clause, but glad you sorted

Page 1 of 1