add record into my database mysql

[Shodow]

how to add record into my database mysql

[jlego]

insert into table_name [ column1 , column2, column3 ] VALUES [ 'data1' , 'data2' , 'data3' ]

http://www.connectionstrings.com/

[debasisdas]

why you need to post this question in vb 6 forum ?

[jlego]

+1

[Shodow]

why you need to post this question in vb 6 forum ?

because i am using vb6
i am making a booking system
i am using database mysql

[Shodow]

insert into table_name [ column1 , column2, column3 ] VALUES [ 'data1' , 'data2' , 'data3' ]

it's not working

[jlego]

what is your error. what does your code look like?

what does your table look like.

[debasisdas]

what is not working ?

Vb code or database code ?

what is the code that you are working on ?

What is the error message ?

[Shodow]

i use this

insert into table_name [ column1 , column2, column3 ] VALUES [ 'data1' , 'data2' , 'data3' ]

[jlego]

..

[jlego]

do you have a database? and do you have a table created inside of the database?

[debasisdas]

Why are you executing the script posted here, that was only an example. Does you table structure matches exactly the example posted here.

How are you calling the SQL from VB ?

How are you connecting to database ?

Have you connected to the database before calling the SQL ?

[Shodow]

why is this?
what is the error?

[AndreRet]

You don't have to use such a complex connection to Mysql at all. Have a look at the following connection method. The first part is to a server, the second part is to your local pc (localhost) -

Public Sub ServerConnect()

Dim conn As ADODB.Connection
Dim strIPAddress As String

Set conn = New ADODB.Connection
strIPAddress = '###Add the ip address here!!!

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=" & strIPAddress & ";" _
            & "DATABASE=ADMS;" _ 'The database name here to connect to!!!
            & "UID=root;" _ 'In 99% it is the root as UID, unless you have specified a different name when you installed MySql!!!
            & "PWD=;" _ 'Here the password is empty, add password if you have set it when you have installed MySql. It is always good to have a password set!!!!
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 'Is all options enabled, add, delete, edit, photo blobs etc.!!!

conn.CursorLocation = adUseClient
conn.Open
End Sub

On localhost -

Public Sub ServerConnect()

Dim conn As ADODB.Connection
Dim strIPAddress As String

Set conn = New ADODB.Connection
strIPAddress = "Localhost"

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=" & strIPAddress & ";" _
            & "DATABASE=ADMS;" _
            & "UID=root;" _
            & "PWD=;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

conn.CursorLocation = adUseClient
conn.Open
End Sub

That is your connection sorted that can be used throughout the application. JUST ENSURE THAT YOU CLOSE THE CONNECTION AFTER YOU HAVE USED IT, for instance after adding a record, close it by calling -

conn.Close

To add a record, I prefer to use individual values rather than using the INSERT function -

Dim Rs As New ADODB.Recordset
Set Rs = New ADODB.Recordset

Call ServerConnect

Rs.Open "SELECT * FROM YourTableNameHere", conn, adOpenStatic,adLockOptimistic
Rs.AddNew

Rs!FieldNameHere = txtName.Text
'MAKE SURE YOU CHANGE THE TABLE NAME AND FIELD NAMES FIRST. DO NOT JUST COPY THE CODE AS IT IS HERE AND EXPECT IT TO WORK...

Rs.Update

Rs.Close
conn.Close

And that is that.:)

[Shodow]

where will i put the server code and the localhost code? module?

[AndreRet]

In a module, yes.

[Shodow]

ip address?

[AndreRet]

If you are running the MySql database on a server (network) then you would add the IP address (say 192.168.0.20 as a SAMPLE) in the "Server" text box. If not, keep it on localhost.

[Shodow]

when i try to add

Private Sub cmdDone_Click()
Dim Rs As New ADODB.Recordset
Set Rs = New ADODB.Recordset

Call ServerConnect

Rs.Open "SELECT * FROM list1", conn, adOpenStatic, adLockOptimistic
Rs.AddNew


            Rs!CustomerName = txtName.Text
            Rs!ContactNumber = txtContact.Text
            Rs!Date = dt.Value
            Rs!TimeStart = cmbStart.Text
            Rs!TimeEnd = cmbEnd.Text
            Rs!Event = cmbEvent.Text
            Rs!Guest = txtGuest.Text
            Rs!Comment = txtComment.Text

Rs.Update

Rs.Close
conn.Close
End Sub

[AndreRet]

As per your new thread. Thanks for marking this.:)