?
Solved

Problem in Updating Data in Vb.net

Posted on 2006-06-02
12
Medium Priority
?
321 Views
Last Modified: 2010-04-23
Hello Experts,

This is my vb6.0 code

'Set RsTrans = Con.Execute("SELECT *  FROM TRANS LEFT JOIN ItemDetl ON TRANS.ITEM_ID = ItemDetl.ID  WHERE TRANS.CRATE=0 ")

'Do While RsTrans.EOF = False
'
'    Set RsTmp = Con.Execute("SELECT CP FROM CN WHERE S='" & RsTrans!item_name & "' AND cn.ed=" & DATEMASK & Format(RsTrans!enddate, "DD MMM YYYY") & DATEMASK & "  and CN.DT>=" & DATEMASK & Format(RsTrans!TR_DATE, "DD MMM YYYY") & DATEMASK & " order by dt")
'        If RsTmp.EOF = False Then
'        convrate = 0
'            convrate = (Abs(RsTrans!Qty) * RsTmp!cp * RsTrans!MFN) / RsTrans!mfd

'           Strsql = "   UPDATE TRANS SET " & _
'                     "  AMOUNT= (qty * rate) *  " & (RsTrans!MFN / RsTrans!mfd) & _
'                     "  ,crate=" & RsTmp!cp & ", cl_amount=" & convrate & " WHERE ID=" & RsTrans!id
'            Con.Execute Strsql
'        End If
'    RsTrans.MoveNext
'    DoEvents
'Loop


