DataGridView Questions/Problems

  • Thread starter Thread starter te_butts
  • Start date Start date
T

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!
 
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
 
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.
 
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
 
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
 
Back
Top