|
This will get the number of records between the 2 dates. Note the date format when passing a string.
Select count(*) from Table where Date between '2009-08-10' and '2009-08-25'
Also look into parameterised queries.
Use
Select * from Table where Date between '2009-08-10' and '2009-08-25'
to get the actual records and then table.rows.count()
|
|
|
|
|
Thanks. I am able to start working on the queries which You sent. But I am getting a small problem in this.
In my database, the date entered is in format, e.g. "08/10/2009 3:14:00 PM".
Now when i am choosing this date from my datetimepicker control, it selects the correct date, but not the time; i mean it selects the current time, say e.g. "08/10/2009 7:55:00 AM".
But I want to filter my database column using the selected date and time from the dateTimePicker control.
My code for filter is :
cmd = New SqlCommand(" SELECT * FROM client_details WHERE DateofJoining BETWEEN #" + Convert.ToDateTime(timefrm.ToString()) + "# AND #" + Convert.ToDateTime(timeto.ToString()) + "# ")
what are the steps involved after this to retrieve the data from the datagridview?
Consider in my example, "timefrm" is 08/10/2009 9:00:00 PM and "timeto" is "08/12/2009 9:00:00 PM" .
I mean, I want to filter my record between these dates and then count the number of filtered rows.
Can anyone help me on this?
|
|
|
|
|
Ok, you MAY have made mistake number one in database design. Store your dates as datetime, not as varchar. You should immediately go and change you format to datetime, wear any pain there is for validating existing data but do it now. Life will ony get worse from here on if you don't.
When storing a datetime from client pass in Today instead of Now - eliminates the time component of the datetime (unless it is required of course)
You should try eliminating the time from the datetimepicker (short format) and add 9pm to the result.
Also look into parameterised queries, they may be complex to start with but will make you life a lot simpler and safer (sql injection[^]).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Ho can I send commands to remote server's command prompt..
Please Help... 
Be an Eagle, Sky is Yours.
|
|
|
|
|
You don't. The CMD prompt has no facility like a "remote shell". What are you trying to do with the remote CMD prompt??
|
|
|
|
|
Telnet? 
If everything was not true, would it be not true that everything is not true? So by saying everything is not true, you are automatically denying that everything is not true. Im so confused...
FreeDOS
- An open source modern
MS-DOS/PC-DOS replacement.
|
|
|
|
|
Hi,
I need to get the information if the current user is in role Administrators!
I start the program non elevatet (because else the uac prompt always show) then I check if an update is available,
if an update is available then I restart the application elevated because i need to install a update!
My issue is how to check if the current user is in role Administrators when the program is started non-elevated??
This is my code to check if user is admin, this only works if program is started elevated! (Windows Vista)
Public Function IsUserAdmin() As Boolean<br />
Dim UserPrincipal As WindowsPrincipal<br />
<br />
UserPrincipal = New WindowsPrincipal(WindowsIdentity.GetCurrent())<br />
If UserPrincipal.IsInRole(WindowsBuiltInRole.Administrator) Then<br />
Return True<br />
Else<br />
Return False<br />
End If<br />
End Function
Does anyone knows how to solve this??
|
|
|
|
|
Hi,
this C# code gets me all roles in a string:
try {
Thread.GetDomain().SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
WindowsPrincipal wp=new WindowsPrincipal(WindowsIdentity.GetCurrent());
foreach(object roleName in Enum.GetValues(typeof(WindowsBuiltInRole)))
if (wp.IsInRole((WindowsBuiltInRole)roleName)) roles.Add(roleName.ToString());
} catch (Exception exc) {...}
string s="User name = "+Environment.UserName+" Windows role = "+string.Join(", ", roles.ToArray());
I trust you can easily convert that to VB.NET
|
|
|
|
|
Hi,
this is virtual the same code like mine, the issue is that on vista when the program started non-elevated ("asInvoker") the current user has permissions of a standard User (I only get the User role), but I need to check if the logged on user is in group administrator (for me it is regardless if the current user started this program as standard user), because then i start a new process with the current user as admin!!
I need to first start the application as standard User because else i always get the windows vista prompt screen!
|
|
|
|
|
Seems fair. Sorry. I don't know then, however I would like to know.
|
|
|
|
|
|
Hello EveryBody,
After a long long time.
Dear Moderator Sorry for posting it here. Could not find a proper place for posting Query related post and also it was urgent.
I have a database in MSAccess.
I have a table as tbl_Menu.
This table has a parent child relation ship.
Parent Node is : MenuItemId and its
Child Node is : MenuItemPopUpHeaderID
The table structure is attached in Excel File with data.(HOW DO I ATTACH THE FILE HERE IN CODE PROJECT?)
I want a query to pick up the last child Item of each Parent Item.
I am using VS 2008 (c#) as front end.
Regards,
Sourabh.
Thanks and Regards,
|
|
|
|
|
Dot Net Jantu wrote: HOW DO I ATTACH THE FILE HERE IN CODE PROJECT?
You don't there is no facility to upload files.
This should be in the General Database forum as it is a query.
What have you tried so far?
|
|
|
|
|
Dear All,
I have manage to process some sorting and calculation in a few columns of my Dataset. I want to do a printout for the data now inside the dataset using Crystal Report Viewer. Can anyone told me how should I put my Crystal Report viewer to display this Dataset? I'm using VB.NET WINFORM 2.0 with ACCESS DATABASE.
This is my code now to process my data inside the dataset:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim i As Integer
Dim BATCH, TRNCDJ, RESONJ, REFNOJ, INVT, CUSTCODE, CUSTNAME, ADD1, ADD2, ADD3 As String
Dim DD, MM, YY, INVNO, QTY, PONO As Integer
Dim Day, Month, Year, D1, ADDRSS, INVNUM As String
Dim mydatatable As New DataTable
Dim ukCulture As CultureInfo = New CultureInfo("en-GB")
Dim myDateTime As DateTime
Try
If ComboBox1.SelectedItem <> "" Then
cn.Open()
MyDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM DATA WHERE ITEMJ='" & ComboBox1.Text & "' ORDER BY DD,MM,YY ASC", cn)
MyDataAdapter.TableMappings.Add("Table", "OrderTable")
DtSet = New System.Data.DataSet
MyDataAdapter.Fill(DtSet)
dt = DtSet.Tables("OrderTable")
DataGridView1.DataSource = DtSet.Tables(0)
cn.Close()
If Trim(TextBox1.Text) <> "" Then
STOCK_BAL = Trim(TextBox1.Text)
Else
STOCK_BAL = 0
End If
mydatatable.Columns.Add("Date", Type.GetType("System.String"))
mydatatable.Columns.Add("CUSTNAME", Type.GetType("System.String"))
mydatatable.Columns.Add("ADDRESS", Type.GetType("System.String"))
mydatatable.Columns.Add("INVOICE_NO", Type.GetType("System.String"))
mydatatable.Columns.Add("PO_NO", GetType(Integer))
mydatatable.Columns.Add("BATCH_NO", Type.GetType("System.String"))
mydatatable.Columns.Add("Qty", GetType(Integer))
mydatatable.Columns.Add("BALANCE", Type.GetType("System.String"))
For i = 0 To dt.Rows.Count - 1
'MessageBox.Show("This is " & i & " ")
BATCH = Trim(DataGridView1.Item(3, i).Value)
DD = Trim(DataGridView1.Item(4, i).Value)
MM = Trim(DataGridView1.Item(5, i).Value)
YY = Trim(DataGridView1.Item(6, i).Value)
If Len(Trim(DD)) = 1 Then
Day = "0" & CStr(DD)
Else
Day = CStr(DD)
End If
If Len(Trim(MM)) = 1 Then
Month = "0" & CStr(MM)
Else
Month = CStr(MM)
End If
If Len(Trim(YY)) = 1 Then
Year = "0" & CStr(YY)
Else
Year = CStr(YY)
End If
D1 = Day & "/" & Month & "/" & Year
If D1 <> "" Then
myDateTime = DateTime.Parse(D1, ukCulture.DateTimeFormat)
End If
QTY = Trim(DataGridView1.Item(7, i).Value)
TRNCDJ = Trim(DataGridView1.Item(8, i).Value)
RESONJ = Trim(DataGridView1.Item(9, i).Value)
REFNOJ = Trim(DataGridView1.Item(10, i).Value)
PONO = Trim(DataGridView1.Item(11, i).Value)
If Trim(DataGridView1.Item(12, i).Value) <> "" Then
INVT = Trim(DataGridView1.Item(12, i).Value)
INVNO = Trim(DataGridView1.Item(13, i).Value)
INVNUM = INVT & CStr(INVNO)
CUSTCODE = Trim(DataGridView1.Item(14, i).Value)
Else
INVT = 0
INVNO = 0
INVNUM = "0"
CUSTCODE = 0
End If
If Trim(DataGridView1.Item(16, i).Value) <> "" Then 'Process the ADDRESS
ADD1 = Trim(DataGridView1.Item(16, i).Value)
ADD2 = Trim(DataGridView1.Item(17, i).Value)
ADD3 = Trim(DataGridView1.Item(18, i).Value)
ADDRSS = ADD1 & " " & ADD2 & " " & ADD3
Else
ADD1 = "0"
ADD2 = "0"
ADDRSS = ADD1 & " " & ADD2
End If
If Trim(DataGridView1.Item(15, i).Value) <> "" Then
CUSTNAME = Trim(DataGridView1.Item(15, i).Value)
Else
CUSTNAME = "Nothing"
End If
If TRNCDJ = "P" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
INVNUM = ""
ADDRSS = ""
PONO = REFNOJ
CUSTNAME = "STOCK PURCHASE"
ElseIf RESONJ = "02" Then
'INVNUM = ""
'ADDRSS = ""
PONO = REFNOJ
CUSTNAME = "INTERNAL TRANSFER"
BATCH = ""
STOCK_BAL = STOCK_BAL + (QTY)
End If
ElseIf TRNCDJ = "R" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
End If
ElseIf TRNCDJ = "G" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
ElseIf RESONJ = "11" Then 'BITA Stock Transfer
CUSTNAME = "STOCK ADJUSTMENT"
ADDRSS = ""
INVNUM = ""
'PONO = REFNOJ
BATCH = ""
STOCK_BAL = STOCK_BAL + (QTY)
End If
ElseIf TRNCDJ = "T" Then
If RESONJ = "01" Then
STOCK_BAL = STOCK_BAL + (QTY)
End If
End If
Dim myrow As DataRow
myrow = mydatatable.NewRow
myrow("Date") = CStr(Day & "-" & Month & "-" & Year)
myrow("CUSTNAME") = CUSTNAME
myrow("ADDRESS") = ADDRSS
myrow("INVOICE_NO") = INVNUM
myrow("PO_NO") = PONO
myrow("BATCH_NO") = BATCH
myrow("Qty") = QTY
myrow("BALANCE") = STOCK_BAL
mydatatable.Rows.Add(myrow)
Next i
End If
DataGridView2.DataSource = mydatatable
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Currently everything runs perfectly in my mydatatable,it shows the output in a datagridview2. But I need to do printout using the crystal report,can anyone please give me some idea or sample how to do this?
Thank you for reading.
Drex
|
|
|
|
|
Let me get this right!
You get a datatable from Access
Then bind the datatable to a DGV
Then you create another datatable to format the data into, by the looks to faff about with the date and some order types
you then bind that result to a second DGV.
I believe Access supports named queries (something like a stored proc), which is where all this formatting should be done. I would recommend SQL Express as a better solution.
With CR I used to design a report that was serviced by a stored proc but I believe you can pass a dataset to a designed report.
|
|
|
|
|
Hello Mycroft,
I'm not so familiar with the stored procedure as you mentioned. Can you show me some sample,how to do this as what you told me? I need to printout the data by using crystal report. Thank you~!
|
|
|
|
|
This is what I have done in the past ...
Since you already have the data in a dataset, you need to create a schema file for Crystal so that you can design the report.
1) Place this in your code somewhere after the datatable is defined.
mydatatable.writeXMLSchema("C:\TEMP\mytable.xsd")
You will only need this once, or you will need it again if you change the structure of your datatable.
2) from your solution explorer, Add New Item, "Crystal Report"
3) Select, "Use report wizard" to create the report
4) choose "Create New Connection\Database Files" as the data source
5) Select the "c:\temp\mytable.xsd" file
6) choose the "table" to add to your list of selected tables. Click Next
7) Select the fields from the table to include in your report
8) follow the rest of the prompts through the wizard, "grouping","Summaries", "Sorted by", "Chart", "Record Selection", "Styles"
9) This is how I load the reports and attach it to a dataset:
Protected Sub displayRpt(ByVal sRptName As String, ByVal ds As DataSet, ByVal crParamFields As ParameterFields)
Dim RptPath As String
Dim rpt As New ReportDocument
' I store my reports in a folder called, "RptSource", one level down from the main website.
RptPath = Server.MapPath("~/Rptsource")
rpt.Load(RptPath + "\" + sRptName)
rpt.SetDataSource(ds)
' Use the following only if you are passing parameters to the report. In my example, I am not passing parameters
'CrystalReportViewer1.ParameterFieldInfo = crParamFields
CrystalReportViewer1.HyperlinkTarget = "_blank" ' This causes the report to pop into a new browser.
CrystalReportViewer1.DisplayGroupTree = False
CrystalReportViewer1.HasToggleGroupTreeButton = False
CrystalReportViewer1.HasDrillUpButton = False
CrystalReportViewer1.HasViewList = False
CrystalReportViewer1.HasSearchButton = False
CrystalReportViewer1.EnableDrillDown = False ' hmmm
CrystalReportViewer1.Height = 800
CrystalReportViewer1.Width = 900
CrystalReportViewer1.ReportSource = rpt
'CrystalReportViewer1.RefreshReport()
End Sub
|
|
|
|
|
|
hi mates,
i have an editable DataGridViewComboBoxColumn,
i've used the gridMeasure_CellValidating and gridMeasure_EditingControlShowing from msdn samples.
the comboboxcolumn can now be editable but the problem is everytime i add an item, i got the error here.,
Private Sub gridMeasure_CellValidating(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) _
Handles gridMeasure.CellValidating
If (TypeOf CType(sender, DataGridView).EditingControl Is DataGridViewComboBoxEditingControl) Then
Dim comboBoxColumn As DataGridViewComboBoxColumn = _
Me.gridMeasure.Columns(2)
If (e.ColumnIndex = comboBoxColumn.DisplayIndex) Then
If (Not comboBoxColumn.Items.Contains(e.FormattedValue)) Then
<code>comboBoxColumn.Items.Add(e.FormattedValue)</code> -->'Items collection cannot be modified when the DataSource property is set.
End If
End If
End If
End Sub
* I found a solution here
If you want to add an item to the bound combobox, you can add it beforehand in the SQL statement. E.g.
If I want to select a list of authors, then want to add another one then.
SELECT first_name, last_name FROM authors
UNION SELECT 'Joe', 'Bloggs'
But i don't know where to put that statement in my code. could somebody help me or give another solutions on how to add an item with a bound combobox meaning i have a datasource.
thanks in advance, 
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
Sub FillUnits()
Dim mCls As YourClass = New YourClass
Dim i As Integer
With ctlGrid
Dim cmb As New DataGridViewComboBoxCell
For i = 0 To record.Count - 1
cmb.Items.Add(mCls.List_All.Item(i).Name.ToString)
Next
ctlGrid(ColumnNo, CurrentRow) = cmb
End With
End Sub
Public Function List_All() As List(Of YourClass)
Dim _Fill As String = "select Name from Table"
Dim con As New SqlConnection(gConnectionString)
Dim cmd As New SqlCommand(_Fill, con)
Dim dList As New List(Of YourClass)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
dList.Add(New Cls_Unit_Master(dr.Item("Name").ToString))
End While
dr.Close()
con.Close()
Return dList
End Function
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
wow. this is great!. thanks so much, i'll try this one.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
try to Put your Code in DataError Event of the Grid
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
one more thing, i used OleDb not SqlDB, can i still apply this to adodb.recordset?
and why comboboxcell? do you have a link so that i can refer. thnx.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
Yes you can use , this is my own code , sorry i have no link for this code but i will not recommend you record set approach an old approach i will recommend you to create your on objects and used Object Oriented Approach
not because i am using but you can see as Visual Studio is built everything there in VS is an object and as well as Microsoft Recommended Approach
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
hi,
do you have the entire class example? i still cant get my problem. im stuck on this. i try your code but i cant get it. would you mine if you post your code here. i mean code where
Dim mCls As YourClass = New YourClass and Cls_Unit_Master is written
thanks
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Badger | [badger,badger,badger,badger...]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/badger.gif)
![Jig | [Dance]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/jig.gif)