[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 770
  • Last Modified:

variable in SQL string

Greetings,

I am trying to use variable in SQL string to select data from Oracle database. But got error "invalid column name". My partial code(in VB.Net) is the following. Thanks in advance!

Private Const sqlText = "Select item,trans_num,trans from tbl1 where trans ={0}"

 Dim MyOracleCMD As New System.Data.OracleClient.OracleCommand(String.Format(sqlText, "12345"))
0
mrong
Asked:
mrong
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://msdn2.microsoft.com/en-US/library/system.data.oracleclient.oraclecommand.parameters(VS.80).aspx

Private Const sqlText = "Select item,trans_num,trans from tbl1 where trans = :trans"
 Dim MyOracleCMD As New System.Data.OracleClient.OracleCommand(sqlText)
 command.Parameters.Add("trans")
 command.Parameters(0).Value = 12345

 
0
 
YZlatCommented:
try

Private Const sqlText = String.Format("Select item,trans_num,trans from tbl1 where trans ={0}", myVariable)

or

Private Const sqlText = "Select item,trans_num,trans from tbl1 where trans ='" & myVariable & "'"
0
 
mrongAuthor Commented:
angelIII,

I tried your suggestions, but got the following error.

Exception Details: System.InvalidCastException: The OracleParameterCollection only accepts non-null OracleParameter type objects, not String objects.

Source Error:


Line 24:
Line 25:         Dim MyOracleCMD As New System.Data.OracleClient.OracleCommand(sqlText)
Line 26:         MyOracleCMD.Parameters.Add("trans") <- error here
Line 27:         MyOracleCMD.Parameters(0).Value = "D639533"
Line 28:         MyOracleCMD.Connection = MyOracleConn
 
0
 
mrongAuthor Commented:
YZlat ,

I tried your code and got error "constant expression is required"
Thanks.
0
 
YZlatCommented:
get rid of the keyword "const":

Private sqlText = String.Format("Select item,trans_num,trans from tbl1 where trans ={0}", myVariable)

or

Private sqlText = "Select item,trans_num,trans from tbl1 where trans ='" & myVariable & "'"
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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