Help with Count function in Access - Access

[wertqt]

Hi,
i used the COUNT function to return the number of rows that satisfy the conditions set. This method works but when i press the load button again, it will still gives me another same set of information. This loop never ends.

C# (VS 2005) Code :

string Cnt = "SELECT Count(1) FROM Module_Repair_Info WHERE StopDate = " + Date + " AND StopTime = " + Time + " AND SerialNo = '" + strSerialNumber + "'";
 
                                        if (Cnt != "0")
                                        {
 
                                            string insertString = "INSERT INTO Module_Repair_Info (" +
                                                                  "FailureNo, XmtrType, SerialNo, [Hardware Rev], [Software Rev], [Firmware Rev], StopDate, StopTime, Stn, Chn " +
                                                                  ") VALUES (" +
                                                                  "' ', '" + Model + "', '" + ModuleSerialNumber + "', '" + Hardware + "', '" + Software + "', '" + Firmware + "', '" + Date + "', '" + Time + "', '" + StationNumber + "', " + ChannelNumber +
                                                                  ")";
 
                                            OleDbCommand myCommand = new OleDbCommand(insertString, myConn2);
                                            myCommand.ExecuteNonQuery();
 
 
                                        }

I was wondering whats wrong with this code that i had written. Pls help as i spent 2 days on this problem.

Thanks in advance!

[kenobewan]

I believe that Cnt is never going to be "0". Have you displayed the value to see what is going on? Have you tried using count(*) if you want to count the number of records?

[wertqt]

hi there,
i tried using Count(*), it still gives me the same string result : "SELECT Count(*) FROM Module_Repair_Info WHERE StopDate = 01-09-2007 AND StopTime = 11:37:52 AND SerialNo = '1746060179'."

I believed that Cnt will not be "0" too but how can i get/return the number of records in this case? I wanted the result to be an integer since it is the number of rows that satisfy the conditions set.

Thanks!

[kenobewan]

If you want count as an integer, try storing it as an integer and testing that in your condition. Please try this and report back.

[wertqt]

i dont think im able to store it as int if i use SELECT statement. I need to use SELECT statement as there are conditions/criterias. That is y i have to use string Cnt = "SELECT .....";
Or is there another way to store it as int?

[kenobewan]

What happens if you use int Cnt = "Select...";? Here is an article that may reflect your concern about using int rather than string, but as long as strongly type and don't declare as object you should be ok:
A Lesson in ASP.NET/SQL Security

Good points about web security too. You may also want to use try/catch blocks.

[wertqt]

hi,
i solved the problem. i forgot to execute the SELECT statement and that is y i couldnt get the int value.

Thanks!

[kenobewan]

Well done :)!