DataGridView Questions/Problems

[te_butts]

Using VS2005, and SQL 2005 Express.

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

VB Form
DataGridView called ProductsDGV

When i created the ProductsDGV it created the BindingDataSource,
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.ConnectionString = "Data Source=ADDLMNARWP13\SQLEXPRESS;Initial
Catalog=myDatabase;Integrated Security=True"
cnxn.Open()
cmd.Connection = cnxn

With cmd.Parameters
.Add(New Data.SqlClient.SqlParameter("ProductID", ProductsID))
.Add(New Data.SqlClient.SqlParameter("ItemCode",
SqlDbType.NVarChar))
.Add(New Data.SqlClient.SqlParameter("Description",
SqlDbType.NVarChar))
.Add(New Data.SqlClient.SqlParameter("Cost", SqlDbType.NVarChar))
End With

cmd.CommandText = "INSERT INTO
dbo.Products(ProductsID,ItemCode,Description,Cost) VALUES
(@ProductsID,@ItemCode,@Description,@Cost)"

cmd.ExecuteNonQuery()
cnxn.Close()

Me.ProductsTableAdapter.Fill(Me.ProductsDataSet.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.ProductTableAdapter.Update(Me.ProductsDataset.Products)
me.ProductsDataSet.Products.Clear
Me.ProductsTableAdapter.Fill(Me.ProductsDataSet.Products)
///

Cor

 

[te_butts]

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.ProductsBindingSource.EndEdit()
Me.ProductsTableAdapter.Update(Me.ProductsDataSet.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.

 

[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.SqlClient
Dim da As SqlDataAdapter, ds As Dataset, conn as SqlConnection

Private Sub Form1_Load(...)
conn = new sqlConnection
conn.ConnectionString = "..."
ds = New Dataset
da = new SqlDataAdapter
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
da.InserCommand = New SqlCommand
da.InsertCommand.Connection = conn
da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn

da.SelectCommand.CommandText = "Select * from your table"
da.Fill(ds,"tbl1")
datagridview1.DataSource = ds.Tables("tbl1")

da.UpdateCommand.CommandText = "Update Table1 Set fld1 = @p1, fld2 = @p2
fld3 = @p3,... Where ID = @ID"
da.UpdateCommand.Parameters.Add("@p1", SqlDBType.Varchar, 50, "fld1")
da.UpdateCommand.Parameters.Add("@p2", SqlDBType.Varchar, 50, "fld2")
...

End Sub

Private Sub datagridview1_CellValueChanged(ByVal sender As Object, ByVal
e As System.Windows.Forms.DataGridViewCellEventArgs) Handles
datagridview1.CellValueChanged
Try
Dim strID As String =
datagridview1.Rows(e.RowIndex).Cells("ID").Value.ToString
Dim s1 As String = datagridview1.Columns(e.ColumnIndex).Name
Dim drF() As DataRow = ds.Tables("tbl1").Select("ID = " & strID)

Dim strArg As String =
datagridview1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString.ToUpp
er
datagridview1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = strArg

For Each dr As DataRow In drF
dr.BeginEdit()
dr(e.ColumnIndex) =
dgrvInactiveDetail.Rows(e.RowIndex).Cells(e.ColumnIndex).Value
dr.EndEdit()
Next
Application.DoEvents()
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

 

[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