- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hello
Been using excel Spreadsheets to collect model information and ran into some issues with users trying to open the same Spreadsheet at the same time and giving us some headaches.
Now we are trying to go further and use a database with MS access,
and as far as i vcould find information on this board the only thing I could do is read information from the Database
Now I want to add information from Ilogic to the database, but just cant find the next empty record to record on it the fields I need. Here is the code Im using.
SyntaxEditor Code Snippet
Sub Main() Dim Table1 As String = "CAD" Dim query As String = "SELECT * FROM " & Table1 Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.accdb;Persist Security Info=False;" Dim ds As New DataSet Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_) cnn.Open() Dim cmd As New OleDbCommand(query, cnn) Dim da As New OleDbDataAdapter(cmd) da.Fill(ds, Table1) cnn.Close() Dim t1 As DataTable = ds.Tables(Table1) Dim row As DataRow Dim Item(2) As String 'Dim oNAME As String Dim OPROJ As String Dim oCREAT As String Dim oDAT As String Dim oCOMENT As String Dim oSTATUS As String oNAME = MT_NAMING OPROJ = iProperties.Value("Project", "Project") oDAT = iProperties.Value("Project", "Creation Date") oCREAT = ThisApplication.GeneralOptions.UserName oCOMENT = DESCRIPCION oSTATUS = STATUS_MASTER For Each row In t1.Rows If row(1) = oNAME Then i = MessageBox.Show("This part Number is allready recorded", "CAD", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1) ElseIf row(1) = "" Then i = MessageBox.Show("Testing empty record", "CAD", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1) row(1)=MT_NAMING End If Next End Sub
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi,
I havent done this with ilogic, but i use VBA to do similar..
This is the code i usually use to add new rows to the database..
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open "Tablename", cn, , , adCmdTable
rs.AddNew
rs!FieldName = DataString
rs.Update
rs.Close
I hope that helps you.
Dean.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Ahhh ok.. i thought u may be able to adapt it to ilogic..
i have no time to look into it for you, maybe someone else can help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Finally did it,
SyntaxEditor Code Snippet
AddReference "System.Data" AddReference "System.Core" AddReference "System.Xml" Imports System.Data.OleDb Imports System.Data Imports System.Xml
SyntaxEditor Code Snippet
Sub Main() ' Dim Tabla As String = "CAD" Dim query As String = "SELECT * FROM CAD WHERE PartNo = '" & MT_NAMING & "'" Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.accdb;Persist Security Info=False;" Dim ds As New DataSet Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_) cnn.Open() Dim cmd As New OleDbCommand(query, cnn) Dim da As New OleDbDataAdapter(cmd) da.Fill(ds, "CAD") cnn.Close() Dim **** = ds.Tables("CAD").rows.count If **** > 0 Then MessageBox.Show(**** & "This Part Number is allready added to the database!", MT_NAMING ) Else Dim OPROJ As String Dim oCREAT As String Dim oDAT As Date Dim oCOMENT As String Dim oSTATUS As String oNAME = MT_NAMING OPROJ = iProperties.Value("Project", "Project") oDAT = iProperties.Value("Project", "Creation Date") oCREAT = ThisApplication.GeneralOptions.UserName oCOMENT = DESCRIPCION oSTATUS = STATUS_MASTER Dim Table_ As String = "CAD" Dim query1 As String = "INSERT INTO " & Table_ & "(Proyect, PartNo, AutorDePieza, FechaCreacion, DescripcionDePieza,Estado) VALUES ('"&iProperties.Value("Project", "Project")&"','"& MT_NAMING &"', '"& oCREAT &"', '"& oDAT &"', '"& oCOMENT &"', '"& STATUS_MASTER &"')" Dim MDBConnString_1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DESKTOP-VDT88EI\database\Ingenieria.accdb;Persist Security Info=False;" Dim ds1 As New DataSet Dim cnn1 As OleDbConnection = New OleDbConnection(MDBConnString_1) cnn1.Open() Dim cmd1 As New OleDbCommand cmd.Connection = cnn1 cmd.CommandText = query1 cmd.ExecuteNonQuery() cnn1.Close() End If End Sub
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Alex Fielder
Inventor Expert
https://github.com/alexfielder/
LinkedIn - Github Inventor Extension Server - Bonkers polygon iLogic thing
Top ten iLogic Tips - API Shortcut In Google Chrome - Assembly Extrusion Example
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Just came across this thread while searching for iLogic Access Commands.
I'm working on a tool that will read and write records into an Access Database. The above code was remixed into something that worked for me. Using an access database found on this forum I was able to determine the next available line (or row) to enter employee data in. I found this particularly helpful in being the bases of my code in recording invoices, PO's and quotes.
Below is the line of code I've used... attach you will find the access database (based on another user's database on this forum).
AddReference "System.Data"
AddReference "System.Core"
AddReference "System.Xml"
Imports System.Data.OleDb
Imports System.Data
Imports System.Xml
Sub Main ()
Dim Table_ As String = "EmployeeInfo"
Dim query As String = "SELECT * FROM " & Table_
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;")
Dim MDBConnString_ As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\rcantos\Downloads\New folder\EmployeeInfo.accdb;Persist Security Info=False;"
Dim ds As New DataSet
Dim cnn As OleDbConnection = New OleDbConnection(MDBConnString_)
con.Open()
Dim cmd As New OleDbCommand(query, cnn)
Dim adp As New OleDbDataAdapter(cmd)
Dim ds12 As DataSet
ds12 = New DataSet
adp.Fill(ds12, "EmployeeInfo")
'DETERMINE THE NEXT AVAILABLE ROW IN ACCESS
If ds12.Tables("EmployeeInfo").Rows.Count = 0 Then
i = i + 1
Else
query = "SELECT MAX(EID) from EmployeeInfo"
cmd = New OleDbCommand(query, con)
adp = New OleDbDataAdapter(cmd)
ds12 = New DataSet
adp.Fill(ds12, "EmployeeInfo")
i = (ds12.Tables(0).Rows(0).Item(0)) + 1
con.Close()
End If
'VERIFY THE ABOVE CODE
MessageBox.Show(i)
End Sub