Inserting A DataTable Into Access

Sep 23, 2005

Alrght I'm new to access databases so I don't want to make the mistake of assuming I know more than I do, so please help me out if you can. Thanks.

I would like to insert a DataTable that is created from reading an ASPX page's TextBoxes into an Access table or two. So here is my code for the insert.

Code: C#

PHP Code:#region Insert
        public bool Insert(ref DataTable InsertData)
        {

            OleDbConnection myConnection = new OleDbConnection();
            OleDbCommand myCommand = new OleDbCommand();
            OleDbDataAdapter myAdapter = new OleDbDataAdapter();
            OleDbParameter param;
            DataTable dt = new DataTable();
            StringBuilder SQL = new StringBuilder();

            try
            {
                // Build SQL Statement
                SQL.Append("INSERT INTO Driver (");
                SQL.Append("    Driver.ID,");
                SQL.Append("    Driver.ReservationID,");
                SQL.Append("    Driver.PrimaryContact,");
                SQL.Append("    Driver.FirstName,");
                SQL.Append("    Driver.LastName,");
                SQL.Append("    Driver.Address1,");
                SQL.Append("    Driver.Address2,");
                SQL.Append("    Driver.City,");
                SQL.Append("    Driver.State,");
                SQL.Append("    Driver.Postal_Code,");
                SQL.Append("    Driver.Ownership,");
                SQL.Append("    Driver.HomePhone,");
                SQL.Append("    Driver.CellPhone,");
                SQL.Append("    Driver.AutoInsurance,");
                SQL.Append("    Driver.Agent,");
                SQL.Append("    Driver.Policy,");
                SQL.Append("    Driver.Phone,");
                SQL.Append("    Driver.Birthday,");
                SQL.Append("    Driver.DriverLicNum,");
                SQL.Append("    Driver.DriverLicState,");
                SQL.Append("    Driver.DriverLicExpDt,");
                SQL.Append("    Driver.DrivingRecord,");
                SQL.Append("    Driver.DrivingRecordDetails,");
                SQL.Append("    Driver.DriverNumber,");
                SQL.Append("    Driver.IsDriver,");
                SQL.Append("    Driver.InsertDt,");
                SQL.Append("    Driver.ReservationID)");
                // Insert Values
                SQL.Append("    VALUES(");
                SQL.Append("    @ReservationID,");
                SQL.Append("    @PrimaryContact,");
                SQL.Append("    @FirstName,");
                SQL.Append("    @LastName,");
                SQL.Append("    @Address1,");
                SQL.Append("    @Address2,");
                SQL.Append("    @City,");
                SQL.Append("    @State,");
                SQL.Append("    @Postal_Code,");
                SQL.Append("    @Ownership,");
                SQL.Append("    @HomePhone,");
                SQL.Append("    @CellPhone,");
                SQL.Append("    @AutoInsurance,");
                SQL.Append("    @Agent,");
                SQL.Append("    @Policy,");
                SQL.Append("    @Phone,");
                SQL.Append("    @Birthday,");
                SQL.Append("    @DriverLicNum,");
                SQL.Append("    @DriverLicState,");
                SQL.Append("    @DriverLicExpDt,");
                SQL.Append("    @DrivingRecord,");
                SQL.Append("    @DrivingRecordDetails,");
                SQL.Append("    @DriverNumber,");
                SQL.Append("    @IsDriver,");
                SQL.Append("    @InsertDt,");
                SQL.Append("    @ReservationID)");
                SQL.Append("    )");

                // Define Connection
                myConnection.ConnectionString = _ConnectionString;

                // Build Command
                myCommand.Connection = myConnection;
                myCommand.CommandType = CommandType.Text;
                myCommand.CommandText = SQL.ToString();

                // Execute 
                myConnection.Open();
                myAdapter.SelectCommand = myCommand;
                myCommand.ExecuteNonQuery();


            }
            catch( Exception myException )
            {
                // Raise Error
                throw new System.Exception("Exception occurred in: " + MODULE_NAME + ".GetListByRVType(). Exception Error: " + myException.Message, myException);
            }
            finally
            {
                // Clean up
                myConnection.Close();
                myCommand.Dispose();
                myAdapter.Dispose();
            }

        }

        #endregion 

View Replies


ADVERTISEMENT

Autofill Datatable Columns With Increasing Numbers

Nov 27, 2006

How to autofill datatable columns with increasing numbers - like the way in Excel? :confused:

Given a datatable with 2 columns : ID & Data.

