Insert records into a database

using "insert into" to add new record from a form to a table

Page 1 of 1

10 Replies - 98430 Views - Last Post: 13 January 2009 - 10:23 PM Rate Topic: -----

#1 ambervo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 20-March 07

Insert records into a database

Posted 27 March 2007 - 09:41 AM

Hi All,
I am trying to add new records to a table called ID_ProjectNo in database named Trackingcustomercalls.mdb
The code I have is attached below. It's not working for me.
Please, help me here if you have this problem before.
Any input is valuable to me. Thank you

////////////////////////////////
Private Sub cmdAddSubmit_Click()
Dim MyConn As ADODB.connection
		
		Set MyConn = New ADODB.connection
		Dim MyRecSet1 As New ADODB.Recordset
				
		MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Amber\Desktop\Trackingcustomercalls\Copy of Trackingcustomercalls.mdb"
		MyConn.Open	  
		
			
	Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo VALUES("txtAddProjectNumber.text","txtAddCustomerName.text","txtAddMachine.text")")
	MyRecSet1.Update
			
	MsgBox "The record was added to database", vbCritical, "Succesfull adding"
	MyConn.Close
End Sub


Is This A Good Question/Topic? 1
  • +

Replies To: Insert records into a database

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Insert records into a database

Posted 27 March 2007 - 10:12 AM

Your insert statement will depend on the column data types of your database. But lets just say, for example, that you have all text fields in your database. Then all you need to do is remove the quotes you have around each of the values.
Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo VALUES(txtAddProjectNumber.text,txtAddCustomerName.text,txtAddMachine.text)")


If your database is expecting numerical values then you will need to convert the Text properties to the correct data type.

How is your table constructed in your database?
Was This Post Helpful? 0
  • +
  • -

#3 ambervo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 20-March 07

Re: Insert records into a database

Posted 27 March 2007 - 10:26 AM

View Postjayman9, on 27 Mar, 2007 - 10:12 AM, said:

Your insert statement will depend on the column data types of your database. But lets just say, for example, that you have all text fields in your database. Then all you need to do is remove the quotes you have around each of the values.
Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo VALUES(txtAddProjectNumber.text,txtAddCustomerName.text,txtAddMachine.text)")


If your database is expecting numerical values then you will need to convert the Text properties to the correct data type.

How is your table constructed in your database?



/////////////

The first field is numeric field. How to insert what in txtAddProjectNumber.text into a numeric field in the table?
Thank you.
Was This Post Helpful? 0
  • +
  • -

#4 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Insert records into a database

Posted 27 March 2007 - 12:21 PM

You will need to convert the value to numeric using a Type Conversion.
You can find the different type conversions available here.
Example converting to type integer:
Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo VALUES(CInt(txtAddProjectNumber.text),txtAddCustomerName.text,txtAddMachine.text)")

Was This Post Helpful? 0
  • +
  • -

#5 ambervo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 20-March 07

Re: Insert records into a database

Posted 27 March 2007 - 12:37 PM

View Postjayman9, on 27 Mar, 2007 - 12:21 PM, said:

You will need to convert the value to numeric using a Type Conversion.
You can find the different type conversions available here.
Example converting to type integer:
Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo VALUES(CInt(txtAddProjectNumber.text),txtAddCustomerName.text,txtAddMachine.text)")


///////////////
I did what you suggested for the numeric field and I got error "No value given for one or more required parameters"

What does the error mean?
What did I do wrong? Pleae help!
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Insert records into a database

Posted 27 March 2007 - 01:26 PM

Please post the structure of your table in your database. Include all the columns and their respective data types.

This error most often occurs when you have more columns in your table than values that you are inserting. You may need to specify the column names in your insert statement that you are inserting values into or you might need to provide values for all of the columns.

Refer to the following link for information on how to construct an Insert statement.
http://www.w3schools.../sql_insert.asp
Was This Post Helpful? 0
  • +
  • -

#7 ambervo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 20-March 07

Re: Insert records into a database

Posted 27 March 2007 - 02:05 PM

View Postjayman9, on 27 Mar, 2007 - 01:26 PM, said:

Please post the structure of your table in your database. Include all the columns and their respective data types.

This error most often occurs when you have more columns in your table than values that you are inserting. You may need to specify the column names in your insert statement that you are inserting values into or you might need to provide values for all of the columns.

Refer to the following link for information on how to construct an Insert statement.
http://www.w3schools.../sql_insert.asp

////////////

Here is what I have for the table name ID_ProjectNo:

CustomerID type AutoNumber (primary key)
ProjectNo Number
CustomerName Text
Machine Text
Comment Text
Was This Post Helpful? 0
  • +
  • -

#8 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Insert records into a database

Posted 27 March 2007 - 04:02 PM

Because you are not inserting values into all the columns that your table contains, you need to use the column names in your Insert statement.
Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo (ProjectNo, CustomerName, Machine) VALUES (CInt(txtAddProjectNumber.text),txtAddCustomerName.text,txtAddMachine.text)")



If you were inserting a value into every column except the autonumber column then you could do it that way you were trying.
Was This Post Helpful? 0
  • +
  • -

#9 hasoon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 12-January 09

Re: Insert records into a database

Posted 12 January 2009 - 01:27 AM

View Postjayman9, on 27 Mar, 2007 - 03:02 PM, said:

Because you are not inserting values into all the columns that your table contains, you need to use the column names in your Insert statement.
Set MyRecSet1 = MyConn.Execute("INSERT INTO ID_ProjectNo (ProjectNo, CustomerName, Machine) VALUES (CInt(txtAddProjectNumber.text),txtAddCustomerName.text,txtAddMachine.text)")



If you were inserting a value into every column except the autonumber column then you could do it that way you were trying.



hi. i need help. ive tried to do all what u said in the previous post. but i still get the error "No value given for one or more required parameters".i want to insert data to all column tht i have in my table,and all of the column is text data type, and ive tried all tht u suggest. and since i get the error still, i tried to put my column name in the code,but still i get the same error.


pls help me...=(

thank you very much really appreciate ur time...
Was This Post Helpful? 0
  • +
  • -

#10 djjeavons  Icon User is offline

  • New D.I.C Head

Reputation: 5
  • View blog
  • Posts: 44
  • Joined: 09-January 09

Re: Insert records into a database

Posted 12 January 2009 - 02:55 AM

Hi hasoon

What code have you come up with so far? If it is similar to that which you quoted then you will need to modify your INSERT statement to include the necessary type specifiers for each column (for example, numeric = no type specifier, text = quote specifier, date = hash specifier). Something like:

"INSERT INTO ID_ProjectNo (ProjectNo, CustomerName, Machine) VALUES (" & Convert.ToInt32(txtAddProjectNumber.text) & ", '" & txtAddCustomerName.text & "', '" & txtAddMachine.text & "')"



A better approach to the above would be to use parameterised queries. This tutorial will provide further information on working with databases (both Access and SQL Server) and explains the different types of SQL statements and how to execute them using both in-line and parameterised SQL and also the use of stored procedures (for SQL Server).


HTH
Was This Post Helpful? 0
  • +
  • -

#11 hasoon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 12-January 09

Re: Insert records into a database

Posted 13 January 2009 - 10:23 PM

View Postdjjeavons, on 12 Jan, 2009 - 01:55 AM, said:

Hi hasoon

What code have you come up with so far? If it is similar to that which you quoted then you will need to modify your INSERT statement to include the necessary type specifiers for each column (for example, numeric = no type specifier, text = quote specifier, date = hash specifier). Something like:

"INSERT INTO ID_ProjectNo (ProjectNo, CustomerName, Machine) VALUES (" & Convert.ToInt32(txtAddProjectNumber.text) & ", '" & txtAddCustomerName.text & "', '" & txtAddMachine.text & "')"



A better approach to the above would be to use parameterised queries. This tutorial will provide further information on working with databases (both Access and SQL Server) and explains the different types of SQL statements and how to execute them using both in-line and parameterised SQL and also the use of stored procedures (for SQL Server).


HTH




hi djjeavons, thank u so much for ur reply.
but all my columns are from type text
nevermind i will change some too numeric and try as u suggested

thank u again

=)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1