How to add data in access using vb6 | Member | | Join Date: Mar 2008
Posts: 52
| |
Hi, i need to extend my system functionality . I need to do add and edit function. Can anyone help me by guide how to do so ? I got error when i use this coding. -
Dim cn1 As ADODB.Connection
-
Dim rs1 As ADODB.Recordset
-
Dim sqlString As String
-
-
' Add new record -
-
sqlString = "Insert INTO Alternator Engine,Volt,Amp)" _
-
& " VALUES (" & txtAlternator.Text & "," & txtVolt.Text & "," & txtAmp.Text & ")"
-
-
Set rs1 = cn1.Execute(sqlString)
-
-
' Clear the fields -
-
txtAlternator.Text = ""
-
txtVolt.Text .Text = "" txtAmp.Text = ""
|  | Expert | | Join Date: Feb 2008
Posts: 414
| | | re: How to add data in access using vb6
Your SQL ordering\syntax is wrong.
The SQL syntax is as follows: -
INSERT INTO [TableName] (fieldname,fieldname,fieldname) VALUES ('" & Value1 & "', '" & Value2 & "', '" & Value3 & "')
-
-
Your missing your table identifier. What table those fields are in.
Also, I COULD be wrong, but I don't think you can have a field name in a table with a space in it (Alternator Engine is not a valid name for a column, if you go check your table design I'm betting thats not the name)
So you need something like this instead: -
sqlString = "Insert INTO MyTable (AlternatorEngine,Volt,Amp)" _
-
& " VALUES ('" & txtAlternator.Text & "'," & txtVolt.Text & "," & txtAmp.Text & ")"
-
Also note that I have placed single quote ' around the txtAlternator.Text. This is because that is likely a string data type in your table design. Any string\text data type in a table will need the single quotes around it '" & x & "', if it is a number\integer\long then you just need the " & x & "
|  | Moderator | | Join Date: Nov 2006 Location: Upstate NY - US
Posts: 2,268
| | | re: How to add data in access using vb6
Hey there!
You've come to the right place, although I am not too skilled in ADODB. I can post something in DAO that works just great:-)
Add reference to Microsoft DAO 3.06 Object Library -
Private Sub subt_Click() 'this funtion will load entry into database
-
-
Dim my_database As Database
-
Set my_database = OpenDatabase("C:\DataMining\Data_Central.mdb")
-
my_database.Execute "insert into Data_Central.Employee(UserID, EmployeeID, TodayDate, DOB,Title,Fname,Mname,Lname,Suffix, Address1,Address2,City,State,PostalCode,Country,Gender,MaritalStatus,HomePhone,MobilePhone,Fax,Email,LicenseNum,Day,Month,Year,County) Values('" & Text1(0).Text & "','" & Text1(1).Text & "' , '" & Text1(2).Text & "' , '" & Text1(3).Text & "','" & Text1(4).Text & "' , '" & Text1(5).Text & "' ,'" & Text1(6).Text & "' ,'" & Text1(7).Text & "' ,'" & Text1(8).Text & "' ,'" & Text1(9).Text & "' ,'" & Text1(10).Text & "' ,'" & Text1(11).Text & "' ,'" & Text1(12).Text & "','" & Text1(13).Text & "' ,'" & Text1(14).Text & "' ,'" & Text1(15).Text & "' ,'" & Text1(16).Text & "' ,'" & Text1(17).Text & "' ,'" & Text1(18).Text & "' ,'" & Text1(19).Text & "' ,'" & Text1(20).Text & "' ,'" & Text1(21).Text & "' ,'" & Text1(22).Text & "','" & Text1(23).Text & "','" & Text1(24).Text & "','" & Text1(25).Text & "')"
-
my_database.Close ' close the database
-
-
End Sub
-
You probably don't need all of the textboxes I have added...
Have fun:-)
|  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,511
| | | re: How to add data in access using vb6
If using adodb then try using this
create a connection object
open the connection -
con.BeginTrans
-
con.execute "your insert statment goes here"
-
'con.execute "your update statment goes here"
-
con.CommitTrans
-
| | Newbie | | Join Date: Jul 2008
Posts: 3
| | | re: How to add data in access using vb6
I wanted to view the table of sqlite database in a datagrid or Flexgrid of VB 6.
and allow datagrid for edit and update to the databae.
Here is my code...
But it showed all the table in a single row of flex grid :s
Option Explicit
'// SQL Lite dll declarations:
Private Declare Sub sqlite3_open Lib "SQLite3VB.dll" (ByVal filename As String, ByRef handle As Long)
Private Declare Sub sqlite3_close Lib "SQLite3VB.dll" (ByVal DB_Handle As Long)
Private Declare Function sqlite3_last_insert_rowid Lib "SQLite3VB.dll" (ByVal DB_Handle As Long) As Long
Private Declare Function sqlite3_changes Lib "SQLite3VB.dll" (ByVal DB_Handle As Long) As Long
Private Declare Function sqlite_get_table Lib "SQLite3VB.dll" (ByVal DB_Handle As Long, ByVal SQLString As String, ByRef errstr As String) As Variant()
Private Declare Function sqlite_libversion Lib "SQLite3VB.dll" () As String ' Now returns a BSTR
'// This function returns the number of rows from the last sql statement. Use this to ensure you have a valid array
Private Declare Function number_of_rows_from_last_call Lib "SQLite3VB.dll" () As Long
Private Sub Command1_Click()
Dim dbfile As String
Dim dbquery As String
Dim db As Long
Dim mvar As Variant
Dim mv1 As Variant
Dim errstr As String
Dim row As Long
Dim col As String
Dim mstr As String
Dim i As Long
Dim L1 As ListItem
Dim j As Long
dbfile = App.Path & "\jas.db"
dbquery = "select * from rec2"
If dbfile = "" Or dbquery = "" Then
MsgBox "Query not found:"
Else
sqlite3_open dbfile, db
If db > 0 Then
mvar = sqlite_get_table(db, dbquery, errstr)
row = number_of_rows_from_last_call
row = row + 1
lbl1.Caption = row
If row > 0 Then
For Each mv1 In mvar
mstr = mv1
col = mstr
If i < row Then
DataGrid1.TextMatrix(i, 1) = col
i = i + 1
End If
If i >= row Then
DataGrid1.TextMatrix(i, 2) = col
End If
Next
End If
End If
sqlite3_close db
End If
End Sub
|  | Similar Visual Basic 4 / 5 / 6 bytes | | | /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|