| Author |
Topic |
|
coolwrld
Starting Member
10 Posts |
 Posted - 2006-02-23 : 11:09:37
|
| I am creating a Access frontend to some SQL Server Tables. One of these tables has an int type field and several money type fields. I am trying to populate the table based on text boxes on an Access form. Because of other reasons instead of just using the SQL table as the record source of the form I am using "INSERT INTO" statements to update the data.Here is my problem if I leave the textfields that are supposed to populate the int and money fields blank I get an syntax error message on the INSERT statement. If I populate the textfields with a 0 the INSERT works fine.The int field and money fields are defined to accept nulls so why is the blank textfield generating an error message instead of inserting a null into the table?Any help or guidance would be greatly appreciated. |
|
|
milesy20
Starting Member
2 Posts |
Posted - 2006-02-23 : 11:34:04
|
| One (more secure) solution would be to create a stored procedure on the SQL server to do the insert and call that with values populated from Acccess. You could then only grant SELECT permissions to the tables and EXEC to the proc and never actually directly grant INSERT (UPDATE, DELETE) access to the tables.You also can do SQL validation and data conversions using TSQL which can be more reliable than Access VBA.HTH |
 |
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-02-23 : 11:37:46
|
| Milesy20,Thanks for the advice. I haven't done a whole lot with stored procedures or TSQL, I'm much more comfortable with Access which I'm sure is probably a drawback but its what I know. I still can't understand why the blank textboxes don't get Inserted as nulls into the fields in question instead of generating an error message. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-02-23 : 12:45:04
|
quote:
Originally posted by milesy20
One (more secure) solution would be to create a stored procedure on the SQL server to do the insert and call that with values populated from Acccess.
Absolutely.But I bet the problem you are having is that you are actually submitting zero-length strings when you think are submitting NULLs.What is the exact error message you are receiving? |
|
|
|
milesy20
Starting Member
2 Posts |
Posted - 2006-02-23 : 14:04:11
|
| blindman has a good point... If you are using an INSERT INTO statement in VBA, you could always do something like this (although I still recommend using a stored proc so that your solution is more robust):strSQL = "INSERT INTO [Table] (field1, field2, ...) " & _ "VALUES (" & _ IIF(LEN(Field1!value) > 0, Field1!value, "NULL") & ", " & _ IIF(LEN(Field2!value) > 0, Field2!value, "NULL") & ", " & _ ... ")" |
|
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-02-23 : 14:07:10
|
| Blindman,You are probably right. The message I get simply says Syntax error in INSERT statement. When I look at the INSERT statement I see something that looks like this ("CCCCCC","CCCCC",'12/02/2006',,,,,,) Where the last six ,s designate where the null values should be based on blank textboxes for the int and money fields. If this statement is changed to look like ("CCCCCC","CCCCC",'12/02/2006',0,0,0,0,0,0) the insert statement works with no errors. |
|
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-02-23 : 14:11:13
|
| Mileys20,Again thanks for the suggestion I didn't even think to check the len in the insert statement like that. I had considered checking the length outside the select and then setting a value that way but it seemed like a bunch of unnecessary code to incorporate when I could just prefill the textboxes with zeros (which is actually what I have done). When I get some time I may go back and try your length checking in the insert and see how that works. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-02-23 : 15:47:40
|
| You could also try to set the default value to 0 on those fields in sql table. That way, if sql will plug the default value in when a new line is inserted. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-02-23 : 23:44:14
|
| Again, you SHOULD do this using a store procedure with optional parameters. But if you are going to create dynamic SQL, then construct your string to substitute blanks with the word NULL, like this:("CCCCCC","CCCCC",'12/02/2006', NULL, NULL, NULL, NULL, NULL, NULL)-or-leave the commas out for the values you are not supplying:("CCCCCC","CCCCC",'12/02/2006')...but, keep in mind that your insert statement must match the values with the correct destination columns. |
|
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-02-24 : 08:45:28
|
| Mileys20,I went ahead and tried your suggestion of checking the textbox length right in the INSERT statement and it works great. I actually incorporated that for some date fields I was having a problem with too. Thanks again for the suggestion.To everyone else thanks for all the suggestions and maybe sometime when I am more comfortable with stored procedures I will go back and revisit that solution as well. |
|
|
|
|
Problem with MS Access frontend to SQL Server