Now i want to conver this code into vb.net
Till now i have written the following code

Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataReader
Imports System.Data.SqlClient.SqlException
Imports System.Math
Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim sql1 As SqlClient.SqlCommand
    Dim Strsql As String

    Dim ds1 As New SqlClient.SqlDataAdapter()

    Dim con As New SqlClient.SqlConnection("data source=COMPAQ;initial catalog=NCDEX;integrated security=SSPI;persist security info=True;workstation id=COMPAQ;packet size=4096")
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents DataSet11 As WindowsApplication4.DataSet1
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
        Me.DataSet11 = New WindowsApplication4.DataSet1()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid()
        Me.Button1 = New System.Windows.Forms.Button()
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT ID, Tr_ID, TnNo, LEDGER_ID, INSTRUMENT, TR_TYPE, TR_DATE, TR_TIME, ITEM_ID" & _
        ", QTY, RATE, CRATE, AMOUNT, CL_AMOUNT, PROFITLOSS, BROKERAGE, SR_AMT, TR_AMT, NE" & _
        "TPROFIT, SUBCLIENT, MAINCLIENT, ST, TRANCHECK1 FROM TRANS"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO TRANS(ID, Tr_ID, TnNo, LEDGER_ID, INSTRUMENT, TR_TYPE, TR_DATE, TR_TI" & _
        "ME, ITEM_ID, QTY, RATE, CRATE, AMOUNT, CL_AMOUNT, PROFITLOSS, BROKERAGE, SR_AMT," & _
        " TR_AMT, NETPROFIT, SUBCLIENT, MAINCLIENT, ST, TRANCHECK1) VALUES (@ID, @Tr_ID, " & _
        "@TnNo, @LEDGER_ID, @INSTRUMENT, @TR_TYPE, @TR_DATE, @TR_TIME, @ITEM_ID, @QTY, @R" & _
        "ATE, @CRATE, @AMOUNT, @CL_AMOUNT, @PROFITLOSS, @BROKERAGE, @SR_AMT, @TR_AMT, @NE" & _
        "TPROFIT, @SUBCLIENT, @MAINCLIENT, @ST, @TRANCHECK1); SELECT ID, Tr_ID, TnNo, LED" & _
        "GER_ID, INSTRUMENT, TR_TYPE, TR_DATE, TR_TIME, ITEM_ID, QTY, RATE, CRATE, AMOUNT" & _
        ", CL_AMOUNT, PROFITLOSS, BROKERAGE, SR_AMT, TR_AMT, NETPROFIT, SUBCLIENT, MAINCL" & _
        "IENT, ST, TRANCHECK1 FROM TRANS"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Tr_ID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "Tr_ID", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TnNo", System.Data.SqlDbType.VarChar, 50, "TnNo"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LEDGER_ID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "LEDGER_ID", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@INSTRUMENT", System.Data.SqlDbType.VarChar, 50, "INSTRUMENT"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TR_TYPE", System.Data.SqlDbType.VarChar, 50, "TR_TYPE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TR_DATE", System.Data.SqlDbType.DateTime, 8, "TR_DATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TR_TIME", System.Data.SqlDbType.DateTime, 8, "TR_TIME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ITEM_ID", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "ITEM_ID", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@QTY", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "QTY", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RATE", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "RATE", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CRATE", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "CRATE", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AMOUNT", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(3, Byte), "AMOUNT", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CL_AMOUNT", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "CL_AMOUNT", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PROFITLOSS", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "PROFITLOSS", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BROKERAGE", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(3, Byte), "BROKERAGE", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SR_AMT", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "SR_AMT", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TR_AMT", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "TR_AMT", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@NETPROFIT", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "NETPROFIT", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SUBCLIENT", System.Data.SqlDbType.VarChar, 50, "SUBCLIENT"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MAINCLIENT", System.Data.SqlDbType.VarChar, 50, "MAINCLIENT"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ST", System.Data.SqlDbType.Bit, 1, "ST"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TRANCHECK1", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "TRANCHECK1", System.Data.DataRowVersion.Current, Nothing))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "data source=COMPAQ;initial catalog=NCDEX;integrated security=SSPI;persist securit" & _
        "y info=True;workstation id=COMPAQ;packet size=4096"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "TRANS", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("ID", "ID"), New System.Data.Common.DataColumnMapping("Tr_ID", "Tr_ID"), New System.Data.Common.DataColumnMapping("TnNo", "TnNo"), New System.Data.Common.DataColumnMapping("LEDGER_ID", "LEDGER_ID"), New System.Data.Common.DataColumnMapping("INSTRUMENT", "INSTRUMENT"), New System.Data.Common.DataColumnMapping("TR_TYPE", "TR_TYPE"), New System.Data.Common.DataColumnMapping("TR_DATE", "TR_DATE"), New System.Data.Common.DataColumnMapping("TR_TIME", "TR_TIME"), New System.Data.Common.DataColumnMapping("ITEM_ID", "ITEM_ID"), New System.Data.Common.DataColumnMapping("QTY", "QTY"), New System.Data.Common.DataColumnMapping("RATE", "RATE"), New System.Data.Common.DataColumnMapping("CRATE", "CRATE"), New System.Data.Common.DataColumnMapping("AMOUNT", "AMOUNT"), New System.Data.Common.DataColumnMapping("CL_AMOUNT", "CL_AMOUNT"), New System.Data.Common.DataColumnMapping("PROFITLOSS", "PROFITLOSS"), New System.Data.Common.DataColumnMapping("BROKERAGE", "BROKERAGE"), New System.Data.Common.DataColumnMapping("SR_AMT", "SR_AMT"), New System.Data.Common.DataColumnMapping("TR_AMT", "TR_AMT"), New System.Data.Common.DataColumnMapping("NETPROFIT", "NETPROFIT"), New System.Data.Common.DataColumnMapping("SUBCLIENT", "SUBCLIENT"), New System.Data.Common.DataColumnMapping("MAINCLIENT", "MAINCLIENT"), New System.Data.Common.DataColumnMapping("ST", "ST"), New System.Data.Common.DataColumnMapping("TRANCHECK1", "TRANCHECK1")})})
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        Me.DataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd"
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.DataSource = Me.DataSet11
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(24, 0)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(496, 272)
        Me.DataGrid1.TabIndex = 0
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(168, 288)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(120, 24)
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(536, 318)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1, Me.DataGrid1})
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim rs As String
        Dim rd2 As SqlDataReader
        Dim RsTmp As SqlDataReader
        Dim RsTrans As SqlDataReader
        Dim DATEMASK As String
        Dim StrSql As String
        Dim DtFrom As Date
        Dim convrate As Double
        Try
            con.Open()
            sql1.Connection = con
            'StrSql = "SELECT DT AS DT  FROM CN WHERE DT>=" & DATEMASK & Format(DtFrom, "DD MMM YYYY") & DATEMASK
            'sql1 = New SqlClient.SqlCommand(StrSql, con)
            'rd2 = sql1.ExecuteReader
            StrSql = "SELECT DT AS DT  FROM CN WHERE DT>=" & DATEMASK & Format(DtFrom, "DD MMM YYYY") & DATEMASK

            sql1 = New SqlClient.SqlCommand(StrSql, con)
            RsTmp = sql1.ExecuteReader

            'While rd2.Read()
            '    MsgBox(rd2.Item("tr_date"))
            'End While
            StrSql = "SELECT *  FROM TRANS LEFT JOIN ItemDetl ON TRANS.ITEM_ID = ItemDetl.ID  WHERE TRANS.CRATE=0 "

            sql1 = New SqlClient.SqlCommand(StrSql, con)
            RsTrans = sql1.ExecuteReader
            While RsTrans.Read()
                sql1 = New SqlClient.SqlCommand("SELECT CP FROM CN WHERE S='" & RsTrans!item_name & "' AND cn.ed=" & DATEMASK & Format(RsTrans!enddate, "DD MMM YYYY") & DATEMASK & "  and CN.DT>=" & DATEMASK & Format(RsTrans!TR_DATE, "DD MMM YYYY") & DATEMASK & " order by dt", con)
                RsTrans = sql1.ExecuteReader
                If RsTrans.Then Then
                    If RsTmp.EOF = False Then
                        convrate = 0
                        convrate = (Abs(RsTrans!Qty) * RsTmp!cp * RsTrans!MFN) / RsTrans!mfd

                        StrSql = "   UPDATE TRANS SET " & _
                                  "  AMOUNT= (qty * rate) *  " & (RsTrans!MFN / RsTrans!mfd) & _
                                  "  ,crate=" & RsTmp!cp & ", cl_amount=" & convrate & " WHERE ID=" & RsTrans!id
                        con.Execute(StrSql)


                    End If
                    RsTrans.NextResult()
                Loop
            End While


