locked
Get values from drop down list and store to temp table, grid view to get records from temp table on button click RRS feed

  • Question

  • User325473304 posted

    Hi! I don't have much knowledge in VB.Net and Id' like to ask how to get selected values on dropdown and store them to temp table, then from that table I have to add those selected values displayed on my grid view.

     

    I have 4 dropdown lists, an Add button and a grid view. There's already an available stored procedure that should be triggered, and then it should store the values on my temp table. Then, have the grid view to get the records on the temp table on page reload.

     

    I don't have the code to start of so I'm really sorry if I don't help much on this. How do I do it?

    Sunday, July 21, 2013 1:25 AM

Answers

  • User516094431 posted

    You have to code in save button click event:

    Call these function:

    Protected Sub Button1_Click(ByVal sender As ObjectByVal e As System.EventArgs) Handles Button1.Click

    {

    storeddlValues()

    showDataInGridView();

    }

    private void storeddlValues()

    {

    Dim Sqlstr as string
    Dim connectionString as String = "give you connection string here"


    Sqlstr="Insert Into tempTable(ddl1 ,ddl2,ddl3,ddl4)
    Values (@ddl1, @ddl2, @ddl3, @ddl4)"
    Try


    Using connection As New SqlConnection(connectionString)
    Dim cmdInsert As New SqlCommand(Sqlstr, connection)
    cmdInsert.Parameters.Add("@ddl1",Data.SqlDbType.nvarchar).value=ddl1.SelectedValue
    cmdInsert.Parameters.Add("@ddl2",Data.SqlDbType.nvarchar).value=ddl2.SelectedValue
    cmdInsert.Parameters.Add("@ddl3",Data.SqlDbType.nvarchar).value=ddl3.SelectedValue
    cmdInsert.Parameters.Add("@ddl4",Data.SqlDbType.nvarchar).value=ddl4.SelectedValue
    connection.Open()
    cmdInsert.ExecuteNonQuery()
    End Using


    Catch Ex As Exception


    End Try

    }

    private void showDataInGridView()

    {

    Dim connStr As String
    connStr = _
    ConfigurationManager.ConnectionStrings("Your_Connection_String_Name").ConnectionString
    Dim cmd = New SqlCommand()
    cmd.CommandType = CommandType.StoredProcedure
    Try
    cmd.Connection.Open()
    gridName.DataSource = cmd.ExecuteReader()
    gridName.DataBind()

    cmd.Connection.Close()
    cmd.Connection.Dispose()
    Catch ex As Exception
    lblStatus.Text = ex.Message

    End Try

    }

    NOTE:

    you can change this code according your requirements.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 21, 2013 1:39 AM
  • User516094431 posted

    My friend This "SaveTempRecipients" parameters are not match. Means sp name prameters name and count both should be same.

    "SaveTempRecipients" SP parameter name should be,

    @EmployeeCode

    @Position

    @Department

    @CostCenter


    I Think you got it. 

    For More detail about calling SP click HERE

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 21, 2013 5:10 AM
  • User516094431 posted

    Use below line of code in try catch:

    Try

    comm.Parameters.Add("@CompanyCode", Data.SqlDbType.Int).Value = CInt(txtCompany.Text)
    comm.Parameters.Add("@EmployeeCode", Data.SqlDbType.NVarChar).Value = ddlEmployee.SelectedValue
    comm.Parameters.Add("@Position", Data.SqlDbType.NVarChar).Value = ddlPosition.SelectedValue
    comm.Parameters.Add("@DepartmendId", Data.SqlDbType.Int).Value = CInt(ddlDepartment.SelectedValue)
    comm.Parameters.Add("@CostCenterCode", Data.SqlDbType.NVarChar).Value = ddlCostCenter.SelectedValue
    comm.Parameters.AddWithValue("@Action", "ok")
    conn.Open()
    comm.ExecuteNonQuery()
    conn.Close()
    Catch Ex As Exception

    Note:

    Friend, Which post are help for you kindly "Marks as Answer". It's help for others.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2013 12:26 AM

