Visual Basic

<WebMethod()> _
Public Function OprationONTbl(ByRef ds As Data.DataSet, ByVal TblName As String, ByVal Insert As Boolean, ByVal Update As Boolean, ByVal StrWhere As String, ByVal retValue As Boolean, ByRef StrRetValue As String) As String
Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim cBuilder As New SqlCommandBuilder(da)

Try
'-*-> cn
Dim cls As New clsGeneral
cn.ConnectionString = cls.GetConnection
cls = Nothing

'-*-> cmd
cmd.CommandText = "Select * From " & TblName
If StrWhere.Trim <> "" Then cmd.CommandText = cmd.CommandText & " WHERE " & StrWhere
cmd.Connection = cn

'-*-> da
da.SelectCommand = cmd

'**************************************'
If Not Insert And Not Update Then
da.Fill(ds, TblName)
End If
'**************************************'
If Insert Then
da.InsertCommand = cBuilder.GetInsertCommand
da.Update(ds.Tables(TblName))
If retValue Then
cmd.CommandText = "Select Max(Id) from " & TblName
cn.Open()
StrRetValue = cmd.ExecuteScalar
cn.Close()
End If
End If
'**************************************'
If Update Then
Dim value() As String = StrWhere.Split("*")
cmd.CommandText = "UpDate " & TblName & " SET " & value(1) & " WHERE " & value(0)
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End If


Return ""
Catch ex As Exception
Return "OprationONTbl : " & ex.Message

Finally
If cn.State = ConnectionState.Open Then cn.Close()
cn = Nothing
cmd = Nothing
da = Nothing
cBuilder = Nothing
End Try

End Function

Now i want get more table form my application and with transaction, that if i send to webService two tables, with one transaction tow tables be full of data,
now i changed above web method but my result is not true ,i test to many state of this code but it has different Error for Inserting data,  

I don't know how i can change this web method for doing my work true.

i write this but it is  mistake,


Dim myTblName() As String = TblName.Split("$")
'-*-> cmd
For i As Integer = 0 To myTblName.Length - 1
cmd.CommandText = "Select * From " & myTblName(i)
If StrWhere.Trim <> "" Then cmd.CommandText = cmd.CommandText & " WHERE " & StrWhere
cmd.Connection = cn
'-*-> da
da.SelectCommand = cmd
'**************************************'
If Not Insert And Not Update Then
da.Fill(ds, myTblName(i))
End If
Next

'**************************************'
If Insert Then
Dim trans As SqlTransaction
cn.Open()
trans = cn.BeginTransaction
cmd.Transaction = trans

For i As Integer = 0 To myTblName.Length - 1
da.InsertCommand = cBuilder.GetInsertCommand
da.Update(ds.Tables(myTblName(i)))

If retValue Then
cmd.CommandText = "Select Max(Id) from " & myTblName(i)
StrRetValue = cmd.ExecuteScalar

End If
Next

Try
trans.Commit()
Catch ex As Exception
trans.Rollback()
End Try

End If
'**************************************'
If Update Then
cn.Open()
Dim trans As SqlTransaction
trans = cn.BeginTransaction
cmd.Transaction = trans
For i As Integer = 0 To myTblName.Length - 1
Dim value() As String = StrWhere.Split("*")
cmd.CommandText = "UpDate " & myTblName(i) & " SET " & value(1) & " WHERE " & value(0)
cmd.ExecuteNonQuery()

Next
Try
trans.Commit()
Catch ex As Exception
trans.Rollback()
End Try
End If
Return ""
Catch ex As Exception
Return "OprationONTbl : " & ex.Message
Finally
If cn.State = ConnectionState.Open Then cn.Close()
cn = Nothing
cmd = Nothing
da = Nothing
cBuilder = Nothing
End Try
End Function

i need your help,

Thank You 

<div class="ForumSig">Hoda </div>