March 5, 2008 at 6:46 pm
I am having a problem trying to figure out why my update queries will not post back to the database. I have tried two different queries below wth no luck. The first query is based on the RecordNumber of the table field (Integer) and the second is based on the Request.QueryString of the web page which is the date selected (Example: 02/12/2008 ) and is CHAR type in the database. I can add to the table, but cannot update any of the records. I would appreciate some guidance or a nudge in the right direction. 😀
ID = RTrim(Request.QueryString("Target"))
EditCalendarDate = Me.txtStartDate.Text
EditDescription = Me.txtDescription.Text
Dim cs As ConnectionStringSettings
cs = ConfigurationManager.ConnectionStrings("ConnectionString1")
Dim connString As String = cs.ConnectionString
Dim dbConnection As New SqlConnection(connString)
Dim SQLUpdateQuery
dbConnection.Open()
' Create the Update Command.
SQLUpdateQuery = "UPDATE CALENDAR SET CalendarDate = '" & EditCalendarDate & "' , Description = '" & EditDescription & "' WHERE [IDNum] = " & RecordNumber & ";"
SQLUpdateQuery = "UPDATE CALENDAR SET CalendarDate = '" & EditCalendarDate & "' , Description = '" & EditDescription & "' WHERE (CalendarDate='" + ID + "')"
'printToDebugWindow(SQLUpdateQuery)
Dim dbCommand As New SqlCommand(SQLUpdateQuery, dbConnection)
dbCommand.ExecuteNonQuery()
dbConnection.Close()
Response.Redirect("welcome.aspx")
Thanks,
Lennie
March 6, 2008 at 8:23 am
I'm guessing that your query is wrong. This is the first assignment to SQLUpdateQuery
SQLUpdateQuery = "UPDATE CALENDAR SET CalendarDate = '" & EditCalendarDate & "' , Description = '" & EditDescription & "' WHERE [IDNum] = " & RecordNumber & ";"
Then you rewrite SQLUpdateQuery
SQLUpdateQuery = "UPDATE CALENDAR SET CalendarDate = '" & EditCalendarDate & "' , Description = '" & EditDescription & "' WHERE (CalendarDate='" + ID + "')"
But in your WHERE condition you have CalendarDate (which I'm guessing is a DateTime field) = ID (guess again : string), so I think that is very likely that you don't have any rows to match your condition.
Is it possible that maybe instead of ID you meant EditCalendarDate?
March 6, 2008 at 6:09 pm
l.g.oxyer (3/5/2008)
'printToDebugWindow(SQLUpdateQuery)
Have you tried to run the SQL (should be the output of your print statement above) directly in the database, not thru the app? Start there to make sure our SQL is correct.
If it was easy, everybody would be doing it!;)
March 7, 2008 at 5:38 am
Every table intended for update must have a unique key. In update statement you put unique key in where clause. If you don't care about concurrency, it's enough (upWherekeyonly update mode).
However, every well behaved application cares about that, so you should include old values of modified columns into where clause and check .RowsAffected of the command (upWhereChanged update mode).
If two users read the same record, but they modify different column, updates won't conflict, if they modify the same column, second user will get .RowsAffected=0 because of old values in where clause. If you don't include old values, second user will overwrite changes of first without knowing. Sometimes upWhereChanged may cause side effects, so you use upWhereAll mode where you include all columns into where clause.
That's the theory. In practice I have no idea how to do it in VB. My last database VB application was in VB3, access database.
March 8, 2008 at 5:13 pm
Hi,
The simple way to see why it is not updating the base table is take the update query and replace all the variables with the values that you want to pass and execute the query. If it is not updating then its problem with your where condition. genererally you should be very carefull in updating the Columns which are CHAR and has date as value.
Thanks -- Vj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply