Hi,
 please find my code below, this one neither giving the error nor updating the record.

Public Function UpdateQuoteData(ByVal ObjDs As DataSet, ByVal strTableName As String, ByVal intQuoteId As Integer)

Dim ObjDa As OracleDataAdapter

Dim strSQL As String

Dim strReturnText As String = ""

Dim objtmpDs As New DataSet

Dim connection As New OracleConnection(connStr)

Dim ObjCmd As OracleCommandBuilder

Try

strSQL = "SELECT * FROM " & strTableName & " WHERE QUOTE_ID = " & intQuoteId

ObjDa = New OracleDataAdapter(strSQL, _connection)

ObjDa.Fill(objtmpDs, strTableName)

ObjCmd = New OracleCommandBuilder(ObjDa)

ObjDa.InsertCommand = ObjCmd.GetUpdateCommand

objtmpDs = ObjDs

ObjDa.Update(objtmpDs, strTableName)

Catch ex As OracleException

strReturnText = ex.Message

Throw ex

Finally

If strReturnText = "" Then

strReturnText = "OK"

End If

End Try

ObjDa.Dispose()

Return strReturnText

End Function

can anyone help me where the problem hides

srinivasareddy wrote:

Hi,  please find my code below, this one neither giving the error nor updating the record. Public Function UpdateQuoteData(ByVal ObjDs As DataSet, ByVal strTableName As String, ByVal intQuoteId As Integer) Dim ObjDa As OracleDataAdapter Dim strSQL As String Dim strReturnText As String = "" Dim objtmpDs As New DataSet Dim connection As New OracleConnection(connStr) Dim ObjCmd As OracleCommandBuilder Try strSQL = "SELECT * FROM " & strTableName & " WHERE QUOTE_ID = " & intQuoteId ObjDa = New OracleDataAdapter(strSQL, _connection) ObjDa.Fill(objtmpDs, strTableName) ObjCmd = New OracleCommandBuilder(ObjDa) ObjDa.InsertCommand = ObjCmd.GetUpdateCommand objtmpDs = ObjDs ObjDa.Update(objtmpDs, strTableName) Catch ex As OracleException strReturnText = ex.Message Throw ex Finally If strReturnText = "" Then strReturnText = "OK" End If End Try ObjDa.Dispose() Return strReturnText End Function can anyone help me where the problem hides

Try changing your query to:

strSQL = "SELECT * FROM '" & strTableName & "' WHERE QUOTE_ID = " & intQuoteId

Strings and Dates in a query need to be enclosed in single-quotes.

SELECT * FROM 'users' WHERE clue = 'yes';    this is not  correct right..?  table name 'USERS' will not be like this. anyway thanks for help

 

Have you checked if after the line "ObjDa.Fill(objtmpDs, strTableName)" the objtmpDs is filled? Try writing "objtmpDs = new DataSet()" before that line of code.

srinivasareddy wrote:

SELECT * FROM 'users' WHERE clue = 'yes';    this is not  correct right..?  table name 'USERS' will not be like this. anyway thanks for help

Frankly, I don't understand why you would pass a table name into an SQL query as a variable.
If this is something you need to do then you should be using dynamic SQL, not static SQL. Dynamic SQL gives you flexibility of passing in different table and/or column names using bind variables.

DECLARE
l_var varchar2(50);
my_column varchar2(25) := 'id';
my_table varchar2(50) := 'users';
sql = varchar2(500) := 'SELECT :my_column INTO l_var FROM :my_table WHERE clue='yes'';
BEGIN
execute mmediate sql;
dbms_output.put_line(l_var);
END;

srinivasareddy wrote:

SELECT * FROM 'users' WHERE clue = 'yes';    this is not  correct right..?  table name 'USERS' will not be like this. anyway thanks for help

(SELECT * FROM 'users' WHERE clue = 'yes';) 
I  am  sorry but the code above is dweezil signature, look again at the post and you will see the relevant code.  Hope this helps.

i am not generating any sql query, it is genearting  string, string contains the sql query, i hope that doesn't matter, becoz i am able to write generic function like above in the post  for insertion.

the problem hides over trasaction. i am using connection _connection, wich has transaction.  

i am doing the following operations  in trasaction

1. Transaction begin
2. Deleting old stuff from the table
3.Inserting Old and new Record to table by using above method.
4. If all above sucess  sucess commit transaction

deleting is working, but not  updating old recrods from fetched from database to dataset. it may be probably dataset behaviour.

any help appriciated