SQL Update statement issues in ASP.Net 1

[nicks60610]

I am sure it is my usual Friday tired mind that's not seeing the issue, or could even be my brain being dead in general :- ) , I am running a report with edit functionality and that works great, until I want to update the data on my SQL server, it keeps erroring out on my SQL update statement...

I am sure it is something really dumb I am not seeing...by the way, the session variables do produce valid values, tested those with response.write statements in the Sub below (while commenting out the database update statement).

Thanks for any help..

Sub DataGrid1_Update(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

Dim strProblemDescription = e.Item.Cells(3).Text
Dim strSavingsAnalysis = e.Item.Cells(7).Text
Dim strQualityAnalysis = e.Item.Cells(9).Text
Dim strSafetyAnalysis = e.Item.Cells(11).Text


Dim strUpdate As String
strUpdate = "Update Kaizen Set ProblemDescription = strProblemDescription and
SavingsAnalysis = strQualityAnalysis and

strSafetyAnalysis where Date = " & Session("Date") & "

and FirstName = " & Session("FirstName") & " and

Title = " & Session("Title") & ""


Const StrConnString As String = "server=srvname;uid=userID;pwd=pwds;database=DB"

Dim objConn2 As New SqlConnection(StrConnString)

objConn2.Open()

Dim cmdUpdate As SqlCommand

cmdUpdate = New SqlCommand(strUpdate, objConn2)

cmdUpdate.ExecuteNonQuery()

objConn2.Close()

DataGrid1.EditItemIndex = -1
BindData()


End Sub

 

[jbenson001]

Hi

You do not need the "and" in the set statements, you need commas

Use

strUpdate = "Update Kaizen Set ProblemDescription = strProblemDescription ,
SavingsAnalysis = strQualityAnalysis,
strSafetyAnalysis = ??? you forgot this value

where Date = " & Session("Date") & "

and FirstName = " & Session("FirstName") & " and

Title = " & Session("Title") &

 

[nicks60610]

Thanks! I made the change, and now I get an "incorrect Syntax" error somewhere in the statement, I need to look over the SQL table and see if there is something mismatched there...

strUpdate = "Update Kaizen Set ProblemDescription = strProblemDescription, SavingsAnalysis = strQualityAnalysis, SafetyAnalysis = strSafetyAnalysis where Date = " & Session("Date") & " and FirstName = " & Session("FirstName") & " and Title = " & Session("Title") & ""

 

[ca8msm]

You realise that you are not actuall updating the fields to the variables don't you? Your SQL Statement should look something like:

strUpdate = "Update Kaizen Set ProblemDescription = '" & strProblemDescription & "', SavingsAnalysis = '" & strQualityAnalysis & "', SafetyAnalysis = '" & strSafetyAnalysis & "' where Date = '" & Session("Date") & "' and FirstName = '" & Session("FirstName") & "' and Title = '" & Session("Title") & "'"

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[nicks60610]

Duh :- ) , I told you I was doing something dumb, thank you ca8msm, I am sure you are getting quite tired of saving my butt, but again, thanks a million!!

 

[ca8msm]

It sometimes just takes another pair of eyes to spot something simple (I know I'm guilty of missing simple errors!).

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[nicks60610]

Well, I ran it and it finished without error messages, but when it rebinds the data in the next to last line of code, it rebinds the existing data, so it seems that my SQL code is not updating the database, but I think this has something to do with the SQL server/tables, and not the code itself because the code looks correct, I cannot see anything wrong with it at all at this point.

 

[ca8msm]

To check you could simply see what strUpdate equals before you actually do the update, and then run that SQL dirctly on the database - you'll know if it is correct as it should say how many rows have been updated.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.