[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

MS Access connection and data insert

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
cakirfatih
Asked:
cakirfatih
  • 9
  • 7
  • 4
2 Solutions
 
Anthony PerkinsCommented:
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
 
ayha1999Commented:
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
 
cakirfatihAuthor Commented:
acperkins,
so the experts do not look at other mora than 200 questions that i accepted you mean
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
cakirfatihAuthor Commented:
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
 
ayha1999Commented:

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
 
Anthony PerkinsCommented:
>>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
 
cakirfatihAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
cakirfatihAuthor Commented:
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
 
ayha1999Commented:
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
 
cakirfatihAuthor Commented:
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
 
cakirfatihAuthor Commented:
acperkins,
that question was litte different than this one, but same answer was posted as this one
and i accepted it

0
 
ayha1999Commented:
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
 
cakirfatihAuthor Commented:
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
 
ayha1999Commented:
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
 
cakirfatihAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
ayha1999Commented:
Hi,

try

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

ayha
0
 
ayha1999Commented:
sorry

'" & Now.ToLongDateString() & "'

ayha
0
 
cakirfatihAuthor Commented:
thanks for the all help guys
my questions continue here
http://www.experts-exchange.com/Web/Q_21371998.html 
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now