• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

How do I capture the RowCnt in my sql statement from the ExecuteNonQuery function?

Hello
Does anyone know how to check a recordset for duplicate rows using the EXECUTENONQUERY function?  I need to know the numbers of record the sql statement produces

See code below.

Declared as...............
    Private Const cSQL_DuplicateRowCheck As String = _
        "SELECT DB_NAME, COUNT (DB_NAME) AS ROWCNT " & _
        "FROM CPR_DB_ENVIRONMENTS " & _
        cWHERE & " " & _
        "GROUP BY DB_NAME " & _
        "HAVING COUNT(DB_NAME) > 1"

Code in app.................
sWHERE = " DB_NAME Like '%" & value & "%'"
cSQL_DupRowCk = Replace(cSQL_DuplicateRowCheck, cWHERE, cWHERE & sWHERE)
DT.SQLString = cSQL_DupRowCk
sErrMsg = DT.RunCommandSQL()


Function the sql is passed to....................
    Function RunCommandSQL() As String
        Try
            CMDExec.CommandType = CommandType.Text
            CMDExec.CommandText = _SQL
            iRetn = CMDExec.ExecuteNonQuery()
            If iRetn > 0 Then
                CMDExec.CommandText = "COMMIT"
                iRetn = CMDExec.ExecuteNonQuery()
            End If
            Return "" 
        Catch EXC As OracleClient.OracleException   'Exception
            Return Str(EXC.ErrorCode) & " - " & EXC.Message
        End Try
    End Function
Thanks
Kevin
0
kevin33809
Asked:
kevin33809
  • 11
  • 9
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Use ExecuteScalar instead.

Checking for duplicates is a different question.
0
 
slightwv (䄆 Netminder) Commented:
you will need to change the select to use ExecuteScalar.  That only returns the first column.  Just return the count.

 "SELECT COUNT (DB_NAME) AS ROWCNT " & _
...
0
 
slightwv (䄆 Netminder) Commented:
If you need both values create a reader and ExecuteReader.

Then it is a simple: reader.SetString(0) for the database name and reader.GetDecimal(1) for the count.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
kevin33809Author Commented:
Could you show me an example code.  I need to count for any unique record that is more than one.  Do I cut down the sql by taking off the Group By and Having clauses?
0
 
kevin33809Author Commented:
Is the code something like this code -- in C++

 string query="SELECT TP_PIN FROM Demographics_tbl WHERE TP_PIN='"+UserPIN.Trim()+"'";
  SqlCommand myCommand = new SqlCommand(query, myConnection);
  SqlDataReader Read1 = null;
  Read1 = myCommand.ExecuteReader();
  myCommand.CommandText = query;
  myCommand.Connection = myConnection;
  Read1.Read();
 
 if (Read1!=null)
  {
  if (Read1.GetString(0) == UserPIN)
  {
 
0
 
slightwv (䄆 Netminder) Commented:
Yes.

What dataprovider are you using to access Oracle?  There  are a lot of VB.Net n the datareader but the exact usage varies a little by provider.

If you are using ODP.Net, I can provide a working example.
0
 
kevin33809Author Commented:
Private Const cConnProvider As String = "oraoledb.oracle"
0
 
kevin33809Author Commented:
Seems to me like it a regular ADO db connection.

    Private Shared InstanceBaseCn As New ADODB.Connection
   Private Shared InstanceORABaseCn As New OracleClient.OracleConnection
0
 
slightwv (䄆 Netminder) Commented:
Ever thought about migrating to ODP.Net?

I'm not sure how the oledb driver handles it.

Below is a code snippet I have in some of my ODP.Net code.  Hopefully it will run in the oleDB code.
reader = cmd.ExecuteReader()
do while reader.read()
	retStr = retstr & reader.GetString(1) & " " & reader.GetString(2)  & " " & reader.GetString(3) & " " & reader.GetString(4) & "<br/>"
loop
reader.close()
reader.dispose()

Open in new window

0
 
kevin33809Author Commented:
I wrote this code that begets the value of 2 for iRetn, but what is E-Scalar saying?  Its counting all the records in the table, or its counting every instance where it finds a record that has a duplicate?

            Dim iRetnCnt As Integer
            CMDExec.CommandType = CommandType.Text
            CMDExec.CommandText = _SQL
            iRetnCnt = CMDExec.ExecuteScalar()

            If iRetn > 0 Then
                        MessageBox.Show("Duplicate record!", "MESSAGE", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
               Return True
            Else
                Return False
            End If
0
 
kevin33809Author Commented:
As far as ExecuteReader is concerned.  It looks great, but I have to learn to set it up first before I use it.  I'm working on it.
0
 
slightwv (䄆 Netminder) Commented:
I don't think you can use executescalar once I re-read the question.

executescalar returns the first value of the first row.  That's it.

It's perfect for things like a simple COUNT or simple select.

Since you are possible returning more than one row with your query, you need a reader or dataset so you can loop through the results.

Sorry for confusing you with scalar.
0
 
kevin33809Author Commented:
I think I'm going to have to learn how to setup an ExecuteReader in Visual Basic, slightwv.  If you have any setup info/code let me know.  If there is someone else who can help me setup an ExecuteReader, I would appreciate you help.  This is just another learning experience.
0
 
slightwv (䄆 Netminder) Commented:
Straight VB or VB.Net?
0
 
kevin33809Author Commented:
VB6 at this point.
0
 
slightwv (䄆 Netminder) Commented:
Sorry can't help with that.

I'll add a few more zones to get more Experts involved.
0
 
kevin33809Author Commented:
Here's what I've done so far.  I finally was able to setup an Oracle Reader in the declaration area in VB.  See code below.
 Dim iOraReader As OracleClient.OracleDataReader

I then made this subroutine below that passes the sql.  ;
 Function RunDupCkCommandSQL() As String
        Try
            Dim iRetnCnt As Integer = 0

            CMDExec.CommandType = CommandType.Text
            CMDExec.CommandText = _SQL
            iOraReader = CMDExec.ExecuteReader()

            Do While iOraReader.Read()
     ERRORS HERE -->  Console.WriteLine(iOraReader.GetString(0))    'JUST SHOW ME WHAT I GOT IN THE CELL.
                  Console.WriteLine(iOraReader.GetString(1))
                  Console.WriteLine(iOraReader.GetString(2))
                  Console.WriteLine(iOraReader.GetString(3))
            Loop

            iOraReader.Close()
            iOraReader.Dispose()

        Catch EXC As OracleClient.OracleException   'Exception
            Return Str(EXC.ErrorCode) & " - " & EXC.Message
        End Try
    End Function

I get the following messages:
--A first chance exception of type 'System.InvalidCastException' occurred in System.Data.OracleClient.dll

--A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.OracleClient.dll

--In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

At this point, I'm not sure where to go with this code to fix it.  Any help out there?
0
 
slightwv (䄆 Netminder) Commented:
Sorry.  I was off on Friday.

The original select statement you provided only has two columns.  The latest code you posted calls getString three times.

Try removing the third call.
0
 
kevin33809Author Commented:
I tried it and it worked fine.  Thanks. Slightwv.
0
 
kevin33809Author Commented:
It worked
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now