Updating record in access (ASP) 1

[runsh]

Does anyone knows how to update specific record from database; for example a user sign in and would be able to update his/her own records only.
I use dreamwaver to set up update records but this will pulls all records in the table and user will have access to everyone records in that table to update.

following code works fine on localhost and old server but it doesn't work on my new server.
any help would be appreciated
<% If "Update Changes!" = Request.Form("Action") And _
Session("Status") = "Contracts" Then
Dim Ustring
Ustring = Session("BeanieBaby")

ContactName = Replace(Request("ContactName"),"'","")
Qualifications = Replace(Request("Qualifications"),"'","")
Position = Replace(Request("Position"),"'","")
Goal = Replace(Request("Goal"),"'","")
Url = Replace(Request("Url"),"'","")
Email = Replace(Session("Email"),"'","")
Status = Replace(Session("Status"),"'","")
Description = Replace(Request("Description"),"'","")
Company = Replace(Request("Company"),"'","")

Set Conn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\premfs1\sites\premium1\run\database\data.mdb;" & _
"Persist Security Info=False"
Conn.Open (sConnection)

sqlst2= "UPDATE Ads "
sqlst2= sqlst2 & "SET ContactName ='" & ContactName & "', "
sqlst2= sqlst2 & "Qualifications ='" & Qualifications & "', "
sqlst2= sqlst2 & "Url ='" & Url & "', "
sqlst2= sqlst2 & "Position ='" & Position & "', "
sqlst2= sqlst2 & "Goal ='" & Goal & "', "
sqlst2= sqlst2 & "Email ='" & Email & "', "
sqlst2= sqlst2 & "Description ='" & Description & "', "
sqlst2= sqlst2 & "Company ='" & Company & "', "
sqlst2= sqlst2 & "Status ='" & Status & "' "
sqlst2= sqlst2 & " WHERE ItemId='" & Ustring & "';"
'response.Write(sqlst2)
Conn.Execute(sqlst2)
Response.Redirect "itemaddsearch.asp"
End If %>

I keep getting error messages say syntex error line 271 and this is in line 271 Conn.Execute(sqlst2)

when I use response.write all records looks fine but still give me above error.

 

[Genimuse]

I don't know your database, but my guess is that ItemId isn't a string, but is instead a number. If that's the case then you'd need to remove the single-quotes from around it, making the WHERE line look like this:

sqlst2= sqlst2 & " WHERE ItemId=" & Ustring & ";"

I could easily be wrong, though, since I know nothing about the database. There could be other SQL problems. If that change doesn't work, I suggest you uncomment that response.Write line, add Response.Flush() after it, and see what the actual SQL looks like.

 

[runsh]

yes ItemId is is autogenerated number.
This is what I get with response.write looks fine. except error

UPDATE Ads SET ContactName ='ben', Qualifications ='BE/BC, Experience is a plus but not Rquired .', Url ='

http://www.pbpr.com',

Position ='sale', Goal ='see detail', Email ='ali@msn.com', Salary ='Yes', Description ='see detail', Company ='compu', FP_time ='Temporary', Status ='Employer' WHERE ItemId=980735976;
Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/jbank/itemedit.asp, line 332


line 332 Conn.Execute (sqlst2)

and when I change it to
set Rs = Conn.Execute (sqlst2)
I get nothing (blank streen)

 

[Genimuse]

