Click here to Skip to main content
15,917,174 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
VB
Public Class PurchaseOrder
    Inherits System.Web.UI.Page
    Dim strcon As String = ConfigurationManager.ConnectionStrings("DeviceDBConnectionString").ToString
    Dim con As SqlConnection = New SqlConnection(strcon)
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        BindGridView()
        GetCompanyDetails()
        GetDepartmentDetails()
    End Sub

    Protected Sub Button5_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button5.Click
        Try
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "INSERT INTO TBL_PURCHASE(PONO, POHDATE,COMPCODE,POAMOUNT,POWARRANTY,POEHDATE,DCCODE)VALUES('" & txtpn.Text & "','" & txtpurdate.Text & "','" & ddlcmp.SelectedValue & "','" & txtamt.Text & "','" & txtwarperiod.Text & "','" & txtdoe.Text & "','" & ddldept.SelectedValue & "')"
            'cmd.CommandText = "INSERT INTO TBL_PURCHASE(PONO, POHDATE,COMPCODE,POAMOUNT,POWARRANTY,POEHDATE,DCCODE)" &_VALUES(@PONO, @POHDATE,@COMPCODE,@POAMOUNT,@POWARRANTY,@POEHDATE,@DCCODE)"
           
            cmd.ExecuteNonQuery()
            '
        Catch ex As Exception
            'MsgBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            MsgBox("Error " & ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles GridView1.SelectedIndexChanged
        txtpn.Text = GridView1.SelectedRow.Cells(1).Text
        txtpurdate.Text = GridView1.SelectedRow.Cells(2).Text
        If GridView1.SelectedRow.Cells(3).Text = " " Then
            txtamt.Text = ""
        Else
            txtamt.Text = GridView1.SelectedRow.Cells(3).Text
        End If
        txtwarperiod.Text = GridView1.SelectedRow.Cells(4).Text
        txtdoe.Text = GridView1.SelectedRow.Cells(5).Text
        If GridView1.SelectedRow.Cells(6).Text Is Nothing Then

            'ddlcmp.SelectedValue = GridView1.SelectedRow.Cells(6).Text
        Else
            ddlcmp.SelectedValue = GridView1.SelectedRow.Cells(6).Text
        End If
        If GridView1.SelectedRow.Cells(7).Text Is Nothing Then
        Else
            ddldept.SelectedValue = GridView1.SelectedRow.Cells(7).Text
        End If



        

    End Sub

    

    Private Function SqlConnection(ByVal p1 As String) As SqlConnection
        Throw New NotImplementedException
    End Function
    Protected Sub GetCompanyDDL()
        Try
            cmd.Connection = con
            cmd.CommandText = "SELECT [COMPCODE], [COMPNAME] FROM [TBL_COMPANY_COD]"
            cmd.Connection.Open()
            dr = cmd.ExecuteReader()
            'ddlSignatory.Items.Add("")
            While dr.Read()
                Dim newListItem As New ListItem()
                newListItem.Text = dr.GetString(1).ToString()
                newListItem.Value = dr.GetValue(0).ToString()
                ddlcmp.Items.Add(newListItem)
            End While
            cmd.Connection.Close()
        Catch ex As Exception
            ddlcmp.Items.Add("")
            cmd.Connection.Close()
        End Try
    End Sub
    Protected Sub GetCompanyDetails()

        Dim da As New SqlDataAdapter("SELECT [COMPCODE], [COMPNAME] FROM [TBL_COMPANY_COD]", strcon)
        Dim dt As New Data.DataTable
        da.Fill(dt)
        da.Dispose()
        ddlcmp.DataSource = dt
        ddlcmp.DataTextField = "COMPNAME"
        ddlcmp.DataValueField = "COMPCODE"
        ddlcmp.DataBind()
        'ddlcmp.ClearSelection()
    End Sub
    Protected Sub GetDepartmentDetails()

        Dim da As New SqlDataAdapter("SELECT [DEPCODE], [DEPDESC] FROM [TBL_DEPARTMENT_COD]", strcon)
        Dim dt As New Data.DataTable
        da.Fill(dt)
        da.Dispose()
        ddldept.DataSource = dt
        ddldept.DataTextField = "DEPCODE"
        ddldept.DataValueField = "DEPDESC"
        ddldept.DataBind()
        'ddlcmp.ClearSelection()
    End Sub
    Protected Sub GetDepartmentDDL()
        Try
            cmd.Connection = con
            cmd.CommandText = "SELECT [DEPCODE], [DEPDESC] FROM [TBL_DEPARTMENT_COD]"
            cmd.Connection.Open()
            dr = cmd.ExecuteReader()
            'ddlSignatory.Items.Add("")
            While dr.Read()
                Dim newListItem As New ListItem()
                newListItem.Text = dr.GetString(1).ToString()
                newListItem.Value = dr.GetValue(0).ToString()
                ddldept.Items.Add(newListItem)
            End While
            cmd.Connection.Close()
        Catch ex As Exception
            ddldept.Items.Add("")
            cmd.Connection.Close()
        End Try
    End Sub

    Protected Sub BindGridView()
        Dim da As New SqlDataAdapter("SELECT [PONO], [POHDATE], [POAMOUNT], [POWARRANTY], [POEHDATE], [COMPCODE], [DCCODE] FROM [TBL_PURCHASE]", strcon)
        Dim ds As New DataSet()
        da.Fill(ds)
        GridView1.DataSource = ds
        GridView1.DataBind()
    End Sub


    Protected Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnupdate.Click
        Try
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE TBL_PURCHASE SET PONO ='" & txtpn.Text & "', POHDATE = '" & txtpurdate.Text & "',COMPCODE = '" & ddlcmp.SelectedValue & "',POAMOUNT= '" & txtamt.Text & "',POWARRANTY = '" & txtwarperiod.Text & "',POEHDATE= '" & txtdoe.Text & "',DCCODE= '" & ddldept.SelectedValue & "'WHERE PONO= '" & txtpn.Text & "'"

            cmd.ExecuteNonQuery()

        Catch ex As Exception
            'MsgBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            MsgBox("Error " & ex.Message)
        Finally
            con.Close()
        End Try
    End Sub
    Public Sub ClearTextBox(ByVal root As Control)
        For Each ctrl As Control In root.Controls
            ClearTextBox(ctrl)
            If TypeOf ctrl Is TextBox Then
                CType(ctrl, TextBox).Text = String.Empty
            End If
        Next ctrl
    End Sub
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        ClearTextBox(Me)
    End Sub
