passing paramter value to sql statement 1

[josie2007]

I am passing the paramter value to my sql statement and below is the code I used to pass the value to sql statement. p.contid is a character field.Oracle is the db. thanks

...
.Append(" AND P.contid = :ContractNumber ")
...


Dim cmdItemDetail As OracleCommand = New OracleCommand()
cmdItemDetail.Parameters.AddWithValue(":ContractNumber", "'"& ContractId & "'")

 

[jbenson001]

Is there a question somewhere in there??

 

[josie2007]

Sorry guys,
For some reason the value is not passing to the sql statement.It just return nothing but when I hard coded the value like this Append(" AND P.contid = '070002' then I get the right outcome and I am just wondering where I made a mistake. thanks

 

[jmeckley]

try placing the quotes to the sql string instead of incorporating them as part of the parameter value.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[GoTerps88]

I create this code snippet and it seems to work. I am using the Oracle Data Provider for .Net, not the OracleClient. But it should work similarly.

string cmd = "SELECT * FROM hr.Employees WHERE Employee_ID = :EmployeeID";

//open connection
objConn.Open();
//build command object
OracleCommand objCmd = new OracleCommand(cmd,objConn);
objCmd.CommandType = CommandType.Text;
objCmd.Parameters.Add(":EmployeeID",txtEmpID.Text);

OracleDataReader objRdr = objCmd.ExecuteReader();

while(objRdr.Read())
{
   Response.Write(objRdr.GetValue(1).ToString();
}
}

 

[josie2007]

Folks, thanks for the reply. I did like what Jason suggested but that did not solve the problem. when I hard coded like this P.contid ='070002' it works fine. It looks a bit stranget to me. any help is appreciated.

.Append(" AND P.contid = ""'"" & :ContractNumber & ""'"" ")

 

[jmeckley]

try this instead. you may need to supply the data type.

cmdItemDetail.Parameters.Add("ContractNumber", OracleDbType.[my type here]).Value = ContractId;

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[josie2007]

That did it for me.Thanks Jason!