DataGrid Data To be Passed on to a SQL Stored Proc and the data to be saved in a table

Question

0.00/5 (No votes)

See more:

Hi There,

This is what I am doing, I have a Form in which I have some textboxes and combo boxes, the data from them is moved to the datagrid and the main thing that I want to do is the data from the datagrid needs to be passed to a SQL stored proc which will push the data to the table.
Please let me know how easily or the best way to accomplish this.

Thanks for all the help

Farman

Posted 25-Apr-13 8:01am

FARMAN786

Add a Solution

Comments

ZurdoDev 25-Apr-13 14:07pm

You'll need to share relevant code. There are many, many ways to do this.

FARMAN786 25-Apr-13 17:45pm

Ryan,

I have submitted the code. let me know what you think is the best.

Thanks a lot

FARMAN786 25-Apr-13 17:46pm

Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
DtGridView.ColumnCount = 7
DtGridView.ColumnHeadersVisible = True

' Set the column header style.
Dim columnHeaderStyle As New DataGridViewCellStyle()

columnHeaderStyle.BackColor = Color.Beige
columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
columnHeaderStyle.WrapMode = DataGridViewTriState.True
DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

' Set the column header names.
DtGridView.Columns(0).Name = "Item Number"
DtGridView.Columns(1).Name = "Vendor"
DtGridView.Columns(2).Name = "Size"
DtGridView.Columns(3).Name = "Item Description"
DtGridView.Columns(4).Name = "Unit Of Measure"
DtGridView.Columns(5).Name = "Item Class"
DtGridView.Columns(6).Name = "Current Cost"

' Populate the rows.
DtGridView.Rows(0).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0551"
DtGridView.Rows(0).Cells(1).Value = cboVendor.DisplayMember = "DisplayName"
DtGridView.Rows(0).Cells(2).Value = CboSize.Text
DtGridView.Rows(0).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows(0).Cells(4).Value = CboUOM.Text
DtGridView.Rows(0).Cells(5).Value = CboItmClass.Text
DtGridView.Rows(0).Cells(6).Value = TxtCurrCost.Text

1 solution

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)

eparthibanmca · Answer 1 · 2013-04-25T18:45:00

Solution 2

update the value to dataTable then pass the datatable to stored procedure for this you may create the TYPE in db side.
its simple

Posted 25-Apr-13 18:45pm

eparthibanmca

Comments

FARMAN786 26-Apr-13 13:19pm

Currently this is what I am doing in order to store the text box values to Datagrid. Do i need to define a datatable and then re do this or what.Please let me know.

<pre lang="vb">Private Sub BtnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddRows.Click
DtGridView.ColumnCount = 7
DtGridView.ColumnHeadersVisible = True

' Set the column header style.
Dim columnHeaderStyle As New DataGridViewCellStyle()

columnHeaderStyle.BackColor = Color.Beige
columnHeaderStyle.Font = New Font("Verdana", 7, FontStyle.Bold)
columnHeaderStyle.WrapMode = DataGridViewTriState.True
DtGridView.ColumnHeadersDefaultCellStyle = columnHeaderStyle

' Set the column header names.
DtGridView.Columns(0).Name = "Item Number"
DtGridView.Columns(1).Name = "Vendor"
DtGridView.Columns(2).Name = "Size"
DtGridView.Columns(3).Name = "Item Description"
DtGridView.Columns(4).Name = "Unit Of Measure"
DtGridView.Columns(5).Name = "Item Class"
DtGridView.Columns(6).Name = "Current Cost"

If CboSize.Text = "Shoes" Then
DtGridView.ClearSelection()
Dim rowNum As Integer = DtGridView.Rows.Add()
Dim Str As String
Dim VendID As String
Str = cboVendor.Text
VendID = Str.Substring(0, 5)
DtGridView.Rows.Item(rowNum).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0551"
DtGridView.Rows.Item(rowNum).Cells(1).Value = VendID
DtGridView.Rows.Item(rowNum).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rowNum).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rowNum).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rowNum).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rowNum).Cells(6).Value = TxtCurrCost.Text

Dim rownum1 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum1).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0601"
DtGridView.Rows.Item(rownum1).Cells(1).Value = VendID
DtGridView.Rows.Item(rownum1).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum1).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum1).Cells(4).Value = CboUOM.Text
DtGridView.Rows.Item(rownum1).Cells(5).Value = CboItmClass.Text
DtGridView.Rows.Item(rownum1).Cells(6).Value = TxtCurrCost.Text

