Group by statement

using the codes below i want to update column trans_quantity in the database but am unable to so kinldy correct me

Public Sub savetriptrans()

Dim tLocationStock As New LocationStockType

Dim transtrip As New TripTransactiontype

Dim ttcustomer As List(Of CustomerType)

Dim ttrip As New Triptype

Dim tCustomer As CustomerType = svcClient.GetCustomer(MdlConn.MainConn, MdlConn.SerchID)

For Each row As DataRow In Itemtable.Rows

tLocationStock = svcClient.GetLocationStock(MdlConn.checkDB, row("StockId"))

Dim sTran As New TransactionFileType

'svcClient.GetCustomer(MdlConn.checkDB, tReceipt.Customer_id)

ttcustomer = svcClient.GetCustomers(MdlConn.checkDB, MdlConn.branchid)

svcClient.Gettrip(MdlConn.checkDB, tReceipt.route_id, Now.Date)

transtrip.trip_date = Now

transtrip.trans_quantity = transtrip.trans_quantity + row("qty")

transtrip.location_Productcode = row("StockId")

transtrip.route_id = tCustomer.route_id

transtrip.customer_id = tCustomer.Customer_id

transtrip.location_product_description = tLocationStock.location_product_description

svcClient.Settriptransaction(MdlConn.checkDB, transtrip, Convert.ToInt32(row("qty")), Convert.ToInt32(row("StockId")))

End Sub

below is my sql statement

Public Function Settriptransaction(ByVal ConnString As String, ByVal ttrip As TripTransactiontype, ByVal qnty As Integer, ByVal Productcode As Integer) As ReturnType

Dim result As New ReturnType

If ttrip Is Nothing Then

Throw New ArgumentNullException("Trip is null")

Else

Using conn As New SqlConnection(ConnString)

Using Command As SqlCommand = conn.CreateCommand()

conn.Open()

If ttrip.trip_transaction_id = 0 Then

'Command.CommandText = " update trip_transactions set trans_quantity='" & qnty & "' where location_Productcode='" & Productcode & "'"

Command.CommandText = "INSERT INTO trip_transactions (route_trip_id,route_id,customer_id,location_Productcode,location_product_description,trans_quantity,trip_date) VALUES (@route_trip_id,@route_id,@customer_id,@location_Productcode,@location_product_description,@trans_quantity,@trip_date) SET @trip_transaction_id = (SELECT SCOPE_IDENTITY());"

Else

Command.CommandText = "UPDATE trip_transactions set route_trip_id=@route_trip_id,route_id=@route_id,customer_id=@customer_id,location_Productcode=@location_Productcode,location_product_description=@location_product_description,trans_quantity='" & qnty & "',trip_date=@trip_date WHERE trip_transaction_id=trip_transaction_id"

'Command.CommandText = " update trip_transactions set trans_quantity='" & qnty & "' where location_Productcode='" & Productcode & "'"

'Command.CommandText = "INSERT INTO trip_transactions (route_trip_id,route_id,customer_id,location_Productcode,location_product_description,trans_quantity,trip_date) VALUES (@route_trip_id,@route_id,@customer_id,@location_Productcode,@location_product_description,@trans_quantity,@trip_date) SET @trip_transaction_id = (SELECT SCOPE_IDENTITY());"

End If

Dim objParameter As New SqlClient.SqlParameter("@trip_transaction_id", SqlDbType.Int)

objParameter.Direction = ParameterDirection.InputOutput

objParameter.Value = ttrip.route_trip_id

Command.Parameters.Add(objParameter)

