[RESOLVED] Manually saving DataGrid

[Roofuss]

Hi

Is it possible to manually save data from my dataGridView to my SQL server database table?

if it is possible can some please tell me how to do it because i don't like using the data sets and reader VB has because it doesn't give me full control over the procedure.

please see the code below it all works fine its just the last two line im struggling with, which is where i am trying to save the data from the two columns in my DataGridView

            Try
                mySqlCommand.CommandText = "INSERT INTO quin (Type, DateRaised, Status, " _
                & "Company, Address, Postcode, Support, TasksCompleted, HoursAllocated, HoursUsed, OverLim, HourlyRate, " _
                & "ExtendedCost, MonthlySupport, Vat, Total, qutask, qucost) " _
                & " VALUES " _
                & "(@Type, @DateRaised, @Status, " _
                & "@Company, @Address, @Postcode, @Support, @TasksCompleted, @HoursAllocated, @HoursUsed, " _
                & "@OverLim, @HourlyRate, @ExtendedCost, @MonthlySupport, @Vat, @Total, @Qutask, @Qucost)"
                mySqlCommand.Parameters.AddWithValue("@Type", cbType.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@DateRaised", tbDateRaised.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Status", cbStatus.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Company", cbCompany.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Address", tbAddress.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@PostCode", tbPostCode.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Support", tbSupport.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@TasksCompleted", lvTasksComp.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@HoursAllocated", tbHoursAlo.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@HoursUsed", tbHoursUsed.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@OverLim", tbOverLim.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@HourlyRate", tbHourRate.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@ExtendedCost", tbExtended.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@MonthlySupport", tbMonthCharge.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Vat", cbVat.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Total", tbTotal.Text.Trim)
                mySqlCommand.Parameters.AddWithValue("@Qutask", ??)
                mySqlCommand.Parameters.AddWithValue("@Qucost", ??)

Thanks in Advance

[CoachBarker]

Instead of using the insert query, why not call a stored procedure instead?

[Roofuss]

Instead of using the insert query, why not call a stored procedure instead?

Can you give me an example of the stored procedure i would use to insert the details from my datagridView into my SQL table?

[CoachBarker]

Do you know how to write a SPROC in SQL Server? I have to run off but will check back later, and if you haven't figured it out I will post an example of the SPORC and the code to run it in VS.

[Roofuss]

sorry no i dont ive never called a procedure from SQL before i usually just write the statements in the vb code.

An example would be great. ill look forward to hearing from you later

thanks coach

[CoachBarker]

Heres what I cam up with:
In SQL Server Management Studio, select the database you are using, go down to Programmibility to expand it, right click on StoredProcedures and select NewStoredProcedure. Select all the text in the NewStoredProcedure page and delete it, then copy and paste all the red text into the page. Go to the top and click on Execute. Close the Programmibility folder and click on Refresh, you should now have a StoredProcedure in the StoreProcedure’s folder called insertIntoQuin.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[insertIntoQuin]
--	Add the parameters for the stored procedure here
--	Replace the variable type in this section with the type
--	they actually are, I did not know the type so I made them all the same	
	@Type1	varchar(50), 
	@DateRaised	varchar(50), 
	@Status1 	varchar(50), 
	@Company	varchar(50), 
	@Address1	 varchar(50), 
	@Postcode	varchar(50), 
	@Support	varchar(50), 
	@TasksCompleted	varchar(50), 
	@HoursAllocated	varchar(50),
	@HoursUsed	varchar(50), 
	@OverLim	varchar(50), 
	@HourlyRate	varchar(50), 
	@ExtendedCost	varchar(50), 
	@MonthlySupport	varchar(50), 
	@Vat	varchar(50), 
	@Total	varchar(50), 
	@qutask	varchar(50),
	@qucost	varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for procedure here
	-- Type, Status and Address are reserved words rename them
INSERT INTO quin
	(Type1, 
	DateRaised, 
	Status1, 
	Company, 
	Address1, 
	Postcode, 
	Support, 
	TasksCompleted, 
	HoursAllocated,
	HoursUsed, 
	OverLim, 
	HourlyRate, 
	ExtendedCost, 
	MonthlySupport, 
	Vat, 
	Total, 
	qutask,
	qucost)
Values
	(@Type1, 
	@DateRaised, 
	@Status1, 
	@Company, 
	@Address1, 
	@PostCode, 
	@Support, 
	@TasksCompleted, 
	@HoursAllocated,
	@HoursUsed, 
	@OverLim, 
	@HourlyRate, 
	@ExtendedCost, 
	@MonthlySupport, 
	@Vat, 
	@Total, 
	@qutask,
	@qucost)
END
GO

It should look something like this:

USE [TestDataBase]-- name of database goes here
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insertIntoQuin]
	-- Add the parameters for the stored procedure here
	@Type1		varchar(50), 
	@DateRaised	varchar(50), 
	@Status1 	varchar(50), 
	@Company	varchar(50), 
	@Address1	 varchar(50), 
	@Postcode	varchar(50), 
	@Support	varchar(50), 
	@TasksCompleted	varchar(50), 
	@HoursAllocated	varchar(50),
	@HoursUsed	varchar(50), 
	@OverLim	varchar(50), 
	@HourlyRate	varchar(50), 
	@ExtendedCost	varchar(50), 
	@MonthlySupport	varchar(50), 
	@Vat	varchar(50), 
	@Total	varchar(50), 
	@qutask	varchar(50),
	@qucost	varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for procedure here
	-- Type, Status and Address are reserved words rename them
INSERT INTO quin
	(Type1, 
	DateRaised, 
	Status1, 
	Company, 
	Address1, 
	Postcode, 
	Support, 
	TasksCompleted, 
	HoursAllocated,
	HoursUsed, 
	OverLim, 
	HourlyRate, 
	ExtendedCost, 
	MonthlySupport, 
	Vat, 
	Total, 
	qutask,
	qucost)
Values
	(@Type1, 
	@DateRaised, 
	@Status1, 
	@Company, 
	@Address1, 
	@PostCode, 
	@Support, 
	@TasksCompleted, 
	@HoursAllocated,
	@HoursUsed, 
	@OverLim, 
	@HourlyRate, 
	@ExtendedCost, 
	@MonthlySupport, 
	@Vat, 
	@Total, 
	@qutask,
	@qucost)
END

In Visual Studio I made an application with 18 text boxes and labels (I used all textboxes because I had no idea what your combo boxes were), and 2 buttons, btnAddRecord and btnExit. I created a database connection to my local SQL Server 2005 database called TestDataBase, added data to the text boxes and clicked Add Record. Mine looks like this:

Option Explicit On
Option Strict On
Imports System.Data.SqlClient

Public Class Form1
    Private Sub btnAddRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddMyRecord.Click
        Try
            Dim SqlConn As New SqlConnection("Data Source=BARKER44\SQLEXPRESS;Initial Catalog=TestDataBase;Integrated Security=True")
            SqlConn.Open()
            Dim mySqlCommand As SqlCommand
            mySqlCommand = New SqlCommand("insertIntoQuin", SqlConn)
            mySqlCommand.CommandType = CommandType.StoredProcedure

            mySqlCommand.Parameters.AddWithValue("@Type1", Me.TextBox1.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@DateRaised", Me.TextBox2.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Status1", Me.TextBox3.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Company", Me.TextBox4.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Address1", Me.TextBox5.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@PostCode", Me.TextBox6.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Support", Me.TextBox7.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@TasksCompleted", Me.TextBox8.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@HoursAllocated", Me.TextBox9.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@HoursUsed", Me.TextBox10.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@OverLim", Me.TextBox11.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@HourlyRate", Me.TextBox12.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@ExtendedCost", Me.TextBox13.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@MonthlySupport", Me.TextBox14.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Vat", Me.TextBox15.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Total", Me.TextBox16.Text.Trim)
            'What control are these two parameters based on?
            mySqlCommand.Parameters.AddWithValue("@Qutask", Me.TextBox17.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Qucost", Me.TextBox18.Text.Trim)

            If mySqlCommand.ExecuteNonQuery() = 0 Then
                MessageBox.Show("No records were inserted table Quin ,Try again", "Example")
            Else
                MessageBox.Show("Records inserted into table Quin successfully", "Example")
            End If
            SqlConn.Close()
        Catch sqlex As SqlException
            MessageBox.Show("Error in btnAddRecord: " & sqlex.ToString())
        End Try
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

But yours should look something like this based on your controls you had listed:

Try
            SqlConn.Open() 'obviously is the database connection
            Dim mySqlCommand As SqlCommand
            mySqlCommand = New SqlCommand("insertIntoQuin", SqlConn)
            mySqlCommand.CommandType = CommandType.StoredProcedure

            mySqlCommand.Parameters.AddWithValue("@Type", cbType.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@DateRaised", tbDateRaised.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Status", cbStatus.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Company", cbCompany.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Address", tbAddress.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@PostCode", tbPostCode.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Support", tbSupport.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@TasksCompleted", lvTasksComp.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@HoursAllocated", tbHoursAlo.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@HoursUsed", tbHoursUsed.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@OverLim", tbOverLim.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@HourlyRate", tbHourRate.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@ExtendedCost", tbExtended.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@MonthlySupport", tbMonthCharge.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Vat", cbVat.Text.Trim)
            mySqlCommand.Parameters.AddWithValue("@Total", tbTotal.Text.Trim)
            'What control are these two parameters based on?
            mySqlCommand.Parameters.AddWithValue("@Qutask", ??)
            mySqlCommand.Parameters.AddWithValue("@Qucost", ??)

            If mySqlCommand.ExecuteNonQuery() = 0 Then
                MessageBox.Show("No records were inserted into table Quin ,Try again", "Example")
            Else
                MessageBox.Show("Records inserted into table Quin successfully", "Example")
            End If
            SqlConn.Close()
        Catch sqlex As SqlException
            MessageBox.Show("Error in btnAddRecord: " & sqlex.ToString())
        End Try

Let me know if it works out for you. If I missed something I am sure someone point it out or will correct it

[Roofuss]

Thanks coach this looks great, in answer to your question on the two parameters these two

           'What control are these two parameters based on?
            mySqlCommand.Parameters.AddWithValue("@Qutask", ??)
            mySqlCommand.Parameters.AddWithValue("@Qucost", ??)

these two parameters are based on two columns in a dataGridView that is on my form. so how would i reference these? basically i have one dataGridView called dgv that has two columns one called colTasks and one called colCost and the data in these columns is what i need to save along with everything you provided above which i great


Thanks
Roo

[CoachBarker]

How are you filling the DGV and are you only displaying 1 row of data or multiple rows?

[Roofuss]

im displaying mulitple rows and i haven't thought about the fill yet i was hoping to get the save procedure working then moving onto filling it after that.

thnaks

[CoachBarker]

This:

'What control are these two parameters based on?
mySqlCommand.Parameters.AddWithValue("@Qutask", Me.DataGridView1.Rows(0).Cells("colTasks").Value.ToString)
mySqlCommand.Parameters.AddWithValue("@Qucost", Me.DataGridView1.Rows(0).Cells("colCost").Value.ToString)

will set the value for the columns if it is row 0, I am not sure how you are planning to select the row though. If I add a DGV and a binding source to my form load event it does add the values from the 2 colums to the table:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim dt As New DataTable
    dt.Columns.Add("colNumber", GetType(Integer)).AutoIncrement = True
    dt.Columns.Add("colTasks", GetType(String))
    dt.Columns.Add("colCost", GetType(String))

    dt.Rows.Add(Nothing, "Clean", "20.00")
    dt.Rows.Add(Nothing, "Sweep", "10.00")
    dt.Rows.Add(Nothing, "Dust", "15.00")

    Me.BindingSource1.DataSource = dt
    Me.DataGridView1.DataSource = Me.BindingSource1
End Sub

[Roofuss]

[QUOTE=CoachBarker;3607556]This:

'What control are these two parameters based on?
mySqlCommand.Parameters.AddWithValue("@Qutask", Me.DataGridView1.Rows(0).Cells("colTasks").Value.ToString)
mySqlCommand.Parameters.AddWithValue("@Qucost", Me.DataGridView1.Rows(0).Cells("colCost").Value.ToString)

Hi Coach, this is good im getting somewhere with the above but like you said it only saves the content on the first row and nothing else.

is there anyway to make it save all the rows. or will i have to make a completely separate table for these 2 columns?

[CoachBarker]

Are you filling the DGV with all the records that you have controls for, if not, what data is in the DGV?

[Roofuss]

Are you filling the DGV with all the records that you have controls for, if not, what data is in the DGV?

Hi Coach no im not filling the DGV with all the records i have controls for. i am using the DGV as its own control on the form. the only data that is going into the DGV is the 2 columns from my quin table "Qutask" & "Qucost" so the most columns the DGV will ever have is 2 but it could have multiple rows.

Thanks
Roo

[CoachBarker]

I assume you will be using the values in the same row then, so in the properties window for the DGV in the SelectionMode, set it to FullRowSelect. Change the properties window to Events(Lightning Bolt) and double click in the RowEnter Event. It will add the event.

Private Sub DataGridView1_RowEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowEnter
    If Me.DataGridView1.Focused = True Then
        Me.colTasks = Me.DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString()
        Me.colCost = Me.DataGridView1.Rows(e.RowIndex).Cells(1).Value.ToString()
    End If
End Sub

Create 2 form level variables:

Public Class Form1
    ' declare 2 form level variables for the 2 columns in the dgv
    ' if you are not doing any calculations on cost leave it as a string
    Public colTasks As String
    Public colCost As String

Add this code to the bottom of your button click, replacing what is there:

        'What control are these two parameters based on?   
        mySqlCommand.Parameters.AddWithValue("@Qutask", Me.colTasks)
        mySqlCommand.Parameters.AddWithValue("@Qucost", Me.colCost)

        If mySqlCommand.ExecuteNonQuery() = 0 Then
            MessageBox.Show("No records were inserted table Quin ,Try again", "Example")
        Else
            MessageBox.Show("Records inserted into table Quin successfully", "Example")
        End If
        SqlConn.Close()
        ' after using the variables they need to be cleared
        Me.colTasks = String.Empty
        Me.colCost = String.Empty
    Catch sqlex As SqlException
        MessageBox.Show("Error in btnAddRecord: " & sqlex.ToString())
    End Try

Let me know how it works.

[Roofuss]

cheers coach that has worked great thanks for all your help

[CoachBarker]

Glad I could help, anymore problems let me know and I'll see what I can do