Split the sentence with space and store it in an array then convert to proper case of the first and last element of that array. After that join the array elements with space.
Group By clasue always produce a count or summation of another field. It never shows other feilds, if you tried to so the values of other fields without aggregating them it shows an error message.
If you want to group by with a single field you only can get the aggregated fields. The syntax should be
select p.prtgst,sum(prd.totaltaxamt)As Rate,sum(prd.TaxableAmt)As Taxable_Amount
from TBL_pur_inv as pr
INNER JOIN tbl_party_ldg AS p ON pr.partyIDpur = p.partyID
INNER JOIN TBL_pur_invdet AS prd ON pr.purinvid = prd.purinvid
where prd.purinvdt ='2018-01-04' GROUP BY prd.pdcgst
But to get every feild values the systax should be
select p.prtgst,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst,sum(prd.totaltaxamt)As Rate,sum(prd.TaxableAmt)As Taxable_Amount
from TBL_pur_inv as pr
INNER JOIN tbl_party_ldg AS p ON pr.partyIDpur = p.partyID
INNER JOIN TBL_pur_invdet AS prd ON pr.purinvid = prd.purinvid
where purinvdt ='2018-01-04' GROUP BY prd.pdcgst,p.prtynm,pr.invno,pr.invamt,PR.purinvdt,prd.pdcgst
@Jayanta_1: In a parameterised sql statement you must have to use the parameter before adding/creating the parameter. In your first sql statement you didn't use the parameter into the statement. use of parameterised sql statement is the best practice to prevent your database from sql injections. The codes should be as @xrj already discussed i.e.
Dim sql = "select Count(*) from pur_invdet where purinvid=@purinvid"
Dim cmd = New OleDb.OleDbCommand(sql, cn)
cmd.Parameters.AddWithValue("@purinvid", TextBox1.Text)
Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar())
But do not understand why did not you do the update and save codes for new items in the for loop to run between the listviewitems and the updates should be run for every items and save for new items. So previous veriable you declared as resulthas no proper functionality to check and do your updates /save.
And I do not understand what you want to do. Please explain briefly what you want to save/update for every listview items or store a cumulative value for a item if there has duplicate value if any.
WHERE FLD_ORDER_ID = '" & id & "' IN (SELECT '" & id & "' FROM TBL_ORDERITEM_A15428 GROUP BY '" & id & "' HAVING COUNT(*) > 1 )")
From my opinion in sql statement has problem. You have used IN predicate. The arithmatic operator equal to i.e. = compares a single value to another single value. In case a value needs to compared to a list of values then the IN predicate is used. The IN predicate helps reduce the need to use multiple OR condition.
Your statement should be
"SELECT FLD_ORDER_ID,FLD_PRODUCT_ID,FLD_QTY,FLD_SUBTOTAL FROM TBL_ORDERITEM_A15428 WHERE FLD_ORDER_ID IN (SELECT FLD_ORDER_ID FROM TBL_ORDERITEM_A15428 GROUP BY FLD_ORDER_ID HAVING COUNT(*) > 1 )"
If the job ofthe function run_sql_query() is to populate dataadapter and datatable then it cannot delete a record from the table. So you have to create another function / sub procedure to delete a record.
You can add a few lines in your codes in refresh_text subprocedure when id is null or whitespace, where you can nothing values to all textboxes and image control.
From my point of view you already complecated your code to check your database and then make permission.
From my point of view first check if there any record exists or not then do the next step. If there is no record then exit from entire proceedure else try to check priviledge of that user.
If txtpassword.Text = "" Or txtusername.Text = "" Then
MessageBox.Show("Please Fill in Empty TextBox!")
Exit Sub
EndIf
'Check User Exits or Not
If Not UserExists() Then
MsgBox("User does not exist!")
Exit Sub
EndIf
"Check for Login
LogInToNext()
Here the codes for UserExits() Function
Private Function UserExists() As Boolean
Dim Result as Boolean = False
Try
con.Open()
Dim query As String
query = "SELECT Count(*) FROM moneycs.user where User_Name= '" & txtusername.Text & "' and Password = '" & txtpassword.Text & "' "
cmd = New MySqlCommand(query, con)
If cmd.ExecuteScalar()>0 then Result=True
cmd.Dispose()
Catch ex As Exception
Finally
con.close()
End Try
Return Result
End Function
Now codes for LogInToNext() SubProceedure
Try
con.Open()
Dim query As String
query = "SELECT User_Type,User_Status FROM moneycs.user where User_Name= '" & txtusername.Text & "' and Password = '" & txtpassword.Text & "' "
cmd = New MySqlCommand(query, con)
dr = cmd.ExecuteReader
if dr.HasRows() Then
dr.Read()
If dr("User_Status")=Active" Then
If dr("User_Type")="Admin" then
frmmlsadmin.Show()
Else
MLSUser.Show()
Endif
Else
MessageBox.Show("You Don't Have Permission To Login System At This Time")
Endif
Endif
dr.Close()
cmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
txtusername.Text = ""
txtpassword.Text = ""
con.Close()
Me.Hide
End Try
Remove two duoble quotation mark at the end of the SQL statement which may the cause to close the session.
Use parameterised query to protect your database from unauthorised SQL injections and also it can help you to use spacial characters in your query.
Dim cmd As New OleDbCommand
sql = "select * from EQUIPOS where NO_VEHICULO = ? and DIA Between ? and ?"
cmd.Parameters.AddWithValue("@vehi", DropDownList2.SelectedValue.ToString)
cmd.Parameters.AddWithValue("@dt1", CDate(TextBox2.Text))
cmd.Parameters.AddWithValue("@dt2", T1)
Is con one and only one connection object in your project and already opened it at the time of starting of your application ? If it is, don't open it in every procedure or function because you already opened it at the time of your application has started and close it when you are trying to close your application. (Not recommanded) If you declared a proceedure lavel connection object and already opened it, don't try to open it another time in that proceedure or if you want to open it further more try to close it first then open it and at the end of that proceedure always try to close it.
Reverend Jim has already give you a lesson how could you use it.
I just tried your codes in my mechine and I have altered one to two lines you can find them in following codes.
Public Class Form1
Dim mRow As Integer = 0
Dim newpage As Boolean = True
Private Sub PrintDocument1_BeginPrint(sender As Object, e As System.Drawing.Printing.PrintEventArgs) Handles PrintDocument1.BeginPrint
mRow = 0
newpage = True
End Sub
Private Sub PrintDocument1_PrintPage(sender As System.Object, e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
With dgvreciept
Dim fmt As StringFormat = New StringFormat(StringFormatFlags.LineLimit)
fmt.LineAlignment = StringAlignment.Center
fmt.Trimming = StringTrimming.EllipsisCharacter
Dim y As Single = e.MarginBounds.Top
Do While mRow < .RowCount
Dim row As DataGridViewRow = .Rows(mRow)
Dim x As Single = e.MarginBounds.Left
Dim h As Single = 0
For Each cell As DataGridViewCell In row.Cells
Dim rc As RectangleF = New RectangleF(x, y, cell.Size.Width, cell.Size.Height)
e.Graphics.DrawRectangle(Pens.Black, rc.Left, rc.Top, rc.Width, rc.Height)
If (newpage) Then
e.Graphics.DrawString(dgvreciept.Columns(cell.ColumnIndex).HeaderText, .Font, Brushes.Black, rc, fmt)
Else
e.Graphics.DrawString(dgvreciept.Rows(cell.RowIndex).Cells(cell.ColumnIndex).FormattedValue.ToString(), .Font, Brushes.Black, rc, fmt)
End If
x += rc.Width
h = Math.Max(h, rc.Height)
Next
If newpage Then
newpage = False
Else
mRow += 1
End If
y += h
If y + h > e.MarginBounds.Bottom Then
e.HasMorePages = True
'mRow -= 1
newpage = True
Exit Sub
End If
Loop
mRow = 0
End With
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
PrintPreviewDialog1.Document = PrintDocument1
PrintDocument1.Print()
PrintPreviewDialog1.Show()
End Sub
End Class
Hope it can help you. Executing it I got perfect result
@සශික : Nothing wrong in your codes to print data. Just alter some lines in your codes. 1) Never declare the variable Dim mRow As Integer = 0 at Event level like PrintPage Event, always declare it at Form level and set its value to 0 at BeginPrint Event. 2) Just modify the codes at line No. 23,24 and 25 like
If newpage then
newpage = False
Else
mRow +=1
End If
y += h
Microsoft Ribbon Control is not made for vb or c# .net application. You could not use it directly. You can use it for your wpf application. If you want to use it in vb or c# .net application you must have to create a user control by wpf.
"Dim data() As String = {"box1", "box2", "box3"}" Here box1,box2,box3 are not the names of the variables, they are string 3 values of the string array data(). You can declare it as Dim data(2) As String data(0)="box1" data(1)="box2" data(2)="box3"
But to store data in an array by using a loop you can use dynamic array. In every rotation you can redimention it to store New one by using Redim keyword before the array name.
You have wrote a wrong code lines to search usertype at line 16 in the if - end if statement. If dr.Read = True Then 'Wrong searching codes here Else End If You already read data by calling ExecuteReader(). So, why are you calling an another Sal statement? You already have data to check UserType. The codes should be like this. If dr.Read = True Then If adaReader("UserType")="Normal" Then 'Do your jobs here for normal Else 'Doylur jobs for Admn End if Else 'Message here End If Hope it can help you.
@Imam_3: Sorry! I overlooked the codes for the Loop.
In vb.net any collection of objects are always 0 base indexed. So to count the total item numbers is always 1 less from the upper bound of that specific collection i.e Collection.count-1. If you have a collection of 10 objects. The index of the first element should be 0 and the index of the Last element should be Collection.Count-1 i.e. 10-1=9. So loop always rotates between 0 to Collection.Count-1 So your loop should be
For i = 0 To ds.Tables("room").Rows.Count-1
'your codes here
Next
Checking duplicate value on every keystroke when you are entering data to a text box is not a perfect thinking. It will disturb the user when he will try to enter data. From my opinion you can perform the duplicate value checking in TextbOX LostFocus Event instead of TextChanged event.
My opinion is you can solve your problems most easily if you create two seperate functons one to check Blank Text and another to check duplicate value. If the function (1) finds the blank text then exit from save. and if the function (2) finds the duplicate value from database then exit from save.
I just here give you an example of a function to check blank text.
Private Function BlankValues() As Boolean
If Trim(Me.txtStuName.Text) = "" Then
MessageBox.Show("Student name is empty.")
Me.txtStuName.Focus()
Return False
End If
If Trim(Me.txtStuRollNo.Text) = "" Then
MessageBox.Show("Roll No. is empty.")
Me.txtStuRollNo.Focus()
Return False
End If
Return True
End Function
And now an example to check duplicate value
Private Function SearchDuplicate() As Boolean
Dim result As Boolean = False
Dim Conn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
Dim Cmd As New System.Data.OleDb.OleDbCommand
Try
Conn.Open()
Cmd.CommandType = System.Data.CommandType.Text
Cmd.CommandText = "Select Count(*) From Student Where RollNo = ?"
Cmd.Parameters.AddWithValue("@EnrollNo", Me.txtStuRollNo.Text)
Cmd.Connection = Conn
Dim rd As Integer = Cmd.ExecuteScalar()
If rd > 0 Then
result = True
End If
Cmd.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
Finally
Conn.Close()
Conn.Dispose()
End Try
Return result
End Function
Now time to save new data.
Private sub SaveNewData()
'First Check for blank value.
'If it finds blank then Exit sub
If Not BlankValues() Then
Exit Sub
End If
'Check here for duplicate value
'i.e. the value already exists in database
'If it true then exit sub
If SearchDuplicate() Then
MessageBox.Show(String.Format("Roll No. {0} already exists.", …
