|
I thought about that but didn't give it a try.
I'm glad you didn't say use First() since I forgot to mention that.
I'll try it.
Long time since we have communicated. Hope all is well with you.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 29-Aug-19 14:33pm.
|
|
|
|
|
You're using First and Last within the group, but I don't see any order specified for that operation.
If you just want the last price that's not free, try something like:
.FPrice = cl
.OrderByDescending(Function(i) If(i.FPrice = 0, 0, 1))
.ThenByDescending(Function(i) i.YOUR_DATE_FIELD_HERE)
.First().FPrice
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
First and Last was a tough choice so I chose first for customer and part number and last for cost and price.
Maybe not the best decision and I knew inflation or supply and demand was a factor when I first wrote it.
When I think about it, it probably doesn't matter if the part number is always the same.
But I didn't see the free item after the paid item coming and missed the thought of it occurring.
On a side note mentioned by Maciej on the OrderBy, Guess I should of used the ShipDate for that, for a true first or last. What do you think?
The code runs and free items have been removed from the report now, which is what I wanted and tried to do in code elsewhere. Not sure if I need this line .FShipDate = cl.Last().FShipDate,
If I could make this better, I'd be open to suggestions. The FCost and FPrice bothers me because they fluctuate. Thought about using average but I'm not sure how to implement it.
'Use Linq to Group the Items Together
Dim gCustomersAll As List(Of MarginItemCustomerProfit) = sTpAll.GroupBy(Function(item) item.FCustomerNo).Select(Function(cl) New MarginItemCustomerProfit() With {
.FCustomerNumber = cl.First().FCustomerNo,
.FItemNumber = cl.First().FItemNo,
.FShipQty = cl.Sum(Function(qty) qty.FShipQty),
.FShipDate = cl.Last().FShipDate, // Not sure if I need this line
.FCost = cl.Max(Function(cost) cost.FCost),
.FPrice = cl.OrderByDescending(Function(i) If(i.FPrice = 0, 0, 1)).ThenByDescending(Function(i) i.FShipDate).First().FPrice,
.FAmount = cl.Sum(Function(amount) amount.FAmount)
}).ToList()
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 29-Aug-19 14:42pm.
|
|
|
|
|
Imports System.Data.SqlClient
Public Class Equipos_de_Computacion
Dim sql As String = ""
Private Sub BtnInsertar_Click(sender As Object, e As EventArgs) Handles btnInsertar.Click
If (Me.txtCodigoInterno.Text = "") Then
MsgBox("El campo idententificacion no puede estar vacio", MsgBoxStyle.Critical, "Atencion")
Me.txtCodigoInterno.Select()
Else
Dim CodigoInterno As Integer
Dim NumerodeSerie As Integer
Dim NumerodeFactura As Integer
Dim FechadeCompra As Date
Dim Precio As Decimal
Dim Acargode As String = ""
Dim Estado As String = ""
Dim Depreciacion As Decimal
Dim Caracteristicas As String = ""
CodigoInterno = CInt(Me.txtCodigoInterno.Text)
NumerodeSerie = CInt(Me.txtNumerodeSerie.Text)
NumerodeFactura = CInt(Me.txtNumerodeFactura.Text)
FechadeCompra = CDate(Me.DateTimePicker1.Value)
Precio = Me.nudPrecio.Value
Acargode = Me.txtACargode.Text
Estado = Me.txtEstado.Text
Depreciacion = Me.NudDepreciacion.Value
Caracteristicas = Me.txtCaracteristicas.Text
cmd.CommandType = CommandType.Text
cmd.Connection = conn
sql = "INSERT INTO [Equipos de Computacion] (CodigoInterno, NumerodeSerie, NumerodeFactura, FechadeCompra, Precio, Acargode, Estado, Caracteristicas, Depreciacion)"
sql += "Values('" & CodigoInterno & "','" & NumerodeSerie & "','" & NumerodeFactura & "','" & FechadeCompra & "', '" & Precio & "' ,'" & Acargode & "','" & Estado & "','" & Caracteristicas & "','" & Depreciacion & "')"
MsgBox(sql)
cmd.CommandText = sql
Try
cmd.ExecuteNonQuery()
MsgBox("Registro insertado correctamente")
Catch ex As Exception
If ex.ToString.Contains("duplicate") Then
MsgBox("El registro ya existe en la base de datos")
Else
MsgBox(ex.ToString)
End If
End Try
End If
End Sub
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvEquiposdeComputacion.CellContentClick
End Sub
Private Sub BtnMostrar_Click(sender As Object, e As EventArgs) Handles btnMostrar.Click
Dim DS As New DataSet
Dim DA As New SqlDataAdapter("Select * from [Equipos de Computacion]", conn)
DA.Fill(DS)
dgvEquiposdeComputacion.DataSource = DS.Tables(0)
End Sub
Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
End Sub
Private Sub BtnTotalDepreciacion_Click(sender As Object, e As EventArgs) Handles btnTotalDepreciacion.Click
NudDepreciacion.Value = CDec(FormatNumber(nudPrecio.Value * 0.25, 2))
End Sub
Private Sub TxtPrecio_TextChanged(sender As Object, e As EventArgs)
End Sub
Private Sub txtPrecio_KeyPress(sender As Object, e As KeyPressEventArgs) Handles nudPrecio.KeyPress
nudPrecio.DecimalPlaces = 2
End Sub
Private Sub txtPrecio_LostFocus(sender As Object, e As EventArgs)
Dim VarMonedaDolares As Double
VarMonedaDolares = nudPrecio.Value
nudPrecio.Value = CDec(FormatCurrency(VarMonedaDolares, 2))
End Sub
End Class
|
|
|
|
|
It means you are somewhere comparing numbers to characters and not including the '' around them or you are trying to put a word into a number field.
You first need to switch your code to using Parameters instead of concatenating your sql because you are susceptible to sql injection attacks. And you can't handle single quotes in your data the way you're doing it, which might actually be causing your issue anyway.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
cmd.CommandText = "INSERT INTO [Equipos de Computacion] (CodigoInterno, NumerodeSerie, NumerodeFactura, FechadeCompra, Precio, Acargode, Estado, Caracteristicas, Depreciacion) VALUES (@CodigoInterno, @NumerodeSerie, @NumerodeFactura, @FechadeCompra, @Precio, @Acargode, @Estado, @Caracteristicas, @Depreciacion)"
cmd.Parameters.AddWithValue("@CodigoInterno", CInt(Me.txtCodigoInterno.Text))
cmd.Parameters.AddWithValue("@NumerodeSerie", CInt(Me.txtNumerodeSerie.Text))
cmd.Parameters.AddWithValue("@NumerodeFactura", CInt(Me.txtNumerodeFactura.Text))
cmd.Parameters.AddWithValue("@FechadeCompra", CDate(Me.DateTimePicker1.Value))
cmd.Parameters.AddWithValue("@Precio", Me.nudPrecio.Value)
cmd.Parameters.AddWithValue("@Acargode", Me.txtACargode.Text)
cmd.Parameters.AddWithValue("@Estado", Me.txtEstado.Text)
cmd.Parameters.AddWithValue("@Caracteristicas", Me.txtCaracteristicas.Text)
cmd.Parameters.AddWithValue("@Depreciacion", Me.NudDepreciacion.Value) Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
Once you've fixed that critical security vulnerability in your code, you'll need to check that the values you're trying to insert match the data types of the columns in your table.
You should also avoid using CInt and CDate to convert user input. If the user types in something that can't be converted, you'll get an exception. Instead, use Integer.TryParse[^] / Date.TryParse[^] and display a warning to the user if their input is invalid.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank u!!, now the only problem that i got is the depreciation box, i dont want to register integer depreciation numbers i want to register decimal depreciation numbers in the database im using this formula
nudDepreciacion.Value = FormatNumber(nudPrecio.Value * 0.25, 2)
.
|
|
|
|
|
So you want to multiply the value in the control by 0.25 and round to two decimal places before you store it in the database?
cmd.Parameters.AddWithValue("@Depreciacion", Math.Round(CDbl(Me.NudDepreciacion.Value) * 0.25, 2))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thank u again but now the number is registered without a coma.
|
|
|
|
|
If it's a number, then you should be storing it as a number. Don't store numbers as strings!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
ok thank u. i wrote the code with the correction that u told me.Now, I wanna know how to store the result of depreciation with a coma that separates decimals because is storing the result without a coma.
|
|
|
|
|
DO NOT STORE NUMBERS AS STRINGS!
Numbers do not have "commas" or other formatting information. They are just numbers.
1,234.56 is the same as 1234.56 , which is the same as 1234.5600 . They are all the same number.
If you're trying to store formatted values in your database, then you're doing it wrong.
Store values in the correct data type, and format the values in the user interface.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Listen to what Richard is saying, if not you are in for a world of pain in the future. Apply the same rules to dates, DO NOT STORE DATES AS STRINGS. Store them as date or datetime data type.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hello !
I'm using entity framework
Is there any way to count how many database hit were made ?
( The reason why I make this question, is because i'm using a 3d party software that add some commands to Entity Framework. One of these is that several different queries (the software claim) are executed using a single database hit. I want to verify this.)
Thank you !
|
|
|
|
|
If you can't tell the difference, what does it matter?
The Master said, 'Am I indeed possessed of knowledge? I am not knowing. But if a mean person, who appears quite empty-like, ask anything of me, I set it forth from one end to the other, and exhaust it.'
― Confucian Analects
|
|
|
|
|
I want to know if this software is telling the truth or no , if yes I want to use it.
|
|
|
|
|
Everyone lies.
The Master said, 'Am I indeed possessed of knowledge? I am not knowing. But if a mean person, who appears quite empty-like, ask anything of me, I set it forth from one end to the other, and exhaust it.'
― Confucian Analects
|
|
|
|
|
|
It is quite possible to send multiple queries in a single request, look up "Little Johnny Tables", that is called sql injection.
You can also get multiple result sets returned in a single request using a dataset, although this was painfully slow when I last looked (about 6 years ago).
If you are using SQL Server then sql profiler will allow you to inspect each call to the database.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
i'm trying a 3d party tools Query Future | Entity Framework Plus[^]
But i'm not sure if it's true what this tool claim to do ?
The sql profiler it says : exec sp_executesql N'---EF+ Query Future 1 of 5....... .After there are 5 queries (I have 5 query that are executed with Future)
But i don't know if these queries are executed in a single database hit ?
|
|
|
|
|
There are several options:
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello !
I'm using Entity Framework 6 with sql server 2008r2 database.
I want to get from database only those records that have vl1>5.( i need to get from database only those objects that have vl1>5 and not to read all the objects and after to apply the condition )
I have 2 queries :
Dim query1 as IeQueryable(of myobj)= (From t in context.myobjs Where vl1<5).Tolist
Dim query2= (From t in context.myobjs.Asqueryable Where vl1<5).Tolist
Are these queries doing the same job that i want ?
|
|
|
|
|
What happens when you try them?
|
|
|
|
|
desanti wrote: and not to read all the objects and after to apply the condition ) Then use an SQL query and execute your statement on the server, instead of using an ORM.
The only difference between the first and second version is the "Asqueryable" part in the second statement; that statement has nothing to do with "where" the query executes. So, to answer, yes, they're doing the same job. Not sure if it's what you want though.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
can someone help me fix my code? i'I'm having problem deserializing the xmlcode save on my database. i can save FMD on database by using Fmd.SerializeXML() but when i'm going to extract record on database i use Fmd.DeserializeXML() but i'm having error. I'm stuck with it.
here is my code.
Public Sub OnVerify(ByVal captureResult As CaptureResult)
' Check capture quality and throw an error if bad.
If Not Me.CheckCaptureResult(captureResult) Then Return
SendMessage(Action.SendMessage, "A finger was captured.")
Dim resultConversion As DataResult(Of Fmd) = FeatureExtraction.CreateFmdFromFid(captureResult.Data, Formats.Fmd.ANSI)
If resultConversion.ResultCode <> Constants.ResultCode.DP_SUCCESS Then
Me.Reset = True
Throw New Exception("" & resultConversion.ResultCode.ToString())
End If
firstFinger = resultConversion.Data
'PULL FINGERPRINT RECORD FROM DATABASE
Dim FMDDataset As New DataSet
Dim DBconn As MySqlConnection = New MySqlConnection(connString)
Dim sql As MySqlCommand = New MySqlCommand("SELECT * " & _
" FROM tbl_fr ", DBconn)
DBconn.Open()
Dim DataAdapter As MySqlDataAdapter = New MySqlDataAdapter()
DataAdapter.SelectCommand = sql
FMDDataset.Clear()
DataAdapter.Fill(FMDDataset, "tbl_fr")
DBconn.Close()
If FMDDataset.Tables("tbl_fr").Rows.Count > 0 Then
Dim counter As Integer = 0
Dim fp1, fp2 As String
fp1 = Fmd.SerializeXml(resultConversion.Data)
Dim byteS As Byte()
'data from database
byteS = FMDDataset.Tables("tbl_fr").Rows(0).Item("frp")
'im having error on this line of code
'DP_INVALID_PARAMETER: Data at the root level is invalid. Line 1, position 1.
MatchFinger = Fid.DeserializeXml(Convert.ToString(byteS))
Dim resultConversionDATA As DataResult(Of Fmd) = FeatureExtraction.CreateFmdFromFid(MatchFinger, Formats.Fmd.ANSI)
Dim idResult As DPUruNet.IdentifyResult = DPUruNet.Comparison.Identify(firstFinger, 0, "resultConversionDATA", 2147, 2)
If (idResult.ResultCode = DPUruNet.Constants.ResultCode.DP_SUCCESS) Then
If (idResult.Indexes.Length >= 1) Then 'Found potential match, dont search second collection (speed optimized)
MsgBox("RECORD MATCH")
Else
MsgBox("RECORD MISMATCH")
End If
End If
counter = counter + 1
Me.BindingContext(FMDDataset, "tbl_fr").Position += 1
Loop While (counter <= FMDDataset.Tables("tbl_fr").Rows.Count)
End If
End Sub
HERE IS MY SERIALIZED DATA SAVED ON MY DATABASE.
'1, 1, '<fid><bytes>Rk1SACAyMAABuAAz/v8AAAFlAYgAxQDFAQAAAFZEgKsAzXlTgMUAzn1TgL8Ae5FSQCUAtiJSgEoA9RdSgHcAsHhSgRQArTJQgQgBCSVPQOgAj4lNgQ0AwINMQEgAkR1MQDwA5WJMQQkBBSJLgJIA+R5KgJIA3XVKQCgAliJJQCMA9WBJQKoAV6dJQIoA3nNJgGgBVwVJgO4A9XxHgOsBAh5HQMwBV3pFQIMBMhREQKABO2JEgK0APLJDgNoAVqNCgBUAt4dCQRIBCSRBQGUBQwpBQGUBSGBAQJ4AhnZAgJ4BYw8/QLcAYqE/QMsAXZ4+gCMA2V8+gQUBJok+gNEBZHE+QK0Ae4k9gDcBIwo9QKUBZBI9QPMBJng9gLkAWqA8QK0AXaE8gRQBIHg8QSUAi487gBsA4l07QQ8BLYM7gIgBNgs7gSUAySs7QJ4Ae3E6QPIBNh45gHwBZ1o5QB0A3V44QOcBSHM3gKQAi343QOgBNBs3gLcAcI82gQwBNiw2QPEBSps2QMwBY3E1AKQBXGs1AOEBLBs0ANAAPKc0APgA+Xo0ASYAui8zAJcBPRYzAPcBQZ4zAAA=<format>1769473<version>1.0.0'
|
|
|
|
|