(a) How to fill the column "Data" with consecutive numbers - 1000, 1001, 1002 ...?
(b) How to fill a specified range of continuous records - eg. records ID100, ID101, ID102 ... ID300 - with (a)?

Many many thanks for any help and discussion!

View 2 Replies View Related

Combine Datatable Rows Based In Common Value

Nov 27, 2012

i have been trying to combine data from 2 different sources, to make unique rows: here it is my situation Data in DGV already in dgv, Unbound:

column1 | Qty
"Sugar", 100
"Salt", 100
"Color", 200
"Malto", 150

Data in datatable:

column1 | Qty

"Sugar", 80
"Salt", 60
"Apio", 25
"Lemon", 60
"Color", 60

So i want a control that matches the column1 in datatable against DGV.Column1, and if value matches only add second column value and if not, then make a new row in DGV.
Final decided outcome:

column1 | Qty|Qty2
"Sugar"| 100 | 80
"Salt" | 100 | 60
"Color"| 200 | 60
"Malto"| 150
"Apio" | | 25

View 1 Replies View Related

Inserting Name Into Word From Access

Jan 28, 2005

Dear All:

I am currently printing a letter from access to word using a command button. So far, so good.

However, I wish to add the person's name on the letter who I am currently viewing on the form. The name of the fields on the form is called "FULL NAME".

Here is the code I am using to print the letter from Access:

Private Sub PrintLetter_Click()
On Error GoTo Err_PrintLetter_Click
Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
Set appWord = New Word.Application
' The path/name of the Word document goes here
Set wdDoc = appWord.Documents.Open("C:documents and settingsuserdesktopEXPRESS DIPLOMA LETTER.doc")
wdDoc.PrintOut (False)
wdDoc.Close (False)
appWord.Quit
Set wdDoc = Nothing
Set appWord = Nothing
Exit_PrintLetter_Click:
Exit Sub
Err_PrintLetter_Click:
MsgBox Err.Description
Resume Exit_PrintLetter_Click

End Sub

Any help is appreciated.

Regrds,

Dee

View 6 Replies View Related

Inserting A Blank Into A Date Field In An Access DB

Dec 28, 2005

I want to update an MS Access table date field with a blank date. How do I go about it? Right now, if the "dateAskFor_Funds" text field is blank, and I try to update the "AskFor_Funds_Date" field in the database, I get an error. That is why I don't do anything.

What expression should I use in the place of

intJunk = 1

in the code caption below.

Private Sub cmdUpdateApplication_Click()

Dim todaysDate
Dim intJunk As Integer
Dim rst As ADODB.Recordset

todaysDate = Date ' MyDate contains the current system date.

Set rst = New ADODB.Recordset
rst.Open "EA_Apps_List", CurrentProject.Connection, adOpenKeyset, adLockOptimistic



If IsNull(Me!dateAskFor_Funds) Or Me!dateAskFor_Funds = "" Then
intJunk = 1
Else
rst!AskFor_Funds_Date = Format(Me!dateAskFor_Funds, "Short Date")
End If



Set rst = Nothing

End Sub

View 1 Replies View Related

Inserting Pictures Making Access Crash?

Nov 14, 2006

focking pissed off. i want to add an image to a some forms, the database is all working and that. but when i put a picture in in the design view and then go to view it the whole thing crashs. i nearly lost the whole data base from putting one on the home page, thank fock i had a back up and now ionly have to redo one form as the other one wont open anymore it will just crash every time i try to open it. i have no idea whats going on, i have other images that i put on it before, but i cant put any on now. anyone every encounted this and how do i over come it!

View 2 Replies View Related

Inserting Data From Excell To Access Table

Mar 23, 2005

I am currently using access 97.

I am trying to read from an excel file that has the same format but changes data every day due to reports that I run from a different program. I just export them to this excel file called test.xls

What I want to do is import this data from the excel file and insert it into existing data in my table. I need to this every day.

Currently I cannot get the data to append. I can only insert the data once and cannot get it to append. I tried using macros, but think it would be better to use VB.

Any help you could provide would be great.

Thank You

View 1 Replies View Related

Access Inserting A Space In Memo Field

Apr 15, 2015

I like to store SQL code as a memo field in a table. I then use a form to collect selection criteria from the user, and this is used to create a 'WHERE' clause which is then appended to the stored SQL. This has always worked, but in one recent case the SQL failed. On examination I discovered that although the SQL is correctly stored in the memo field, when Access retrieves the code it inserts a space character in the middle of one of the words, thereby of course creating an invalid SQL string. It's easy to work around it, but why this happens? A bug in Access?

View 1 Replies View Related

Inserting Photos As Records In Access 2003

Sep 6, 2012

