SQL Insert Statment

[myasin78]

I am trying to insert data into DB using VB.
I have connected, test connection, select data, all fine.
the only problem is that i can't insert data into the tables.

here is some function that might help understand the problem.

thanks in advance.

'declartion
Public Rs1 As New ADODB.Recordset
Public buffer As String
Public dbConn As New ADODB.Connection
Public strSql As String
Public insSql As String

Private Sub DBConnect_Click()
buffer = "DSN=yamato;UID=myasin;PWD=jordan"
dbConn.Open buffer
strSql = "SELECT Docket,patentno, serialno FROM Patent"
Rs1.Open strSql, dbConn, adOpenKeyset
End Sub

Private Sub cmdInsert_Click()
'insert data into the database
Dim docket As Single, patentno As Single, serialno As Single
docket = Val(txt1.Text)
patentno = Val(txt2.Text)
serialno = Val(txt3.Text)
Rs1.MoveLast
strSql = "Insert into patent (Docket, PatentNo, SerialNo) values(docket,patentno,serialno)"
Rs1.Execute strSql, dbConn // here is the problem, //

End Sub

 

[RoyVidar]

Hi!

Try:
[tt]strSql = "Insert into patent (Docket, PatentNo, SerialNo) values (" & docket & ", " & patentno & ", " & serialno & &quot"[/tt]

- all on one line, must concatinate the values of the variables.

Roy-Vidar

 

[myasin78]

I got this error:
data member not defined.


at the following line.
Rs1.Execute strSql, dbConn


thanks

 

[RoyVidar]

ADO recordset doesn't have an execute method, the connection object has.

[tt]dbConn.Execute strSQL[/tt]

- check the helpfiles for optional arguments

Roy-Vidar

 

[myasin78]

I got no error this time, but how can commit to make sure that is it really working.

thanks


myasin

 

[RoyVidar]

"check the helpfiles for optional arguments"

From the help files:
RecordsAffected Optional. A long variable to which the provider returns the number of records that the operation affected.

Don't use this method much, but this argument provides me with enough information.

you could also do a requery on the recordset and use the find method to check it

- else I really do recommend the help files, where there are good examples of how to use this method

BTW if the rs1 is a public, you could just use the addnew method of the recordset in stead of using the execute method of the connection object.

[tt]with rs
.addnew
!docket = Val(txt1.Text)
!patentno = Val(txt2.Text)
!serialno = Val(txt3.Text)
.update
end with[/tt]

Roy-Vidar

 

[myasin78]

I see what you saying, i saw all the values that i have entered. when i used ".update" i got error, .update was not defined, i also tried Rs1.update.

I will try .addnew in a minute, where do get help files.

thanks,

myasin

 

[RoyVidar]

Hmmm - this is VBA forum, if ADO is available as a reference thru the Tools | Reference thingie, then the help for it should also be available - see you're using VB not VBA, don't know if there's a difference (There is at least one VB forum also, see the related forums box just below the forum mvp list).

If you don't get any in VB then you might try to open one of the office programs, hit ALT+F11, then set a reference to the ADO library. Help files should be available. If not - versions 2000+ of office VBA help is not installed by default, but needs to be checked. You might have to install the help files from the office CD.

.update - well it is a method of the ado recordset as well as the dao recordset, but in dao one had to use update for the record to be updated. In ADO a moveprevious or whatever to such effect... would automaticly update a record - but old habits die hard, I always use the .update when updating also ADO.

If rs1.update errors, there must be something else wrong, now taking a look at how you open it, I see why, only specifying adOpenKeyset creates a non updateable recordset. You need also to specify locktype. This is how I usually open a recordset in ADO (exept I mostly work with tables within the same database, and use currentproject.connection in stead of defining and opening a connection object):

[tt]With rs1
.activeconnection=dbConn
.locktype=adlockoptimistic
.cursortype=adopenkeyset ' or adopendynamic
.open strSQL, options:=adcmdtext
if not .bof and not .eof then
...
end if
end with[/tt]

- hmmm first I don't see you're using the recordset in stead of the connection, then I don't see you're using a recordset that's not updateable, think I'll need some new glasses;-) (wonder what else I've missed)

Roy-Vidar

 

[myasin78]

for the past 3 years, I have been programming in C++ and Perl. this is my 3th day in VB. I'm ganna have to install ADO library to look into the help files. in the mean time, I am kind lost.
let me understand what is going on.

1) define database -> buffer = "DSN=Yamato;UID=user;PWD=Pwd"
2) Open Connection -> dbConn.open buffer.
3) write an sql statment -> "insert Blob into table"
4) know we should execute the insert statment, to insert that into DB.
5) I am using Rs1 to exec the statment.

let me post my code, let me know what you think.
put you commet next to code, so i could understand.
I really appreciate your help and patent.



Option Explicit
'public declaration
Public Rs1 As New ADODB.Recordset
Public buffer As String
Public dbConn As New ADODB.Connection
Public strSql As String
Public insSql As String
Private Sub cmdclose_Click()
End 'close window
End Sub
Private Sub cmddbclose_Click()
'Close DB Connection
Rs1.Close
dbConn.Close
Set Rs1 = Nothing
Set dbConn = Nothing
MsgBox ("Closing DB Connection&quot
End Sub
Private Sub cmdInsert_Click()
'insert data into the database
Dim docket As Long, patentno As Long, serialno As Long
docket = Val(txt1.Text)
patentno = Val(txt2.Text)
serialno = Val(txt3.Text)
Rs1.MoveLast
strSql = "Insert into patent (Docket, PatentNo, SerialNo) values( " & docket & "," & patentno & ", " & serialno & &quot"

End Sub
Private Sub Command1_Click()

buffer = "DSN=yamato;UID=myasin;PWD=mypwd"
dbConn.Open buffer
strSql = "SELECT Docket,patentno, serialno FROM Patent"
Rs1.Open strSql, dbConn, adOpenDynamic
MsgBox ("DB Connection is Open&quot

End Sub
Private Sub cmdMoveNext_Click()
With Rs1
If Not .EOF Then
txtoutput.Text = Rs1.Fields(0)
txtpatent.Text = Rs1.Fields(1)
txtserial.Text = Rs1.Fields(2)
listbox.AddItem Rs1.Fields(0)
.MoveNext
Else
MsgBox ("End of File&quot
.MoveLast
End If
End With
End Sub
Private Sub cmdMovePrev_Click()

With Rs1
If Not .BOF Then
txtoutput.Text = Rs1.Fields(0)
txtpatent.Text = Rs1.Fields(1)
txtserial.Text = Rs1.Fields(2)
.MovePrevious
Else
MsgBox ("Begning of File&quot
.MoveFirst
End If
End With
End Sub

 

[RoyVidar]

Think this is as far as I go.

Hope you'll get the Help files. In the meantime, find a book or two on the subject, do a keyword search here on TT in perhaps the VB forum, and there might also be something in some of the Access fora. Here's one ADO resource, there are many more

http://docs.sun.com/source/817-2514-10/Ch11_ADO86.html

Roy-Vidar

 

[myasin78]

Thank You!

 

[PHV]

myasin78, search your computer for files like dao*.chm

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884