I want to do a SQL SELECT COUNT in VB .NET to an Access DB. I can get the code to work using a text fields and text criteria but if I use a date field and Date criteria it returns 0 when there are some 47 records that it should count.
Dim Results As String
Dim InvDT As String = Date.Today
InvDT = "#" & InvDT & "#"
Dim strCnn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:MainData.mdb" connection string to your DB'
[Code] .....
I'm writing an app for someone where I am pulling in work orders table information. The information includes: worknum, fname, lname, customername, description, etc. I do not plan to update the data in this table, only to read from it.I'd like to display a combo box for the work order numbers. The user would start typing the information for the work order in and it would autocomplete based on what's in the list. As work orders are displayed in the combo box, I want to update the data in text boxes representing the various fields.
I'm thinking rather than query the database several times, it would be a good idea to create a DataSet with information for the work orders table within the last x months then query the data from that dataset. I know there is a DataSet.Select method, but it looks like it returns the entire row. Is it possible to retrieve just a column of distinct values? For example, I'd like to do a select statement similar to this:
SELECT worknum FROM {WorkOrderDataSet} WHERE date >'{today-x}
The first part in curly brackets is meant to represent the work order dataset I create when first importing the data from Access. The second part in curly brackets will be replaced with variable data to represent a date to reference. I also don't think I'll need distinct because the workorder is unique (edit: is a primary key)Is it possible to retrieve just a column of distinct values from a DataSet with multiple columns? How do I do this?
I'm using VB.NET with the MySQLConnector, I have an ID column with differents values, i.e:
101
102
201
202
203
302
304
305
306
I want to select all the values that start with "3" and then, select the MAX of them, in this case, "306"
(1) how exactly to format the textbox value in to datetime format like dd/mm/yy HH:mm irrespective of system regional settings
(2) is it necessary to pass the dtavalue as parameter to a access database
in format "MM/dd/yy HH:mm"
I am using select command for one of my button
Dim edit1 As OleDbCommand = New OleDbCommand("select * from [exporter] where exp_id='" & t1.Text & "'", con1)
now:-
t1 is a textbox
and in my table exp_id is "Number" now when i run this i get an error at the time executing reader (Data type mismatch in criteria expression.) but when i change exp_id properties to "Text" it run's properly
I have a datatable as shown in the figure. Let me explain my required based on this image. I have 7 rows of data. The rows 1 and 2 contains columns till UnitSqcNo same. I want only row 3 among the two. In general I want select all the rows with distinct model, unittype, unit and rest with greater CompId. ie the table should look like
View 1 RepliesI would need to update a record using inline SQL string, when I use the first SQL string I get the error "data type mismatch in criteria expresion" but when I remove the time fields and run the SQL string the code works
[Code]....
I want to retrieve query from MS.Access database criteria is between some date range,but I get the following error when execute my reader:Syntax error in date in query expression 'D_ate BETWEEN # @DateFrom # AND # @DateTo #'.
Dim StrSQL As String
StrSQL = "Select * from Pla_Comp Where D_ate BETWEEN # @DateFrom # AND # @DateTo #"
myCommand = New OleDbCommand(strSQL, conn)
[code]....
The problem that I'm having is that when I try to select a different date, the text doesn't go away. That may make no sence, but let me tell y'all what I've done.
1: Created a GroupBox
2: Put a DateTimePicker in the GroupBox
3: Put TextBoxs in the GroupBox
4: Changed the DateTimePicker to look like this:
A)Checked - False
B)Allow Drop - True
I thought that if I changed that, then the text in the text box would go to that specific date. It didn't do that, it just stays the same. What I'm trying to do is enter text in say 3/7/11 and when I choose date 6/12/12 it will display blank text boxes for me to enter something different.
I know how to do this in C#, but my dev team doesn't use C#...here is the answer in C#:How do I do this in VB?Essentially, if I knew how to write lambda expressions in VB, I would be set, but the materials I hav
View 2 RepliesHow will I select a substring with 2 criteria? I have a Richtextbox where I select a number after a : with this code; For Each line As String In Richtextbox1.Lines Dim equalsPos As Integer = line.IndexOf(":") + 1 txtFound.AppendText(line.Substring(equalsPos, line.Length - equalsPos)) Next This is working ok, but the whole line.length is appended for number2. The line ex. Some text:my number some text-my number2. I want my number appended to txtFound, and my number2 appended to txtFound2. With my code only number2 can be appended correct.
View 4 RepliesI'm trying to get a simple count of rows meeting some criteria. The value returned does not coincide with the correct number of rows when running the same query in Management Studio (SQL 2005)
vb.net
Public Function RowsAffectedCount() As Integer
Dim result As Integer = 0
[code]....
'result' always returns 1, but I have 3 records meeting that criteria in the database. Been staring at the code for a couple hours now and can't figure it out... I just need to know if the Count of rows returned from a SELECT statement is greater than 1 (at least two records).
How can I count date? E.g. today date = 6/4/2009 and last date = 3/3/2009. So the algorithm should be like this : new date = today date - last date.
View 3 Replieshow can i do a date count like from 1st jan 2010 to 19th may 2010, how many days are there in it? is there such function like datecount?
View 3 Replieshow do i add a count down (eg, 20secs) and unless the user clicks something, then "Yes" is automatically selected? [code]
View 3 RepliesMy tables sectionName column has an existing value. I want to update its Adviser column but my select count always evaluate to false. I want to check if my table's SectionName column record matches the selected value of my CboSectionName. if there is a match then i'll check if such section has an adviser already. If there is an adviser already, then ask the user if he/she wants to change the adviser of that section. If he/she wants to change/update the adviser then update it else exit. however, if there is no adviser yet then assign an adviser to column adviser from cboAdviser. If no record yet in table's SectionName column then prompt the user that there is no section name yet, then exit. I have been debugging the code below for hours now but I can seem to locate why my sql count always resulted to false. [Code]
View 11 RepliesI've tried to write a code which will count the orders for each person in my users table. This is what I have but it's not working. What is wrong?
vb.net
Dim command As New SqlClient.SqlCommand("SELECT *, COUNT(Orders.Username) FROM Users LEFT OUTER JOIN Orders ON Users.Username = Orders.IssuedBy ORDER BY Users.PersonName")
I want to make a little program that will count down the hours to a specific date. I.E. "Hours until: ##/##/##"
Like they do for new year and other big events in the world or whatever but I'm having big problems. I don't know how to use datediff to compare the difference in hours from 1 date to the next.
I'm trying to access the Count Property on the array of rows returned by the datatables select method, this is after converting the Web Project to 3.5
View 2 RepliesI have a sql statement
SQL = "select * from Candidate where test_date = " & TextBox2.Text
I am getting error in the line myDataAdapter.Fill(myDataSet)
Conversion failed when converting the varchar value '03/15/11' to data type int
In my sql server test_date column data type is varchar.
I am using the following code to find a date field in a Table. I think my syntax for the Sql statement is wrong. Date is stored as a Short date in the table. The variable is a shortdate.
[Code]...
Actually, I want to be able to select a date (example, 2/10/2010-) and it add it to the textbox, then select another date(example,3/28/2010) from the same datetimepicker and it appends it to the textbox.(It will be like this on the final click, 2/10/2010-3/28/2010 and save it to the Access database).
View 9 RepliesIn earlier vb.net 2008 I used the DateTime to read the date in dd/mm/yy format. I use to change the culture info to UK format. So that the date will be selected from SQL server as in dd/mm/yy format.
But I know it's not good to play with CultureInfo. Even though I used like the following manner.
Sub Form_Load()
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-GB", False)
End Sub
class:
Public Sub insetTotalPaidAmt(ByVal dtSDate As Date)
Dim comm As OleDbCommand
Try
strQry = "INSERT INTO rpt_Paid_Amt(ST_ID,ST_PAY_AMT,ST_DIS_AMT) " & _
"SELECT S.ST_ID,SUM(P.PAY_AMOUNT),SUM(P.PAY_DIS) " & _
[Code]...
When a user selects the datetimepicker, or types in the date in the text box, the calendar then highlights that date (for instance 02/02/2009) and puts the date in the text box.
If the user deletes the date from the textbox and then selects the drop down arrow again to show the calendar, the 02/02/2009 date is still highlighted. The user can select a different date or type in a date (including the highlighted one) - no problem. But if they wanted to select the same date they deleted, they cannot. No error message, it just leaves the text box blank.
Yes, it's very rare that that happens and they can highlight a different date, then go back in and highlight the 02/02/2009 date in a matter of two clicks but they want it fixed .
Not sure how I go about getting the calendar to clear the highlighted date once it has been deleted from the textbox?
I need to have a query which list all the user according to the date value in the database. Problems is this, in the database the date format is 5/5/2009 4:30:12 but I want to compare with 5/5/2009. I think the value of the based date is 5/5/2009 12:00:00 and that's why I couldn't query it.
The query is something like
dim db = new databcontext
dim user = from u in db.datacontext where u.signUpTime = 5/5/2009 select u.
The data type for my DATE column in SQL is smalldatetime. I want to perform a query which selects records based on the the selected date of a calendar object.
A user clicks on the date and the date is stored in ddlfilter2 DDLfilter2.Items.Add(CalendarMain.SelectedDate)
mystring = "SELECT * FROM view_main where date" & "='" & DDLFilter2.Text & "'"
produces this "select * from view_main where date='24/03/2009'
I then get an error saying "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
In the SQL database the data is displayed as "24/03/2009 00:00:00" is this why the query is failing?
I would like to select transaction id based from its creation date. All transactions are displayed on form load event on a datagrid. The select function should be executed on 2 datetimepicker (the first should be the start date, the last should be the end date).So there are three controls on a form, 2 datetimepicker and a datagrid. I managed to load all transactions into a datagrid, and managed to select/filter those transaction based on id, but not date.the table is called 'transaction_details', it contains 'transaction_id', 'transaction_date', and few other columns. transaction_id is the primary key.
View 2 RepliesI have a table with these columns:
IDsales, salesDate, salesmanID, itemDesc
I need an SQL querry to select all the sales from a certain date, with the summed sales grouped by salesmanID. The problem with the querry is that the data in the salesDate field is in a long format (with dd/mm/yyyy hh:mm:sss) and I dont know how to set the querry to disregard the "time" info, and take in account the "date" part only.
Using Microsoft SQL Server I connect to tblTransactions. Within there is transDate. I have a from and to date selecter tool in vb.net. In my select statement how do set it to select only records greater than datefrom but less than dateto...?
I tried SELECT * from tblTransactions where date >- ...