I 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.
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]...
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?
My DATE field always display with time in vb.net is there a select query to format it into yyyy-MM-dd?
View 3 RepliesI have this situation where I have a SqlDatasource control and the select query is like:
SELECT col1, col2 FROM table1 WHERE colDate = @date
The source of @date is a label with the text: 2009-05-29 12:06:00 I get the following error when I run the query:
Conversion failed when converting date and/or time from character string
I tried using convert(datetime, @date), as well as different date/time formatting of the label itself. Everytime I get the error.
However, when I run the query in the management studio like:
SELECT col1, col2 FROM table1 WHERE colDate = '2009-05-29 12:06:00'
I found out that the @date is parsed as 05-29-2009 01:30:00 TT I don't know where the TT is coming from? And I'm sure SQL Server wouldn't be able to handle it?
I am trying to get a function so when i press the calculate button it somes up with sales totals and sales commissions. this is just a simple project i just cant find the answer.
View 5 RepliesI need my program to output and input a customer ID and sales amount multiple times. I need it to display both outputs, customer ID and sales amount, on mulltiple lines. It also will need to accumulate the sales amount and then display the total sales. it should look something like that.[code...]
I think that I was supposed to use a messagebox but wrote the program a different way. the form.vb was already locked, but I added two text boxs. Not sure if I did this right because I need to enter multiple sales for one customer ID.[code...]
I'm trying the fetch only the date within the current and return how many rows Dim dc as new dataclassesdatacontext dim q=from p in dc.worktime where p.name.equals(session("name") and p.date ).count what I should I put after "p.date" ? And if I want to search for the record which there are record that are insert five days earlier, how to do that ? use (datetime.now - 5) ?
View 3 RepliesI want to select to multiple sql tables using select query in vb.net..I have 2columns in sql table..I have tried this query.. But i'm getting error.
TextBox11.Text = Val(27)
cmd = New SqlCommand("SELECT outside,power FROM vijay21 INNER JOIN vijay22 ON vijay21.outside=vijay22.outside WHERE outside BETWEEN " & Val(TextBox11.Text) & " AND " &
[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 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).
I have an ado.net statement I want to convert to use NHibernate:
Dim sql As New StringBuilder()
sql.AppendLine("SELECT r.RoleId, r.RoleName ")
sql.AppendLine("FROM dbo.aspnet_Roles r ")
[code]...
I have pulled information from a database into a datatable (dtCustomerInfo) then I expanded on the columns by processing the data and I can display this in a datagridview without any problem. However, the data is in detail and I want to ultimately summarize by customer name.The code below has a commented sql statement that is the statement I ultimately want but it is not working. However, the idea is that I want to Select from one datatable into another but run aggregate sum on several columns so that I will get one record per customer showing totals for each column.So I broke it down to just pull all data possible into the second table.[code]I do know the grouping in the current select * statement does nothing but my issue is more that the columns do not show up.
View 3 RepliesOn an SQL query I have. I have the select query working fine but according to the result I need to set the column of a table to 0. I have the following Select query:
CODE:
So where the CommissionCalculator SetUp > 0 I want it to Update the CommissionStructure SetUp column to 0. I was thinking I might need an IF statement but I dont know how to work with them in SQL.
I'm tying myself in knots with AND OR NOT qualifyers to return all rows having two different items. From a column headed 'Instrument' I want to retun 'CELLO' and 'VIOLIN' Where am I going wrong with the following?
View 8 Replieshow to select multiple table in vb2010? i trying to create a login so i need to retrieve the data in 3 different tables in one database i have a code but not work.[code]
View 2 RepliesLinq to SQL query to Select the maximum "MeanWindSpeed" value? The database name is "WeatherArchives" The TableName is "TblValues" The Column is "MeanWindSpeed" And Also, I would like to have a query to get me the Max "MeanWindSpeed" in each year,
View 1 Repliesi have a project which is room scheduling. i'm having a problem on how to make my query to be able to select the available rooms for the givin time start and time end.
View 14 RepliesI am trying to link to a Access database at runtime using a SELECT FROM WHERE query, which I can do but.......
with the WHERE part I want to select several conditions, 4 actually and it is not working, what am I doing wrong?
Here is my code,
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=V:GYMDBFLineProdSanderProduction.mdb")
[Code]....
Public Shared Function getrealrequests(ByVal itemname1 As String) As ArrayList
Dim sqlStr As String = "data source=localhost;initial catalog=Customers;integrated security = true"
Dim dbRecords As ArrayList = New ArrayList
Dim dbConn As SqlConnection = New SqlConnection(sqlStr)
[Code] .....
I'm trying to pass the value of 'itemname1' to the select query. What's the correct syntax to get it to work?
I've a collection with the data like this.[code]...
how can select the distinct data using linq?
Dim MyQuery = From c In xdoc.Descendants() _
Where c.Attribute(OriginY) IsNot Nothing _
Order By Val(c.Attribute(OriginY).Value), Val(c.Attribute(OriginX).Value) _
Select c.Attribute(UniStr)
Right above you can see my First! linq attempt! And here comes my first question.
How can i select more than one column in a linq query in vb.net?
For example... Select c.Attribute(UniStr) AND c.Attribute(OriginY)
there are three tables in dbf , table1,2,3 respectively all are contains similar structure but different data Structure : S.no Quiz Question i want the query which select the Quiz Question/records through user given Range from Textbox1.text to Textbox2.text
View 3 RepliesI am using VS 2010, SQL 2008 express and doing windows applicaion programming in vb.net.I want to write a select query(or Stored Procedure) such that the conditions mentioned under WHERE clause can be variable .like :Select * from TABLE where F1 = var1 and F2 = var2 and F3 = var3.Now on the basis of some conditons the equations in where clause should beused.Say condition is variable value >6.Now in this case ,if
var1 =7 var2 =3 var3 = 8 then the query shud behave like
:
select * from TABLE where F1 = var1 and F3 = var3
[code].....
My following code is only retrives number month(1,2,3,4....12)and not month name(January,February,.....December).Before that i had try using function datepart, Monthname and date_format but i still din get my answer.[code...]
View 3 RepliesI have this query which I want to submit after the user click a button.
Update tbl_books Set Bk_Rent = Bk_Rent - 1 Bk_Avl = Bk_Avl + 1 Where Bk_Id = ( Select * from tbl_transactions where Trans_Id = @a )
The problem is as far as I know, Update and Select queries have different formats. Update statements will use the ExecuteNonQuery()while Select needs a dataset. My problem is how can I execute these two statements at the same time. I tried this code but it didn't work out.
Dim command4 As New OleDb.OleDbCommand
Dim adapter4 As New OleDb.OleDbDataAdapter
Dim dataset As New DataSet
[Code].....
I 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]...
I'm connecting to a MS Access DB using ADO.NET.
edit: i'm using VS 2010 I want to perform an SQL query based on certain criteria using the following string:
SQL = "Select * from tblTransactions " & _
" WHERE holding_PM_code='" & cbPMCode.Text & "'" & _
" AND transaction_status='Active'" & _
[Code]...
Should I pass a parameter in the query or shouldn't this suffice? edit2: i've checked the SQL and it does show the correct date format to search for (ie, dd/MM/yyyy)... but when I hover the mouse cursor over over 'dtiAsAtDate.Value' it comes up as '#mm/dd/yyyy#'.