Update Datagrid - errors? 1

[nicks60610]

This is what I want to do, update a datagrid where a user enters some data, and this is the update part:

Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click


Dim dgi As DataGridItem
For Each dgi in DataGrid1.Items
'Read in the Primary Key Field
'Dim ID As Integer = Convert.ToInt32(DataGrid1.DataKeys(dgi.ItemIndex))
Dim SunderlandOnly As String = CType(dgi.FindControl("Sunderland Only"), TextBox).Text
Dim PcsInspected As String = CType(dgi.FindControl("Pcs Inspected"), TextBox).Text
Dim PcsDefective As String = CType(dgi.FindControl("Pcs Defective"), TextBox).Text
Dim PcsSold As String = CType(dgi.FindControl("Pcs Sold"), TextBox).Text
Dim ProductComplaints As String = CType(dgi.FindControl("Product Complaints"), TextBox).Text
Dim PcsSoldBF As String = CType(dgi.FindControl("Pcs Sold (Big Flats)"), TextBox).Text
Dim ProdCompBF As String = CType(dgi.FindControl("Product Complaint (Big Flats)"), TextBox).Text


'Issue an UPDATE statement...

Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Quality Metrics Database.mdb"


Dim conMetrics As OleDbConnection = New OleDbConnection(connectString)


conMetrics.Open()

Dim strUpdateQuality As String

Dim dcmdUpdateQuality = New OleDb.OleDbCommand(strUpdateQuality, conMetrics)

strUpdateQuality = "UPDATE [All Plants Summary Data] SET [Sunderland Only] = @Sunderland , [Pieces Inspected] = @PcsInsp, [Pieces Defective] = @PcsDef, [Pieces Sold] = @PcsSold, [Product Complaints] = @ProdComp, [Pieces Sold Big Flats] = @PcsSoldBF, [Product Complaints Big Flats] = @ProdCompBF where [Month] = '" & Session("Month") & "' and [Year] = '" & Session("Year") & "' and [Plant] = '" & Session("Plant") & "'"

'dcmdUpdateQuality.Parameters.Clear()
dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)
dcmdUpdateQuality.Parameters.Add("@PcsInsp", PcsInspected)
dcmdUpdateQuality.Parameters.Add("@PcsDef", PcsDefective)
dcmdUpdateQuality.Parameters.Add("@PcsSold", PcsSold)
dcmdUpdateQuality.Parameters.Add("@ProdComp", ProductComplaints)
dcmdUpdateQuality.Parameters.Add("@PcsSoldBF", PcsSoldBF)
dcmdUpdateQuality.Parameters.Add("@ProdCompBF", ProdCompBF)

dcmdUpdateQuality.ExecuteNonQuery()

Next

End Sub

And this is the error I receive on this line:

dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)

Object reference not set to an instance of an object

Does this mean that there is no data, i.e. it never retrieves any data from the database??

 

[nicks60610]

Makes perfect sense...I will try this first thing tomorrow morning, thanks again dvannoy!

 

[nicks60610]

I tried that, but it complains that Textbox1.txt is not declared...and i thought by doing this I declared what each value was?

Dim dgi As DataGridItem

For Each dgi In DataGrid1.Items
'Read in the Primary Key Field
Dim StrID As Integer = Convert.ToInt32(dgi.FindControl("Index"))

Dim SunderlandOnly As String = CType(dgi.FindControl("Textbox1"), TextBox).Text

Dim PcsInspected As String = CType(dgi.FindControl("Textbox2"), TextBox).Text

Dim PcsDefective As String = CType(dgi.FindControl("Textbox3"), TextBox).Text

Dim PcsSold As String = CType(dgi.FindControl("Textbox4"), TextBox).Text

Dim ProductComplaints As String = CType(dgi.FindControl("Textbox5"), TextBox).Text

Dim PcsSoldBF As String = CType(dgi.FindControl("Textbox6"), TextBox).Text

Dim ProdCompBF As String = CType(dgi.FindControl("Textbox7"), TextBox).Text

Then by calling these values here:

dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)

dcmdUpdateQuality.Parameters.Add("@PcsInsp", PcsInspected)

dcmdUpdateQuality.Parameters.Add("@PcsDef", PcsDefective)

dcmdUpdateQuality.Parameters.Add("@PcsSold", PcsSold)

dcmdUpdateQuality.Parameters.Add("@ProdComp", ProductComplaints)

dcmdUpdateQuality.Parameters.Add("@PcsSoldBF", PcsSoldBF)

