?
Solved

MS Access connection and data insert

Posted on 2005-03-30
20
Medium Priority
?
190 Views
Last Modified: 2010-04-07
Hi guys,

From the following page http://128.194.183.140/OnlineApplications/AALC06/Webform1.aspx
i need to insert all data to an MS Access database.

you can look at my database from here as well
http://128.194.183.140/OnlineApplications/AALC06.zip

can anyone help me to create a connection to the MS Access database first,
then guide me how to create the SQL INSERT statement for this online application

i am ready to ask some more questions if you are ready to help too.

thanks
0
Comment
Question by:cakirfatih
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
20 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13668736
You might find experts here more responsive if you take the time to maintain your abandoned questions.  Here are listed all your open questions:

1 03/08/2005 500 How to tile a header image  Open ASP.NET
2 03/09/2005 500 Adobe Acrobat 5.0 Professional problem w...  Open Adobe Acrobat
3 03/15/2005 500 How to edit .aspx and .ascx pages  Open ASP.NET
4 03/21/2005 500 How to present a warning screen  Open Microsoft Network
5 03/21/2005 500 Software inventory on client PCs  Open Microsoft Network
6 03/22/2005 500 MS Outlook 2003 and auto signature probl...  Open Microsoft Outlook
7 03/23/2005 500 PDA sync with Outlook  Open Miscellaneous
8 03/24/2005 500 Using image to Click to top  Open HTML
9 03/30/2005 500 SQL INSERT statement to MS Access  Open ASP.NET
10 03/30/2005 500 MS Access connection and data insert  Open ASP.NET
11 01/31/2005 500 Exchange Server account lockout  Open Exchange Server
12 02/15/2005 500 PDF to Word conversion  Open Microsoft Word
13 02/16/2005 500 Report query with many variables  Open Microsoft Access
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13668744
HI,

connection here;

Imports System
Imports System.Data.Oledb

   Dim Con As New OleDbConnection( _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=c:\InetPub\wwwroot\YourSite\Data\YouDatabase.mdb;Jet OLEDB:Database Password=Yourpassword;")

dim strSQL as string = "Insert into Yourtable value (yourfield1,yourfield2) values('" & variable1 & "', '" & variable2 & "'"

dim cmd as oledbcommand= New OleDbCommand(str, Con)

try
   Cmd.ExecuteNonQuery()
catch ex as exception
   reposnse.write (ex.tostring)
finally
con.close
con.dispose
con=nothing
end try

hope this help.s

ayha


0
 

Author Comment

by:cakirfatih
ID: 13668867
acperkins,
so the experts do not look at other mora than 200 questions that i accepted you mean
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cakirfatih
ID: 13669007
ayha,
do i have to include the Jet OLEDB:Database Password=Yourpassword;") part in the connection string
my database does not have a password

also, woud you explain what the below code does
try
   Cmd.ExecuteNonQuery()
catch ex as exception
   reposnse.write (ex.tostring)
finally

thanks
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13669751

HI,

If no password keep it blank.

try
   Cmd.ExecuteNonQuery()  <------ writing to table
catch ex as exception
   reposnse.write (ex.tostring)  <------- if any error occurred it will written to the screen.
                                              ----You can specify you own message also yere.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13671819
>>so the experts do not look at other mora than 200 questions that i accepted you mean<<
I don;t know, I really could not say.
0
 

Author Comment

by:cakirfatih
ID: 13672110
anyways, thanks for the suggestion
i have sent a message to community forum to close some of the waiting questions.

let see if thats increase some experts' involvement:)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13672399
Also, it would probably help if you did not duplicate this question (and violate the 500 points maximum):
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21370593.html
0
 

Author Comment

by:cakirfatih
ID: 13672550
i am using the below code to connect to an access database and insert some data

Function Get_DSN() As String
        Get_DSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
              & Server.MapPath("Database\AALC06.mdb")
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ValidateTextBoxes()

        If noEntry = True Then
            Exit Sub
        End If

        Dim Cmd As OleDbCommand
        Dim strDSN As String
        strDSN = Get_DSN()

        Dim Conn As New OleDbConnection(strDSN)

        'strSQL = "insert into Students values(" & "'" & txtFirstName.Text & "')"
        Dim InsertStudent As String = "INSERT INTO Students(UIN, Fname, Lname)" & _
                                        "Values('" & txtUIN.Text & "', '" & txtFirstName.Text & "','" & txtLastName.Text & "')"

        Conn = New OleDbConnection(strDSN)
        Cmd = New OleDbCommand(InsertStudent, Conn)

        Conn.Open()
        'Cmd.ExecuteNonQuery()
        'Cmd.Connection.Close()

        Try
            Cmd.ExecuteNonQuery()
        Catch ex As Exception
            Response.Write(ex.ToString)
        Finally
            Conn.Close()
            Conn = Nothing
        End Try


    End Sub
----------------------------------------------

with this code i am getting this error

System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at AALC06.WebForm1.Button1_Click(Object sender, EventArgs e) in \\gap-fs4\wwwroot$\OnlineApplications\AALC06\WebForm1.aspx.vb:line 474

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

any idea on this error?
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13672584
Hi,

check u your database property and make sure that it is not readonly and has write permissin to ASP.net machine account.

ayha
0
 

Author Comment

by:cakirfatih
ID: 13672747
is this going to be the ASPnet machine account of the test server where application files and databse resides
or or the machine that i am using to build applications

i am connecting to another server here

and i gave the ASPNET account write permission on the server, i still have the same error
also when i check the database from the folder, i gues it stays open somehow. I see the .ldb file open there
0
 

Author Comment

by:cakirfatih
ID: 13672839
acperkins,
that question was litte different than this one, but same answer was posted as this one
and i accepted it

0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13672846
hi,

this is where "application files and databse resides"

right click the databse and make sure that it is not readonly and write permissions.

ayha
0
 

Author Comment

by:cakirfatih
ID: 13673203
ayha,
ASPNET machine account has write permisson on the folder where my database is , and database is not read only
maybe the problem is with my connection string
Function Get_DSN() As String
        Get_DSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
              & Server.MapPath("Database\AALC06.mdb")
    End Function

my .NET solution AALC06 is under a folder called OnlineApplications in my web server
http://128.194.183.140/OnlineApplications/AALC06/Webform1.aspx like this

in this case what is my MapPath exactly, how can i make sure that i am using the correct MapPAth address
0
 
LVL 7

Accepted Solution

by:
ayha1999 earned 1200 total points
ID: 13673341
Hi

try this

Function Get_DSN() As String
        Get_DSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
              & Server.MapPath("Database\AALC06.mdb")
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ValidateTextBoxes()

        If noEntry = True Then
            Exit Sub
        End If

        Dim strDSN As String
        strDSN = Get_DSN()

        Dim Conn As New OleDbConnection(strDSN)

        'trSQL = "insert into Students values(" & "'" & txtFirstName.Text & "')"
        Dim InsertStudent As String = "INSERT INTO Students(UIN, Fname, Lname)" & _
                                        "Values('" & txtUIN.Text & "', '" & txtFirstName.Text & "','" & txtLastName.Text & "')"

    If Conn.State <> ConnectionState.Open then
              Conn.Open
    End If

DIm cmd as oleDbCommand=New OledbCommand(InsertStudent,Conn)

        Try
            Cmd.ExecuteNonQuery()
        Catch ex As Exception
            Response.Write(ex.ToString)
        Finally
            Conn.Close()
            Conn = Nothing
        End Try

    End Sub

see what happens now.

ayha
0
 

Author Comment

by:cakirfatih
ID: 13673522
i gave everyone write permission on the database
and now its working

could you tell me how i can insert a DateTime value with a long format, this with data and time 03/12/05 05:25 pm for example

i am using the Getdate( ) function but it is not inserted in  my database

Dim InsertStudent As String = "INSERT INTO Students(UIN, Date_Inserted, Fname, Lname)" & _
                                        "Values('" & txtUIN.Text & "', Getdate(),'" & txtFirstName.Text & "','" & txtLastName.Text & "')"
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 13673749
Getdate() is a T-SQL function (not JET).  Try:
Dim InsertStudent As String = "INSERT INTO Students(UIN, Date_Inserted, Fname, Lname)" & _
                                        "Values('" & txtUIN.Text & "','" & now.ToString("yyyy-MM-dd hh:mm:ss") & "','" & txtFirstName.Text & "','" & txtLastName.Text & "')"
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13674942
Hi,

try

'" & now.Now.ToLongDateString() & "'

ayha
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13674947
sorry

'" & Now.ToLongDateString() & "'

ayha
0
 

Author Comment

by:cakirfatih
ID: 13675280
thanks for the all help guys
my questions continue here
http://www.experts-exchange.com/Web/Q_21371998.html 
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question