DataGridView Questions/Problems

[=?Utf-8?B?dGVfYnV0dHM=?=]

Using VS2005, and SQL 2005 Express.

Here is the situation:
SQL
- 1 Table called Products
- First Column called ProductsID (Uniqueidentifi er)(newid()) - So it adds
new UI when new row is inserted.
- Second column called ItemCode(NVarCh ar)

VB Form
DataGridView called ProductsDGV

When i created the ProductsDGV it created the BindingDataSour ce,
TableAdapter, and DataSet. As I went through the wizard it says that SELECT,
INSERT, UPDATE, AND DELETE commands are also created.

Problem:
When i have buttons to INSERT, UPDATE, AND DELETE, the database changes
perfectly. When i add the same code that i have for the INSERT button to an
event of the ProductsDGV like RowLeave i get the error "The parameterized
query ('@ProductID ...)' which was not supplied.

Here is my code in the RowLeave Event of the ProductsDGV:

Dim cnxn As New Data.SqlClient. SqlConnection
Dim cmd As New Data.SqlClient. SqlCommand

cnxn.Connection String = "Data Source=ADDLMNAR WP13\SQLEXPRESS ;Initial
Catalog=myDatab ase;Integrated Security=True"
cnxn.Open()
cmd.Connection = cnxn

With cmd.Parameters
.Add(New Data.SqlClient. SqlParameter("P roductID", ProductsID))
.Add(New Data.SqlClient. SqlParameter("I temCode",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("D escription",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("C ost", SqlDbType.NVarC har))
End With

cmd.CommandText = "INSERT INTO
dbo.Products(Pr oductsID,ItemCo de,Description, Cost) VALUES
(@ProductsID,@I temCode,@Descri ption,@Cost)"

cmd.ExecuteNonQ uery()
cnxn.Close()

Me.ProductsTabl eAdapter.Fill(M e.ProductsDataS et.Products)

Thanks for the help!

[Cor Ligthert[MVP]]

Hi,

.Add(New Data.SqlClient. SqlParameter("@ ProductID", ProductsID))
etc,

But why do you not simple use

\\\
Me.ProductTable Adapter.Update( Me.ProductsData set.Products)
me.ProductsData Set.Products.Cl ear
Me.ProductsTabl eAdapter.Fill(M e.ProductsDataS et.Products)
///

Cor
"te_butts" <te*****@discus sions.microsoft .comschreef in bericht
news:96******** *************** ***********@mic rosoft.com...

Using VS2005, and SQL 2005 Express.

Here is the situation:
SQL
- 1 Table called Products
- First Column called ProductsID (Uniqueidentifi er)(newid()) - So it adds
new UI when new row is inserted.
- Second column called ItemCode(NVarCh ar)

VB Form
DataGridView called ProductsDGV

When i created the ProductsDGV it created the BindingDataSour ce,
TableAdapter, and DataSet. As I went through the wizard it says that
SELECT,
INSERT, UPDATE, AND DELETE commands are also created.

Problem:
When i have buttons to INSERT, UPDATE, AND DELETE, the database changes
perfectly. When i add the same code that i have for the INSERT button to
an
event of the ProductsDGV like RowLeave i get the error "The parameterized
query ('@ProductID ...)' which was not supplied.

Here is my code in the RowLeave Event of the ProductsDGV:

Dim cnxn As New Data.SqlClient. SqlConnection
Dim cmd As New Data.SqlClient. SqlCommand

cnxn.Connection String = "Data
Source=ADDLMNAR WP13\SQLEXPRESS ;Initial
Catalog=myDatab ase;Integrated Security=True"
cnxn.Open()
cmd.Connection = cnxn

With cmd.Parameters
.Add(New Data.SqlClient. SqlParameter("P roductID", ProductsID))
.Add(New Data.SqlClient. SqlParameter("I temCode",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("D escription",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("C ost",
SqlDbType.NVarC har))
End With

cmd.CommandText = "INSERT INTO
dbo.Products(Pr oductsID,ItemCo de,Description, Cost) VALUES
(@ProductsID,@I temCode,@Descri ption,@Cost)"

cmd.ExecuteNonQ uery()
cnxn.Close()

Me.ProductsTabl eAdapter.Fill(M e.ProductsDataS et.Products)

Thanks for the help!

[=?Utf-8?B?dGVfYnV0dHM=?=]

I have done that and i get different errors all over the place.

Here is what i did to fix the problem.

In the database:
Changed the ProductID to INT, and made Is Identity = yes.
Made ProductID a Primary Key.

Code:
Me.Validate()
Me.ProductsBind ingSource.EndEd it()
Me.ProductsTabl eAdapter.Update (Me.ProductsDat aSet.Products)

Deleted Productbindings , tableadapter, and dataset, remade them and all is
good in the world. Select, updating, and inserting works good, but delete is
not working yet.

"Cor Ligthert[MVP]" wrote:

Hi,

.Add(New Data.SqlClient. SqlParameter("@ ProductID", ProductsID))
etc,

But why do you not simple use

\\\
Me.ProductTable Adapter.Update( Me.ProductsData set.Products)
me.ProductsData Set.Products.Cl ear
Me.ProductsTabl eAdapter.Fill(M e.ProductsDataS et.Products)
///

Cor
"te_butts" <te*****@discus sions.microsoft .comschreef in bericht
news:96******** *************** ***********@mic rosoft.com...

Using VS2005, and SQL 2005 Express.

Here is the situation:
SQL
- 1 Table called Products
- First Column called ProductsID (Uniqueidentifi er)(newid()) - So it adds
new UI when new row is inserted.
- Second column called ItemCode(NVarCh ar)

VB Form
DataGridView called ProductsDGV

When i created the ProductsDGV it created the BindingDataSour ce,
TableAdapter, and DataSet. As I went through the wizard it says that
SELECT,
INSERT, UPDATE, AND DELETE commands are also created.

Problem:
When i have buttons to INSERT, UPDATE, AND DELETE, the database changes
perfectly. When i add the same code that i have for the INSERT button to
an
event of the ProductsDGV like RowLeave i get the error "The parameterized
query ('@ProductID ...)' which was not supplied.

Here is my code in the RowLeave Event of the ProductsDGV:

Dim cnxn As New Data.SqlClient. SqlConnection
Dim cmd As New Data.SqlClient. SqlCommand

cnxn.Connection String = "Data
Source=ADDLMNAR WP13\SQLEXPRESS ;Initial
Catalog=myDatab ase;Integrated Security=True"
cnxn.Open()
cmd.Connection = cnxn

With cmd.Parameters
.Add(New Data.SqlClient. SqlParameter("P roductID", ProductsID))
.Add(New Data.SqlClient. SqlParameter("I temCode",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("D escription",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("C ost",
SqlDbType.NVarC har))
End With

cmd.CommandText = "INSERT INTO
dbo.Products(Pr oductsID,ItemCo de,Description, Cost) VALUES
(@ProductsID,@I temCode,@Descri ption,@Cost)"

cmd.ExecuteNonQ uery()
cnxn.Close()

Me.ProductsTabl eAdapter.Fill(M e.ProductsDataS et.Products)

Thanks for the help!

[Rich P]

The wizard(s) provides a generic set of commands for your dataset. This
makes it fairly restricted - especially if you are binding the data. An
easier way to handle your situation is to define your own
sqlDataAdapter, Dataset, sqlCommands,... and then fill your dataset

-----------------------------------------
Imports System
Imports System.Data.Sql Client
Dim da As SqlDataAdapter, ds As Dataset, conn as SqlConnection

Private Sub Form1_Load(...)
conn = new sqlConnection
conn.Connection String = "..."
ds = New Dataset
da = new SqlDataAdapter
da.SelectComman d = New SqlCommand
da.SelectComman d.Connection = conn
da.InserCommand = New SqlCommand
da.InsertComman d.Connection = conn
da.UpdateComman d = New SqlCommand
da.UpdateComman d.Connection = conn

da.SelectComman d.CommandText = "Select * from your table"
da.Fill(ds,"tbl 1")
datagridview1.D ataSource = ds.Tables("tbl1 ")

da.UpdateComman d.CommandText = "Update Table1 Set fld1 = @p1, fld2 = @p2
fld3 = @p3,... Where ID = @ID"
da.UpdateComman d.Parameters.Ad d("@p1", SqlDBType.Varch ar, 50, "fld1")
da.UpdateComman d.Parameters.Ad d("@p2", SqlDBType.Varch ar, 50, "fld2")
...

End Sub

Private Sub datagridview1_C ellValueChanged (ByVal sender As Object, ByVal
e As System.Windows. Forms.DataGridV iewCellEventArg s) Handles
datagridview1.C ellValueChanged
Try
Dim strID As String =
datagridview1.R ows(e.RowIndex) .Cells("ID").Va lue.ToString
Dim s1 As String = datagridview1.C olumns(e.Column Index).Name
Dim drF() As DataRow = ds.Tables("tbl1 ").Select(" ID = " & strID)

Dim strArg As String =
datagridview1.R ows(e.RowIndex) .Cells(e.Column Index).Value.To String.ToUpp
er
datagridview1.R ows(e.RowIndex) .Cells(e.Column Index).Value = strArg

For Each dr As DataRow In drF
dr.BeginEdit()
dr(e.ColumnInde x) =
dgrvInactiveDet ail.Rows(e.RowI ndex).Cells(e.C olumnIndex).Val ue
dr.EndEdit()
Next
Application.DoE vents()
da.Update(ds, "tbl1")
End Sub
--------------------------------------------

The wizard writes something like this for you except it is very generic
(and way more code). This is more streamlined and easier to manipulate.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Cor Ligthert[MVP]]

Are you sure that you are using the delete methods, you write that, but in
the same case I have often seen that people then were using the remove
methods, which removes from a datatable, but therefore not from the
database.

Cor

"te_butts" <te*****@discus sions.microsoft .comschreef in bericht
news:95******** *************** ***********@mic rosoft.com...

>I have done that and i get different errors all over the place.

Here is what i did to fix the problem.

In the database:
Changed the ProductID to INT, and made Is Identity = yes.
Made ProductID a Primary Key.

Code:
Me.Validate()
Me.ProductsBind ingSource.EndEd it()
Me.ProductsTabl eAdapter.Update (Me.ProductsDat aSet.Products)

Deleted Productbindings , tableadapter, and dataset, remade them and all is
good in the world. Select, updating, and inserting works good, but delete
is
not working yet.

"Cor Ligthert[MVP]" wrote:

>Hi,

.Add(New Data.SqlClient. SqlParameter("@ ProductID", ProductsID))
etc,

But why do you not simple use

\\\
Me.ProductTabl eAdapter.Update (Me.ProductsDat aset.Products)
me.ProductsDat aSet.Products.C lear
Me.ProductsTab leAdapter.Fill( Me.ProductsData Set.Products)
///

Cor
"te_butts" <te*****@discus sions.microsoft .comschreef in bericht
news:96******* *************** ************@mi crosoft.com...

Using VS2005, and SQL 2005 Express.

Here is the situation:
SQL
- 1 Table called Products
- First Column called ProductsID (Uniqueidentifi er)(newid()) - So it
adds
new UI when new row is inserted.
- Second column called ItemCode(NVarCh ar)

VB Form
DataGridView called ProductsDGV

When i created the ProductsDGV it created the BindingDataSour ce,
TableAdapter, and DataSet. As I went through the wizard it says that
SELECT,
INSERT, UPDATE, AND DELETE commands are also created.

Problem:
When i have buttons to INSERT, UPDATE, AND DELETE, the database changes
perfectly. When i add the same code that i have for the INSERT button
to
an
event of the ProductsDGV like RowLeave i get the error "The
parameterized
query ('@ProductID ...)' which was not supplied.

Here is my code in the RowLeave Event of the ProductsDGV:

Dim cnxn As New Data.SqlClient. SqlConnection
Dim cmd As New Data.SqlClient. SqlCommand

cnxn.Connection String = "Data
Source=ADDLMNAR WP13\SQLEXPRESS ;Initial
Catalog=myDatab ase;Integrated Security=True"
cnxn.Open()
cmd.Connection = cnxn

With cmd.Parameters
.Add(New Data.SqlClient. SqlParameter("P roductID",
ProductsID))
.Add(New Data.SqlClient. SqlParameter("I temCode",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("D escription",
SqlDbType.NVarC har))
.Add(New Data.SqlClient. SqlParameter("C ost",
SqlDbType.NVarC har))
End With

cmd.CommandText = "INSERT INTO
dbo.Products(Pr oductsID,ItemCo de,Description, Cost) VALUES
(@ProductsID,@I temCode,@Descri ption,@Cost)"

cmd.ExecuteNonQ uery()
cnxn.Close()

Me.ProductsTabl eAdapter.Fill(M e.ProductsDataS et.Products)

Thanks for the help!