I have created a tabel in access 2003 which has information about employees and i have to put even their photos as records so i can see them in a report that i have created too.

View 5 Replies View Related

Reports :: Inserting Blank Lines To Access Report

Jul 27, 2013

I have a specific form, paper version, that I'd like to reproduce in an Access report. This form, which has 34 blank lines in the paper version, is used when people need to borrow some Tools or Equipment on a short period of time.

My report is made up in Access, but, if a person signs for, lets say, three different piece of Equipment, once it is sent to the printer, it prints three lines, which are the equipement signed for and it leave a big empty space before it reach the page footer.

What I need is something that will fill that empty space with blank lines. I found the following on the Net which meets my needs up to a point. What I mean is once it print the last line, it keeps on printing the last line until it meets the total count of 34 lines.

Here what I found on the Net: (note that I have a =count(*) into the Group Header)

Option Compare Database 'Use database order for string comparisons
Option Explicit
Global TotCount As Integer
' Call the SetCount() function from the group header section's
' OnPrint property using the syntax: =SetCount(Report)

[Code] ....

I submitted an attach file (blank_lines.gif) to show you what I end up with.

View 3 Replies View Related

Modules & VBA :: Ms Access Lock Table While Inserting Data

Oct 31, 2014

In a Access 2003 database, I have an "Inscriptions" (subscription) database with a primary key on 2 fields idPersonnel (employee) and idSession. I have made a form so that user can select a session (in a listbox), then one or more employee (another listbox) and suscribe them to that session by using a button, which, on VBA side, first check that there is enough room on the session (defined by "MaxParticipants" field on "Sessions" table, linked to "Inscriptions" table on idSession), then insert data in "Inscriptions" table

This is working fine in a single-user environnement, but fails if 2 people want to join some employees on the same session at the same time, as I have a confirmation message between check and insertion. Therefore 2 users can select employees, get the confirmation message (at this point both are told there is enough room), resulting in having more people than expected joined to the session. Fortuneatly, if both users try to insert the same employee(s) to that table, one will get a duplicate error, but insertion will be made if employees are different.

On another DB engine, such as SQL server, I would use a stored procedure that would lock the table, do the check and the insertion then unlock the table.

But it does not seem to be possible in MS Access. What are the possibilities in MS Access to prevent a session from having more than maximum number of participants ?

View 1 Replies View Related

General :: Access 2003 / Inserting Field Into Import Specification?

Aug 16, 2012

I'm using Access 2003.

if I create an Import Specification, how do I add an extra field in the middle of the field list? All I can see to do is re-enter all fields from the new field downward.

View 3 Replies View Related

Inserting Time Into Access 2010 Query Field When Character Is Entered In That Field

Mar 4, 2015

Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.

View 10 Replies View Related

Error At Inserting New Value

Jan 24, 2006

i got the atached message when i am trying to insert a new value, the wierd thing is when i do okayu, the value is being insrted...

View 5 Replies View Related

Need Help Inserting Table

Jul 31, 2006

I have a database that contains a few tables and queries etc. I made a copy of this database so that I could change some of the VB code with out affecting the database information. I have completed all the code and now I want to input those old tables into my finished verion. I am not sure how to do this. Any ideas on how I could do this would greatly be appreciated. Thanks.

View 8 Replies View Related

Inserting A Video

Feb 26, 2007

Hi
I really need to insert a movie in my database. I have tried inserting a Windows Media Player object in a form, but I dont know how to link it to a movie on my computer. Please help me with this. By the way I'm using DAO, if that helps at all.

Please help me as soon as possible, I'm in a hurry
Thanks in advance

View 4 Replies View Related

Inserting Columns

Dec 30, 2004

i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas?
thanks
briar

View 6 Replies View Related

Inserting Records...

Aug 4, 2005

Ok, I know this is already part of another thread, but my problem is a bit more complex...

The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.

I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.

Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.

That's my solution, but there may be something simpler. Any thoughts?

View 7 Replies View Related

Inserting Into A Table

Apr 23, 2007

just wondering if anyone knows how to insert about 10 lines of data into an already existing table?

View 3 Replies View Related

Textbox Value Not Inserting

Jan 23, 2007

Hi,

Can anyone tell me what i'm doing wrong with this insert statement?


INSERT INTO TBL_PROCESS_NAME
VALUES( ' & [Forms]![frmIntroduction]![txtProcess_Name] & ' )


the form is loaded when the query is run, i've tried taking out the & and the ' but to no avail, all i get in the table is [Forms]![frmIntroduction]![txtProcess_Name]

Thanks

Michael

