Newbie Problems Updating SQL

A

Aaron

I am having some problems updating an SQL view with a button click.
Could someone point me in the right direction?

This code runs without any errors, but the sql command never makes it the
the SQL server.

I'm not using a parameters collection or anything, basically because I am
not sure how to map textboxes to SQL Parameters. If there is a better
approach, please let me know...

Thanks,
Aaron



Public Sub Button4_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button4.Click

'Dim sqlUpdateCommand1 As New SqlClient.SqlCommand()
Dim con
con = Me.SqlConnection1.ConnectionString
Dim nID As Integer = Me.GetSelectedRecordID(Me.DataGrid1,
DataSet11, "Searches_Activity")
If nID = 0 Then
Return
End If
Me.SqlUpdateCommand1.CommandText = "UPDATE Searches_Activity SET
Activity_Activity_CD = '" & txtACD.Text() & "', Activity_Result_CD = '" &
txtRCD.Text() & "', Activi" & _
"ty_Date = '" & txtActDate.Text() & "', Activity_FollowUp = '" &
txtFollowUpDate.Text() & "', Activity_Notes = '" & txtNotes.Text() & "',
Activity_Notes_Confidential = '" & txtConfNotes.Text() & "'," & _
" App_Name_Full = '" & txtFullname.Text() & "', App_Title_Long = '" &
txtTitle.Text() & "', App_Company = '" & txtCompany.Text() & "',
App_Phone = '" & txtphone.Text() & "', App_Cell = '" & txtAppCell.Text()
& "', App_" & _
"BPhone = '" & txtBPhone.Text() & "', App_Salary = '" & txtAppSalary.Text
() & "', App_TotalComp = '" & txtCompensation.Text() & "', App_Resume =
'" & txtResume.Text() & "', App_Bphone_Ext = '" & txtWorkExt.Text() & "',
App_Reg" & _
"ion_CD = '" & txtRegCode.Text() & "' where activity_ID = "
Me.SqlUpdateCommand1.CommandText =
Me.SqlUpdateCommand1.CommandText & nID
Dim UpdateSQL
UpdateSQL = Me.SqlUpdateCommand1.CommandText
'SqlDataAdapter1.Update(DataSet11, "Table")

SqlDataAdapter1.Update(DataSet11)

End Sub
 
W

William Ryan [eMVP]

If you use the Dataadapterconfiguration wizard for instance to gen your
update code..and you use TextBox.DataBindings.Add("Text", DataSet,
"DataTable.FieldName") where "Text" is the name of the control property you
want to bind to, you'll have the fields mapped. Try this: Dim i as
Integer = SqlDataAdapter1.Update(DataSet11, "TableName)

If I is 0 then no records were updated (which we already know)

Actually, I just saw the problem...put a breakpoint at the end right before
you call Update and see what your Actual SQL Statement is...it's going to
look soemthing like "Where activity_ID = (Whatever the Select statement
originally way.)
 
A

Aaron

I have checked the sql update command during debug and it is formulated
correctly. I also do not see the command when I run SQL Profiler. I am
just not sure if I have all of the pieces for the update command in
place.

I will look at the wizard and see if I can use it without breaking any of
my existing code.

Thanks,
Aaron
 
W

William Ryan [eMVP]

I see now, I lost something due to truncation... anyway, what are you seeing
in Profiler? Try using the databindings and an binding context....it'll
make the whole thing much cleaner. I can send you an example if you want.
 
A

Aaron

I am seeing nothing in Profiler. I am not sure if I am actually issuing
a command to update the table or not. When I use the "Configure data
adpater" an insert and select command are automatically made for me, but
it cannot create the delete/update command because it"Could not determine
which columns uniquely identify the rows for "Searches_Activity"."

So I am trying to programatically create an update statement.
Unfortunately, I have no .NET experience and very limited VB knowledge.
Therefore, I am not sure if I have a complete "structure" for updating.

Because the Wizard creates the insert/select statement as well as the
connection string, I am not sure of what is required...I have created the
update syntax and it certainly looks right, but I can't seem to get the
update command to actually get to the SQL server. That's why I think I
might be missing something.

In my click event for this button what components are required for an
update? Do I need to redefine a connection string or something?

One other piece of information is that when I run the update code, the
data is updated in a datagrid that I have on the same form, but when I re
run the select query, the data remains as it was.

