Hi,
I have a console application. I m querying a table and there is no records returning back from this query. Here is how I do it:
myReader3 = comm2.ExecuteReader()
If myReader3 brings record I update the table.And I would like to make insert to another table if myReader3 has no results. How can I achieve this?
Sample code:
...
'Check Table A if VendorID & ItemID exists
Dim comm2 As New Data.SqlClient.SqlCommand("select VendorID, ItemID from TableA where VendorID = @VendorID and ItemID = @ConsumedItemID", connSQL3)
comm2.Parameters.AddWithValue("@VendorID", vendorID)
comm2.Parameters.AddWithValue("@ConsumedItemID", ItemID)
connSQL3.Open()
Try
myReader3 = comm2.ExecuteReader()
While myReader3.Read()
Console.WriteLine(String.Format("VendorID: {0}, *** ItemID: {1}", _
myReader3(0), myReader3(1)))
'Update CurrentMonth Table
Dim comm3 As New Data.SqlClient.SqlCommand("UPDATE TableA SET ConsumedAmount=@ConsumedAmount where VendorID=@VendorID and ItemID = @ConsumedItemID", connSQL4)
comm3.Parameters.AddWithValue("@VendorID", myReader3(0))
comm3.Parameters.AddWithValue("@ConsumedItemID", myReader3(1))
comm3.Parameters.AddWithValue("@ConsumedAmount", myReader1(0))
connSQL4.Open()
comm3.ExecuteNonQuery()
Console.WriteLine(String.Format("VendorID: {0}, *** ItemID: {1}, *** ConsumedAmount: {2}", _
myReader3(0), myReader3(1), myReader1(0)))
End While
Catch ex As Exception
Console.WriteLine(String.Format("There is No such VendorId: {0} & ItemID: {1} in CurrentMonth.", vendorID, ItemID))
'INSERT INTO TableA Table
Dim comm4 As New Data.SqlClient.SqlCommand("INSERT INTO CurrentMonth (VendorID, ItemID,ConsumedAmount) VALUES (@VendorID, @ConsumedItemID,@ConsumedAmount)", connSQL5)
comm4.Parameters.AddWithValue("@VendorID", vendorID)
comm4.Parameters.AddWithValue("@ConsumedItemID", ItemID)
comm4.Parameters.AddWithValue("@ConsumedAmount", myReader1(0))
connSQL5.Open()
comm4.ExecuteNonQuery()
Console.WriteLine(String.Format("VendorID: {0}, *** ItemID: {1}, *** ConsumedAmount: {2}", _
myReader3(0), myReader3(1), myReader1(0)))
End Try
I have a console application. I m querying a table and there is no records returning back from this query. Here is how I do it:
myReader3 = comm2.ExecuteReader()
If myReader3 brings record I update the table.And I would like to make insert to another table if myReader3 has no results. How can I achieve this?
Sample code:
...
'Check Table A if VendorID & ItemID exists
Dim comm2 As New Data.SqlClient.SqlCommand("select VendorID, ItemID from TableA where VendorID = @VendorID and ItemID = @ConsumedItemID", connSQL3)
comm2.Parameters.AddWithValue("@VendorID", vendorID)
comm2.Parameters.AddWithValue("@ConsumedItemID", ItemID)
connSQL3.Open()
Try
myReader3 = comm2.ExecuteReader()
While myReader3.Read()
Console.WriteLine(String.Format("VendorID: {0}, *** ItemID: {1}", _
myReader3(0), myReader3(1)))
'Update CurrentMonth Table
Dim comm3 As New Data.SqlClient.SqlCommand("UPDATE TableA SET ConsumedAmount=@ConsumedAmount where VendorID=@VendorID and ItemID = @ConsumedItemID", connSQL4)
comm3.Parameters.AddWithValue("@VendorID", myReader3(0))
comm3.Parameters.AddWithValue("@ConsumedItemID", myReader3(1))
comm3.Parameters.AddWithValue("@ConsumedAmount", myReader1(0))
connSQL4.Open()
comm3.ExecuteNonQuery()
Console.WriteLine(String.Format("VendorID: {0}, *** ItemID: {1}, *** ConsumedAmount: {2}", _
myReader3(0), myReader3(1), myReader1(0)))
End While
Catch ex As Exception
Console.WriteLine(String.Format("There is No such VendorId: {0} & ItemID: {1} in CurrentMonth.", vendorID, ItemID))
'INSERT INTO TableA Table
Dim comm4 As New Data.SqlClient.SqlCommand("INSERT INTO CurrentMonth (VendorID, ItemID,ConsumedAmount) VALUES (@VendorID, @ConsumedItemID,@ConsumedAmount)", connSQL5)
comm4.Parameters.AddWithValue("@VendorID", vendorID)
comm4.Parameters.AddWithValue("@ConsumedItemID", ItemID)
comm4.Parameters.AddWithValue("@ConsumedAmount", myReader1(0))
connSQL5.Open()
comm4.ExecuteNonQuery()
Console.WriteLine(String.Format("VendorID: {0}, *** ItemID: {1}, *** ConsumedAmount: {2}", _
myReader3(0), myReader3(1), myReader1(0)))
End Try