View 7 Replies View Related

Inserting Into A Table With 2 Pks

Apr 5, 2007

The security table is made up of two primary keys: thing, personorgroup
When i run this statement to insert into the security table

INSERT INTO security (thing, personorgroup, accessrights)
SELECT '252600649', '4020', '255'
FROM PROFILE
WHERE not exists(select * from security
where security.thing = '252600649' and security.personorgroup = '4020');

I get this error:
Server: Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'.
The statement has been terminated.

Anybody know how i can perform my insert successfully? :D

View 9 Replies View Related

Inserting Value In Table

Sep 23, 2004

i am trying to achieve a simple thing (at least it seems so to me!)...
I am trying to add a new record to a table (LP_Product_Name) with only one field (Product_Name) as a part of a 'on Lost Focus' event of a text box (txb_ProductName).
Code:Private Sub txb_productname_LostFocus()' Declare Variables Dim db As Database Dim rs As DAO.Recordset Set db = Nothing Set rs = Nothing' Assign Values to Variables Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset("LP_Product_Name")' Enter New Product name to the table With rs .AddNew .Fields("Product_Name") = Me.txb_productname End With ' Close variables Set db = Nothing Set rs = Nothing' Restore Visible formMe.cmb_productname.Visible = TrueMe.cmb_productname.SetFocusMe.txb_productname.Visible = FalseEnd Sub
When I type something in the textbox and shift focus to another field, nothing happens... no error message and no added value to the table! I dont know what is going on. It just doesnt work!

On the same form I also have a combo box that uses the same table (LP_Product_Name) as its rowsource. I want the Combo box to immediately show this added value.

Can someone please help!?!
Thanks!

View 7 Replies View Related

Inserting Values

Nov 16, 2006

HI,
I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow.
my form made of a combination of two tables attributes ad when I enter say:

name
tel number
address
car
car reg
booking time

then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking.
thanks

View 1 Replies View Related

Help With Inserting Date

Jan 8, 2007

Hi All,
This should be a simple thing however Im having some problems. Im trying to insert a record into access. I can insert everything except the date. I cant seem to get the right format. Below is what Im trying:
Code:strSQL ="INSERT INTO GISrequest (givenName, surName, email, division, title, purpose, audience, date) VALUES ('firstmname', "&strVal1&", 'anemail@somewhere.com', 'thedivision', 'thetitle', 'purposestring', 'audience',#" & AccessDate(NOW()) & "#)"

I think the error is in how I format the date, but Im not sure. Ive looked on the net for examples but I havent found anything that works... can someone suggest what may be wrong or perhaps provide (or point me to ) an example of inserting a date into an access database with ASP?

Many Thanks

View 3 Replies View Related

Inserting Error???

Jan 10, 2007

hEYA all, ive got a problem, im trying to inserting data from an array to my access table- but cant do this due to this error:-Data type mismatch in criteria expression.

Heres my insert code- which its genereating the error at:
Code:sub InsertDBCmd( )'================================================= ========================================SET objRs = SERVER.createObject( "ADODB.recordSet" )dim sqlStatmentsqlStatment = "INSERT INTO Members "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "[A/C no], [Address_Code], "sqlStatment = sqlStatment & "[Managing Director], [Title], [Buying Group Member], [M D Sal], [Rebate Contact], " sqlStatment = sqlStatment & "[Rebate Title], [Rebate Sal], [Replied], [Member Name], [S/Holder 2], [S/Holder], " sqlStatment = sqlStatment & "[ALPHA TYPE], [Member Type], sqlStatment = sqlStatment & ") "sqlStatment = sqlStatment & "VALUES "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "'" & C & "', '" & No & "', " For n = 1 to 15 if n <> 15 then sqlStatment = sqlStatment & "'" & FieldsArray(n) & "', " else sqlStatment = sqlStatment & "'" & FieldsArray(n) & "' " end if Next sqlStatment = sqlStatment & ") "'rESPONSE.WRITE(sqlStatment)'RESPONSE.ENDobjRs.OPEN sqlStatment, objConn,1,3'================================================= ========================================end sub

Any code or suggestions pls??

View 1 Replies View Related

Comparing 2 Tbles And Then Inserting New Only

Sep 17, 2005

I have two tables named datadistint and links
and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.

Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat)
Select 1,3,2,4,5 From datadistint Where
1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links)
and 2 Not in (Select LinkDescription From links)
and 4 Not in (Select maincat From links)
and 5 Not in (Select cat From links)

i ran the querry as above but it doesn't work what should i do or what's wrong with this querry,
because i need to compare all five fields and then insert only new values.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved