Solved

VB.net Count of records in SQL database not working

Posted on 2011-09-13
9
365 Views
Last Modified: 2012-05-12
Hi

I am using the code below to add a record to my Table1 and then
see if it is there by using a count of the number of records that
contain the three values I entered. For some resaon it always gets
a zero at the line after  '################
I have hidden my ConnectionString, which definitely works
Public Sub A()
        Dim oDate As String = "10/10/2011" 'Date.Now.ToString
        Dim oText As String = "test"
        Dim oNumber As Decimal = 10.5
        Dim sSQL As String
        sSQL = "Insert Into Table1 ([oDate],[oText], [oNumber]) Select '" & oDate & "' as Expr1, '" & oText & "' as Expr2, " & oNumber & " as Expr3"
        Call oAppend(sSQL)
        Dim oSQL_Check As String = "Select Count(*) As MyCount From Table1 Where [oDate] = '" & oDate & "' And [oText] = '" & oText & "' And [oNumber] = " & oNumber
        If IsThere(oSQL_Check) = False Then
            MsgBox("not")
        End If
    End Sub



    Sub oAppend(ByVal sSQL As String)

        Dim cn As New OleDbConnection(ConnectionString)

        Try

            '// define the sql statement to execute
            Dim cmd As New OleDbCommand(sSQL, cn)

            '    '// open the connection
            cn.Open()

            cmd.ExecuteNonQuery()


        Catch ex As Exception
            MsgBox(ex.Message)

        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try


    End Sub

    Function IsThere(ByVal sSQL_Check As String) As Boolean

        Dim conn As New OleDbConnection(ConnectionString)
        Try

            '// define the sql statement to execute
            Dim cmd As New OleDbCommand(sSQL_Check, conn)

            '    '// open the connection
            conn.Open()
            '################
            Dim oCount As Integer = CInt(cmd.ExecuteScalar())

            If oCount > 0 Then
                IsThere = True
            Else
                IsThere = False
            End If

        Catch ex As Exception
            MsgBox(ex.Message)

        Finally
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
    End Function

Open in new window

0
Comment
Question by:murbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 17

Assisted Solution

by:Shanmuga Sundaram
Shanmuga Sundaram earned 166 total points
ID: 36528760
did you manually check what this returns in Enterprise maanger

"Select Count(*) As MyCount From Table1 Where [oDate] = '" & oDate & "' And [oText] = '" & oText & "' And [oNumber] = " & oNumber
0
 

Author Comment

by:murbro
ID: 36528803
I'm not sure what you mean. I looked at the table data and was there
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 167 total points
ID: 36528865
Call oAppend(sSQL)
        Dim oSQL_Check As String = "Select Count(*) As MyCount From Table1 Where [oDate] = '" & oDate & "' And [oText] = '" & oText & "' And [oNumber] = " & oNumber
MsgBox(oSQL_Check)
        If IsThere(oSQL_Check) = False Then
            MsgBox("not")
        End If


see the query in MsgBox(oSQL_Check)
and run directly on dtabase ? what it results ?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36529006
Would you be open to doing this a better way?
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36529080
I asked whether it is possible for you to get that query during runtime (in debug mode) and execute it in sql enterprise manager and ensure that the query is correct.
0
 
LVL 2

Expert Comment

by:junkymail1
ID: 36529339
I attached some code that I use for vb.net to access the db stuff you need.  If you use the SqlDataReader class to get your data, you can find what you are looking for.
Dim SQLConn As New SqlConnection() 'The SQL Connection
        Dim SQLCmd As New SqlCommand() 'The SQL Command
        Dim reader As SqlDataReader

        Dim RegistryClass As Registry_Class = New Registry_Class()
        Dim ConnString As String = RegistryClass.GetSQLConnectString()

        Try
            SQLConn.ConnectionString = ConnString 'Set the Connection String
            SQLConn.Open() 'Open the connection
            SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command

            Dim SQLStr As String = "SELECT * FROM dbo.Table_ID"

            SQLCmd.CommandText = SQLStr 'Sets the SQL String
            reader = SQLCmd.ExecuteReader()

            'Get what you need here
            Dim FieldCount As Integer = reader.FieldCount ' Gets the number of columns in the current row.
            Dim HasRows As Boolean = reader.HasRows ' Gets a value that indicates whether the SqlDataReader contains one or more rows.
            Dim RecordsAffected As Integer = reader.RecordsAffected ' Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.
            Dim VisibleFieldCount As Integer = reader.VisibleFieldCount ' Gets the number of fields in the SqlDataReader that are not hidden.

            ' You can iterate through the results here.
            While reader.Read()
                Dim listitem As ListViewItem = ListView1.Items.Add(reader(0))
                listitem.SubItems.Add(reader("Product_Name"))
            End While
        Catch ex As Exception
            MsgBox("RecipeSelector_Load() Error:" + ex.Message)
        End Try

        SQLConn.Close() 'Close the connection
        SQLConn.Dispose()
        SQLConn = Nothing
        SQLCmd.Dispose()
        SQLCmd = Nothing

Open in new window

0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 167 total points
ID: 36529397
You are handling oDate as text. There is probably a difference in the format sent by your application and the one expected by the server.

Date.Now.ToString won't give the same format on all computers, because it it dependant on the Control Panel. Your server or database on its side is expecting a date format that migth be different. In SQL Server, it is month-day-year by default, but this could have been changed by the DBA.

Try never to manipulate a date as String in your code. Date.ToString should be reserved for display.

There are many ways to deal with dates, here is a common one that formats the date as month-day-year:
String.Format("{0:MM-dd-yyyy}", Date.Today)

Open in new window

Simply adjust the format to what is expected by your server.

And note that I used Date.Today instead of Date.Now. Your code and comments seem to imply that you are calling Now but treat only the date. Now is a very bad thing then, because it also sets the time. This can cause problems later in searchs and date manipulations if you do not care about the time. Reserve Now for when you really need the time.
0
 

Author Closing Comment

by:murbro
ID: 36529633
thank you for the help
0
 

Author Comment

by:murbro
ID: 36534397
Hi acperkins. Yes I would like to find a better way. I have posted a further question relating to this at
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27307018.html
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question