dcmdUpdateQuality.Parameters.Add("@ProdCompBF", ProdCompBF)

But like you said, it seems that the values are not transferred so there is nothing to update....

 

[nicks60610]

However, when I do this

Response.Write(SunderlandOnly)
Response.End()

Right after the looping through the row code below:

Dim dgi As DataGridItem

For Each dgi In DataGrid1.Items
Dim StrID As Integer = Convert.ToInt32(dgi.FindControl("Index"))
Dim SunderlandOnly As String = CType(dgi.FindControl("Textbox1"), TextBox).Text

I do get the value I entered for Textbox1, but only one value, so it does not loop through the rows and read each value...but nonetheless, a value is produced, so the issue is getting that value down to the update query...

 

[dvannoy]

you have to decalre all the controls that are in the grid.

e.g.

this is how I do it using the sqlclient. I don't use oledb but it should be some what the same.

Dim txt As TextBox

Dim dgItem As DataGridItem

For Each dgItem In dgDetail.Items
txt = dgItem.FindControl("txt")

Dim strSQL As String = _
"UPDATE [TableName] SET [Field] = @txt"

Dim cn As SqlConnection = New SqlConnection

Dim cmdExp As SqlCommand

cn.Open()

cmdExp = New SqlCommand(strSQL, cn)

Dim txtParam As New SqlParameter("@txt", SqlDbType.VarChar, 10)

cmdExp.Parameters.Add(txtParam)

cn.Close()
Next
dg.EditItemIndex = -1
Binddg()

 

[nicks60610]

Awesome, I will give this a try this afternoon - thanks!

 

[nicks60610]

ok, this bugs me, even when I do an insert I am not getting anything?

For Each dgi In DataGrid1.Items

'Read in the Primary Key Field

Dim StrID As Integer = Convert.ToInt32(dgi.FindControl("Index"))
Dim SunderlandOnly As String = CType(dgi.FindControl("Textbox1"), TextBox).Text
Dim PcsInspected As String = CType(dgi.FindControl("Textbox2"), TextBox).Text
Dim PcsSold As String = CType(dgi.FindControl("Textbox3"), TextBox).Text
Dim PcsDefective As String = CType(dgi.FindControl("Textbox4"), TextBox).Text
Dim ProductComplaints As String = CType(dgi.FindControl("Textbox5"), TextBox).Text
Dim PcsSoldBF As String = CType(dgi.FindControl("Textbox6"), TextBox).Text
Dim ProdCompBF As String = CType(dgi.FindControl("Textbox7"), TextBox).Text

'Issue an INSERT statement...
Dim strUpdateQuality As String
Dim dcmdUpdateQuality As OleDbCommand
strUpdateQuality = "INSERT INTO [All Plants Summary Data] ([Year], [Plant], [Month], [SD Description],[Pieces Inspected],[Pieces Defective],[Pieces Sold],[Product Complaints],[CPPM Pieces Sold],[CPPM Complaint Pieces])

Values (@Year, @Month, @Plant, @SunderlandOnly, @PcsInspected, @PcsSold, @PcsDefective, @ProductComplaints, @PcsSoldBF, @ProdCompBF)"


Here, if I do a Response.Write on any of the above values, I see a number or text entry of the field I did a response.write of,

but as soon as I parameterize these values below and then try to use them...nada. it is as if the values disappear in the parameters, or Access cannot handle parameters?


dcmdUpdateQuality.Parameters.Clear()
dcmdUpdateQuality.Parameters.Add("@Year", Year.SelectedItem.Value)
dcmdUpdateQuality.Parameters.Add("@Month", Month.SelectedItem.Value)
dcmdUpdateQuality.Parameters.Add("@Plant", Plant.SelectedItem.Value)
dcmdUpdateQuality.Parameters.Add("@SunderlandOnly", SunderlandOnly)
dcmdUpdateQuality.Parameters.Add("@PcsInspected", PcsInspected)
dcmdUpdateQuality.Parameters.Add("@Pcssold", PcsSold)
dcmdUpdateQuality.Parameters.Add("@PcsDefective", PcsDefective)
dcmdUpdateQuality.Parameters.Add("@ProductComplaints", PcsSold)
dcmdUpdateQuality.Parameters.Add("@PcsSoldBF", PcsSoldBF)
dcmdUpdateQuality.Parameters.Add("@ProdCompBF", ProdCompBF)

 

[dvannoy]

let me look at some things. I may have a project I did in access sometime ago. I will let you know

 

[nicks60610]

