vb program insert into >>problem

[rhex_tendo]

---

'We solve problems in different way ->>
'Inserting records

Dim rs as New ADODB.Recordset
Set rs=New ADODB.Recordset
rs.Open "SELECT Field1,Field2,Field3 FROM TABLE", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Field1=TextBox1.text
rs!Field2=TextBox2.text
rs!Field3=TextBox3.text
rs.Update
rs.Close

'
'

[rhex_tendo]

if txtRef is of some value like REF1'2, how is it being saved?

'He use Replace function to replace ' to `
'if txtRef has the value of REF1'2, what will be saved to the database is REF1`2
'
'or pwd sab i.trap daan nga dili makainput ang user ug special characters nga dili needed
'lahi nsb na nga code...
'
'

[SYNCH]

So you are saying that the way i do coding is wrong? if So, can you tell me how to correct the way i do insertion on a sql server?

No one said that it is wrong but it is not the preferred coding. Just imagine the implication of having long queries stored in a variable with many fields, etc. Think about maintaining your code in the long run. In fact, you are to move your data layer isolated from your presentation layer but that's another thing.

What I prefer to code is the one I posted and see the benefits of it. If you are going to use Command and Parameter objects to save data, whether it is SQL Server or Oracle or Access, you do not need to worry whether to use quote to delimit data (dates -> hashes for Access {Access do support quote sometimes}, quote for SQL Server).

Opening a Recordset just to insert a record is not efficient if your table is of millions of records. Note, however that these vary to different databases. Using Access database, it actually retrieves all the records before it creates a blank record (AddNew), hence you need to set a flag to force it to append only, ADO -> see ADO Provider Properties and Settings, DAO -> add dbAppendOnly flag.

'He use Replace function to replace ' to `
'if txtRef has the value of REF1'2, what will be saved to the database is REF1`2

What if ' is significant to the data?

[rhex_tendo]

'"what if?" mao jd ni kasagaran question sa mga tarag.himo ug program..
'
'rs.addnew bitaw ako gamit before, insert into nsb ako gamit now kay few lines lang,
'mao sb lagi, kinahanglan careful jud sa pagcode..
'han-ay jud sab ba para dili mglibog....
'depende rsab na sa naghimo unsa iya paagi or ila paagi kung grupo..
'
'thanks s info bro, I learned something..
'
'

[Cometburn]

What if ' is significant to the data?

the ' will be replaced by ` and will be saved on the db. For the reason that single quotes(') would generate an error during insertion.

during retrieval of the field with ` on it, will be replaced with '.

Thats how the data is saved.

[SYNCH]

the ' will be replaced by ` and will be saved on the db. For the reason that single quotes(') would generate an error during insertion.

during retrieval of the field with ` on it, will be replaced with '.

Thats how the data is saved.

OK. Say months or years later, you changed your program due to some enhancements, will you be able to remember that after retrieving the data you need to convert all ` to '? What if your database will be shared to other applications, are you going to warn the other programmers to convert `?

The point is, do not alter the data unless there is a good reason. Changing a character just to bypass a delimiter is not even an application logic, it is just a hack as to get around the ' problem. We already have the fix and that is using the Parameter object.

"what if?" mao jd ni kasagaran question sa mga tarag.himo ug program..

In fact, 80% of your time you should be asking why are you writing that logic (logic designing). As a programmer, you only need 10~20% of your time to code because you already thought about it. Design your logic first before writing. 10 lines of good quality code (LOC) is better than 100 without careful thought.

[rhex_tendo]

'<nice...hehehe....
'as of now, balik ko gamit sa rs.addnew...rs.update
'sample lng 2 nga code bro, gi.modify lang to nako para magamit nako kadaghan
'..tanan save sa database kadto ra nga module ako gamit... ok rmn sb xa ...hehehe
'..mgpractice pako anang command ug parameter...
'/nice>

[sanggano_boy]

the ' will be replaced by ` and will be saved on the db. For the reason that single quotes(') would generate an error during insertion.

during retrieval of the field with ` on it, will be replaced with '.

Thats how the data is saved.

in VB query you can use ('') double single quote or try (\') backslash single quote to replace the single quote in the query or from the text field.

- daghan nanga buang aning single quote sa query.

you can replace() function to do that sa VB.

for me dili advisable mo use ka ug other characters replacing single quote
lahi rajud ang Programmer's kaysa Programmer`s

- Cebujobline.com | Cebu's Free job posting and resume bank

[sunduwu]

Private Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
Dim sqlstr As String

rs.Open sqlstr, Conn, adOpenDynamic, adLockOptimistic

sqlstr = " insert into user_act(id,password,uname,date)Values ('"&txtid.text&"','"&txtpassword.text"','"&txtunam e.text&"','#"&txtdate.text&"#)"


unsa sayop ani bro...dli man maka save cya data sa database? expert i need ur help

kanang naka red ( '# ) mao na tanaw nako nga error... ug dapat naka format ang imo txtdate.text, ug dapat cguro naa space between sa mga ampersand (&) for string concatenation

Unsa mana imo database MySQL, MS SQL, MS ACCESS?

try ani nga pagka sulat ang sql string nimo

sqlstr = "insert into user_act(id,password,uname,date) Values('" & txtid.text & "','" & txtpassword.text & "','" & txtuname.text &"',#" & format(txtdate.text,"Short Date") & "#)"

kng data type mismatch japon... try alisdi ang "date" nga field into "sdate" or bisan unsa nga gus2 nimo sa field name

before ko maka limot dapat kani ----> rs.Open sqlstr, Conn, adOpenDynamic, adLockOptimistic after na sa imo sql query string

hinaot nga nakatabang ko... ^_^

[skeptic_rob]

kanang naka red ( '# ) mao na tanaw nako nga error... ug dapat naka format ang imo txtdate.text, ug dapat cguro naa space between sa mga ampersand (&) for string concatenation

Unsa mana imo database MySQL, MS SQL, MS ACCESS?

try ani nga pagka sulat ang sql string nimo

sqlstr = "insert into user_act(id,password,uname,date) Values('" & txtid.text & "','" & txtpassword.text & "','" & txtuname.text &"',#" & format(txtdate.text,"Short Date") & "#)"

kng data type mismatch japon... try alisdi ang "date" nga field into "sdate" or bisan unsa nga gus2 nimo sa field name

before ko maka limot dapat kani ----> rs.Open sqlstr, Conn, adOpenDynamic, adLockOptimistic after na sa imo sql query string

hinaot nga nakatabang ko... ^_^

how about im using the cbo.*** sa ako insert unsa on ani pag himu sql string sa sa insert nga category....mao sad ni naka trigger sa ako code..