7 Replies - 2807 Views - Last Post: 25 April 2006 - 12:00 AM Rate Topic: -----

#1 snow1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-April 06

PROBLEM with insert into.. help!

Posted 22 April 2006 - 01:06 AM

i am having trouble adding a new record into an access database, and below is the code that i used. can anyone please help me?? please...


Private Sub cmd_Insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_Insert.Click

Dim TableName As String = TextBox1.Text
Dim Fields As String = TextBox2.Text
Dim Values As String = TextBox3.Text

InsertData(TableName, Fields, Values)

End Sub



Public Function InsertData(ByVal TableName As String, ByVal Fields As String, ByVal Values As String)
'function for adding a new record

Dim i, j As Integer
Dim Val() As String
Dim ValStr As String
Dim Str As String


Val = Split(Values, ",")
j = UBound(Val)

For i = 0 To j


ValStr = ValStr & "'" & Val(i) & "'"


If i < j Then
ValStr = ValStr & ","
End If
Next
MsgBox("ValStr" & ValStr)

Str = "INSERT INTO" & TableName & "(" & Fields & ") VALUES (" & Values & ")"

Dim ObjConnection As New OleDb.OleDbConnection
ObjConnection = MyConnection()

Dim ObjCommand As New OleDb.OleDbCommand
ObjCommand.Connection = ObjConnection
ObjCommand.CommandType = CommandType.Text
ObjCommand.CommandText = Str

ObjCommand.ExecuteNonQuery()


End Function

Is This A Good Question/Topic? 0
  • +

Replies To: PROBLEM with insert into.. help!

#2 sontek   User is offline

  • D.I.C Regular
  • member icon

Reputation: 8
  • View blog
  • Posts: 283
  • Joined: 13-September 01

Re: PROBLEM with insert into.. help!

Posted 22 April 2006 - 12:05 PM

You need to open up the connection before you run execute query and close the connection afterwards.

That would be like objConnection.Open()



But some things I also think you should do is look into using a string builder for building the select statements rather than concatenating.
Was This Post Helpful? 0
  • +
  • -

#3 Jayman   User is offline

  • Student of Life
  • member icon

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

Re: PROBLEM with insert into.. help!

Posted 22 April 2006 - 12:41 PM

Inside your SQL insert statement the values in your list need to be comma seperated (which you did with the split) and they need to be enclosed in single quotes inside the parenthesis.

Example:
INSERT INTO aTable VALUES ('11', 'test', '22')



What error messages are you getting??
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus   User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 250
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: PROBLEM with insert into.. help!

Posted 22 April 2006 - 01:11 PM

Values to be inserted only need to be enclosed on quotes oif they are text values...no quotes are required for values to be inserted in a field type that is numerical....
Was This Post Helpful? 0
  • +
  • -

#5 Jayman   User is offline

  • Student of Life
  • member icon

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

Re: PROBLEM with insert into.. help!

Posted 22 April 2006 - 05:02 PM

True, numerical values would never be enclosed in quotes. I should have clarified that point. My example was using string values, even the numbers.

But if look at this code, this individual is using string values. And as such they need to be enclosed in single quotes.
Was This Post Helpful? 0
  • +
  • -

#6 snow1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 22-April 06

Re: PROBLEM with insert into.. help!

Posted 24 April 2006 - 10:19 AM

btw, im using string values in my vb code, however, the data type in my database is declared as an integer (or number, in the case of ms access) is it possible that that is the problem? if yes, then what should i do? first convert the strings into int? because inputs will be taken from textboxes. the error message is [syntax error in INSERT INTO statement]

another thing, if im using a sql database, all the values must be enclosed with a single quot right? even if values are of integer type? yes or no?

thanks for all your help! and sorry have a lot of questions.... i just learned database programming this february. thankyou thankyou
Was This Post Helpful? 0
  • +
  • -

#7 Jayman   User is offline

  • Student of Life
  • member icon

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

Re: PROBLEM with insert into.. help!

Posted 24 April 2006 - 10:33 AM

You will need to convert the Strings to Integer before trying to insert into the database. So either use ConvertTo or Parse into the correct data type for the database.

Your insert statement needs to have String values enclosed in single quotes. However, you will not enclose the numeric values in single quotes.

The data types of the values in the INSERT statement must match the data types required by your database, in order for it to work (ie. String to String, Integer to Numeric, etc).

Example:
INSERT INTO aTable VALUES (11, 'string value', 22, 'string value')


Was This Post Helpful? 0
  • +
  • -

#8 born2c0de   User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 187
  • View blog
  • Posts: 4,673
  • Joined: 26-November 04

Re: PROBLEM with insert into.. help!

Posted 25 April 2006 - 12:00 AM

Yes....you need to use single quotation marks.
Include them in the quotes.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1