Command.Parameters.Add("@route_trip_id", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.route_trip_id

Command.Parameters.Add("@route_id", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.route_id

Command.Parameters.Add("@customer_id", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.customer_id

Command.Parameters.Add("@location_Productcode", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.location_Productcode

Command.Parameters.Add("@location_product_description", SqlDbType.VarChar, 225, ParameterDirection.Input).Value = ttrip.location_product_description

Command.Parameters.Add("@trans_quantity", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.trans_quantity

Command.Parameters.Add("@trip_date", SqlDbType.Date, ParameterDirection.Input).Value = Now.Date

'Try

result.return_code = Command.ExecuteNonQuery()

If result.return_code = 1 Then

result.return_identity_code = Command.Parameters("@trip_transaction_id").Value

result.return_message = "Trip Process succesfull"

Else

End If

'Catch ex As Exception

' result.return_message = "PatientRegistration Status already exists"

'End Try

End Using

End If

Return result

End Function

am able to insert but not to update , i want to update column trans_quantity

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Carlos kambui10y ago
using the codes below i want to update column trans_quantity in the database but am unable to so kinldy correct me
Public Sub savetriptrans()
Dim tLocationStock As New LocationStockType
Dim transtrip As New TripTransactiontype
Dim ttcustomer As List(Of CustomerType)
Dim ttrip As New Triptype
Dim tCustomer As CustomerType = svcClient.GetCustomer(MdlConn.MainConn, MdlConn.SerchID)
For Each row As DataRow In Itemtable.Rows
tLocationStock = svcClient.GetLocationStock(MdlConn.checkDB, row("StockId"))
Dim sTran As New TransactionFileType
'svcClient.GetCustomer(MdlConn.checkDB, tReceipt.Customer_id)
ttcustomer = svcClient.GetCustomers(MdlConn.checkDB, MdlConn.branchid)
svcClient.Gettrip(MdlConn.checkDB, tReceipt.route_id, Now.Date)
transtrip.trip_date = Now
transtrip.trans_quantity = transtrip.trans_quantity + row("qty")
transtrip.location_Productcode = row("StockId")
transtrip.route_id = tCustomer.route_id
transtrip.customer_id = tCustomer.Customer_id
transtrip.location_product_description = tLocationStock.location_product_description
svcClient.Settriptransaction(MdlConn.checkDB, transtrip, Convert.ToInt32(row("qty")), Convert.ToInt32(row("StockId")))
Next
End Sub
below is my sql statement
Public Function Settriptransaction(ByVal ConnString As String, ByVal ttrip As TripTransactiontype, ByVal qnty As Integer, ByVal Productcode As Integer) As ReturnType
Dim result As New ReturnType
If ttrip Is Nothing Then
Throw New ArgumentNullException("Trip is null")
Else
Using conn As New SqlConnection(ConnString)
Using Command As SqlCommand = conn.CreateCommand()
conn.Open()
If ttrip.trip_transaction_id = 0 Then
'Command.CommandText = " update trip_transactions set trans_quantity='" & qnty & "' where location_Productcode='" & Productcode & "'"
Command.CommandText = "INSERT INTO trip_transactions (route_trip_id,route_id,customer_id,location_Productcode,location_product_description,trans_quantity,trip_date) VALUES (@route_trip_id,@route_id,@customer_id,@location_Productcode,@location_product_description,@trans_quantity,@trip_date) SET @trip_transaction_id = (SELECT SCOPE_IDENTITY());"
Else
Command.CommandText = "UPDATE trip_transactions set route_trip_id=@route_trip_id,route_id=@route_id,customer_id=@customer_id,location_Productcode=@location_Productcode,location_product_description=@location_product_description,trans_quantity='" & qnty & "',trip_date=@trip_date WHERE trip_transaction_id=trip_transaction_id"
'Command.CommandText = " update trip_transactions set trans_quantity='" & qnty & "' where location_Productcode='" & Productcode & "'"
'Command.CommandText = "INSERT INTO trip_transactions (route_trip_id,route_id,customer_id,location_Productcode,location_product_description,trans_quantity,trip_date) VALUES (@route_trip_id,@route_id,@customer_id,@location_Productcode,@location_product_description,@trans_quantity,@trip_date) SET @trip_transaction_id = (SELECT SCOPE_IDENTITY());"
End If
Dim objParameter As New SqlClient.SqlParameter("@trip_transaction_id", SqlDbType.Int)
objParameter.Direction = ParameterDirection.InputOutput
objParameter.Value = ttrip.route_trip_id
Command.Parameters.Add(objParameter)
Command.Parameters.Add("@route_trip_id", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.route_trip_id
Command.Parameters.Add("@route_id", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.route_id
Command.Parameters.Add("@customer_id", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.customer_id
Command.Parameters.Add("@location_Productcode", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.location_Productcode
Command.Parameters.Add("@location_product_description", SqlDbType.VarChar, 225, ParameterDirection.Input).Value = ttrip.location_product_description
Command.Parameters.Add("@trans_quantity", SqlDbType.Int, ParameterDirection.Input).Value = ttrip.trans_quantity
Command.Parameters.Add("@trip_date", SqlDbType.Date, ParameterDirection.Input).Value = Now.Date
'Try
result.return_code = Command.ExecuteNonQuery()
If result.return_code = 1 Then
result.return_identity_code = Command.Parameters("@trip_transaction_id").Value
result.return_message = "Trip Process succesfull"
Else
End If
'Catch ex As Exception
' result.return_message = "PatientRegistration Status already exists"
'End Try
End Using
End Using
End If
Return result
End Function
am able to insert but not to update , i want to update column trans_quantity
Suvendu Shekhar Giri10y ago
Would like to request to explain the actual requirement instead of suggesting to use sql keywords. We'll try to suggest you the optimum way to solve your problem.
However, looking at your requirement, something like following can be used to determine if you have more than 3 students with age 5-
SELECT COUNT(*)
FROM student
WHERE student_age=5
Please let me know if your requirement is something else.
Thanks