Member Avatar for ctyokley

Ok so i have a Function that connects to a database and runs the executeschaler() command. the executeschaler is attached to an object. When running the command it will search the database for ceartin information and return certain values, however it returns an error when i use the Try End Try (catch ex as exception).

The code for the function is:

Function SQLCHECKUSER() As Object
        Dim query1 As String = "SELECT [User_Name] FROM [FDXUSER] WHERE ([User_Name] ='" & User_Name.Text & "')"
        Dim query2 As String = "SELECT [employee_number] FROM [FDXUSER] WHERE ([employee_number] ='" & empnumber.Text & "')"
        Dim query3 As String = "SELECT [email] FROM [FDXUSER] WHERE ([email] ='" & email_address.Text & "')"
        Dim sqlcommand As New OleDbCommand(query1, Sqlconnection)
        Dim sqlcommand2 As New OleDbCommand(query2, Sqlconnection)
        Dim sqlcommand3 As New OleDbCommand(query3, Sqlconnection)
        Dim obj As Object
        Dim obj2 As Object
        Dim obj3 As Object

        obj = sqlcommand.ExecuteScalar()
        obj2 = sqlcommand2.ExecuteScalar()
        obj3 = sqlcommand3.ExecuteScalar()
       
        If obj Or obj2 Or obj3 Is Nothing Then
            Return errmessage = "SUCCESS"
        Else
            If obj Is Nothing Then
                Return errmessage = "INSERT"
            Else
                Return errmessage = "USER"
            End If
            If obj2 Is Nothing Then
                Return errmessage = "INSERT"
            Else
                Return errmessage = "EMPLOYEE"
            End If
            If obj3 Is Nothing Then
                Return errmessage = "INSERT"
            Else
                Return errmessage = "EMAIL"
            End If

        End If

    End FunctionFunction SQLCHECKUSER() As Object
        Dim query1 As String = "SELECT [User_Name] FROM [FDXUSER] WHERE ([User_Name] ='" & User_Name.Text & "')"
        Dim query2 As String = "SELECT [employee_number] FROM [FDXUSER] WHERE ([employee_number] ='" & empnumber.Text & "')"
        Dim query3 As String = "SELECT [email] FROM [FDXUSER] WHERE ([email] ='" & email_address.Text & "')"
        Dim sqlcommand As New OleDbCommand(query1, Sqlconnection)
        Dim sqlcommand2 As New OleDbCommand(query2, Sqlconnection)
        Dim sqlcommand3 As New OleDbCommand(query3, Sqlconnection)
        Dim obj As Object
        Dim obj2 As Object
        Dim obj3 As Object

        obj = sqlcommand.ExecuteScalar()
        obj2 = sqlcommand2.ExecuteScalar()
        obj3 = sqlcommand3.ExecuteScalar()
       
        If obj Or obj2 Or obj3 Is Nothing Then
            Return errmessage = "SUCCESS"
        Else
            If obj Is Nothing Then
                Return errmessage = "INSERT"
            Else
                Return errmessage = "USER"
            End If
            If obj2 Is Nothing Then
                Return errmessage = "INSERT"
            Else
                Return errmessage = "EMPLOYEE"
            End If
            If obj3 Is Nothing Then
                Return errmessage = "INSERT"
            Else
                Return errmessage = "EMAIL"
            End If

        End If

    End Function

The error i get is: "Conversion from string "ctyokley" to type 'Long' is not valid. "

ctyokley is representing the usernam. So i tried the textbox.text.tostring and that didnt work. Any suggestions?

  • 2 Contributors
  • 2 Replies
  • 101 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by sknake

Recommended Answers

All 2 Replies

Member Avatar for ctyokley

Hmm no answers or know why it is doing this?

Member Avatar for sknake

Change your queries over to using parameters so you can tell what the problem is:

Imports System
Imports System.Data
Imports System.Data.SqlClient

public class MainClass
   Shared Sub Main()
      Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")

      'Create Command object
      Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()

      Try
         ' Open Connection
         thisConnection.Open()
         Console.WriteLine("Connection Opened")


         ' Create INSERT statement with named parameters
         nonqueryCommand.CommandText = _
            "INSERT INTO Employee VALUES (@MyID, @MyFirstName, @MyLastName?"

         ' Add Parameters to Command Parameters collection
         nonqueryCommand.Parameters.Add("@MyFirstName", SqlDbType.VarChar, 30)
         nonqueryCommand.Parameters.Add("@MyLastName", SqlDbType.VarChar, 30)
         nonqueryCommand.Parameters.Add("@MyID", SqlDbType.Int)

         ' Prepare command for repeated execution
         nonqueryCommand.Prepare()

         ' Data to be inserted
         Dim names() As String = {"Z", "S", "J", "D"}
         For i As Integer = 0 To 3
            nonqueryCommand.Parameters("@MyFirstName").Value = names(i)
            nonqueryCommand.Parameters("@MyLastName").Value = names(i)

            nonqueryCommand.Parameters("@MyID").Value = i+10
            Console.WriteLine("Executing {0}", _
               nonqueryCommand.CommandText)
            Console.WriteLine("Number of rows affected : {0}", _
               nonqueryCommand.ExecuteNonQuery())
         Next i
      Catch ex As SqlException
         ' Display error
         Console.WriteLine("Error: " & ex.ToString())
      Finally
         ' Close Connection
         thisConnection.Close()
         Console.WriteLine("Connection Closed")

      End Try
   End Sub
End Class

Taken from:
http://www.java2s.com/Code/VB/Database-ADO.net/UseParametersinyoursqlcommand.htm

This is one of many reasons you shouldn't build queries dynamically.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.