now in this i am not able to find a command equal to EOF in vb.net.
Please help me in solving this.

I would be very thankfull if any one can give immediate reply.

Thanks

Vikas


 
0
Comment
Question by:vikas_maderna
  • 4
  • 4
9 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 16822337
Hi vikas_maderna,
----------

didn't read all of that but to answer your question about EOF, its not available you do a while loop until you can't read anymore like

i do not understand this part
        ......
        RsTrans = sql1.ExecuteReader
        If RsTrans.Then Then
            If RsTmp.EOF = False Then
        ......

i assume you need to check on EOF for both?

then take out the first
        If RsTrans.Then Then

you do not have to check because you already have a

        While RsTrans.Read()
        .....
        Wend

this will take care of the EOF for you

since you only check once here for RsTmp i assume you need to return only one value in RsTmp then use this

        If RsTmp.HasRows() = False Then
        ....
        Else
        ....
        End if

----------
bruintje
share what you know, learn what you don't
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16822360
           While RsTrans.Read()

                sql1 = New SqlClient.SqlCommand("SELECT CP FROM CN WHERE S='" & RsTrans!item_name & "' AND cn.ed=" & DATEMASK & Format(RsTrans!enddate, "DD MMM YYYY") & DATEMASK & "  and CN.DT>=" & DATEMASK & Format(RsTrans!TR_DATE, "DD MMM YYYY") & DATEMASK & " order by dt", con)

                RsTemp = sql1.ExecuteReader

                While RsTemp.Read()
                        convrate = 0
                        convrate = (Abs(RsTrans!Qty) * RsTmp!cp * RsTrans!MFN) / RsTrans!mfd

                        StrSql = "   UPDATE TRANS SET " & _
                                  "  AMOUNT= (qty * rate) *  " & (RsTrans!MFN / RsTrans!mfd) & _
                                  "  ,crate=" & RsTmp!cp & ", cl_amount=" & convrate & " WHERE ID=" & RsTrans!id
                        con.Execute(StrSql)

                End While
            End While
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16822432
@vb_jonas, maybe i'm missing something but wouldn't that code update the same RsTrans!id looping through RsTmp?

i would've put the value from RsTmp in a
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:bruintje
ID: 16822450
posted to early

i would've put the value from RsTmp in a variable makes it clearer but i could be interpreting this wrong
0
 

Author Comment

by:vikas_maderna
ID: 16822513
Hello,

Now this is my code of vb.net
*************************************************
Imports System.Data.SqlClient

Imports System.Data.SqlClient.SqlDataReader

Imports System.Data.SqlClient.SqlException

Imports System.Math