Dim rownum2 As Integer = DtGridView.Rows.Add()
DtGridView.Rows.Item(rownum2).Cells(0).Value = String.Join("-", New String() {CType(cboVendor.SelectedValue, DataRowView)("VENDORSKUCODE").ToString, Me.txtStyle.Text, CType(cboPrimaryColor.SelectedValue, DataRowView)("ColorNumber").ToString & CType(cboSecondaryColor.SelectedValue, DataRowView)("ColorNumber").ToString}) + "-" + "0651"
DtGridView.Rows.Item(rownum2).Cells(1).Value = VendID
DtGridView.Rows.Item(rownum2).Cells(2).Value = CboSize.Text
DtGridView.Rows.Item(rownum2).Cells(3).Value = TxtItmDesc.Text
DtGridView.Rows.Item(rownum2).Cells(4).Value = CboU

FARMAN786 26-Apr-13 14:20pm

Any Example code will be highly appreciated.

FARMAN786 29-Apr-13 15:03pm

Hi There,

I went further ahead and was able to get the results but not fully. I am unable to insert all the rows from the Grid to the database, except one and also it shows me an error "The Connection was not closed. The Connection's current state is open", please see the code below and let me know how it can be corrected, all the help appreciated.

Private Sub BtnSendToGP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSendToGP.Click
Dim VarItemNumber As String
Dim VarVendor As String
Dim VarItemDescription As String
Dim VarUnitOfMeasure As String
Dim VarItemClass As String
Dim Conn As SqlConnection

VarItemNumber = Nothing
VarVendor = Nothing
VarItemDescription = Nothing
VarUnitOfMeasure = Nothing
VarItemClass = Nothing

Conn = New SqlConnection("Server = gpdb02; Initial Catalog=XXX; User=XX; Password=XXXXXXX" & "Data Source=Dynamics;Integrated Security=SSPI;")

For cn As Integer = 0 To DtGridView.RowCount - 1
VarItemNumber = DtGridView(0, cn).Value.ToString
VarVendor = DtGridView(1, cn).Value.ToString
VarItemDescription = DtGridView(3, cn).Value.ToString
VarUnitOfMeasure = DtGridView(4, cn).Value.ToString
VarItemClass = DtGridView(5, cn).Value.ToString

Dim myCommand As SqlCommand = New SqlCommand("dbo.taUpdateCreateItemRcd", Conn)
Conn.Open()
myCommand.CommandType = Data.CommandType.StoredProcedure
myCommand.Parameters.AddWithValue("@I_vITEMNMBR", VarItemNumber)
myCommand.Parameters.AddWithValue("@I_vITEMTYPE", 1)
myCommand.Parameters.AddWithValue("@I_vVCTNMTHD", 1)
myCommand.Parameters.AddWithValue("@I_vTAXOPTNS", 2)
myCommand.Parameters.AddWithValue("@I_vDECPLQTY", 1)
myCommand.Parameters.AddWithValue("@I_vDECPLCUR", 3)
myCommand.Parameters.AddWithValue("@I_vPurchase_Tax_Options", 2)
myCommand.Parameters.AddWithValue("@I_vKPCALHST", 1)
myCommand.Parameters.AddWithValue("@I_vKPERHIST", 1)
myCommand.Parameters.AddWithValue("@I_vKPTRXHST", 1)
myCommand.Parameters.AddWithValue("@I_vKPDSTHST", 1)
myCommand.Parameters.AddWithValue("@I_vPRICMTHD", 1)
myCommand.Parameters.AddWithValue("@I_vPriceGroup", "DEFAULT".ToString)
myCommand.Parameters.AddWithValue("@I_vUseItemClass", 1)
'myCommand.Parameters.AddWithValue("@VarVendor", VarVendor)
myCommand.Parameters.AddWithValue("@I_vITEMDESC", VarItemDescription)
myCommand.Parameters.AddWithValue("@I_vUOMSCHDL", VarUnitOfMeasure)
myCommand.Parameters.AddWithValue("@I_vITMCLSCD", VarItemClass)
myCommand.Parameters.AddWithValue("@O_iErrorState", 0)
myCommand.Parameters.AddWithValue("@oErrString", 0)
myCommand.ExecuteNonQuery().ToString()
Next
Conn.Close()
MsgBox("All The Records Added To GP Successfully", MsgBoxStyle.Information)

End Sub