VB.Net to MS Access via SQL Problem

[LethalPacifier]

I have a web form that accesses a MS Access (.mdb) file. The has on one table with a dozen columns and records. All columns are text. From the VB.Net page I issue an SQl UPDATE string that uses valuse from various controls on the page. The string is as follows:
(VB.Net):
Return "UPDATE AnalystList " & _
"SET AD_Domain = """ & tbDName.Text & """, " & _
"First_Name = """ & tbFName.Text & """, " & _
"Last_Name = """ & tbLName.Text & """, " & _
"Status = """ & status & """, " & _
"Status_Description = """ & tbStatusDescription.Text & """, " & _
"Work_Responsibility = """ & tbWorkResp.Text & """, " & _
"Administrator = """ & tmpAdmin & """, " & _
"Moderator = """ & tmpMod & """, " & _
"User = """ & tmpUser & """, " & _
"Date_Time = """ & Date.Now.ToString & """ " & _
"WHERE AD_Domain = """ & qsAgentDName & """;"

(Output when displayed):
UPDATE AnalystList
SET AD_Domain = "xxx1234",
First_Name = "Larry",
Last_Name = "Bird",
Status = "IN",
Status_Description = "Stuff",
Work_Responsibility = "Stuff",
Administrator = "N",
Moderator = "Y",
User = "Y",
Date_Time = "S"
WHERE AD_Domain="xxx1234";

There is a different string for different update privledge levels (Admin, Moderator, User). There above is Administrator and the only one I have a problem with. Moderator does not have "Admin", "Mod", and "User" fields and User also excludes "Work_Resp", "First_Name", and "Last_Name". When the User or Mod strings are sent the DB is always updated. When the Admin string is sent the DB has never updated. I appears to have something to do with the Admin, Mod, and User entries. They are text fields in Access with a 1-char max length. I have tried increasing and originally had then a type Yes/No set to True/False but delivering Boolean or Integer valuse never worked either.

Is there any reason or limitation for this not to work. Additionally, I have run the "Output) string from within Access and it worked. So I don't know if there is a type issue between VB.Net and Access or what.

Thanks in advance for any help and reading this.

 

[LethalPacifier]

Additionally, no error is generated in VB when sending this Admin string.

 

[macleod1021]

The only thing that comes to mind is how are you declaring the tmpMod, tmpAdmin & tmpUser? There may be an explicit cast issue if you have them declared as boolean. Other than that, it looks like it should work.

 

[LethalPacifier]

As of now all variables are either String types or .Text from a control, which I guess is String, from a VB.Net form. I am not using any boolean or numeric (Integer, Byte, Short) types any longer. But in any case, the String type didn't solve the problem.

The Output portion is a Javascript 'alert' displaying the entire SQL statement as I assume it is sent to Access.

 

[macleod1021]

Well, if it's not causing an error on the update, then Access is either allowing it or handling any errors itself; or you have error handling catching it. Put a breakpoint at the update command and use the immediate window to see exactly what is being passed. Then step through to see if any type of error is happening. There may be an Error Handling routine that is kicking in and blocking the true problem.

 

[LethalPacifier]

OK, hindsight is 20/20.

Well the problem was nothing to do with how the parameter was passed. I found that "User" is a MS Access reserved word. It cannot be used as a column header. It let me name the colmun "User" but when keeping all parameters in the SQL string except "User" the UPDATED completed. Renaming the column fixed it.

Sorry for the wild goose chase but thanks much for your assistance macleod1021.