Insert Data In Database
Hello!
What is the best way to insert 100 vars with the sql command Insert ?
The 100 vars are in one single table.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
What Is The Best Way To Submit Data To Server And Insert Data To Database?
In my program, according to users action it will generate some data, now I want to:
insert data to a database on server
when users submit data, they should have an ID&password, I should be able to check it.
now, what I am doing is:
1) users submit data to server by ftp;
2) a NT service on server monitoring ftp files, if new files are uploaded, then read the file and insert the data to database;
Is there any better way to do this?
Insert Data Into A Database
I have succeeded to insert data into the correct fields in the database, BUT i am having alot of trouble with putting in data in a date/time format.
I have the data in the following fields...
Example:
18-12-03 15:59:25
The fields
DateDay = 18
DateMonth = 12
DateYear = 03
TimeHour = 15
TimeMin = 59
TimeSec = 25
I want to insert that data into the field DateTime (format dd-mm-yy hh:nn:ss) nn = minutes
So any help/ideas?
INSERT INTO table
(number, datetime)
VALUES (15, xxxxxxxx)
Insert Data Access Database
im very new to VB. I created vb forums that lookup data from a acess database...... But how would i inset data from a vb for into a access database... how owuld i start this and are their any examples???
Thanks
Insert Data To External Database
I have an unbound text box on an Access Form, named ExcelTrain. I have another field in an oracle database that Access is linked to, named EXCELLENCE.
I want to be able to enter information into ExcelTrain (on the Access form) and have that value inserted into the field EXCELLENCE on Oracle…..
************************************
EXCELLENCE = Field in an Oracle Table, which is linked through ODBC in Access.
ExcelTrain = and unbound field on a form in Access.
What do I need to define?
Dim stSql As String???
Dim ExcelTrain As String???
Do I need an if/then statement Or can I use an sql statement alone?
Am I on the right track with the code below?
Private Sub ExcelTrainInput_AfterUpdate()
If ExcelTrain = "Y" Then
ExcelTrain = "INSERT INTO Diana.OracleTable " '1
stSql = stSql & "VALUES (" & EXCELLENCE & ") " '
Else
'do nothing
End If
End Sub
Thank You for any help on this!
Insert Data Into Database Problem
hi there,
i ve got a problem with adding data into database , basically i m using my own typeDataset to insert into table when it run, it run properly but didnt store any value into database .. here is my code
Dim tdsReligion As New dsReligion
con1.Open()
daReligion.Fill(tdsReligion)
Dim rows As dsReligion.ReligionTypeRow = tdsReligion.ReligionType.NewReligionTypeRow()
rows.ReligionName = TextBox1.Text
'rows.ReligionID = 1
tdsReligion.ReligionType.AddReligionTypeRow(rows)
tdsReligion.AcceptChanges()
daReligion.Update(tdsReligion)
con1.Close()
please help
Help Flexgrid Data To Insert Into Database
I need help, I have tried to put data from a MsFlexgrid into an Access Database, but at the second record a get this message: Run-Time error '-2147467259' it says that I have tried to duplicate values in the index .....
This is my part of the code
dim LongRow as long
For LongRow = 1 To Flex.Rows
Conn.Execute "INSERT INTO GLOSSARIO" & "(DEFINIZIONE,DESCRIZIONE)VALUES" & "('Flex.TextMatrix(LongRow, 0)','Flex.TextMatrix(LongRow, 1)');"
Next LongRow
Conn.close
What is wrong??
Using SQL INSERT To Place New Data In An ACCESS DATABASE
Could someone please take a look at my program and help! me
I want to write into the database with either the combo boxes or text boxes.... but how do i do it when the database has relasions??.... and with one button....
this is the button code so far...
Private Sub indskriv_Click()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim titel As String
Dim sprog As String
Dim år As String
Dim selskab As String
Dim skuespiller As String
Dim instr As String
data = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=film.mdb;Persist Security Info=False"
con.Open (data)
Sql = "insert into film (titel) values ('" & titel_ind.Text & "')"
con.Execute Sql
Sql = "SELECT * FROM film WHERE titel = ('" & titel_ind.Text & "')"
rs.Open Sql, con, adOpenDynamic
titel = rs("filmid")
rs.Close
Sql = "insert into selskab (navn) values ('" & selsk_ind.Text & "')"
con.Execute Sql
Sql = "SELECT * FROM selskab WHERE navn = ('" & selsk_ind.Text & "')"
rs.Open Sql, con, adOpenDynamic
selskab = rs("id")
'skuespiller = "insert into skuespiller (fornavn, efternavn) values ('" & rolle_for.Text & "','" & rolle_ef.Text & "')"
'con.Execute skuespiller
'tsu = "insert into Film (Titel, Sprog, Udgivelsesår, selskab) values ('" & titel_ind.Text & "','" & sprog_ind.Text & "','" & år_ind.Text & "','" & selskab & "')"
'con.Execute tsu
titel_ind.Text = ""
sprog_ind.Text = ""
år_ind.Text = ""
selsk_ind.Text = ""
MsgBox "du har tilføjet dit indtastede data"
End Sub
the code is not at all finished.... but i would be glad if someone could help me?.... so DL the "program" and take a peek if you want
it is in danish..... so the button you are looking for is "indskriv".... under "indskriv en film"
Insert Data From Text File To Database
Hi All,
I am working on Attendence module, I have a text file which consistof data which I want to insert into my database table attend_tbl
I have made 4 columns in my database as
Date | EmpNo | Time_In | Time_Out
now the text file I have which is attend.txt, it also shows data in 4colums having the same fields as made in my database table.
30/07/2004 000002 1030AM 1034PM
30/07/2004 000006 1016AM 0900PM
please guide me the way throguh which I can insert the record of textfile into database table in theire relevant columns
Please Help me with aforementioned query.
thanx.
Insert Data To MS Access Database From Forms!
Hi Guys.
I am just wondering how I insert data from my window with the textboxes and everything to my table in my Access database.
Also, I have the customerID number on my form and in the database it is an autonumber. How do i show the customers actual customerID when the form window opens?
Cheers and thanks in advance.
Problem When Retrieving Data From Access And Insert Into SQL Database
Hi,
Iam trying to retrive data based on certain criteria and insert into data base in sql server of same structure as access database.
Everything works fine on a workstation with win 98 OS. But when I run the exe on the server it self (win 2000 server) I face the below runtime error:
Runtime Error ‘-2147467259’ ODBC – call Failed
Below is the part of code where I encountered the error. Similar to below statements I have multiple selection & inserts. Errors occurs some time for first table insert and some times 2/3rd.
sql1 = "select * from Document_Master"
rsSql1.Open sql1, dbconn, adOpenForwardOnly, adLockReadOnly
If rsSql1.EOF = False And rsSql1.BOF = False Then
Do While Not rsSql1.EOF
sql2 = "Select * from [" & mysqlconn & "]. [Document_Master]
where Location_Code = '" & rsSql1!Location_Code & "'
rsSql2.Open sql2, dbconn
If rsSql2.EOF = True And rsSql2.BOF = True Then
SQL = "Insert into [" & mysqlconn & "].[Document_Master]
select * from Document_Master where Location_Code = '"
& rsSql1!Location_Code & "'
dbconn.Execute SQL
rsSql2.Close
rsSql1.MoveNext
Loop
End If
rsSql1.Close
Should I go for DTS? If I incorporate script generated by DTS Schedule, after creating package for my application will it work on a PC with out VB & MS SQL Server??
Please help!
Thanks
Insert Data Into A Row In A Database Table In SQL Server With Visual Basic
I figured out the how to retrieve data from SQL, but I have not managed to add data to a table.
Here's what I've got so far. I would appreciate it greatly if somebody could fill in the blanks that would make this procedure work and insert the data.
Public Sub AddToDatabase()
Set RCconn = New adodb.Connection
Set TuneCMD = New adodb.Command
' Establish Recordset
Set Results = New adodb.Recordset
'Establish a Connection
With RCconn
.Provider = "SQLOLEDB"
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Tuneman"
End With
'Open the connection
RCconn.Open
Results.CursorType = adOpenStatic
Results.CursorLocation = adUseClient
Results.Source = "INSERT INTO OrderInfo VALUES('" & frmAddMusicData.txtBand.Text & "')"
Set Results.ActiveConnection = RCconn
Set Results = Nothing
RCconn.Close
Set RCconn = Nothing
End Sub
Insert Sql Wont Insert Data After Decimal
i have a form that uses a Calculation script to do the vat calculation
Code:Option Explicit ' Requires variables to be declared before they are used
Rem * Declarations ***********************************************************
Dim dblNumber As Double 'Number Entered by user
Dim dblResult As Double 'Result of calulation
Dim intOption As Integer 'Operator held (+,-,*,/)
Dim dblMemory As Double 'Number stored in the Memory
Dim blnOption As Boolean 'Number","+","."
Dim blnEqual As Boolean 'Equals error
Dim blnMemory As Boolean 'Memory error
Dim blnError As Boolean 'If Point '.' is press more than once
Dim strVat As String 'VAT user number entered
Dim dblTotal As Double 'VAT Total
Dim strAnswer As String 'VAT Answer
Public Sub VATCALC()
On Error Resume Next
dblTotal = (Invoicesto.Price.Text * Invoicesto.Vatpc.Text) / 100 '* strVat '(txtSubTotal.Text * 17.5) / 100
Invoicesto.exvat.Text = Invoicesto.Price.Text - dblTotal 'Round(strAnswer, 2)
End Sub
VATCALC works, however when i add the product to the table the calulations have no decimal and data after it.
SQL Insert Into Doesn't Insert Any Data
I have couple of problems. I am creating a MP3 player that scans the HD for MP3's and puts them into an Access database (XP, hope this doesn't make a difference). Anyway, I can use this method below to get the data into the database:
Code:
Data2.Recordset.AddNew
Data2.Recordset.Fields("Artist") = Trim(T1)
Data2.Recordset.Update
Data3.Recordset.AddNew
Data3.Recordset.Fields("Artist") = Trim(T1)
Data3.Recordset.Fields("Album") = Trim(T2)
Data3.Recordset.Update
Data1.Recordset.AddNew
Data1.Recordset.Fields("Artist") = Trim(T1)
Data1.Recordset.Fields("Album") = Trim(T2)
Data1.Recordset.Fields("Title") = Trim(T3)
Data1.Recordset.Fields("Genre") = Genre
Data1.Recordset.Fields("Year") = Year
Data1.Recordset.Fields("AudioLink") = T4
Data1.Recordset.Update
And it will actually get the data into the tables and fields that I need exactly. But my problem with this is I read the records in thru SQL:
strSQL1 = "SELECT [Album].* FROM [Album] WHERE Artist= '" & T1_A & "' ORDER BY [Album].[Album]"
Set RS = DB.OpenRecordset(strSQL1, dbOpenDynaset)
Set Data2.Recordset = RS
But when I select a record, it will give me a run-time error 3075. If I comment out the "Set" commands and put in say a MsgBox (strSQL1) instead, it will display the following:
SELECT [Album].* FROM [Album] WHERE Artist='Britney Spears
And nothing else after the name. It should have a ' ORDER BY [Album].[Album] following, but not matter what I try it won't.
If I go into the database and retype the exact same information on top of what was there, it will then work fine, example, if the recordset adds the record "Britney Spears" to the database with the code at the top, and I retype it, it will then work perfectly.
So instead of using the recordset methods, I decided to try and use the SQL Insert Into command:
sqlSTR5 = "INSERT INTO [Artist] (Artist) VALUES ('[" & Replace(T1, "'", "''") & Chr(34) & "]')"
sqlSTR6 = "INSERT INTO [Album] (Artist, Album) VALUES ('[" & Replace(T1, "'", "''") & "]','[" & Replace(T2, "'", "''") & "]')"
sqlSTR7 = "INSERT INTO [Main] (Artist, Album, Title, AudioLink) VALUES ('" & Replace(T1, "'", "''") & "', '" & Replace(T2, "'", "''") & "', '" & Replace(T3, "'", "''") & "', '" & Replace(T4, "'", "''") & "')"
Data2.Database.Execute sqlSTR5
Data2.Refresh
Data3.Database.Execute sqlSTR6
Data3.Refresh
Data1.Database.Execute sqlSTR7
Data1.Refresh
But the problem I get with this is that it searches through all the records, but when I look at the actual database, nothing is in it at all, for any table or records.
Any help would be greatly appreciated!!!!
Thanks, Corey
Select And Insert From Shared Database Into The Local Database.
Hei hei Kiran;
felt happy for your intrest.
Ya, I will explain a bit more about my problem.
Now I have a MS Access 2002 Database which is working perfectly in one system.
Now I have splitted this database with the 'Database splitter' tools menu.I put the backend in a shared folder and front end in every client system and it is working perfectly, without doing any changes.
But as the databse is in the shared folder and the user will have access to this folder only when he has net connection but most of the time they have to work offline.
The user must have access to change only one set of data at a time.
So I have to copy only that set of data from the database in the shared folder to the tables which are in the frontend MDB file.
I think as I don't need to copy all tables, I have to create empty tables which I need to copy and fill the required data into them with VBA code.
for this I need to kow how to connect to the databse in the shared folder and insert into the local database.(of course I have to use the style of connection to connect to the local database as well.)
Is it comprehensible to you , if not i am sorry , please ask where did u not understand.
Any way Thanks a lot for your intrest .
Kiran Karnati.
Insert Data Into Flexgrid Where Date Or Data Equals Value Of MonthView
im trying to insert records into a FlexGrid where the date of the records is equal to the date of the Month View; but whenever the form loads or the date is clicked no records come up.
(the code may be right and it may just be another part of the program that is ruining it but either way im stumped.)
The code im using for both form_load and date_click is:
With dtaManager
If Len(App.Path) <= 3 Then
.DatabaseName = App.Path & "Manager.mdb"
Else
.DatabaseName = App.Path & "Manager.mdb"
End If
query = "select Date,Time,Name,Street,Town,"
query = query & "State,PCode,"
query = query & "Phone from Manager "
query = query & "where Date = '*" & MVCalender.Value & "*' order by Time"
.RecordSource = query
.Refresh
End With
MSDates.Refresh
Help if u can PLease
This stuff can really get to u :(
Thanks,
Monks
How To Insert Temporary Data In Access(table) Using Data Grid
How to insert data into a table using a data grid.
A little background,i am using SS TAB in my forms i need to hold data temporarly im my data grid
and then finaly at click it should be inserted in the table.The SS TAB is linked to ADOC,which is inturn
linked to the table in access.
How To Insert Temporary Data In Access(table) Using Data Grid
How to insert data into a table using a data grid.
A little background,i am using SS TAB in my forms i need to hold data temporarly im my data grid
and then finaly at click it should be inserted in the table.The SS TAB is linked to ADOC,which is inturn
linked to table.
Edited by - manav74 on 10/29/2003 1:01:07 AM
QUESTION - Sql Insert From One Database Into Another Database
hello,
i'm trying to add information from one database to another database.
for instance data1.mdb and data2.mdb
for data1.mdb:
one table called sis; fields are -> student_id, l_name, f_name
for data2.mdb:
several tables, but one only working with table tlc.; fields are -> student_id, l_name, f_name, logged_status, transactions
now this is the code that i'm using to insert into the database:
sql4 = "select * from sis where student_id = '" & txtStudentSSN.Text & "'"
rsSis.Open sql4, sisCnn
If (txtStudentSSN.Text = rsSis.Fields("student_id")) Then
StudentId = rsSis.Fields("student_id")
sql4 = "insert into tlc(student_id) in data2.mdb select tlc.student_id, tlc.l_name, tlc.f_name from tlc where student_id = ' " & StudentId & " ' "
End If
Set rsSis = Nothing
but my code is not working. can someone pls help me out?
thx.
Insert Data Into A Accees Data Base
how can i insert data into a access database .
ie defining the connections and the query???
when opportunities arises ,only the prepared mind is fovoured
How To Insert To Database
hi
My project is book sales.My inserting table name subscriber_senddetails.
Every Month only one book sent to subscriber.More than one sent means display message "Already this month books sent to this id'.
In my inserting table no records means it work well.
But another time i inserting same id,same month means it cant throw error message "already book sent this id"
my data base oracle 9i
Any body know this concept means pls reply me.
Code:
dim bkentriesrs As New ADODB.Recordset
If bkentriesrs.State = 1 Then bkentriesrs.Close
bkentriesrs.Open "select * from subscriber_senddetails where subscriber_id='" & sid & "' and month_of_issue ='" & cmbmonsendsub.Text & " " & cmbyearsendsub.Text & "'", Dbcon, adOpenDynamic
If bkentriesrs.EOF Then
Dbcon.Execute "insert into subscriber_senddetails(subscriber_id,month_of_issue) values('" & Trim(sid) & "','" & UCase(Trim(cmbmonsendsub.Text)) & " " & UCase(Trim(cmbyearsendsub)) & "')"
Dbcon.Execute "update subscriber set years_of_subscription=years_of_subscription - 1 where subscriber_id='" & sid & "' "
MsgBox "You Sucessfully Sent This Month Book to" & " " & sid
Else
MsgBox (sid & " already registered for this month")
End If
Edit by italkid: Don't use the word "Urgent" when looking for free help...
Insert Into Database
Hi
I'm having problems with inserting new data into database.
Here comes code.
Please help me.
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:vbpodacišifre.mdb;"
MyConn.Open
Dim sqlStr As String
sqlStr = "INSERT INTO Šifre (šifra, talijanski_opis, euro, hrvatski_opis, oznaka, dobavljač, napomena) Values (Text1.Text, text2.Text, text4, text3.Text, combo1, text6.Text, text5.Text)"
MsgBox sqlStr
Set MyRecSet = MyConn.Execute(sqlStr)
MyConn.Close
Also if somebode can help me with deleting records from database.
THNX
Insert Into Database Help!!
I am trying to insert data into the fields of a table and I have a question to change the data in the field 8 of the table. To be brief what iam trying to do is the if i have the text6 as value 3 and text 4 as 20 that means the record is loaded 60 times in the table, but what i want to do is i want to replace the field 8 with "2" after it reaches 20 and with "3" after it reaches 40 ... ie.. from 1 to 20 it should show 1 and from 20 to 40 as 2 and so on . I know i have to replace the sql code ,,, but kinda running into problems ,,, need ur thoughts on this... thanks
sql = "INSERT INTO table1(field1,field2,field3,field4,field5,field6,field7,field8,field9) VALUES("
sql = sql & "'"
sql = sql & Text1
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & Text2
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & Text3
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & "XYZ"
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & Label11.Caption
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & Text5
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & Format(Now, "yyyymmdd")
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
sql = sql & "1"
sql = sql & "'"
sql = sql & ","
sql = sql & "'"
For j = 1 To Text6
For k = 1 To Text4
MyConn.Execute (sql & Text9 & "')")
Text9= Val(Text9) + 1
Next k
Next j
Insert IP In Yo Database
Hi guys!
I have a problem : i have a IP - sheet : 203.255.57-127.0 . I want insert all IPs possible of that IP - sheet in to databse . Please help me !(We need to analyse that IP - Sheet and insert in to database)
INSERT INTO A Different Database
I want to copy the contents of some fields in one database to other fields in a separate database. I'm using the INSERT INTO function to do this, but I keep getting an error. I'm not sure if I should be using this function.
Code:
Set DBConn = New ADODB.Connection
DBConn.CursorLocation = adUseClient
DBConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= C:Database1.mdb"
DBConn.Execute "INSERT INTO Customer IN 'C:Database2.mdb' ( [Customer Number], [Customer Name], Address1, [Inv Address],
Phone1, Fax1, Contact, Email, WebSite, [Vat Rate], [Credit Ref], [Currency] )
SELECT DISTINCTROW Customer.RecNo AS [Customer Number], Customer.Name AS [Customer Name], [Address] AS Address,
Customer.InvAddress AS [Inv Address], [Phone] AS Phone, [Fax] AS Fax, Customer.Contact AS Contact, Customer.Email AS Email, Customer.Website AS WebSite,
VatCode.Percentage AS [Vat Rate], Customer.CreditRef AS [Credit Ref], Curr.Description AS [Currency]
FROM (Customer LEFT JOIN VatCode ON Customer.Balance = VatCode.Code) LEFT JOIN Curr ON Customer.Currency = Curr.Number;
The error I'm getting is:
Syntax error in 'INSERT INTO' statement
Insert Into Database
i have 3 user entry fields and 1 auto generating today's date need to be inserted into fields in database at SQL server.How do i do this?
How Do I Insert Into Database
i need to know how to insert a total into a database from code. the code is
If cmbPreperation.Visible = True Then
Select Case (cmbPreperation.List(cmbPreperation.ListIndex))
Case "Downspouts": dblRate = 2
Case "Shutters": dblRate = 3
Case "Storm Windows": dblRate = 3
Case "Hand Wash Areas": dblRate = 250
Case "Hand Wash Soffits": dblRate = 100
Case "Glazing": dblRate = 10
Case "Caulk": dblRate = 1
Case "10 min spot": dblRate = 6
Case "20 min spot": dblRate = 3
Case "Feather Sand": dblRate = 3
Case "Easy Soffit": dblRate = 15
Case "Hard Soffit": dblRate = 9
Case "Easy Scrape": dblRate = 70
Case "Medium Scrape": dblRate = 50
Case "Hard Scrape": dblRate = 35
Case "Area Sand": dblRate = 60
End Select
i need to enter the calculation into a database called "main" and the group called "customerdatabase" with the cells equil to the names in perenthises. i need the code to enter next to the "case" statement
thankyou
How To Insert JPG Into Database
Hi all,
I'm building an application that will store JPG's. Using ADO how can I affect the OLE Object field in my Access database with the content of a JPG. In other words I want to be able, once the JPG stored into the database, to view the JPG directly from the database without it being on a drive somewhere. I'ved structured my database with an OLE Object field. Is this the right kind of data field? If not what is the right one to use? Can anyone help me please.
Thanks,
Miguel
Database Insert
I am trying to insert a new record into my database and I am receiving the following error:
"-2147217900 (80040e14)
Unknown field or variable name RC102"
If I use a explicit value in the insert statement such as 'RC111' it will insert this in the db, but not if I use my variable newlot.
newlot = "RC102"
sql = "Insert into trdglot.lot-no(lot-number, issued) Values (" & newlot & ", yes)"
cmdlot.ActiveConnection = connlot
cmdlot.CommandType = adCmdText
cmdlot.CommandText = sql
rslot.LockType = adLockOptimistic
Set rslot = New Recordset
Set rslot = cmdlot.Execute
Thanks.
Insert Null Into Database
i'm Using this Execute Command
db.execute "Insert into tbl1 (Field1,Field2,Field3) values(Value1,Value2,Value3)"
These values are directly fromt he Text Boxes.
Between these three Values any one value can be null. So how cani Check any Value to Null and if it is Null then how can i insert Null into Database. bcz Throgh the Form when i leave any Text Box Blank it Give me Syntax Error. While i want to insert Empty value in that Field.
any Help will be Appriciated
Insert Date Into Database
Hello,
I hope to know that how to insert a date which user select from DTPicker like 25/7/2005 into a database where the datatype for this field is dateTime?
TQ!
Database Insert With 2 Connections
I have two connections pointing on different databases.
those databases have the same tables and fields
I want to insert data in connection1.table1 from connection2.table2
what should I try ?
How To Insert Ole Object In Database
hello friends
I have to insert a ole object in a database table using VB.
i have to create a command button in a form and by clicking this i will brows the world file and it should be saved in the table field.
plz help me regarding this.
regards
vip
Using VB 5.0 To Insert Into Access Database
In executing the following code I get no error message, just nothing seems to happen. The accrec table does not change. Any ideas?
Thanks,
met12
Set dbs = OpenDatabase("c:vbinvenVB.mdb")
dbs.Execute "Insert into accrec" & "(Acc, Invoice, Check, Invoicedate, Paiddate, Salescredit, Tax, InitialDebit, Balance) VALUES " & "('2332','4444','123123','5/7/2004','5/7/2004','JLT','1.25','9.75','10.00');"
dbs.Close
Access Database INSERT INTO
I'm trying to insert data into a Microsoft Access Database (.mdb).
Code:
Code:
Public Sub InsertData(ByVal strData1 As String, ByVal strData2 As String, ByVal strData3 As String)
Open_DBCon
Set cmdCommand = New ADODB.Command
With cmdCommand
Set .ActiveConnection = dbcon
.CommandType = adCmdText
.CommandText = "INSERT INTO inserttest " & "(test1, test2, test3) VALUES " & "('" & strData1 & "', '" & strData2 & "', '" & strData3 & "');"
Set adoRec = .Execute
Set adoRec = Nothing
End With
Set cmdCommand = Nothing
Close_DBCon
End Sub
I've also tried it like this:
.CommandText = "INSERT INTO inserttest (test1, test2, test3) VALUES ('" & strData1 & "', '" & strData2 & "', '" & strData3 & '" "
but it still errors, too.
It errors "Syntax error in INSERT INTO statement".
Am I doing it incorrectly?
Insert Into Database From Csv File, VB6
Hi everyone.
I have some code that reads data from a csv file into a mssql server database.
The problem is that the values that the code is trying to insert into the database are the table column headings, and not the actual data values. (This is the content of the csv file: Col1,Col2,Col3,Col4
1,2,3,4). This code is not written by me, and I have some trouble finding out why this is happening. I would really appreciate if someone could take a look at the code posted below, and give me some feedback on why this is happening.
Code:
Private Sub Command1_Click()
Dim cn As New ADODB.connection
mServer = "MyServer"
mDb = "MyDataBase"
mUsr = "sa"
mPwd = ""
cn.ConnectionString = _
"Provider=SQLOLEDB;Persist Security Info=False;User ID=" + mUsr + ";" _
+ "Password=" + mPwd + ";Initial Catalog= " + mDb + " ; Data Source=" + mServer + ""
ImportTextFile cn, "tbl_MyTable", "C:MyFile.csv"
End Sub
Public Function ImportTextFile(cn As Object, _
ByVal tblName As String, FileFullPath As String, _
Optional FieldDelimiter As String = ",", _
Optional RecordDelimiter As String = vbCrLf) As Boolean
'PURPOSE: Imports a delimited text file into a database
'PARAMTERS:cn -- an open ado connection
' : tblName -- import destination table name
' : FileFullPath -- Full Path of File to import form
' : FieldDelimiter -- (Optional) String character(s) in
' file separating field values
' within a record; defaults
' to ","
' : RecordDelimiter -- (Optional) String character(s)
' separating records within text
' file; defaults to vbcrlf
'RETURNS: True if successful, false otherwise
'ImportTextFile cn, "qry_LagerUt", "C:mxlog.txt"
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sFileContents As String
Dim iFileNum As Integer
Dim sTableSplit() As String
Dim sRecordSplit() As String
Dim lCtr As Integer
Dim iCtr As Integer
Dim iFieldCtr As Integer
Dim lRecordCount As Long
Dim iFieldsToImport As Integer
'These variables prevent
'having to requery a recordset
'for each record
Dim asFieldNames() As String
Dim abFieldIsString() As Boolean
Dim iFieldCount As Integer
Dim sSQL As String
Dim bQuote As Boolean
'cn.Open
On Error GoTo ErrHandler
If Not TypeOf cn Is ADODB.connection Then Exit Function
If Dir(FileFullPath) = "" Then Exit Function
If cn.State = 0 Then cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = tblName
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
iFieldCount = rs.Fields.Count
rs.Close
ReDim asFieldNames(iFieldCount - 1) As String
ReDim abFieldIsString(iFieldCount - 1) As Boolean
For iCtr = 0 To iFieldCount - 1
asFieldNames(iCtr) = "[" & rs.Fields(iCtr).Name & "]"
abFieldIsString(iCtr) = FieldIsString(rs.Fields(iCtr))
Next
iFileNum = FreeFile
Open FileFullPath For Input As #iFileNum
sFileContents = Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
'split file contents into rows
sTableSplit = Split(sFileContents, RecordDelimiter)
lRecordCount = UBound(sTableSplit)
'make it "all or nothing: whole text
'file or none of it
cn.BeginTrans
For lCtr = 0 To lRecordCount - 1
'split record into field values
sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _
iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)
'construct sql
sSQL = "INSERT INTO " & tblName & " ("
For iCtr = 0 To iFieldsToImport - 1
bQuote = abFieldIsString(iCtr)
sSQL = sSQL & asFieldNames(iCtr)
If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
Next iCtr
sSQL = sSQL & ") VALUES ("
For iCtr = 0 To iFieldsToImport - 1
If abFieldIsString(iCtr) Then
sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr))
Else
sSQL = sSQL & sRecordSplit(iCtr)
End If
If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
Next iCtr
sSQL = sSQL & ")"
cn.Execute sSQL
Next lCtr
cn.CommitTrans
rs.Close
Close #iFileNum
Set rs = Nothing
Set cmd = Nothing
ImportTextFile = True
MsgBox "sucsess"
Exit Function
ErrHandler:
On Error Resume Next
If cn.State <> 0 Then cn.RollbackTrans
If iFileNum > 0 Then Close #iFileNum
If rs.State <> 0 Then rs.Close
Set rs = Nothing
Set cmd = Nothing
End Function
Private Function FieldIsString(FieldObject As ADODB.Field) _
As Boolean
Select Case FieldObject.Type
Case adBSTR, adChar, adVarChar, adWChar, adVarWChar, _
adLongVarChar, adLongVarWChar
FieldIsString = True
Case Else
FieldIsString = False
End Select
End Function
Private Function prepStringForSQL(ByVal sValue As String) _
As String
Dim sAns As String
sAns = Replace(sValue, Chr(39), "''")
sAns = "'" & sAns & "'"
prepStringForSQL = sAns
End Function
Big thank you to anyone that can help me out with this.
Kind regards
objelland
Insert Database Not In Order
how come when i insert different sets of data ...i got the order all mess up? is this the norm?( yes though i can re-arrange by order)
Multiple Insert Into Database
I hv a SQL server table 'spmidishad'. Theere are 7 columns. One of it is Partner.
Well, my searchresults.asp displays all the records retrived from my database (spmidishad). At the end of one row, there is a checkbox. Once a few is checked, i will press on the IMPORT button. This import button (the form action is from import.asp)
is to import (insert) all the checked records into the database.. All rows inserted (the data) are the same except for the Partner column..
Well, i want someone who can help me to figure out how I am suppose to do this..? Hope u understand what Im talking about here... Thankx..
Database Insert Issue
Greetings,
I'm having an interesting time trying to get this insert statement to work in my VB app:
INSERT INTO Data (CharId, Value, DateTime, SubgroupItem, Trace1, TraceId1, Trace2, TraceId2, Trace3, TraceId3, Trace4, TraceId4) VALUES (142, -147.380069, '03/06/02 10:35:38', 0, 'M10', 50, '2', 52, '65', 51, 'N', 56)
I've tested and tested the query right in MS Access as well, but I keep getting a syntax error when the "Value" and/or "DateTime" value is in the query.
The data type for Value is Number, format Double; and the data type for DateTime is Date/Time, format Genreral Date.
What's frustrating, is I even take values that are ALREADY IN the database and plug them in for Value and DateTime, and I still get the same freakin' error:
"Syntax Error in 'INSERT INTO' statement.
So its totally frustrating me. Like I say the query syntax works great when I don't have Value or DateTime in it.
Any advice anyone? Please help.
Thank you,
Jim
Insert '&&' Symbol Into Database
This code will loop through any string you pass it, doubling each ampersand it finds.
VB Code:
Public Function DoubleAmp(WhichString As String) As String On Error Resume Next 'init Dim vIterate As Boolean Dim vTmpLng As Long Dim vStartPos As Long vStartPos = 1 vIterate = True Do While vIterate = True 'check for ampersand & double (for label fields, etc) vTmpLng = InStr(vStartPos, WhichString, "&", vbTextCompare) If vTmpLng <> 0 Then WhichString = Left(WhichString, vTmpLng) & "&" _ & Right(WhichString, Len(WhichString) - vTmpLng) vStartPos = vTmpLng + 2 Else vIterate = False End If Loop DoubleAmp = WhichStringEnd Function
Insert Statement Used From One Database To Another
hi all,
i have two tables in two different database i want to use the insert and
select statement to copy the data from one table to another can anyoone help
me with the codes i use mysql as back end
Insert SQL Into To Database Table
Hello
I am using a datagrid and want to save a record the user has added to access database. Does anyone have any code on how to write the sql insert into from a datagrid to access database. As this is a one to many l will have to insert to 2 different tables.
Many thanks in advance
Steve
Insert Two Database Table
i want to some data . I use access 2000 and data connection us ado.
it problem has becase i use same table design,same database name and same password.access no has user authorize level.
Code:
Public Const DefaultLProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
Public Const DefaultUID = ";User ID="
Public Const DefaultInitialCatalog = ";Initial Catalog="
Public Const DefaultDataSource = ";Data Source="
Public Const DefaultPersistSecurity = ";Persist Security Info=False"
Public Const DefaultDataPassword = ";Jet OLEDBatabase Password="
Public Const DefaultLUserName = ""
Public Const DefaultSUserName = ";User ID=Admin"
Public Function LConnection(DBN As String)
LConnectionStr = ""
LConnectionStr = DefaultLProvider & DefaultDataSource & DBN & DefaultPersistSecurity & DefaultDataPassword & "sa"
Public Sub LDbConnection(LocType As ADODB.LockTypeEnum)
Set Lconn = New ADODB.Connection
Lconn.Open LConnectionStr ', DefaultLUserName, DefaultLUserPassword, LocType
End Sub
Other Data base connection is same above.I insert into first database Table to other database Table it prolem is Invalid database password.
Insert Into Other Database Table
Hi ,
I have a problem with adodc insert into two database .It has same table and same password.I want to insert records from Table1 (at db1.mdb) to Table1 (at db2.mdb).
db1.mdb password is (123) and other is same.
It problem is (Invalid Password)
Please help me how to solute this problem.I use adodc connection use.
How To Insert A Record In Database Using SQL?
hi
i am using oracle in backend.
i am having a problem in sql statement when i use the following staement it gives compiler error.
i wana ask what am i doing wrong?
Dim s As String
s = "insert into product(ITEMNO,ITEMDS)VALUES('"&text1.Text&"','"&text2.Text&"')"
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft ODBC for Oracle};UID=fareeha;PWD=dfdre740;"
cn.Execute s
cn.Close
Set cn = Nothing
thanks
fareeha
DATABASE INSERT/UPDATE
Dear all,
Is there any easy coding to update all values where id = 1 from Table A on Server A to the same table on Server B ?
Or I must programing like this...
' SEECT DATA FROM SERVER A' DATABASE
cmmA.CommandText = "select * from datacard where id = 1"
cmmA.CommandType = adCmdText
Set rst = cmmA.Execute
While Not rst.EOF
Code = rst("code")
btype = rst("btype")
name = rst("e_name")
name = rst("c_name")
sector = rst("e_sector")
...........................ETC
' INSERT DATA TO SERVER B' DATABASE
cmmB.CommandText = "insert into datacard value(code,btype,name,sector,e_directors,c_directors,e_secretary,c_secretary,e_reg_office , reg_office, head_office, c_head_office, e_share_reg, c_share_reg, e_auditors,c_auditors , e_bankers, c_bankers, e_leg_adv, c_leg_adv, e_sum_review, c_sum_review, e_sum_prospect,c_sum_prospect , e_chairman, c_chairman, e_major_shareholder, c_major_shareholder, e_oldname,c_oldname , e_profile, c_profile, e_structure, c_structure, e_review, c_review, e_prospect, c_prospect,additional,additional0,additional1,additional2,additional3,additional4,auth_share,iss_share , tel, fax, email, url, c_edit_time, e_edit_time)"
cmmB.CommandType = adCmdText
Set rst = cmmB.Execute
Its too long ~_~
Any method to simplify those coding?
THANKS!
Insert Database Into Program
Can u somehow insert the database (I am currenty using an MS access database) into your application
so that if u give away the app to someone they cant enter the database thru Access to alter the databse
I only want them to do that from within the app.
Aktaion - Very new to VB and Databases
so this might me a dumb question (?)
Thanx
|
TRACKER
