Access Databases and AccessDataSource Control

Re: Inserting records into access database

hans_v — Wed, 01 Jul 2009 16:00:29 GMT

amit.jain:
than use olebd insted of SQL

So with OleDb you're not required to use SQL??????

amit.jain:
and use accessDataSource instead of SqlDataSOurce

Quess what? The AccessDataSource control inherits the SqlDataSource class and replaces the ConnectionString property with a DataFile property to make it more convenient to connect to a Microsoft Access database

http://msdn.microsoft.com/en-us/library/8e5545e1.aspx

So everything the accessDatasource can do, the sqlDatasource control can do that also. The only difference is the connectionstring, I think it's better to use the sqlDatasource so you can store the connectionsting in the web.config, and more important, when you've a password protected mdb you cannot use the accessDataSource anyway....

Re: Inserting records into access database

amit.jain — Wed, 01 Jul 2009 15:37:14 GMT

FrankEnem:
pls I am using access database.

than use olebd insted of SQL , and use accessDataSource instead of SqlDataSOurce

refer link below

http://forums.asp.net/p/1364640/2829655.aspx

Re: Inserting records into access database

hans_v — Wed, 01 Jul 2009 14:56:52 GMT

adeelehsan:
Because USERS may be a reserve word or reserve name of an existing object.

No it is not (USER is). The complete list of Jet (in an ASP.NET environment we're dealing with a Jet Database Engine) can be found here

papabear:
Basically you need to specify the parameters using the @

No, that's not required. Jet will thread any unknown string as a parameter. Off course, it is a good habit using @

papabear:
then refer to them on the InsertParameters section...

Be aware that when using OleDB, parameters are not recognized by their name, but by their position. So the parameters must be addes in the parametercollection in the same order they appear in the sql string.

SqlDataSource1.InsertParameters.Add("Paremeter1", Me.Account.Text)
SqlDataSource1.InsertParameters.Add("Paremeter2 Or any other name, even an empty string will do", Me.AccountName.Text)

is OK

SqlDataSource1.InsertParameters.Add("@ACCOUNTNAME", Me.AccountName.Text)
SqlDataSource1.InsertParameters.Add("@ACCOUNTID", Me.Account.Text)

is NOT OK

FrankEnem:
SqlDataSource1.InsertCommand = "Insert INTO USERS (ACCOUNTID, ACCOUNTNAME)" & _
"Values(01011,Accounts Payable)"
SqlDataSource1.UpdateParameters.Clear()
SqlDataSource1.InsertParameters.Add("ACCOUNTID", Me.Account.Text)
SqlDataSource1.InsertParameters.Add("ACCOUNTNAME", Me.AccountName.Text)

In this case the first 'parameter' isn't threaded as a parameter, because it is a numeric value. Furthurmore, parameters may not have spaces in them. If you have parameters (or field or tablenames) with spaces, or you're using Reserved words, you should enclose them in brackets, like [Accounts Payable]. Change the code as papabear showed you and it should work

Re: Inserting records into access database

adeelehsan — Wed, 01 Jul 2009 13:31:35 GMT

Hello

Try using AccessDataSource instead of SqlDataSource. Also enclose the table name "USERS" in square braces like:

insert into [USERS]........................

Because USERS may be a reserve word or reserve name of an existing object.

Re: Inserting records into access database

papabear — Wed, 01 Jul 2009 13:30:48 GMT

Your insert statement should be more like this:

SqlDataSource1.InsertCommand="Insert INTO USERS (ACCOUNTID, ACCOUNTNAME) " & _
" VALUES(@AccountID, @AccountName)"
SqlDataSource1.InsertParameters.Add("@AccountId", Me.Account.Text)
SqlDataSource1.InsertParameters.Add("@AccountName", Me.AccountName.Text)

Basically you need to specify the parameters using the @ and then refer to them on the InsertParameters section...

See http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert(VS.80).aspx for more...

hope this helps,

Inserting records into access database

FrankEnem — Wed, 01 Jul 2009 12:49:25 GMT

Please I have a web form with two textbox Account Id and Account Name. I have another button submit. I want to insert the values in the textbox in the access table when the submit button is click.

------------

I have created an sqldatasource; sqldatasource1 on the webform and written this code on the button_click

SqlDataSource1.InsertCommand = "Insert INTO USERS (ACCOUNTID, ACCOUNTNAME)" & _
"Values(01011,Accounts Payable)"
SqlDataSource1.UpdateParameters.Clear()
SqlDataSource1.InsertParameters.Add("ACCOUNTID", Me.Account.Text)
SqlDataSource1.InsertParameters.Add("ACCOUNTNAME", Me.AccountName.Text)
SqlDataSource1.Insert()

This gave me an error message " Syntax Error in INsert"

------------

If you are responding to this thread, pls bear in mind that I an new and started this just a few days ago. I will like everything to be very simple.

Thanks