Database Discussion Boards

Re: Reference

Corporal Agarn29-Nov-13 2:06

Corporal Agarn

29-Nov-13 2:06

You do know you posted in the Database forum?

Re: Reference

tsunamigang29-Nov-13 2:12

tsunamigang

29-Nov-13 2:12

I do know.But I believed that there will be someone to help me.

Re: Reference

Chris Quinn29-Nov-13 2:41

Chris Quinn

29-Nov-13 2:41

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.
=========================================================

Re: Reference

Richard MacCutchan29-Nov-13 6:15

Richard MacCutchan

29-Nov-13 6:15

What do you mean by OOPS? Do you rather mean Object Oriented Programming? And why are you not willing to try a bit of Google searching for yourself? Especially as your question has nothing to do with Database. I would suggest reading http://www.codeproject.com/Messages/1278600/How-to-get-an-answer-to-your-question.aspx[^] before you do your Google searches.

Veni, vidi, abiit domum

Re: Reference

RedDk29-Nov-13 8:43

RedDk

29-Nov-13 8:43

http://code.msdn.microsoft.com/vstudio/site/search?f%5B0%5D.Type=VisualStudioVersion&f%5B0%5D.Value=10.0&f%5B0%5D.Text=Visual%20Studio%202010&f%5B1%5D.Type=ProgrammingLanguage&f%5B1%5D.Value=VB&f%5B1%5D.Text=VB.NET&f%5B2%5D.Type=Platform&f%5B2%5D.Value=Data&f%5B2%5D.Text=Data[^]

how to find the return value of reader in mysql

Member 1026351928-Nov-13 1:20

Member 10263519

28-Nov-13 1:20

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
{
}

Re: how to find the return value of reader in mysql

Simon_Whale28-Nov-13 1:32

Simon_Whale

28-Nov-13 1:32

simple

if(rdr.HasRows == true)
{
  //do stuff
}
else
{
  //do no rows returned stuff
}

here is an example for you.

http://stackoverflow.com/questions/12609959/how-to-check-if-sqldatareader-has-no-rows[^]

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: how to find the return value of reader in mysql

Member 1026351928-Nov-13 1:51

Member 10263519

28-Nov-13 1:51

eventhough it's not working properly.
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();
                                 //int c = rdr1.RecordsAffected;
                                 // MessageBox.Show("count" + c);
                                  //if (rdr1 !=null && rdr1.Read() && rdr1.HasRows)
                                  if (rdr1.HasRows==true)
                                  {
                                      //ListViewItem item1 = new ListViewItem();
                                      item.SubItems[OUTTime.Index].Text = s2;
                                      //item.SubItems[5].Text = s2;
                                      //ListView1_EPC.Items.Add(item);
                                      //ListView1_EPC.Items[0].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;
                                      // ListView1_EPC.Items.Add(item);
                                      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();

                                  }

Re: how to find the return value of reader in mysql

Simon_Whale28-Nov-13 1:57

Simon_Whale

28-Nov-13 1:57

Define not working properly?

also to read from the reader you have to do this

if(rdr1.HasRows == true)
{
 while (rdr1.Read())
 {
   //have made an assumption here that you have a ID field in the data
   //so that you can see how to get data back from the reader.
   int id = Convert.ToInt(rdr1["ID"]);
 }
}

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: how to find the return value of reader in mysql

Member 1026351928-Nov-13 17:52

Member 10263519

28-Nov-13 17:52

no i don't want to read the data of reader. datatype of out_time and in_time in mysql table is datetime.
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();

                                   }



                               }//if

here contains current date. everytime (even select command returns data ) it is executing else block only.is there any mistake in select query.

Re: how to find the return value of reader in mysql

Member 1026351928-Nov-13 19:11

Member 10263519

28-Nov-13 19:11

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.......

Cursors

tsunamigang27-Nov-13 18:40

tsunamigang

27-Nov-13 18:40

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.

Re: Cursors

GuyThiebaut27-Nov-13 21:41

GuyThiebaut

27-Nov-13 21:41

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.

Re: Cursors

tsunamigang29-Nov-13 2:41

tsunamigang

29-Nov-13 2:41

Exactly. I have not fetched next row at the bottom. Thanks for your reply.

Re: Cursors

GuyThiebaut29-Nov-13 2:43

GuyThiebaut

29-Nov-13 2:43

Cool

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Cash Register Express - PC America

Member 1042751427-Nov-13 1:43

Member 10427514

27-Nov-13 1:43

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

Re: Cash Register Express - PC America

Mycroft Holmes27-Nov-13 12:05

Mycroft Holmes

27-Nov-13 12:05

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

SELECT * INTO OPENDATASOURCE

jojoba201126-Nov-13 18:45

jojoba2011

26-Nov-13 18:45

XML

<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,&#39;&#39;
FROM         OPENDATASOURCE(
         &#39;SQLOLEDB&#39;,
         &#39;Data Source=.\sqlexpress;User ID=sa;Password=sa&#39;
                 ).[fuzzyTest].dbo.tblGroup</pre>

modified 27-Nov-13 1:02am.

Re: SELECT * INTO OPENDATASOURCE

Eddy Vluggen27-Nov-13 7:34

Eddy Vluggen

27-Nov-13 7:34

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Exporting PostgreSQL data to Excell

Member 1042769726-Nov-13 7:21

Member 10427697

26-Nov-13 7:21

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 >= '2013-01-01'
and account_invoice.date_invoice <= '2013-12-31'

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.

Re: Exporting PostgreSQL data to Excell

Eddy Vluggen26-Nov-13 7:41

Eddy Vluggen

26-Nov-13 7:41

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Exporting PostgreSQL data to Excell

Member 1042769726-Nov-13 7:59

Member 10427697

26-Nov-13 7:59

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.

Re: Exporting PostgreSQL data to Excell

Mycroft Holmes26-Nov-13 11:44

Mycroft Holmes

26-Nov-13 11:44

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

Re: Exporting PostgreSQL data to Excell

Jörgen Andersson26-Nov-13 8:53

Jörgen Andersson

26-Nov-13 8:53

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

get the data from two tables

Member 1026351925-Nov-13 20:44

Member 10263519

25-Nov-13 20:44

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.