If you have an example, that would be great. Perhaps I can figure out if
I am missing anything or what the story is...
 
M

michael

Aaron said:
I am having some problems updating an SQL view with a button click.
Could someone point me in the right direction?

This code runs without any errors, but the sql command never makes it the
the SQL server.

I'm not using a parameters collection or anything, basically because I am
not sure how to map textboxes to SQL Parameters. If there is a better
approach, please let me know...

Thanks,
Aaron



Public Sub Button4_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button4.Click

'Dim sqlUpdateCommand1 As New SqlClient.SqlCommand()
Dim con
con = Me.SqlConnection1.ConnectionString
Dim nID As Integer = Me.GetSelectedRecordID(Me.DataGrid1,
DataSet11, "Searches_Activity")
If nID = 0 Then
Return
End If
Me.SqlUpdateCommand1.CommandText = "UPDATE Searches_Activity SET
Activity_Activity_CD = '" & txtACD.Text() & "', Activity_Result_CD = '" &
txtRCD.Text() & "', Activi" & _
"ty_Date = '" & txtActDate.Text() & "', Activity_FollowUp = '" &
txtFollowUpDate.Text() & "', Activity_Notes = '" & txtNotes.Text() & "',
Activity_Notes_Confidential = '" & txtConfNotes.Text() & "'," & _
" App_Name_Full = '" & txtFullname.Text() & "', App_Title_Long = '" &
txtTitle.Text() & "', App_Company = '" & txtCompany.Text() & "',
App_Phone = '" & txtphone.Text() & "', App_Cell = '" & txtAppCell.Text()
& "', App_" & _
"BPhone = '" & txtBPhone.Text() & "', App_Salary = '" & txtAppSalary.Text
() & "', App_TotalComp = '" & txtCompensation.Text() & "', App_Resume =
'" & txtResume.Text() & "', App_Bphone_Ext = '" & txtWorkExt.Text() & "',
App_Reg" & _
"ion_CD = '" & txtRegCode.Text() & "' where activity_ID = "
Me.SqlUpdateCommand1.CommandText =
Me.SqlUpdateCommand1.CommandText & nID
Dim UpdateSQL
UpdateSQL = Me.SqlUpdateCommand1.CommandText
'SqlDataAdapter1.Update(DataSet11, "Table")

SqlDataAdapter1.Update(DataSet11)

End Sub
Click to expand...
 
W

William Ryan [eMVP]

Aaron:


If you don't see anything being fired at all, then the likely culprit is
that your DataAdapter doesn' think there are any changes pending. If those
textboxes aren't data bound, you'll need to add them to the datatable.

Add this line of code before you call update.