All replies

  • User516094431 posted

    You have to code in save button click event:

    Call these function:

    Protected Sub Button1_Click(ByVal sender As ObjectByVal e As System.EventArgs) Handles Button1.Click

    {

    storeddlValues()

    showDataInGridView();

    }

    private void storeddlValues()

    {

    Dim Sqlstr as string
    Dim connectionString as String = "give you connection string here"


    Sqlstr="Insert Into tempTable(ddl1 ,ddl2,ddl3,ddl4)
    Values (@ddl1, @ddl2, @ddl3, @ddl4)"
    Try


    Using connection As New SqlConnection(connectionString)
    Dim cmdInsert As New SqlCommand(Sqlstr, connection)
    cmdInsert.Parameters.Add("@ddl1",Data.SqlDbType.nvarchar).value=ddl1.SelectedValue
    cmdInsert.Parameters.Add("@ddl2",Data.SqlDbType.nvarchar).value=ddl2.SelectedValue
    cmdInsert.Parameters.Add("@ddl3",Data.SqlDbType.nvarchar).value=ddl3.SelectedValue
    cmdInsert.Parameters.Add("@ddl4",Data.SqlDbType.nvarchar).value=ddl4.SelectedValue
    connection.Open()
    cmdInsert.ExecuteNonQuery()
    End Using


    Catch Ex As Exception


    End Try

    }

    private void showDataInGridView()

    {

    Dim connStr As String
    connStr = _
    ConfigurationManager.ConnectionStrings("Your_Connection_String_Name").ConnectionString
    Dim cmd = New SqlCommand()
    cmd.CommandType = CommandType.StoredProcedure
    Try
    cmd.Connection.Open()
    gridName.DataSource = cmd.ExecuteReader()
    gridName.DataBind()

    cmd.Connection.Close()
    cmd.Connection.Dispose()
    Catch ex As Exception
    lblStatus.Text = ex.Message

    End Try

    }

    NOTE:

    you can change this code according your requirements.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 21, 2013 1:39 AM
  • User325473304 posted

    You have to code in save button click event:

    Call these function:

    Protected Sub Button1_Click(ByVal sender As ObjectByVal e As System.EventArgs) Handles Button1.Click

    {

    storeddlValues()

    showDataInGridView();

    }

    private void storeddlValues()

    {

    Dim Sqlstr as string
    Dim connectionString as String = "give you connection string here"


    Sqlstr="Insert Into tempTable(ddl1 ,ddl2,ddl3,ddl4)
    Values (@ddl1, @ddl2, @ddl3, @ddl4)"
    Try


    Using connection As New SqlConnection(connectionString)
    Dim cmdInsert As New SqlCommand(Sqlstr, connection)
    cmdInsert.Parameters.Add("@ddl1",Data.SqlDbType.nvarchar).value=ddl1.SelectedValue
    cmdInsert.Parameters.Add("@ddl2",Data.SqlDbType.nvarchar).value=ddl2.SelectedValue
    cmdInsert.Parameters.Add("@ddl3",Data.SqlDbType.nvarchar).value=ddl3.SelectedValue
    cmdInsert.Parameters.Add("@ddl4",Data.SqlDbType.nvarchar).value=ddl4.SelectedValue
    connection.Open()
    cmdInsert.ExecuteNonQuery()
    End Using


    Catch Ex As Exception


    End Try

    }

    private void showDataInGridView()

    {

    Dim connStr As String
    connStr = _
    ConfigurationManager.ConnectionStrings("Your_Connection_String_Name").ConnectionString
    Dim cmd = New SqlCommand()
    cmd.CommandType = CommandType.StoredProcedure
    Try
    cmd.Connection.Open()
    gridName.DataSource = cmd.ExecuteReader()
    gridName.DataBind()

    cmd.Connection.Close()
    cmd.Connection.Dispose()
    Catch ex As Exception
    lblStatus.Text = ex.Message

    End Try

    }

    NOTE:

    you can change this code according your requirements.

     

     

    Thank you very much for responding! I'll go try this and get back to you for the results.

    Sunday, July 21, 2013 1:53 AM
  • User325473304 posted

    Thanks mshoaiblibra for the help! I'll try this first and get back to you for the results.

    Sunday, July 21, 2013 1:56 AM
  • User325473304 posted

    You have to code in save button click event:

    Call these function:

    Protected Sub Button1_Click(ByVal sender As ObjectByVal e As System.EventArgs) Handles Button1.Click

    {

    storeddlValues()

    showDataInGridView();

    }

    private void storeddlValues()

    {

    Dim Sqlstr as string
    Dim connectionString as String = "give you connection string here"


    Sqlstr="Insert Into tempTable(ddl1 ,ddl2,ddl3,ddl4)
    Values (@ddl1, @ddl2, @ddl3, @ddl4)"
    Try


    Using connection As New SqlConnection(connectionString)
    Dim cmdInsert As New SqlCommand(Sqlstr, connection)
    cmdInsert.Parameters.Add("@ddl1",Data.SqlDbType.nvarchar).value=ddl1.SelectedValue
    cmdInsert.Parameters.Add("@ddl2",Data.SqlDbType.nvarchar).value=ddl2.SelectedValue
    cmdInsert.Parameters.Add("@ddl3",Data.SqlDbType.nvarchar).value=ddl3.SelectedValue
    cmdInsert.Parameters.Add("@ddl4",Data.SqlDbType.nvarchar).value=ddl4.SelectedValue
    connection.Open()
    cmdInsert.ExecuteNonQuery()
    End Using


    Catch Ex As Exception


    End Try

    }

    private void showDataInGridView()

    {

    Dim connStr As String
    connStr = _
    ConfigurationManager.ConnectionStrings("Your_Connection_String_Name").ConnectionString
    Dim cmd = New SqlCommand()
    cmd.CommandType = CommandType.StoredProcedure
    Try
    cmd.Connection.Open()
    gridName.DataSource = cmd.ExecuteReader()
    gridName.DataBind()

    cmd.Connection.Close()
    cmd.Connection.Dispose()
    Catch ex As Exception
    lblStatus.Text = ex.Message

    End Try

    }

    NOTE:

    you can change this code according your requirements.

     

    Hi, I've encountered minor errors upon code change and fixed it, here's what my code looks like now:

    Private Sub storeddlValues()
    
            Dim conn As SqlConnection
            Dim comm As SqlCommand
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("MySqlServer").ConnectionString
    
            conn = New SqlConnection(connectionString)
            comm = New SqlCommand()
    
            comm.Connection = conn
            comm.CommandType = System.Data.CommandType.StoredProcedure
            comm.CommandText = "SaveTempRecipients"
    
            Try
    
                Using connection As New SqlConnection(connectionString)
                    comm.Parameters.Add("@EmployeeCode", Data.SqlDbType.NVarChar).Value = ddlEmployee.SelectedValue
                    comm.Parameters.Add("@Position", Data.SqlDbType.NVarChar).Value = ddlPosition.SelectedValue
                    comm.Parameters.Add("@Department", Data.SqlDbType.NVarChar).Value = ddlDepartment.SelectedValue
                    comm.Parameters.Add("@CostCenter", Data.SqlDbType.NVarChar).Value = ddlCostCenter.SelectedValue
                    conn.Open()
                    comm.ExecuteNonQuery()
                    conn.Close()
                End Using
    
            Catch Ex As Exception
                
            End Try
    
        End Sub

    But then I can't fix this error: Procedure or function expects a parameter which was not supplied.

     

    Sunday, July 21, 2013 4:46 AM
  • User516094431 posted

    My friend This "SaveTempRecipients" parameters are not match. Means sp name prameters name and count both should be same.

    "SaveTempRecipients" SP parameter name should be,

    @EmployeeCode

    @Position

    @Department

    @CostCenter


    I Think you got it. 

    For More detail about calling SP click HERE

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 21, 2013 5:10 AM
  • User325473304 posted

    mshoaiblibra

    My friend This "SaveTempRecipients" parameters are not match. Means sp name prameters name and count both should be same.

    "SaveTempRecipients" SP parameter name should be,

    @EmployeeCode

    @Position

    @Department

    @CostCenter


    I Think you got it. 

    For More detail about calling SP click HERE

     

    Thanks! I've included CompanyCode for the mean time. I almost got it, but then I encountered an object ref issue in the middle. It doesn't really point out which part went wrong. Here's my complete code:

    Protected Sub Add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Add.Click
            Me.storeddlValues()
            Me.BindGrid()
        End Sub
    
    
        Private Sub storeddlValues()
    
            Dim conn As SqlConnection
            Dim comm As SqlCommand
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("MySqlServer").ConnectionString
    
            conn = New SqlConnection(connectionString)
            comm = New SqlCommand()
    
            comm.Connection = conn
            comm.CommandType = System.Data.CommandType.StoredProcedure
            comm.CommandText = "SaveTempRecipients"
    
            Try
    
                Using connection As New SqlConnection(connectionString)
                    comm.Parameters.Add("@CompanyCode", Data.SqlDbType.Int).Value = CInt(txtCompany.Text)
                    comm.Parameters.Add("@EmployeeCode", Data.SqlDbType.NVarChar).Value = ddlEmployee.SelectedValue
                    comm.Parameters.Add("@Position", Data.SqlDbType.NVarChar).Value = ddlPosition.SelectedValue
                    comm.Parameters.Add("@DepartmendId", Data.SqlDbType.Int).Value = CInt(ddlDepartment.SelectedValue)
                    comm.Parameters.Add("@CostCenterCode", Data.SqlDbType.NVarChar).Value = ddlCostCenter.SelectedValue
                    comm.Parameters.AddWithValue("@Action", "ok")
                    conn.Open()
                    comm.ExecuteNonQuery()
                    conn.Close()
                End Using
    
            Catch Ex As Exception
                UIPageHelpers.ShowMessage(Master, Ex.Message, True)
    
            End Try
    
        End Sub
    
        Private Sub BindGrid()
            Dim conn2 As SqlConnection
            Dim cmd As SqlCommand
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("MySqlServer").ConnectionString
    
            conn2 = New SqlConnection(connectionString)
            cmd = New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            Try
                cmd.Connection.Open()
                gvRecipients.DataSource = cmd.ExecuteReader()
                gvRecipients.DataBind()
    
                cmd.Connection.Close()
                cmd.Connection.Dispose()
            Catch ex As Exception
                UIPageHelpers.ShowMessage(Master, ex.Message, True)
    
            End Try
    
    
        End Sub

    I'm currently doing my research too, I hope you have some comments on this. Thank you my friend for the patience.

    Sunday, July 21, 2013 7:52 AM
  • User516094431 posted

    Use below line of code in try catch:

    Try

    comm.Parameters.Add("@CompanyCode", Data.SqlDbType.Int).Value = CInt(txtCompany.Text)
    comm.Parameters.Add("@EmployeeCode", Data.SqlDbType.NVarChar).Value = ddlEmployee.SelectedValue
    comm.Parameters.Add("@Position", Data.SqlDbType.NVarChar).Value = ddlPosition.SelectedValue
    comm.Parameters.Add("@DepartmendId", Data.SqlDbType.Int).Value = CInt(ddlDepartment.SelectedValue)
    comm.Parameters.Add("@CostCenterCode", Data.SqlDbType.NVarChar).Value = ddlCostCenter.SelectedValue
    comm.Parameters.AddWithValue("@Action", "ok")
    conn.Open()
    comm.ExecuteNonQuery()
    conn.Close()
    Catch Ex As Exception

    Note:

    Friend, Which post are help for you kindly "Marks as Answer". It's help for others.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2013 12:26 AM
  • User325473304 posted

    I've marked them, thanks for the reminder! :D

    Here's my code so far:

    Protected Sub Add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Add.Click
            Me.storeddlValues()
            Me.BindGrid()
        End Sub
    
    
        Private Sub storeddlValues()
    
            Dim conn As SqlConnection
            Dim comm As SqlCommand
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("MySqlServer").ConnectionString
    
            conn = New SqlConnection(connectionString)
            comm = New SqlCommand()
    
            comm.Connection = conn
            comm.CommandType = System.Data.CommandType.StoredProcedure
            comm.CommandText = "SaveTempRecipients"
    
            Try
    
                Using connection As New SqlConnection(connectionString)
                    comm.Parameters.Add("@CompanyCode", Data.SqlDbType.Int).Value = CInt(txtCompany.Text)
                    comm.Parameters.Add("@EmployeeCode", Data.SqlDbType.NVarChar).Value = ddlEmployee.SelectedValue
                    comm.Parameters.Add("@Position", Data.SqlDbType.NVarChar).Value = ddlPosition.SelectedValue
                    comm.Parameters.Add("@DepartmendId", Data.SqlDbType.Int).Value = CInt(ddlDepartment.SelectedValue)
                    comm.Parameters.Add("@CostCenterCode", Data.SqlDbType.NVarChar).Value = ddlCostCenter.SelectedValue
                    comm.Parameters.Add("@Action".ToString, Data.SqlDbType.NVarChar).Value = Label1.Text
                    conn.Open()
                    comm.ExecuteNonQuery()
                    conn.Close()
                End Using
    
            Catch Ex As Exception
                UIPageHelpers.ShowMessage(Master, Ex.Message, True)
    
            End Try
    
        End Sub
    
        Private Sub BindGrid()
            Dim conn2 As SqlConnection
            Dim cmd As SqlCommand
            Dim adp As SqlDataAdapter
            Dim ds As DataSet
            Dim str As String
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("MySqlServer").ConnectionString
    
            conn2 = New SqlConnection(connectionString)
            str = "Select * from TempRecipient"
            cmd = New SqlCommand(str, conn2)
            adp = New SqlDataAdapter(cmd)
            ds = New DataSet()
    
            Try
                cmd.Connection.Open()
                adp.Fill(ds)
                gv.DataSource = ds
                gv.DataBind()
                cmd.Connection.Close()
                cmd.Connection.Dispose()
    
            Catch ex As Exception
                UIPageHelpers.ShowMessage(Master, ex.Message, True)
    
            End Try
    
    
        End Sub

    Everything's working well, but them my gridview does not show up. No errors or anything suspicious, and I've already checked my table it's already filled (thank you again for that!) but the gridview is not showing up. Can you all teach me how to do that? :(

     

    Monday, July 22, 2013 1:34 AM
  • User516094431 posted

    Thanks for this, It's my pleasure friend.

    Actually ds contain mutiple Datatable, so you have to tell about table count to Gridview. 

     Try
                cmd.Connection.Open()
                adp.Fill(ds)
                gv.DataSource = ds
                gv.DataBind()
                cmd.Connection.Close()
                cmd.Connection.Dispose()

    you have to use 

    gv.DataSource = ds.Table[0]
    Monday, July 22, 2013 4:22 AM