Sql vs Oledb error

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • =?Utf-8?B?TWlrZVM=?=
    #1

    Sql vs Oledb error

    Below is the code for 2 separate functions. The first one gets data from an
    Access database and works fine. The second one is almost a duplicate except
    it gets data from an SQL database. It works fine if I insert the actual
    value for the criteria. In other words, if I insert "mscott" in the place of
    "txtData.Te xt", it will return the expected results. But if I use the
    criteria from my input field, I get the following error:

    "Invalid column name 'mscott'." (mscott = value from text box)

    Can anyone tell me how to correct this and why is this different in SQL?

    *************** *************** *************** *********
    Private Sub GetDataAccess()
    Using conn As New OleDb.OleDbConn ection _
    ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source= _
    C:\Data\Access\ ItemMaster.mdb" )
    Using com As OleDb.OleDbComm and = conn.CreateComm and()
    conn.Open()
    com.CommandType = Data.CommandTyp e.Text
    com.CommandText = "Select Dept From Security Where UserID =
    " & _
    txtData.Text
    com.Parameters. Add("UserID", OleDb.OleDbType .VarChar).Value
    = _
    txtData.Text
    Dim strDept As String = String.Empty

    Try
    strDept = com.ExecuteScal ar().ToString()
    Catch ex As NullReferenceEx ception
    MsgBox("UserID not found....")
    txtData.Text = String.Empty
    End Try
    lblData.Text = strDept
    conn.Close()
    End Using
    End Using
    End Sub

    *************** *************** *************** **********
    Private Sub GetDataSQL1()
    Using conn As New SqlClient.SqlCo nnection _
    ("Server=KIN-SSQL02;" & "Database=F TQ;" & "User ID=abc;" _
    & "Password=123;" )
    Using com As SqlClient.SqlCo mmand = conn.CreateComm and()
    conn.Open()
    com.CommandType = Data.CommandTyp e.Text
    com.CommandText = "Select Dept from Security Where UserID =
    " & _
    txtData.Text
    com.Parameters. Add("UserID", SqlDbType.VarCh ar).Value = _
    txtData.Text
    Dim strData As String = String.Empty

    Try
    strData = com.ExecuteScal ar().ToString()
    Catch ex As NullReferenceEx ception
    MsgBox("User ID not found....")
    txtData.Text = String.Empty
    End Try
    lblData.Text = strData
    conn.Close()
    End Using
    End Using

    End Sub

    *************** *************** *************** *******

    Thanks in advance,

    Mike

    Tags: None
    • rowe_newsgroups
      #2
      Re: Sql vs Oledb error

      Try changing it to this:

      //////////////////////////////////
      Private Sub GetDataSQL1()
      Using conn As New SqlClient.SqlCo nnection("Serve r=KIN-
      SSQL02;Database =FTQ;User ID=abc;Password =123;"), _
      com As SqlClient.SqlCo mmand = conn.CreateComm and()
      conn.Open()
      com.CommandType = Data.CommandTyp e.Text
      com.CommandText = "Select Dept from Security Where UserID =
      @UserId"
      com.Parameters. Add("@UserID", SqlDbType.VarCh ar).Value =
      txtData.Text
      Dim strData As String = String.Empty

      Try
      strData = com.ExecuteScal ar().ToString()
      Catch ex As NullReferenceEx ception
      MsgBox("User ID not found....")
      txtData.Text = String.Empty
      End Try
      lblData.Text = strData
      End Using
      End Sub
      ////////////////////////////


      Let me know how it works.

      Thanks,

      Seth Rowe






        Comment

        • =?Utf-8?B?TWlrZVM=?=
          #3
          Re: Sql vs Oledb error

          Thanks Seth....that works great.

          "rowe_newsgroup s" wrote:
          Try changing it to this:
          >
          //////////////////////////////////
          Private Sub GetDataSQL1()
          Using conn As New SqlClient.SqlCo nnection("Serve r=KIN-
          SSQL02;Database =FTQ;User ID=abc;Password =123;"), _
          com As SqlClient.SqlCo mmand = conn.CreateComm and()
          conn.Open()
          com.CommandType = Data.CommandTyp e.Text
          com.CommandText = "Select Dept from Security Where UserID =
          @UserId"
          com.Parameters. Add("@UserID", SqlDbType.VarCh ar).Value =
          txtData.Text
          Dim strData As String = String.Empty
          >
          Try
          strData = com.ExecuteScal ar().ToString()
          Catch ex As NullReferenceEx ception
          MsgBox("User ID not found....")
          txtData.Text = String.Empty
          End Try
          lblData.Text = strData
          End Using
          End Sub
          ////////////////////////////
          >
          >
          Let me know how it works.
          >
          Thanks,
          >
          Seth Rowe
          >
          >
          >
          >
          >
          >
          >

            Comment

            • zacks@construction-imaging.com
              #4
              Re: Sql vs Oledb error

              On Sep 20, 12:22 pm, MikeS <Mi...@discussi ons.microsoft.c omwrote:
              Below is the code for 2 separate functions. The first one gets data from an
              Access database and works fine. The second one is almost a duplicate except
              it gets data from an SQL database. It works fine if I insert the actual
              value for the criteria. In other words, if I insert "mscott" in the place of
              "txtData.Te xt", it will return the expected results. But if I use the
              criteria from my input field, I get the following error:
              >
              "Invalid column name 'mscott'." (mscott = value from text box)
              >
              Can anyone tell me how to correct this and why is this different in SQL?
              >
              *************** *************** *************** *********
              Private Sub GetDataAccess()
              Using conn As New OleDb.OleDbConn ection _
              ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source= _
              C:\Data\Access\ ItemMaster.mdb" )
              Using com As OleDb.OleDbComm and = conn.CreateComm and()
              conn.Open()
              com.CommandType = Data.CommandTyp e.Text
              com.CommandText = "Select Dept From Security Where UserID =
              " & _
              txtData.Text
              com.Parameters. Add("UserID", OleDb.OleDbType .VarChar).Value
              = _
              txtData.Text
              Dim strDept As String = String.Empty
              >
              Try
              strDept = com.ExecuteScal ar().ToString()
              Catch ex As NullReferenceEx ception
              MsgBox("UserID not found....")
              txtData.Text = String.Empty
              End Try
              lblData.Text = strDept
              conn.Close()
              End Using
              End Using
              End Sub
              >
              *************** *************** *************** **********
              Private Sub GetDataSQL1()
              Using conn As New SqlClient.SqlCo nnection _
              ("Server=KIN-SSQL02;" & "Database=F TQ;" & "User ID=abc;" _
              & "Password=123;" )
              Using com As SqlClient.SqlCo mmand = conn.CreateComm and()
              conn.Open()
              com.CommandType = Data.CommandTyp e.Text
              com.CommandText = "Select Dept from Security Where UserID =
              " & _
              txtData.Text
              com.Parameters. Add("UserID", SqlDbType.VarCh ar).Value = _
              txtData.Text
              Dim strData As String = String.Empty
              >
              Try
              strData = com.ExecuteScal ar().ToString()
              Catch ex As NullReferenceEx ception
              MsgBox("User ID not found....")
              txtData.Text = String.Empty
              End Try
              lblData.Text = strData
              conn.Close()
              End Using
              End Using
              >
              End Sub
              >
              *************** *************** *************** *******
              >
              Thanks in advance,
              >
              Mike
              If the column UserID is a varchar, then the value from txtData.Text
              must be wrapped in single quotes.

              The only difference between the way you were trying it and the way a
              suggested workaround works, is the workaround specifies the value as a
              parameter, where the "wrapping in single quotes" is not applicable.

                Comment

                Previous template Next
                Working...