2 same data in field error

Question

0.00/5 (No votes)

See more:

Hello programmers, got a small problem here. As you see I was tasked to do where there are 2 data or more with diferrent category
Example

 CostCenter   Category                 CourseCode      CourseTitle         SO on....
 123          ABC                      imdead          dying               ...
 123          DEF                      dead            funeral             ...
 123          GEH                      Flower          wew                 ...
 
 456          ABC                      imdead           dying              ...
 456          DEF                      dead             funeral            ...
456           GEH                      Flower           wew                ...

Now when i want to show only the data varied on the cost center of (123) it doesnt show up but instead it shows the data of the last costcenter with the same data
plss help
Here is my code

VB

Imports System.Math
Imports System.Web
Imports System.Data.SqlClient
Imports System.Data
Partial Class PlanvsAct
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If (IsPostBack = False) Then
            costcenter()
        End If
    End Sub
    Public Sub costcenter()
        Dim rs As String
        Dim cn As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
        rs = "Select [CostCenter] from [tblDiscipline]"
        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand(rs, cn)
        ddcc.DataSource = cmd.ExecuteReader
        ddcc.DataValueField = "CostCenter"
        ddcc.DataTextField = "CostCenter"
        ddcc.DataBind()
        cn.Close()

    End Sub
    Public Sub categorybind()
        Dim strSQL As String
        Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
        strSQL = "Select [Category] from [tblCategory] "
        connection.Open()
        Dim command As New SqlCommand(strSQL, connection)
        ddcategory.DataSource = command.ExecuteReader
        ddcategory.DataTextField = "Category"
        ddcategory.DataValueField = "Category"
        ddcategory.DataBind()
        connection.Close()

    End Sub

    Public Sub coursecodebind()
        Dim strSQL As String
        Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
        strSQL = "Select [CourseCode] from tblTrainingPlan where ([Category] LIKE '%' + @Category + '%')"
        connection.Open()
        Dim command As New SqlCommand(strSQL, connection)
        command.Parameters.AddWithValue("@Category", ddcategory.SelectedValue)
        ddcoursecode.DataSource = command.ExecuteReader
        ddcoursecode.DataTextField = "CourseCode"
        ddcoursecode.DataValueField = "CourseCode"
        ddcoursecode.DataBind()
        connection.Close()
    End Sub
    Public Sub binddata()
        Dim strSQL As String
        Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
        strSQL = "Select * from [tblTrainingPlan] where ([CourseCode] LIKE '%' + @CourseCode + '%')"
        connection.Open()
        Dim command As New SqlCommand(strSQL, connection)
        command.Parameters.AddWithValue("@CourseCode", ddcoursecode.SelectedValue)
        Dim datareader As SqlClient.SqlDataReader = command.ExecuteReader
        Do While datareader.Read()
            lblid.Text = (datareader("ID").ToString)
            txtTitle.Text = (datareader("CourseTitle").ToString)
            txtcollege.Text = (datareader("College").ToString)
            txtDuration.Text = (datareader("Duration").ToString)
            txtattendees.Text = (datareader("TargetAttendees").ToString)
            txtbatches.Text = (datareader("TotalBatches").ToString)

            a1.Text = (datareader("TH_F_NoAttendeesPerBatch").ToString)
            s1.Text = (datareader("TH_F_NoSessionPerBatch").ToString)
            h1.Text = (datareader("TH_F_NoHoursPerSession").ToString)

            a2.Text = (datareader("TH_MOT_NoAttendeesPerBatch").ToString)
            s2.Text = (datareader("TH_MOT_NoSessionPerBatch").ToString)
            h2.Text = (datareader("TH_MOT_NoHoursPerSession").ToString)

            a3.Text = (datareader("TH_P_NoAttendeesPerBatch").ToString)
            s3.Text = (datareader("TH_P_NoSessionPerBatch").ToString)
            h3.Text = (datareader("TH_P_NoHoursPerSession").ToString)

            pd.Text = (datareader("PerDiem").ToString)
            tr.Text = (datareader("Transportation").ToString)
            ac.Text = (datareader("Accomodation").ToString)
            vm.Text = (datareader("MealVenue").ToString)
            cs.Text = (datareader("Consultant").ToString)
            mt.Text = (datareader("Material").ToString)

            con.Text = (datareader("Contract").ToString)
            am.Text = (datareader("Amount").ToString)

            txtMal.Text = (datareader("MealAllowance").ToString)
            txtTA.Text = (datareader("TransAllowance").ToString)

            tth.Text = (datareader("TotalTrainingHours").ToString)
        Loop
        connection.Dispose()
        connection.Close()
    End Sub



    Public Sub control()
        If ddcoursecode.SelectedValue = "" Then
            txtTitle.Text = ""
            txtcollege.Text = ""
            txtDuration.Text = ""
        End If
    End Sub

    Protected Sub ddcoursecode_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddcoursecode.SelectedIndexChanged
        binddata()

    End Sub

    Protected Sub ddcategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddcategory.SelectedIndexChanged
        coursecodebind()
    End Sub

    Public Sub calculationbind()
        Dim x, y, z As Double
        x = Round((CDbl(a1.Text) * CDbl(s1.Text) * CDbl(h1.Text)), 2)
        y = Round((CDbl(a2.Text) * CDbl(s2.Text) * CDbl(h2.Text)), 2)
        z = Round((CDbl(a3.Text) * CDbl(s3.Text) * CDbl(h3.Text)), 2)
        'tth.Text = Round((x + y + z), 2)

        'txtsession.Text = Round((CDbl(s1.Text) + CDbl(s2.Text) + CDbl(s3.Text)), 2)
        'txthour.Text = Round((CDbl(h1.Text) * CDbl(h2.Text) * CDbl(h3.Text)), 2)

        'txtTE.Text = Round((CDbl(txtMal.Text) + CDbl(txtTA.Text) + CDbl(pd.Text) + CDbl(tr.Text) + CDbl(cs.Text) + CDbl(mt.Text) + CDbl(vm.Text) + CDbl(ac.Text)), 2)

    End Sub
    Public Sub calculationbind2()

    End Sub
    Protected Sub ddcc_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddcc.SelectedIndexChanged
        categorybind()
    End Sub
End Class

Posted 12-Jul-11 16:45pm

janwel

Updated 12-Jul-11 17:00pm

v2

Add a Solution

Comments

arathi_suresh 12-Jul-11 22:48pm

Please provide your code

Venkatesh Mookkan 12-Jul-11 22:48pm

Your question is not clear. What do you mean by calling Math (3A) ?

arathi_suresh 12-Jul-11 23:11pm

Which query did you use for showing cost center of 123?

janwel 12-Jul-11 23:12pm

Sir is there any possible way of just calling out the connection so that i dont have to type it all over again?

2 solutions

Solution 1

Found it..
the error was the querry itself ^_^

VB

Public Sub binddata()
       Dim strSQL As String
       Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
strSQL = "Select * from [tblTrainingPlan] where ([CourseCode] LIKE '%' + @CourseCode + '%' ) AND ([CostCenter] LIKE '%' + @CostCenter + '%' ) "
       connection.Open()
       Dim command As New SqlCommand(strSQL, connection)
       command.Parameters.AddWithValue("@CostCenter", ddcc.SelectedValue)
       command.Parameters.AddWithValue("@CourseCode", ddcoursecode.SelectedValue)

       Dim datareader As SqlClient.SqlDataReader = command.ExecuteReader
       Do While datareader.Read()

Posted 12-Jul-11 17:11pm

janwel

Comments

Venkatesh Mookkan 12-Jul-11 23:19pm

Fast man. You should add category also in the where clause. And LIKE operator is not necessary here.

janwel 12-Jul-11 23:26pm

ok sir ^_^

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Venkatesh Mookkan · Accepted Answer · 2011-07-12T17:17:00

I understand your problem.

The problem is in the query. As you know that CostCenter, Category, Course Code can have multiple data in table. But the combination of all the three can have only one record.

So, your queries should be like,

CostCenter:

Select distinct [CostCenter] from [tblDiscipline]

CategoryBind:

SQL

Select distinct [Category] from [tblCategory] WHERE [CostCenter] = @SELECTED_COSTCENTER

CourseBind:

SQL

Select distinct [CourseCode] from tblTrainingPlan where ([Category] = @Category AND [CostCenter=@SELECTED_COSTCENTER])

BindData:

SQL

Select * from [tblTrainingPlan] where ([CourseCode] = @CourseCode AND [Category] = @Category AND [CostCenter=@SELECTED_COSTCENTER])

Mark it as answer if it is helpful