Copy the SQL generated, open up the database in Access and create a new query in design view, cancel the table dialog (you won't need to select any tables for this), and then from the View menu choose SQL View. Paste you SQL in and click the Run (!) button. You should get a better error message there.

Another guess is that perhaps any of the following fields are not text, and are expecting some kind of number: Position, Salary, FP_time, Status.

And there's no need to Set RS = anything, you're not requesting a recordset here.

 

[runsh]

except ItemId all are text filed.

When I run the sql as you said it gives me this error:
Data type mismatch in criteria expression.

when I check help button this is what shows:

The window name "langref" passed to HH_GET_WIN_type has not been specified.

I don't know what these mean!

 

[Genimuse]

Data Type Mismatch tells us that one of the things you're inserting as a text field isn't. Are you absolutely CERTAIN that every field is a text field? My strongest hunch is that Salary is a Yes/No field, which means that instead of sending it 'Yes', you need to send it Yes.

 

[Genimuse]

To check, in Access go to the Tables tab, click on the Ads table, and click Design. Is the Data Type for every one of those fields absolutely "Text" or "Memo"?

If so, my next guess is that one of the text fields is shorter than the data you're trying to insert.

 

[runsh]

Thanks for your help this is my Ads table:
ID AutoNumber
ItemId Text (generates atuo nnmbre field size is 50)
Position Text
Description Memo
MUrl Text
ContactName Memo
Email Text
Status Text
E_Date Date/Time
Qualifications Memo
Company Text
Goal Memo
Region Text

is it better to make ItemId number instead of text?
Is there a problem with date/time. this is set as now().

 

[Genimuse]

No, autonumber is good. And you're not sending E_Date, so it shouldn't be a problem.

I'm back to text length. When you click on some portion of the line for each field (you could click on the name, for example), at the bottom of the screen you'll see the length of each of the text fields. What are they? Could one be too short for what you're trying to insert?

 

[runsh]

Thanks Genimuse for your help. I checked the table fields, they seem to be ok 50. 100, 200. I tested with only few words, still not working.
This runs ok on my localhost and old server I had but not on window 2003 server that I recently signed up with.
I am not sure with the problem is.
thanks again

 

[Genimuse]

Ah, I see. Sorry, that shouldn't be related to the code at all. It makes the error message very strange.

 

[Tarwn]

Hold on a second, what does this mean:
ItemId Text (generates atuo nnmbre field size is 50)

This isn't an autonumber field, it is a text field with some sort of randomize in the defaul or something like that. Ifg it was an autonumber field then it would be like your ID field, type=autonumber.

The error message you got on clicking the help button had nothing to do with your SQL statement, it sounds liek either your database burped and couldn't find the helpfile for your language, that your language is set wrong and it couldn't find a helpfile for that language, or tat you don'thave the help files installed (and thus it couldn't find the help file ).

Since your ItemId field is actually a text field you will need to surround it in single quotes and treat it like a tet field like in your original SQL statement:
UPDATE Ads SET ContactName ='ben', Qualifications ='BE/BC, Experience is a plus but not Rquired .', Url ='

http://www.pbpr.com',

Position ='sale', Goal ='see detail', Email ='ali@msn.com', Salary ='Yes', Description ='see detail', Company ='compu', FP_time ='Temporary', Status ='Employer' WHERE ItemId='980735976';

I would also fix the field name for URL, which you listed above as a Text field called MURL:
UPDATE Ads SET ContactName ='ben', Qualifications ='BE/BC, Experience is a plus but not Rquired .', MUrl ='

http://www.pbpr.com',

Position ='sale', Goal ='see detail', Email ='ali@msn.com', Salary ='Yes', Description ='see detail', Company ='compu', FP_time ='Temporary', Status ='Employer' WHERE ItemId='980735976';


I also don't see the FP_time or Salary fields in your table above, did you just not paste these two or do you need to ad them to the table?

-T

 

[runsh]

thanks Tarwan
the ID is autonumber and ItemId is text with field text of 50. ItemId generates autonumber when itemes are entered. is up to 10 digits. This is the actual number it generated when a position was posted (Sales position #290654610) this number corespond to ItemId.
Is it possible that this be the source of problem?
regarding FP_time yes I forgot to post it.

Is it possible for program work fine on localhost and other server but not updating files on window server 2003. rest of the program work ok except updating.

 

[runsh]

Also I forget to ask if date/time could be a problem. I read somewhere that date/time is updated automatically when record is updated.