End Class
Posted
Updated 2-Jun-13 21:04pm
v2
Comments
_Amy 3-Jun-13 3:06am    
Which line?
akhil.krish 3-Jun-13 3:44am    
Insert Command.
select the dddept.selected value
" Error conversion failed when converting the varchar value'IT' to data type
Bernhard Hiller 3-Jun-13 3:06am    
in which line does that happen?
akhil.krish 3-Jun-13 3:44am    
Insert Command.
select the dddept.selected value
" Error conversion failed when converting the varchar value'IT' to data type

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

The chances are that if you check you inputs first, and use parametrized queries your problem will disappear without any further work. But if you leave it as you are, your database is at risk of severe damage...
 
Share this answer
 
Error might be there in your Button5_Click Event or Button4_Click Event.
Not a problem. This exception is obvious, when you are trying to convert a String(or Empty Char) to integer type.

In those events you are building a command text. And also you are passing dynamic parameters to it. In those parameters some are Integers, some are Strings. Somewhere, in place of an integer you are passing Empty String or a String. Try debugging the code and check for that.

Refer the links below which will explain "how to add parameters in command text?":
Adding Parameters to Commands[^]
C# SqlParameter[^]
MSDN : SqlCommand.Parameters Property[^]

--Amit
 
Share this answer
 
Check your Session["uid"].ToString() is having the value, if session is null or blank, then you can not converted with integer value.
 
Share this answer
 
So, from the line where exception is occurring, it is clear that DCCODE is an Integer field in Table, whereas you are sending a string value ddldept.SelectedValue.

You need to convert that to INT. So you can do like below...
C#
int selectedValue;
Int32.TryParse(ddldept.SelectedValue, out selectedValue);

Now use this selectedValue in the query.

NOTE
I just tried to fix the exception you are getting now. But you should implement the solutions advised by OriginalGriff and _Amy.
 
Share this answer
 
v2
Comments
akhil.krish 3-Jun-13 4:49am    
Dear Tadit dash,

i wil try on u r solution.. but same error occuring,

problem is ddldept value intger but i write single quote i think problem is that, and i wil remove the sigle quote but again error comming on " error invalid column name 'IT'
Please put a debugger and check all the values are coming properly or not. After all the values get replaced in the query, copy that and execute that query in the DataBase directly. See what is the problem. You will be clear then.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900