Dim sql1, Sql2, Sql3, Sql4

    Dim Strsql As String

    Dim ds1 As New SqlClient.SqlDataAdapter()

    Dim con As New SqlClient.SqlConnection("data source=COMPAQ;initial catalog=NCDEX;integrated security=SSPI;persist security info=True;workstation id=COMPAQ;packet size=4096")

       Dim rs As String

        Dim rd2 As SqlDataReader

        Dim RsTmp As SqlDataReader

        Dim RsTrans As SqlDataReader

        Dim DATEMASK As String

        Dim StrSql As String

        Dim DtFrom As Date

        Dim convrate As Double
        On Error GoTo Errhandle
        DATEMASK = "'"
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        DtFrom = Now
        StrSql = "SELECT DT AS DT FROM CN WHERE DT>=" & DATEMASK & Format(DtFrom, "dd MMM yyyy") & DATEMASK
        sql1 = New SqlClient.SqlCommand(StrSql, con)

        RsTmp = sql1.ExecuteReader

        StrSql = "SELECT * FROM TRANS LEFT JOIN ItemDetl ON TRANS.ITEM_ID = ItemDetl.ID WHERE TRANS.CRATE=0 "

        sql1 = New SqlClient.SqlCommand(StrSql, con)
        RsTmp.Close()
        RsTrans = sql1.ExecuteReader

        While RsTrans.Read()

            Sql2 = New SqlClient.SqlCommand("SELECT CP FROM CN WHERE S='" & RsTrans!item_name & "' AND cn.ed=" & DATEMASK & Format(RsTrans!enddate, "DD MMM YYYY") & DATEMASK & " and CN.DT>=" & DATEMASK & Format(RsTrans!TR_DATE, "DD MMM YYYY") & DATEMASK & " order by dt", con)
            RsTmp.Close()
            RsTmp = Sql2.ExecuteReader

            While RsTmp.Read

                convrate = 0

                convrate = (Abs(RsTrans!Qty) * RsTmp!cp * RsTrans!MFN) / RsTrans!mfd

                StrSql = " UPDATE TRANS SET  AMOUNT= (qty * rate) * " & (RsTrans!MFN / RsTrans!mfd) & " ,crate=" & RsTmp!cp & ", cl_amount=" & convrate & " WHERE ID=" & RsTrans!id

                Dim cmd As New SqlClient.SqlCommand(StrSql, con)

                cmd.Connection = con

                cmd.ExecuteNonQuery()
            End While
            RsTrans.NextResult()
        End While

Errhandle:
        MsgBox(Err.Number & " " & Err.Description)
        Resume Next

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

In this i am getting the error:

"5 There is already an open Datareader Associated with this connection which must be closed first".

Could you please explain the problem and let me know where i have to change.

As i cant open a new connection for each datareader.

Thanks

Vikas

0
 
LVL 44

Expert Comment

by:bruintje
ID: 16822683
can you explain what you want to do in pseudocode?

you need a date from a table and with that date query another one?
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16822903
I would use DataTables and fill them with data, and execute the update-command after iterating the rows in the datatables. (The Adapter.Fill opens and closes the connection automatically) Something like this:

----------

        Dim myConnection As New SqlClient.SqlConnection

        Dim dataAdapterTrans As New SqlClient.SqlDataAdapter("SELECT * FROM TRANS LEFT JOIN ItemDetl ON TRANS.ITEM_ID = ItemDetl.ID  WHERE TRANS.CRATE=0", myConnection)

        Dim dataTableTrans As New DataTable
        dataAdapterTrans.Fill(dataTableTrans)

        Dim dataAdapterCN As New SqlClient.SqlDataAdapter
        Dim dataTableCN As New DataTable

        Dim myUpdateCommand As New SqlClient.SqlCommand

        Dim convrate As Single
        Dim Strsql As String
        Dim DATEMASK As String = ""

        Dim dataRowCN As DataRow

        Dim dataRowTrans As DataRow
        For Each dataRowTrans In dataTableTrans.Rows
            dataAdapterCN.SelectCommand = "SELECT CP FROM CN WHERE S='" & dataRowTrans("item_name") & "' AND cn.ed=" & DATEMASK & Format(RsTrans!enddate, "DD MMM YYYY") & DATEMASK & "  and CN.DT>=" & DATEMASK & Format(dataRowTrans("TR_DATE"), "DD MMM YYYY") & DATEMASK & " order by dt"
            dataAdapterCN.Fill(dataTableCN)
            For Each dataRowCN In dataTableCN.Rows
                convrate = 0
                convrate = (Math.Abs(dataRowTrans("Qty")) * dataRowCN("cp") * dataRowTrans("MFN")) / dataRowTrans("mfd")
                Strsql = "   UPDATE TRANS SET " & _
                          "  AMOUNT= (qty * rate) *  " & (dataRowTrans("MFN") / dataRowTrans("mfd")) & _
                          "  ,crate=" & dataRowCN("cp") & ", cl_amount=" & convrate & " WHERE ID=" & dataRowTrans("id")
                myUpdateCommand.CommandText = Strsql
                myConnection.Open
                myUpdateCommand.ExecuteNonQuery()
                myConnection.Close
            Next
        Next
0
 
LVL 12

Accepted Solution

by:
vb_jonas earned 2000 total points
ID: 16822904
change RsTrans!enddate to dataRowTrans("enddate")...
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16998769
My last code works as (if I understand the question right) vikas_maderna want.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question