 |
meddlesome
Wanderer
Joined: 24 Aug 2012
Posts: 70
|
 Posted: Mon Sep 17, 2012 12:36 pm
ADO Database AddNew
|
Reading a database that is in place is no issue. I have done that with the following...
| Code: |
#ADD dbCounter 1
#if (@dbCounter<=97) {
#VAR rs %comcreate( "ADODB.Recordset")
#VAR ConnStr "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\dragcall.mdb"
#VAR Sql "SELECT * FROM list2 WHERE altID = "@dbCounter
#CALL @rs.Open( @Sql, @ConnStr)
#SEND {Fly @rs.Fields("phrase").Value}
#CALL @rs.Close
} {#VAR dbCounter 0} |
What I am looking for is how to Add records to an already existing database.
I have searched up and down the forum, but get nothing of use.
In VB I would have done it as such.
| Code: |
Private Sub QueryDatabase()
On Error GoTo ErrorWithSQL
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [TimeClock] WHERE Serial = '" & txtUnitID & "'"
Dim strUser As String
Dim SecoundPass As Boolean
Dim blnStopTime As Boolean
Dim intMinutes As Integer
Dim intInterval As Integer
Dim cmd As New ADODB.Command
If cn.State = 1 Then Call CloseDB
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Timekeeper.mdb;Jet OLEDB:Database Password=pass3word;"
cn.Open
With cmd
.ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText
End With
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
End With
If Not rs.EOF And Not rs.BOF Then 'Serial was found
If rs.Fields("User") & "" = strUser Then
If rs.Fields("Stopped") Then
intMinutes = 0
blnStopTime = False
lblVerify.Caption = "Time Started"
lblVerify.ForeColor = vbGreen
Else
strTimeSpent = Format(rs.Fields("StartTime") - Time, "hh:mm")
blnStopTime = True
lblVerify.Caption = "Time Stopped"
lblVerify.ForeColor = vbRed
End If
Else
Form2.Label1.Caption = Form2.Label1.Caption & rs.Fields("User") & ""
Form2.Show
Form1.Hide
Exit Sub
End If
Else
'***************ADD NEW RECORD
rs.AddNew
lblVerify.Caption = "Time Started"
lblVerify.ForeColor = vbGreen
End If
intMinutes = Val(Left(strTimeSpent, 2)) * 60
intMinutes = intMinutes + Val(Right(strTimeSpent, 2))
If Val(rs.Fields("Minutes") & "") + intMinutes > intInterval Then
Form3.Show
Me.Hide
Exit Sub
End If
rs.Fields("Serial") = txtUnitID.Text
rs.Fields("User") = strUser
rs.Fields("Minutes") = Str(Val(rs.Fields("Minutes") & "") + intMinutes)
rs.Fields("Date") = Format(Date, "MM/DD/YYYY")
rs.Fields("StartTime") = Time
rs.Fields("Stopped") = blnStopTime
rs.Update
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
txtUnitID.Text = ""
Timer1.Enabled = True
Exit Sub
ErrorWithSQL:
MsgBox "Error with SQL" + vbCr + "at " + Str(Err.Number) + vbCr + Err.Description, , "SQL Error"
End Sub |
The above is a snip from a program I wrote to illustrate ADO is not exactly new to me. The issue is what and how to use it to call the rs.AddNew
I have tried so many variants my head is spinning. |
|
_________________
Intel Core 2 Quad Q9450 @2.66GHz
MDAC 2.8 SP1 ON WINDOWS XP SP3
Msjet40.dll ver 4.0.9511.0 + Security Bulletin MS08-028
CMUD v237
Order number: **7829 |
 |
|
|
meddlesome
Wanderer
Joined: 24 Aug 2012
Posts: 70
|
Posted: Mon Sep 17, 2012 2:06 pm
|
So let's give an example of what I am doing...
| Code: |
#T+ identify
#VAR cn %comcreate( "ADODB.Connection")
#SHOW "#VAR cn %comcreate( 'ADODB.Connection')"
#VAR rs %comcreate( "ADODB.Recordset")
#SHOW "#VAR rs %comcreate( 'ADODB.Recordset')"
#VAR sql "SELECT * FROM list"
#VAR cmd %comcreate("ADODB.Command")
#IF (@cn.state = 1) {closeDB}
#VAR ConnStr "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\items.mdb"
#CALL @cn.Open
#VAR @cmd.ActiveConnection @cn
#CALL @cmd.CommandText @sql
#CALL @cmd.CommandType 1 //Evaluate as a textual definition
#CALL @rs.CursorType 2 // adOpenDynamic
#CALL @rs.CursorLocation 3 // adUseClient
#CALL @rs.LockType 3 // adLockOptimistic
#CALL @rs.Open cmd
#CALL @rs.AddNew
|
Now my suspicions are that the connection string is not working quite right. That is because I never see that 2nd #SHOW
| Code: |
| #SHOW "#VAR rs %comcreate( 'ADODB.Recordset')" |
In fact, I have a #SHOW after each command above, but omitted them for clarity, and none of them except the first one displays.
Past the #CALL @rs.AddNew are a collection of triggers that do a #VAR rs.Fields("FieldName") %1. They give ominous warnings
| Quote: |
| Item cannot be found in the collection corresponding to the requested name or ordianl. |
about the ordinal not being found. That would be true if the database was never opened, so I disregard them for now. Then I get to the prompt trigger that closes the database with a standard #CALL cn.close;#CALL rs.close and get a message that I can't perform this operation on a database that is closed. |
|
_________________
Intel Core 2 Quad Q9450 @2.66GHz
MDAC 2.8 SP1 ON WINDOWS XP SP3
Msjet40.dll ver 4.0.9511.0 + Security Bulletin MS08-028
CMUD v237
Order number: **7829 |
|
|
|
meddlesome
Wanderer
Joined: 24 Aug 2012
Posts: 70
|
Posted: Mon Sep 17, 2012 2:53 pm
|
Ok, UPDATE:
I decided to restart CMUD and retry. Got somethings that I missed and different results from above.
| Quote: |
Your divination is complete...
#VAR cn %comcreate( 'ADODB.Connection')
Object: the Gem of Souls [gem soul red heart soulgem]
#VAR rs %comcreate( 'ADODB.Recordset')
#VAR sql 'SELECT * FROM list'
#VAR cmd %comcreate('ADODB.Command')
#IF (@cn.state = 1) {closeDB}
#VAR ConnStr 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\items.mdb'
Item Type: WAND Effects: MAGIC NO-DROP NO-STORE NO-AUCTION
Equipable Location(s): TAKE HOLD
ERROR: Operator binaryint requires two arguments
ERROR: Operator DIV requires two arguments
|
I also got a message box that fell behind the warning messages that I didn't see before. This maybe my solution here.
| Quote: |
| [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. |
|
|
_________________
Intel Core 2 Quad Q9450 @2.66GHz
MDAC 2.8 SP1 ON WINDOWS XP SP3
Msjet40.dll ver 4.0.9511.0 + Security Bulletin MS08-028
CMUD v237
Order number: **7829 |
|
|
|
Rahab
Wizard
Joined: 22 Mar 2007
Posts: 2320
|
Posted: Tue Sep 18, 2012 1:08 pm
|
I don't know anything about ADO, but my first uninformed question would be, is there a reason
you aren't using the Cmud sql functions: #SQLDB, #SQLCLOSE, %sql, and %sqldb? |
|
|
|
|
meddlesome
Wanderer
Joined: 24 Aug 2012
Posts: 70
|
|
_________________
Intel Core 2 Quad Q9450 @2.66GHz
MDAC 2.8 SP1 ON WINDOWS XP SP3
Msjet40.dll ver 4.0.9511.0 + Security Bulletin MS08-028
CMUD v237
Order number: **7829 |
|
|
|
Rahab
Wizard
Joined: 22 Mar 2007
Posts: 2320
|
Posted: Wed Sep 19, 2012 12:47 pm
|
Ah! When asking complicated questions, it is _very_ important to mention that you are using version 2.37 instead of the current version. There are _huge_ differences between 2.37 and 3.xx.
I hope someone else can help with ADO and COM and version 2.37. |
|
|
|
|
meddlesome
Wanderer
Joined: 24 Aug 2012
Posts: 70
|
Posted: Wed Sep 19, 2012 2:13 pm
|
I was hoping that would get picked up in my signature.
|
|
_________________
Intel Core 2 Quad Q9450 @2.66GHz
MDAC 2.8 SP1 ON WINDOWS XP SP3
Msjet40.dll ver 4.0.9511.0 + Security Bulletin MS08-028
CMUD v237
Order number: **7829 |
|
|
|
Rahab
Wizard
Joined: 22 Mar 2007
Posts: 2320
|
Posted: Wed Sep 19, 2012 4:58 pm
|
Oh, you do have it in your signature. I apologize for not noticing--I usually ignore signatures.
|
|
|
|
|
|
|
|