Thanks dvannoy! I already owe you big for all the help you have given me already...

 

[dvannoy]

one question.. could this be a permissions issue?

 

[nicks60610]

Hmmm, good question...

The database is local on my C drive and I am only running it in debug mode so far...so I would think that in debug mode (logged in as myself) I would have access to write to the database, but you're right...let me try that..

 

[nicks60610]

When I run this:

Response.Write(dcmdUpdateQuality.CommandText)
Response.End()

I get nothing, which tells me that the information I see here:

Dim SunderlandOnly As String = CType(dgi.FindControl("Textbox1"), TextBox).Text

Is not showing up here:

dcmdUpdateQuality.Parameters.Add("@SunderlandOnly", SunderlandOnly)

And the only thing between these two lines is this:

Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Quality Metrics Database.mdb"
Dim conMetrics As OleDbConnection = New OleDbConnection(connectString)
conMetrics.Open()
dcmdUpdateQuality = New OleDbCommand(strUpdateQuality, conMetrics)


strUpdateQuality = "INSERT INTO [All Plants Summary Data] ([Year], [Plant], [Month], [SD Description],[Pieces Inspected],[Pieces Defective],[Pieces Sold],[Product Complaints],[CPPM Pieces Sold],[CPPM Complaint Pieces])

Values ([All Plants Summary Data].[SD Description] =@Sunderlandonly.....)"

 

[dvannoy]

try something like this.. The more I look into this a think access does not like parameters to much. really all your doing is executenonquery. delcare your textboxes etcc in your grid and use and use the name as you values.

Dim strSQL As String
Dim cn As New OleDb.OleDbConnection
Dim conn As OleDb.OleDbCommand

strSQL = "UPDATE Table SET BLAAA = "

conn = New OleDbCommand(strSQL, cn)
cn.Open()
conn.ExecuteNonQuery()
cn.Close()
conn = Nothing

 

[nicks60610]

If I do this, I get values in my Response Write statement:

Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click

Dim dgi As DataGridItem
Dim strUpdateQuality As String
Dim dcmdUpdateQuality As OleDbCommand

For Each dgi In DataGrid1.Items

Dim StrID As Integer = Convert.ToInt32(dgi.FindControl("Index"))
Dim SunderlandOnly As String = CType(dgi.FindControl("Textbox1"), TextBox).Text
Dim PcsInspected As String = CType(dgi.FindControl("Textbox2"), TextBox).Text
etc, etc.

strUpdateQuality = "INSERT INTO [All Plants Summary Data] ([Year], [Plant], [Month], [SD Description],[Pieces Inspected],[Pieces Defective],[Pieces Sold],[Product Complaints],[CPPM Pieces Sold],[CPPM Complaint Pieces]) Values ([All Plants Summary Data].[SD Description] = " & SunderlandOnly & " ,

[All Plants Summary Data].[Pieces Inspected] = " & PcsInspected & " ,)" etc, etc.

dcmdUpdateQuality = New OleDbCommand(strUpdateQuality, conMetrics)

 

[nicks60610]

ok..getting closer, it took the insert, but it complained about this:

No value given for one or more required parameters

Which I assume is that I have some blank fields and it did not like blanks...so either the database does not allow null values, or the way the database treats null values is not accepted per my insert statement...

 

[dvannoy]

which value is null?

 

[nicks60610]

Well, sometimes several of the fields can be null as different regions enter data, for example Sunderland Only is only entered by Sunderland, so all other regions would not enter data in this field, and then PcsSold may only have data for 6 out 12 possible, etc, etc.

 

[dvannoy]

I think in access you can set required to no on number fields. I'm not sure what type of text your talking about but you can also do soemthing like

select case Sunderland
case nothing
sunderland = something
end select

when looping through the grid look to see if there is any values if not you can set something in there like a 0.

just a thought

you can also do

If Yourtextbox <> "" Then
YourParameter.Value = text
Else
YourParameter.Value = DBNull.Value
End If

 

[nicks60610]

I think I see another problem...grrrr...this table has an Index which is the Primary Key, so when I try to insert, it still complains that something is left out.....arrgghhh, never had this much problem with Oracle or SQL...this is why Access should be banned..LOL

 

[nicks60610]

Even when I fill in all the fields I get the error:

NO value given for one or more required parameters,

Which makes me believe it does not A) like the way my string variables are set, or B) the primary key is spooking with me here...

 

[dvannoy]

it's got to be your key...duplicate the table and point to the new table without a key and see if it works then.