|
I'm having trouble with this query, use to work in SQL Server 2008, but in 2012, I'm getting rows returned from various dates
So I'm just trying to get all the orders from Dec 3, 2013, I'm passing integer values into the function
12
3
2013
and using parameters Day, Month, Year
My Original SQL
"SELECT * From CompletedOrders " & _
"UNION " & _
"SELECT * FROM CompletedOrdersHistory " & _
"WHERE Day(OrderDate)=<a href="/Members/theday">@TheDay</a> " & _
"AND Month(OrderDate)=@TheMonth " & _
"AND Year(OrderDate)=<a href="/Members/theyear">@TheYear</a>"
Not really sure how to write this or what to search on. I have spent about an hour on this searching the internet.
[update]
I got this to work, but I have to use the greater than, so now I have to back date the date, which will get me trouble at the start of the month, guess I'll try looking up removing a day in SQL
DECLARE @TheDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 2;
set <a href="/Members/Year">@year</a> = 2013;
set @TheDate = DateFromParts(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>);
SELECT * From CompletedOrders
WHERE OrderDate > @TheDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @TheDate;
Well this is what I ended up with. It works, not sure of efficient it is
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 3;
set <a href="/Members/Year">@year</a> = 2013;
set @startDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 0, 0, 0, 0);
set @stopDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 23, 59, 59, 999);
SELECT * From CompletedOrders
WHERE OrderDate > @startDate AND OrderDate < @stopDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @startDate AND OrderDate < @stopDate;
modified 4-Dec-13 22:19pm.
|
|
|
|
|
Hi, i am a fresher.i am trying to insert values using front end,i am getting error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
During table creation for Date i used datetime datatype.
in front end design i used code like this.
Insert into Invoice(date) values('" + _Date + "');
i am using the DataGrid to Display the table in same page,
i tried to give the date in both the format 1/2/2013 and 2013/01/02 but the error.
plz help me to solve this Problem. plz provide the simple code to datetime conversion.
|
|
|
|
|
Member 10437599 wrote: '" + _Date + "'
THAT is a string. Cast it as datetime. Like this ... CAST('"+_Date+"' AS [datetime])
|
|
|
|
|
You are doing a number of things wrong. You should use a datepicker instead of a textbox, this will help you control the format of your data, ALWAYS use the DATETIME data type, NEVER use varchar/string for datetime data.
Learn to use parameterised queries[^] they will make you life a lot simpler and eliminate the data type issue (oh and make your code more secure).
Instead of editing into a datagrid I recommend you use a detail control or a dialog to do your data entry, this give you much greater control easier to manage.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a project with MySQL server database. unfortunately, MySQL doesn't provide a feature to password protect my database (unlike Microsoft SQL Server).
So what's your advise?
Shall I install my application on a separate MySQL instance so I will only have the password?
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
but this will protect the server!
What I want is to give my database a password so their administrator who'll access the same server won't be able to access my entire database even if he has a root for the same server because my database is password protected.
This option is available in Microsoft SQL Server and it's working great
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: This option is available in Microsoft SQL Server and it's working great
Are you sure about that? Any administrator of the domain / local machine can easily give themselves unrestricted access[^] to a SQL instance, even if they're not set up as a user in SQL.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Jassim Rahma wrote: So what's your advise?
Webservices, and hosting the server yourself. Easy argumentation; you'll only be the database-admin if the server is yours.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Update: This did turn out to be a pretty simple issue, so I am marking it now as an Answer. See the end of the message for the Answer...
I suspect I am making a very simple error, because I have a Windows Form application where I have added an Entity Framework DataSource bound to controls via a BindingSource/BindingNavigator that is not saving changes to the underlying SQL database.
I started by dragging the Entity Framework DataSource (as Details) onto a form.
This automatically created the form controls, the BindingSource and BindingNavigator.
When I first brought up the form, no records where being returned from the Entity Framework DataSource, so I created the following variable to initially load data that I was then able to view:
Private TblList As ObjectQuery(Of tblMyTableRec) = From tbl In ObjCntxt.tblMyTableRecs
Then I set the DataSource property of the appropriate BindingSource to this ObjectQuery:
MyBindingSource.DataSource = TblList
So far, so good ... The BindingNavigator now lets me move through the various records in the 'tblMyTableRecs' table.
But, when I make a modification of the data in any of the records (via one of the data bound controls), my changes are not being saved to the underlying SQL database the Entity Framework is setup to use.
I would greatly appreciate any suggestions about what steps I might need to take so that I am able to have changes saved in my SQL database.
Answer: The code generated for the Windows Form BindingSource and BindingNavigator do not automatically perform the Entity Framework ObjectContext SaveChanges operation. Once the SaveChanges function was executed in the Save button Event logic for the appropriate Entity Framework ObjectContext, the changes were saved to the SQL database.
Thanks, Dean
modified 5-Dec-13 21:18pm.
|
|
|
|
|
Hi friends. Can you tell me a perfect and simple website or book to know about the basic concept of OOPS in VB.net. Dont suggest simply google it. Specify some link or e-book.
|
|
|
|
|
You do know you posted in the Database forum?
|
|
|
|
|
I do know.But I believed that there will be someone to help me.
|
|
|
|
|
You don't think you would be more likely to receive relevant advice from the VB forum?
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
|
|
hi,
am selecting data from table . and executed reader with rdr=cmd.executereader()
based on the result i need to write the logic.
if( returns null)
{
}
else
{
}
|
|
|
|
|
|
here is the code
string query1 = "SELECT tag_id,category_id,product_id,product_name,gate_id,remarks FROM transaction where tag_id Like '" + sEPC + "' AND out_time=NULL";
MySqlCommand cmd1 = new MySqlCommand(query1, connection);
MySqlDataReader rdr1 = cmd1.ExecuteReader();
if (rdr1.HasRows==true)
{
item.SubItems[OUTTime.Index].Text = s2;
rdr1.Close();
String s1 = "update transaction set out_time='" + s2 + "' where tag_id='" + sEPC + "' ";
MySqlCommand cmd2 = new MySqlCommand(s1, connection);
cmd2.ExecuteNonQuery();
}
else
{
rdr1.Close();
item.SubItems[INTime.Index].Text = s2;
String a0 = ListView1_EPC.Items[0].Text;
String a1 = item.SubItems[1].Text;
String a2 = item.SubItems[2].Text;
String a3 = item.SubItems[3].Text;
String a4 = item.SubItems[4].Text;
String a5 = item.SubItems[5].Text;
String str = "insert into transaction(tag_id,category_id,product_id,product_name,gate_id,in_time,remarks)values('" + a0+ "','" + a1 + "','" + a2 + "','" + a3 + "','" + a4 + "','" +s2 + "' ,'"+a5+"')";
MySqlCommand cmd2 = new MySqlCommand(str, connection);
cmd2.ExecuteNonQuery();
}
|
|
|
|
|
Define not working properly?
also to read from the reader you have to do this
if(rdr1.HasRows == true)
{
while (rdr1.Read())
{
int id = Convert.ToInt(rdr1["ID"]);
}
}
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
my problem is:
1.select the data from transaction where tagid=? and outtime=null
2. if reader is having rows then write outtime into transaction table.
else
insert row into transaction table with only intime, no outtime
here is the code :
string query1 = "SELECT tag_id,category_id,product_id,product_name,gate_id,remarks FROM transaction where tag_id Like '" + sEPC + "' AND out_time=NULL";
MySqlCommand cmd1 = new MySqlCommand(query1, connection);
MySqlDataReader rdr1 = cmd1.ExecuteReader();
if (rdr1.HasRows==true)
{
item.SubItems[OUTTime.Index].Text = s2;
rdr1.Close();
String s1 = "update transaction set out_time='" + s2 + "' where tag_id='" + sEPC + "' ";
MySqlCommand cmd2 = new MySqlCommand(s1, connection);
cmd2.ExecuteNonQuery();
}
else
{
rdr1.Close();
item.SubItems[INTime.Index].Text = s2;
String a0 = ListView1_EPC.Items[0].Text;
String a1 = item.SubItems[1].Text;
String a2 = item.SubItems[2].Text;
String a3 = item.SubItems[3].Text;
String a4 = item.SubItems[4].Text;
String a5 = item.SubItems[5].Text;
String str = "insert into transaction(tag_id,category_id,product_id,product_name,gate_id,in_time,remarks)values('" + a0+ "','" + a1 + "','" + a2 + "','" + a3 + "','" + a4 + "','" +s2 + "' ,'"+a5+"')";
MySqlCommand cmd2 = new MySqlCommand(str, connection);
cmd2.ExecuteNonQuery();
}
}
here contains current date. everytime (even select command returns data ) it is executing else block only.is there any mistake in select query.
|
|
|
|
|
ok i got it. thanks for the help..
But now the problem is , i can have multiple records with same tag_id,
when i update all the records with same tag_id is updating.
i need to update only the latest record. how can i solve this.......
|
|
|
|
|
Can SUM() Function be used inside cursors??
I used it but the code goes on executing. more than 10 minutes.
Why??
modified 28-Nov-13 2:03am.
|
|
|
|
|
tsunamigang wrote: Can SUM() Function be used inside cursors??
Yes.
tsunamigang wrote: I used it but the code goes on executing. more than 10 minutes.
Why??
Show us yer code, then we may be able to help.
It sounds to me like you have not set up the condition on the cursor loop allowing the cursor to exit or you are not reading the next record from the cursor hence potentially leading to an endless loop.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 28-Nov-13 5:40am.
|
|
|
|
|
Exactly. I have not fetched next row at the bottom. Thanks for your reply.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
