Problem with INSERT INTO from a TextBox

F

furiousferret

Member
Joined
Dec 21, 2005
Messages
16
Programming Experience
1-3
Hello All,

I'm having a problem with an app that is supposed to insert data from the Textbox into a database field. For simplicity's sake, I just created a simple app and isolated the problem. I cannot get the TextBox info to insert into my query. I've been spinning on this problem for hours and I know its something easy. Any help would be appreciated.

VB.NET: 
[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.OleDb
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Form1
[/SIZE][SIZE=2][COLOR=#0000ff]Inherits[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.Form
#[/SIZE][SIZE=2][COLOR=#0000ff]Region[/COLOR][/SIZE][SIZE=2] " Windows Form Designer generated code "
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2]()
[/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].New()
[/SIZE][SIZE=2][COLOR=#008000]'This call is required by the Windows Form Designer.
[/COLOR][/SIZE][SIZE=2]InitializeComponent()
[/SIZE][SIZE=2][COLOR=#008000]'Add any initialization after the InitializeComponent() call
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Form overrides dispose to clean up the component list.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Overloads[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Overrides[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Dispose([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] disposing [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] disposing [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] (components [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]components.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Dispose(disposing)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Required by the Windows Form Designer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] components [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.ComponentModel.IContainer
[/SIZE][SIZE=2][COLOR=#008000]'NOTE: The following procedure is required by the Windows Form Designer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'It can be modified using the Windows Form Designer. 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Do not modify it using the code editor.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] TextBox1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Button1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] TextBox2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.TextBox
<System.Diagnostics.DebuggerStepThrough()> [/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] InitializeComponent()
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Button1 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].SuspendLayout()
[/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'TextBox1
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1.Location = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Point(136, 8)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1.Multiline = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1.Name = "TextBox1"
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1.Size = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Size(576, 200)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1.TabIndex = 0
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1.Text = ""
[/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Button1
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Button1.Location = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Point(16, 16)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Button1.Name = "Button1"
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Button1.TabIndex = 1
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Button1.Text = "Insert Into"
[/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'TextBox2
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2.Location = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Point(144, 336)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2.Name = "TextBox2"
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2.Size = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Size(584, 20)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2.TabIndex = 2
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2.Text = ""
[/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Form1
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].AutoScaleBaseSize = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Size(5, 13)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].ClientSize = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Drawing.Size(752, 366)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Controls.Add([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox2)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Controls.Add([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Button1)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Controls.Add([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TextBox1)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Name = "Form1"
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Text = "Form1"
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].ResumeLayout([/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2]#[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Region
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Set Database Variables
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MessageQueue.mdb;" [/SIZE][SIZE=2][COLOR=#008000]'User Id=Chris;Password=;" 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oleConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(connString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oleAdapt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] txtRead [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] qString1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "INSERT INTO Messages ([Text]) VALUES ('" & txtRead & "')"
[/SIZE][SIZE=2][COLOR=#008000]'The below string works, well keep it in there until we can get the other one functioning.
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Dim qString1 As String = "INSERT INTO Messages ([Text]) VALUES ('test')"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] qComm1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(qString1, oleConn)
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
oleConn.Open()
TextBox1.Text() = txtRead.ToString
qComm1.ExecuteNonQuery()
oleConn.Close()
TextBox2.Text = qString1
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE]
 
Sort by date Sort by votes
Are you getting an OleDbException or is the query just not doing it?
Eihter way lets start by geting rid of that awful string concatenation...

VB.NET: 
Dim qString1 AsString = "INSERT INTO Messages ([Text]) VALUES (?)"
Dim qComm1 AsNew OleDbCommand(qString1, oleConn)
qComm1.Parameters.Add("@Test",OleDbType.VarWchar,255,"The original column name").value = txtread

Try that and then let me know what kind of problem you are having, i.e exception thrown etc.
 
Upvote 0 Downvote
The Query is not inserting the the text from the textbox into the INSERT INTO form. It inserts a new record, but nothing shows. When I show the query in the other text box it shows:

INSERT INTO Messages ([Text]) VALUES ('')

I added the code and it does the same thing.
 
Upvote 0 Downvote
And as an afterthought:
It still won't work: you'll have to give the parameter a value (or concatenate the statement) after assigning txtRead a value.

By the way: you do not need the variabel txtRead, you can use TextBox1.Text directly.
 
Upvote 0 Downvote
Don Delegate said:
And as an afterthought:
It still won't work: you'll have to give the parameter a value (or concatenate the statement) after assigning txtRead a value.

By the way: you do not need the variabel txtRead, you can use TextBox1.Text directly.
Click to expand...

That's what I thought. However, when I used just TextBox1.Text in there, it wouldn't work.
 
Upvote 0 Downvote
If you have already added the parameter to the command:
VB.NET: 
[SIZE=2][COLOR=#0000ff]Private [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, _
[/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
  oleConn.Open()[/SIZE]
[SIZE=2][SIZE=2] qComm1.Parameters(0).Value = TextBox1.Text[/SIZE][/SIZE][SIZE=2][SIZE=2]
[/SIZE]qComm1.ExecuteNonQuery()
  oleConn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

Else (ADO.NET 2.0 syntax, not sure if AddWithValue existed in ADO.NET 1.1, if not use Parameters.Add().Value )
Also note that adding the parameter in the event handler will prevent you from executing the command more than once (you'll add a parameter each time).
VB.NET: 
[SIZE=2][COLOR=#0000ff]Private [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, _
[/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
  oleConn.Open()[/SIZE]
[SIZE=2][SIZE=2] qComm1.Parameters.AddWithValue("Text", TextBox1.Text)[/SIZE][/SIZE][SIZE=2][SIZE=2]
[/SIZE] qComm1.ExecuteNonQuery()
  oleConn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

Or with concatenation (not recommended)
VB.NET: 
[SIZE=2][COLOR=#0000ff]Private [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, _
[/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim [/COLOR][SIZE=2]command as new OleDBCommand ( _
    "INSERT INTO Messages ([Text])  VALUES ('" + TextBox1.Text + "')", _
    oleConn)[/SIZE][/SIZE]
[SIZE=2][SIZE=2] oleConn.Open()
[/SIZE][/SIZE][SIZE=2] command.ExecuteNonQuery()
  oleConn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]
 
Upvote 0 Downvote
Don Delegate is correct but note that string concatenation in SQL is a no go area, it can be difficult to debug, error prone, and a real pain to read. Stick to using parameterised query's, and forget about concatenating strings like that.
 
Upvote 0 Downvote
Okay, I have it working, but only when I press the button once, otherwise it puts in the same value over and over again (which I dont want) I've tried looping the parameter.add, but it throws this error:

Additional information: Invalid index 0 for this OleDbParameterCollection with Count=0.
VB.NET: 
[SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Set Database Variables
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MessageQueue.mdb;"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oleConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(connString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] qString1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "INSERT INTO Messages ([Text]) VALUES (?)"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] qComm1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(qString1, oleConn)
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
oleConn.Open()
[/SIZE][SIZE=2][COLOR=#008000]'qComm1.Parameters.Add(New OleDbParameter("@textfill", TextBox1.Text))
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oleParams [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbParameterCollection
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] i [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] i = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] qComm1.Parameters.Count - 1
qComm1.Parameters.Add(oleParams(i))
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]qComm1.Parameters(i).Value = TextBox1.Text
qComm1.ExecuteNonQuery()
oleConn.Close()
TextBox2.Text = qString1
TextBox1.Clear()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE]

This thing is kicking my butt :(
 
Upvote 0 Downvote
Uhm, looks like you keep missing the point. :)
A slightly different approach:
VB.NET: 
[COLOR=black][FONT=Courier New]
[COLOR=green]' Event handlers[/COLOR]
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] ButtonInsert_Click([COLOR=blue]ByVal[/COLOR] sender [COLOR=blue]As[/COLOR] System.Object, _
[COLOR=blue]ByVal[/COLOR] e [COLOR=blue]As[/COLOR] System.EventArgs) [COLOR=blue]Handles[/COLOR] ButtonInsert.Click
    insertMessage(TextBoxMessage.Text)
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] FormLoad([COLOR=blue]ByVal[/COLOR] sender [COLOR=blue]As[/COLOR] System.Object, _
[COLOR=blue]ByVal[/COLOR] e [COLOR=blue]As[/COLOR] System.EventArgs) [COLOR=blue]Handles[/COLOR] [COLOR=blue]MyBase[/COLOR].Load
    setUpDB()
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=green]' Private fields for Connection and insertCommand[/COLOR]
[COLOR=blue]Private[/COLOR] connection [COLOR=blue]As[/COLOR] OleDb.OleDbConnection
[COLOR=blue]Private[/COLOR] insertCommand [COLOR=blue]As[/COLOR] OleDb.OleDbCommand
 
[COLOR=green]' Private method to insert a record[/COLOR]
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] insertMessage([COLOR=blue]ByVal[/COLOR] message [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR])
[COLOR=green]   ' Set the parameter value (use name to retrieve the parameter)[/COLOR]
    insertCommand.Parameters([COLOR=maroon]"message"[/COLOR]).Value = message
    insertCommand.Connection.Open()
    [COLOR=blue]Try[/COLOR]
        insertCommand.ExecuteNonQuery()
     [COLOR=blue]Catch[/COLOR] ex [COLOR=blue]As[/COLOR] Exception
        MessageBox.Show(ex.ToString)
    [COLOR=blue]Finally[/COLOR]
        insertCommand.Connection.Close()
    [COLOR=blue]End[/COLOR] [COLOR=blue]Try[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] setUpDB()
[COLOR=blue]   Const[/COLOR] connString [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] = _
      [COLOR=maroon]"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\MessageQueue.mdb"[/COLOR]
    [COLOR=blue]Const[/COLOR] insertCommandText [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] = _
      [COLOR=maroon]"INSERT INTO Messages ([Text]) VALUES (?)"[/COLOR]
    connection = [COLOR=blue]New[/COLOR] OleDb.OleDbConnection(connString)
    insertCommand = [COLOR=blue]New[/COLOR] OleDb.OleDbCommand(insertCommandText, connection)
[COLOR=green]   ' Add parameter once, give it a name,
   '  set it's type, size and the Column[/COLOR]
    insertCommand.Parameters.Add( _
         [COLOR=maroon]"message"[/COLOR], OleDb.OleDbType.VarWChar, 0, [COLOR=maroon]"Text"[/COLOR])
    [COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Class[/COLOR]
[/FONT][/COLOR]
 
Upvote 0 Downvote

Similar threads

M
  • Question
Question I am using VB2019 I want to create a database, I got problems
Replies
1
Views
1K
jmcilhinney
jmcilhinney
L
  • Question
Problems with Filestream
Replies
3
Views
578
raulrabbit56
R
Dhagz03
  • Question
Question ExcuteReader Error
Replies
7
Views
540
jmcilhinney
jmcilhinney
RAFOO
  • Question
database starting with “imports”?
Replies
1
Views
523
JohnH
JohnH
jcardana
  • Solved
Resolved Creating Textbox Array: System.NullReferenceException
Replies
6
Views
2K
jcardana
jcardana

Share this page

Latest posts

Back
Top