|
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.
|
|
|
|
|
Cool 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Friends,
I have a few questions. The company I work for is using SQL server 2008 R2 with the Point Of Sale software for the retail operations. The company has 15 locations and we are growing fast. I am not new to SQL, but I have not had the opportunity to work with it like I do at this job. The main focus is reporting.
Here are my questions:
1) How do you get the information from a DB you will need to start to build queries, when you want to build a query from a proprietary database?
2) I am working on reporting through a GUI interface. My goal is to have the knowledge to build CUSTOM reports via queries. With CRE we have the option for RAW SQL Queries. What is the best way for a beginner to educate himself on SQL Server 2008 R2 SSRS (e.g. Books, Lab, etc.)?
3) On the same line as question 2, we would like to build a portal to interface with the 15 retail locations to the corporate office in order to obtain "Real Time" reporting. What is the best reference material for this project?
Thanks for your time!
Any help, will be greatly appreciated!
Mr. Khris Kaylor
|
|
|
|
|
This is not a set of questions you should rely on a forum to answer. You are going to need a professional in the area of setting up a distributed database environment with replication back to HO and probably setting up an OLAP reporting structure. It can be VERY expensive to get this wrong.
SSRS is excellent for canned reports but not the right tool for dynamic queries, for that you need to look at Business Intelligence (BI) solutions and visualisation tools.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<pre lang="sql">SELECT *
INTO OPENDATASOURCE('SQLNCLI',
'Data Source=.\sqlexpress;User ID=sa;Password=sa')
.[fuzzyTest].dbo.tblGroup
FROM [CMS].[Common].[File];</pre>
This is not working and giving error !
I dont wanna to use
<pre lang="sql">INSERT INTO [CMS].[Common].[File]
(Name,IdParent,[Desc])
SELECT [Name],[IdOstan]+30,''
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=.\sqlexpress;User ID=sa;Password=sa'
).[fuzzyTest].dbo.tblGroup</pre>
modified 27-Nov-13 1:02am.
|
|
|
|
|
jojoba2011 wrote: This is not working and giving error !
What error? Syntax error?
jojoba2011 wrote: I dont wanna to use
Why? What's wrong with that?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am using an ODBC to transfer the data from a view I made in postgresql into excell.
in my query I have 2 parameters that define the the date range but I was wondering if there is a way to set it up so that the ODBC can get these date parameters and look for the info pertaining to the dates I demand?
By which I mean, say my query got the data for 2013 because I have
and account_invoice.date_invoice >=
and account_invoice.date_invoice <=
as conditions, but I would like to be able to give the ODBC a range from january 1st to January 30th and get the data in that range.
|
|
|
|
|
Member 10427697 wrote: but I would like to be able to give the ODBC a range
ODBC is merely the driver; you'd need to change the query that's executed (SELECT * FROM VIEW?) to include a WHERE clause.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
my 2 conditions are part of the where clause
I have it like
where bla bla
and
and
and...
but if I do it for a month I will ahve to change the query everytime I want to get the data for the next month, I want to know if its possible to make it work automatically.
|
|
|
|
|
Member 10427697 wrote: I want to know if its possible to make it work automatically
Of course it is, think about it, you want the dates of the previous month calculated based on the current date. I'm absolutely certain PostGres has datetime functions similar to SQL Server.
Alternatively you can pass in the 2 date parameters from the calling method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Definitely not sure if I understood your question correctly, but have a look at the prepare[^] command.
Just remember to add UseServerSidePrepare=1 to the odbc.ini if you want to use the added performance.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
i have two tables (employee,attendance)
employee feilds:-
empno(pk),idno,,empname,compname,deptname all data type varchar(50)
attendance feilds:-
idno(pk),empno,intime,outtime,remarks all data type varchar(50)
my task is enter the value in textbox then check the data baseofter then display the remaining text box filling by using idno how to get the values in two tables
thank's
|
|
|
|
|
What do you enter in your textbox? You just need a sql statement to draw data from database?
|
|
|
|
|
Member 10263519 wrote: thank's
for what, you have stated your school work but not asked a question unless you do something and ask a question when you need help we can't do anything to help you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Member 10263519 wrote: all data type varchar(50)
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Everyone!
All of a sudden we are seeing the above error message, in one of our automated jobs. This job runs at 3am everyday.
This error occurs on random days, not on a regular basis.
There is not much out on this particular error.
The temp table is defined in a stored procedure.
Thanks!!!
|
|
|
|
|
Not enough information to diagnose.
|
|
|
|
|
vkEE wrote: All of a sudden we are seeing the above error message, in one of our automated jobs.
I've never encountered a PC that "suddenly" interprets code in a different way.
vkEE wrote: This error occurs on random days, not on a regular basis.
..nor does it pick random days to crash. Find out what those days have in common; check if there's exceptional heavy traffic on the network during those days. Check your firewall-log. Check the schedule of your backups, automatic updates and virusscans; are they similarly "random"?
Are you sure you got the correct type of temporary table? There's subtle differences between #tmpTable, ##tmpTable and @tmpTable.
vkEE wrote: The temp table is defined in a stored procedure.
Post the code of the stored procedure and you'll get more and better replies.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you for your response.
The job runs at 3am just for that reason, so we don't have resource issues. It has crashed 3 times the past few months, but our seniors want this matter researched since it is a new bug in their test environment. They don't want this bug to migrate to production environment.
I got this error message of the log file, and it is defined with single hash, #.
|
|
|
|
|
vkEE wrote: <layer>our seniors want this matter researched since it is a new bug in their test environment. They don't want this bug to migrate to production environment.
Unless you are in full control of your environment and able to debug every device-driver, some bugs will remain unsolved.
vkEE wrote: It has crashed 3 times the past few months
Do you know at what time? Anything in the event-log at that specific time? Does it run under .NET 4? If yes, you could consider making a minidump.
vkEE wrote: and it is defined with single hash, #.
I was hinting at researching the three. Whether it's the correct one will depend on the context of it's use in the stored procedure. You will have to verify the things mentioned, and again, you'd get a more usefull answer if you provide the code for the stored procedure.
There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server. ..and it becomes more confusing[^] if you are EXECUTEing dynamic SQL.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
vkEE wrote: The temp table is defined in a stored procedure.
You have an execution path that is skipping the creation of the table but then attempting to use it.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
