Update problem

[aceaceace]

Someone please help me and I can't get an answer anywhere.

I don't know what I'm doing wrong.


When I make a change to the datagrid and click the button that has the following code it doesn't update for some reason.

DataGrid1.Updat e()
SqlDataAdapter1 .Update(DataSet 11)


Please help...

[willakawill]

Copied you to .NET just in case someone there has more experience with these controls

[aceaceace]

Thank you very much

[Sorcerdon]

aceaceace,

Your datagrid has a property called OnUpdateCommand .
It sets up the function that will run when you click the update button.

Lets pretend that that function is called dgUpdate meaning OnUpdateCommand ="dgUpdate."

You must set up a Sub called gdUpdate:

Sub dgUpdate(ByVal source As Object, ByVal e As System.Web.UI.W ebControls.Data GridCommandEven tArgs)
...
Insert Code here
...
End Sub

You must place your update code into that block.
I do it this way:
Sub dgUpdate(ByVal source As Object, ByVal e As System.Web.UI.W ebControls.Data GridCommandEven tArgs)

Dim Param1 As String = Trim(e.Item.Cel ls(1).Text.ToSt ring()) 'For text
Dim Param2 As String = "2" 'hard coded
Dim Param 3 As String = Trim(CType(e.It em.FindControl( "txtBoxID") , TextBox).Text) 'for text boxes
Dim sSqlStr As String
Dim sSqlConnection As SqlConnection
Dim cSqlCommand As SqlCommand
sSqlStr = "EXEC dbo.uspUpdate " & _
" @param1=" & Param1 & _
",@param2=' " & Param2 & "'" & _
",@param3=' " & Param3 & "'"

sSqlConnection = New SqlConnection(C onfigurationSet tings.AppSettin gs("ConnStr"))
cSqlCommand = New SqlCommand(sSql Str, sSqlConnection)
sSqlConnection. Open()
cSqlCommand.Exe cuteNonQuery()
sSqlConnection. Close()
End Sub

[aceaceace]

I still don't see what I'm doing wrong

Please help


This is the code in the form:
Option Explicit On

Imports System.Data.Sql
Imports System.Data.Sql Client
Imports System.Data

Public Class Form1



Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click

DataGrid1.SetDa taBinding(ds, "Customers" )

End Sub


Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button2.Click


DataGrid1.Updat e()


End Sub
End Class





This is the code in the module:

Option Explicit On

Imports System.Data.Sql
Imports System.Data.Sql Client
Imports System.Data



Module Module1


Public ds As New DataSet()

Public connectionStrin g As String = "Data Source=AARON-OHJV7ZWBV;Initi al Catalog=North1D B;Integrated Security=True"

Public connection As New SqlConnection(c onnectionString )

Public command As New SqlCommand("SEL ECT * FROM Customers")

Public SqlDataAdapter1 As SqlDataAdapter = New SqlDataAdapter




Public Sub conn11()

ds = New DataSet("Custom ers")

command.Command Type = CommandType.Tex t

connection.Open ()

command.Connect ion = connection

SqlDataAdapter1 .SelectCommand = command

SqlDataAdapter1 .TableMappings. Add("Table", "Customers" )

SqlDataAdapter1 .Fill(ds)


End Sub









End Module

[aceaceace]

I forgot to add this for the form.


Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load



Call conn11()



End Sub

[SanjuMtr]

I forgot to add this for the form.


Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load



Call conn11()



End Sub

Hi,
when you going to update/Insert/Delete Data through Data Set (& Dataadapteor) I assume that you define the Dataadapter at run time.
then You need a Commandbuilder .
if you didn't create the commandbuilder object object,you would never be able to update your dataset because there are no Insertcommand,U pdatecommand or Deletecommand objects create at design time.

I am giving a simple example hot to use command builder object in your vb.net code

'I have Use Sqlclient namespace U can also use oledb
 'in that case use oledbcommandbuilder
' i assume u have declare dataadapter,dataset properly

dim cbTemp as sqlcommandbuilder= new sqlcommandbuilder(sqlDataadapter1)

sqlconnection1.open()
sqldataadapter1.fill(dsTemp,"Customer")
sqlconnection1.close()
'""now u can write the bellow code and change the effect
sqldataadapter1.update(dsTemp,"Customer")

[aceaceace]

I tried your code and I received this error:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

[Denburt]

The SelectCommand must also return at least one primary key or unique column. Check your table and make sure you have specified a primary key or a unique column.

[aceaceace]

Please bare with me. I'm very new to hard coding this. I don't know how to do that. The help files are next to useless to me as well. I'm about ready to just pay someone to show me how to do this.

[Denburt]

Well, I am quite rusty so if you can bare with me... :)

Take a look at this link, it will explain about the Schema and how it works to pull in the column information. Once you have your column information you should (if the tables were set up correctly) have a unique index.

http://msdn2.microsoft.com/en-us/library/system.data.schematype(VS.71).aspx

You should read this then click on the link FillSchema

A FillSchema operation adds a DataTable to the destination DataSet. It then adds columns to the DataColumnColle ction of the DataTable, and configures the following DataColumn properties if they exist at the data source:

* AllowDBNull
* AutoIncrement. You must set AutoIncrementSt ep and AutoIncrementSe ed separately.
* MaxLength
* ReadOnly
* Unique

FillSchema also configures the PrimaryKey and Constraints properties according to the following rules:

* If one or more primary key columns are returned by the SelectCommand, they are used as the primary key columns for the DataTable.
* If no primary key columns are returned but unique columns are, the unique columns are used as the primary key if, and only if, all the unique columns are nonnullable. If any of the columns are nullable, a UniqueConstrain t is added to the ConstraintColle ction, but the PrimaryKey property is not set.
* If both primary key columns and unique columns are returned, the primary key columns are used as the primary key columns for the DataTable.

Note that primary keys and unique constraints are added to the ConstraintColle ction according to the preceding rules, but other constraint types are not added.

Primary key information is used during Fill to find and replace any rows whose key columns match. If this is not the desired behavior, use Fill without requesting schema information.

[aceaceace]

Thanks Denburt. I basically understand what their saying in theory but how would I apply this to my example I posted for instance? I'm sorry, but I may the most basic thinking person on this forum. :)