Inserting data into an access database

Hello,

I am trying to insert IpAddress and other stuff into an access database in my ASP.NET 2.0 application but keep getting an exception on the INSERT statement. Can anyone tell me what I am doing wrong Thanks.

Dim ipAddress1 As String = Request.ServerVariables("REMOTE_ADDR")

Dim UserHostName As String = Request.UserHostName

Dim UserAgent As String = Request.UserAgent

Dim UrlReferer As String = ""

Dim DateTime As Date = Now

Dim ipAddress2 As String = Request.UserHostAddress

Dim strSQL As String

strSQL = "INSERT INTO tblAddresses (IpAddress1, UserHostName, UrlReferer, UserAgent, DateTime) " & _

" VALUES ('" & ipAddress1 & "','" & UserHostName & "','" & UrlReferer & "','" _

& UserAgent & "'," & DateTime & "); "

AccessDataSource1.InsertCommand = strSQL

Try

AccessDataSource1.Insert()

Finally

MsgBox("Default.aspx.PageLoad Error", MsgBoxStyle.Critical)

End Try

Answer this question

Inserting data into an access database

Gravedigger
Parameters do not require controls, you just have to add parameters to your Command object.
Juandre
Thank you for your reply. I have tried the # singns but that did not help.

I would rather use parameters but the parameter requires a control reference. I do not want to put controls on the form for this data. I guess I could and make them invisible but I would rather not.

Incidentally, does anyone know how to access the controls inside a Wizard control on and ASP.NET 2.0 page I will be using parameter for this.

Thanks guys (and girls : )
Brad Eck
Hi

I believe that the field DateTime is a reserved word and would probably explain the reason for the exception. If you can change the field name or enclose it in square brackets that should help. Also, the DateTime variable should be enclosed within single quotes shouldn't it

Of course, using parameters would probably negate the need to do any of the above but if you want a quick fix then hopefully the above will help.

HTH
Roco72
DateTime types have to be wrapped in pound signs (#). You should look into using parameters, instead of concatenating values into a string; they generally take care of these kind of things for you.
Jason.
Thank you David! You were right. The square brackets did the trick and the fellow who replied to your post was incorrect. Apostrophe's do not work.

My other problem was that I was missing an apostrophe or comma. Thanks so much for you help everyone.
Arjen Stolk
Thank you for your reply. Yes I have tried that. The problem stems from two of the insert values, one problem is the date, wrapping it with # signs does not fix the problem, the other problem is the with the UserAgent field. I have the field set to type Memo to accept the large amount of data that is in the UserAgent string but that doesn't help.

I think I will take your advice on setting the default date to Now in the table.
jesuitx

Working with a parameterized query is definitely the right way to go. Since the remaining issue involves dealing with the wizard control, you might be best served asking that question on the ASP.NET forums.

There are a number of forums on the ASP.NET site dedicated to data access questions. I'd recommend taking a look here http://forums.asp.net/

I hope this information proves helpful.
zoomkat

My first guess would be that the format of the date string is wrong. Can't you make the DB set Now as the default value on that column

It looks like you have quotes around your strings. Have you tried copying strSQL in the debugger and running it in Access, does Access allow that
figo2476

Great point, David.

Even with the parameters, the DateTime column name should be delimited with the appropriate characters. The Jet provider reports that the apostrophe character is the appropriate delimiter, but you could also use square brackets.

INSERT INTO MyTable (..., `DateTime`, ...) VALUES (..., , ...)

I hope this information proves helpful.
Inserting data into an access database

Answer this question