Debug.Assert(myDataSet.HasChanges). If you get a big obnoxious box asking
if you want to ignore, continue or quit, then your DataSet doesn't think it
has any changes (actually it doesn't) so nothing is going to be submitted.
That's the only reason I can think of provided you are connecting to the
right DB with your connection string, that you won't see the update at all
in profiler.


Does your table have a key? That's gonna be a problem for the Configuration
Wizard, and to be honest, a table without a key is going to be some
headaches in most instances. If you do have a key, is that field being used
in the query? If not, then include it. This alone should allow the
Configuration wizard to write the logic you need, and if you are really new
to ADO.NET, then this should get you through the first of your problem.

As far as changing the connection string, No, unless you are connecting to a
different Database which isn't the case.

The reason the data changes in the grid is b/c a DataSet is a local copy of
one or more queries. So any changes you make there will appear immediately,
but they have no relation to the backend until you submit those changes with
DA.Update or you call your own update logic.

Let's do this first....if you don't have a key, just add an Identity field
or GUID and then rerun the configuration wizard. See if this doesn't fix
the problem (assuming that the configuration wizard tells you it can
generate the code). If you are simply binding to grid, (using DataSource =
DataSet.DataTable) then when you call update, you should be good to go.



HTH,

Bill
 
A

Aaron

First of all, I am now seeing events in profiler, but only in the
following scenario. I believe this has something to do with currency
manager or something. Maybe you could explain what is happening here...

I kind of have to describe the app a little bit.

This is a basic select/update application. There is a textbox where a
user can input a comma-delim string of "Job" numbers and the recordset is
returned in a datagrid.

When a user clicks (Actually on DataGrid1_CurrentCellChanged) the
datagrid row, the data in the row is also populated in several textboxes.
The datagrid is read-only and the textboxes are bound to the dataset with

txtTEXTBOXNAME.Add("Text",DS, "SQL_COLUMN")


The textboxes are to be used to update the data in that record. Make
some changes to the persons name or telephone number or whatever, hit the
update button and the data is to be written to the table.

Now, when I make a change to the value in the textbox and hit the update
record button, nothing happens and there is no activity in profiler.

I added your Debug.Assert Code and did in fact see the large obnoxious
msgbox.

If I make changes to the textbox, and then click back on the datagrid
(The same record as was edited) and then hit the update button, then I
see the sql statement come accross profiler and no sign of the
debug.assert msgbox. I do get an error in the rowheader of that record
in the datagrid stating that my "From clause specifies more than one
table". I don't know about that as there is no from clause in an update
statement and certainly not one in my sql command. When I examine the
profiler data, I can see that there are two single quotes (''DATA'')
around the data in the update string. I will reformat the string, but
that still does not address the multiple tables in the from
clause...whew.


As far as the Key issue, this is actually a view made up of six different
tables. As far as I know, you can't have a primary key on a view. If
that is wrong, please let me know how it is done. Each of the tables in
the view have a primary key.

How can I ad an Identity Field or Guid? Is that something in the SQL
view or on the dataset?

Thank you for your help and time,

Aaron



The click event of Button4 is where I am trying to perform the update.
 
W

William Ryan [eMVP]

Aaron:

Ok, until that obnoxious message box goes away, what we know is that
whatever changes appear to be made, they DataSet and the Adapter isn't
seeing them.

Just for giggles, go in and delete a row from the
datatable...myDataTable.Rows(0).Delete, and then fire the update... The
assertion should no longer fail b/c there are in fact changes.

Does this line > txtTEXTBOXNAME.Add("Text",DS, "SQL_COLUMN") Actually say
txtTextboxName.DataBindings.Add(....)?

So, there's a view involved. While there are such things as Indexed Views
in SQL Server 2000, AFAIK, that's not going to help here. Are there
redundancies based on the view? For instance, if normally there'd only be
one parent record, but I create a veiw with a Cross Join for instance that
repeats otherwise distinct data. That could be a big part of the problem.

If the Assertion is not failing, that's a good sign..So am I correct in
saying that if you update the table via the textBox, the assertion passes
(no messagebox) but if you do it in the grid it fails(Big Obnoxious Box?).
 
A

Aaron

Aaron:

Ok, until that obnoxious message box goes away, what we know is that
whatever changes appear to be made, they DataSet and the Adapter isn't
seeing them.
Click to expand...
Yep, that's exactly what it seems like.

Just for giggles, go in and delete a row from the
datatable...myDataTable.Rows(0).Delete, and then fire the update...
The assertion should no longer fail b/c there are in fact changes.
Click to expand...

I didn't do this because I am not sure what this is, if it is deleting a
row from the returned dataset or if it is deleting a record from the DB.
But I would agree with you. I also do not get the message when updating
from the datagrid.

Does this line > txtTEXTBOXNAME.Add("Text",DS, "SQL_COLUMN") Actually
say txtTextboxName.DataBindings.Add(....)?
Click to expand...

Yes. I was trying to remember....

txtRegCode.DataBindings.Add("Text", dv, "App_Region_CD")
txtFullname.DataBindings.Add("Text", dv, "App_Name_Full")
txtTitle.DataBindings.Add("Text", dv, "App_Title_Long")
txtCompany.DataBindings.Add("Text", dv, "App_Company")
txtNotes.DataBindings.Add("Text", dv, "Activity_Notes")
So, there's a view involved. While there are such things as Indexed
Views in SQL Server 2000, AFAIK, that's not going to help here. Are
there redundancies based on the view? For instance, if normally
there'd only be one parent record, but I create a veiw with a Cross
Join for instance that repeats otherwise distinct data. That could be
a big part of the problem.
Click to expand...
This view is very simple. Here is the code

CREATE VIEW dbo.Searches_Activity
AS
SELECT Activity.Activity_CD AS Activity_Activity_CD,
Activity.Result_CD AS Activity_Result_CD,
Activity.Agent AS Activity_Agent,
Activity.Date AS Activity_Date,
Activity.FollowUp AS Activity_FollowUp,
Activity.Notes AS Activity_Notes,
Activity.Notes_Confidential AS Activity_Notes_Confidential,
Job.Job_Num, Job.Date AS Job_Date,
Job.Description AS Job_Description, Job.Agent AS Job_Agent,
Job.Title_Long AS Job_Title_Long,
Job.Product_CD AS Job_Product_CD,
Job.Region_CD AS Job_Region_CD,
Job.Salary AS Job_Salary,
Client.Name_Last AS Client_Name_Last,
Client.Name_First AS Client_Name_First,
Client.Name_Full AS Client_Name_Full,
Client.Title_Long AS Client_Title_Long,
Client.Title1 AS Client_Title_CD,
Applicant.Name_Last AS App_Name_Last,
Applicant.Name_First AS App_Name_First,
Applicant.Name_Full AS App_Name_Full,
Applicant.Title_Long AS App_Title_Long,
Applicant.Title1 AS App_Title_CD,
Job.Title_CD AS Job_Title_CD,
Company.Company AS App_Company,
Company1.Company AS Client_Company,
Job.Location AS Job_Location,
Applicant.Phone AS App_Phone,
Applicant.CellPhone AS App_Cell,
Applicant.Email AS App_Email,
Applicant.BPhone AS App_BPhone,
Applicant.BEmail AS App_BEmail,
Applicant.Person_ID,
Applicant.Salary AS App_Salary,
Applicant.Updated AS App_Updated,
Applicant.TotalComp AS App_TotalComp,
Activity.Activity_ID,
Applicant.Resume AS App_Resume,
Applicant.BPhone_Extension AS App_Bphone_Ext,
Applicant.Region_CD AS App_Region_CD
FROM Person Applicant LEFT OUTER JOIN
Company ON
Applicant.Company_ID = Company.Company_ID RIGHT OUTER JOIN
Person Client RIGHT OUTER JOIN
Job RIGHT OUTER JOIN
Activity LEFT OUTER JOIN
Company Company1 ON
Activity.Client_Company_ID = Company1.Company_ID ON
Job.Job_ID = Activity.Job_ID ON
Client.Person_ID = Activity.Client_ID ON
Applicant.Person_ID = Activity.Applicant_ID

I didn't actually make this view...Pre-existing condition. We use this
view in several applications...
If the Assertion is not failing, that's a good sign..So am I correct
in
saying that if you update the table via the textBox, the assertion
passes (no messagebox) but if you do it in the grid it fails(Big
Obnoxious Box?).
Click to expand...

Vice versa. The update command is captured in profiler when I perform
the following operation:
1. Change a recordset (I have been changing App_Bphone_Ext)
2. Click on the same row in the datagrid.
3. Click the Update record button (Button4)

After performing those steps, profiler gives me :
(If you notice the App_Bphone_Ext = ''1111'', this is my change)

sp_executesql N'UPDATE Searches_Activity SET Activity_Activity_CD =
''1'', Activity_Result_CD = ''1'', Activity_Date = ''6/13/2001 12:00:00
AM'', Activity_FollowUp = ''7/2/2001 12:00:00 AM'', Activity_Notes =
''Candidate would be interested in discussing opportunity after his
FYE.'', Activity_Notes_Confidential = ''vm/em 6/7,6/13'', App_Name_Full =
''SOMETHING,John'', App_Title_Long = ''Sales Director'', App_Company =
''HYPERSOLUTIONS CORPORATION'', App_Phone = '''', App_Cell =
''9444254370'', App_BPhone = ''97334574904'', App_Salary = '''',
App_TotalComp = '''', App_Resume = ''N'', App_Bphone_Ext = ''1111'',
App_Region_CD = ''NY'' where activity_ID = 275560'

If I simply change the text in one of the textboxes and then click the
update record button, nothing is captured in profiler...
 
A

Aaron

Aside from the problems outlined above, the real issue is going to be
updating the SQL View. Views are not updateable unless the updated data is
all from the same table. So, once I get around that, then I can resume
troubleshooting my update code.


I do appreciate all of your help.

Thank you,
Aaron


news:[email protected]:
 
Top