Storing data to Access from Visual Studio VB ASP

[NiallP17]

I am trying to store three values that I take from my web form and place them into a database that I have.

At the minute when I try this, the (UserID, Title, Description) line doesn't accept and the Title and Description are underlined in blue. I have tried putting in & instead of the comma but then it takes it all as one long string.

I have tried so many ways to try and get it to place the right data into the right space in my database and I cannot seem to work out what I'm doing wrong.

Any help would be greatly appreciated.

Many thanks

    Function SubmitRequest() As Boolean
 
        Dim myConn As OleDbConnection
        Dim myCmd As OleDbCommand
        Dim myDr As OleDbDataReader
        Dim RequestTable As New DataTable
        Dim UserID As Integer = CType(Session.Item("UserID"), String)
        Dim Title As String = txtRequestTitle.Text
        Dim Description As String = txtProblemDescription.Text
 
 
 
 
        myConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Helpdesk\Test\Database.accdb;Persist Security Info=True")
        myConn.Open()
        'myCmd = New OleDbCommand("select ID, Title, ProblemDescription from Request where StaffID=" & UserID & "", myConn)
        'myCmd = New OleDbCommand("INSERT StaffID, Title, ProblemDescription) VALUES ( & UserID & Title & Description" & "", myConn)
        'myCmd = New OleDbCommand("INSERT Request StaffID, Title, ProblemDescription) VALUES " & '"UserID, Title, Description"'"), myConn)
        'myCmd = New OleDbCommand("INSERT INTO Request StaffID, Title, ProblemDescription) VALUES (@UserID, @Title, @Description)", myConn)
 
        myCmd = New OleDbCommand("INSERT INTO Request StaffID, Title, ProblemDescription) VALUES "(UserID, Title, Description), myConn)
        'myDr = myCmd.ExecuteReader
        'RequestTable.Load(myDr)
 
 
 
        Return True
 
 
    End Function
 

[Rabbit]

The correct format for an insert query is:

INSERT INTO tableName (comma delimited field list)
VALUES (comma delimited value list)

[NiallP17]

myCmd = New OleDbCommand("INSERT INTO Request (StaffID, Title, Description) VALUES "(UserID, Title, Description), myConn)

Do you mean like that? Because Title, Description are underlined blue as 'Too many arguments to 'Public ReadOnly Default Property Chars(index as integer) As Char'.

[Rabbit]

You didn't append your value list correctly.

[NiallP17]

So I am listing them wrong? How am I suppose to list them?

[Rabbit]

The commas and parentheses need to be a part of the string. The variables you want to append need to be outside of the string.

[NiallP17]

I'm really sorry but I don't understand what that means

[Rabbit]

What I mean is this:

myCmd = New OleDbCommand("INSERT INTO Request (StaffID, Title, Description) VALUES (" & UserID & ",'" & Title & "','" & Description & "')", myConn)

[NiallP17]

That line of code worked but now I am getting an error on the line

myDr = myCmd.ExecuteReader

It is saying 'Syntax Error (missing operator) in query expression 'the wifi is not working')'.

The expression is what i am trying to store as the description in my table.

[Rabbit]

Please output the full SQL query it is attempting to run. The most likely cause is that you have quotes in your string which you need to escape.

[NiallP17]

myCmd = New OleDbCommand("INSERT INTO Request (StaffID, Title, Description) VALUES (" & UserID & ",'" & Title & "','" & Description & "')", myConn)

That is the SQL I am using

[Rabbit]

That's your code. I want to see the SQL string that is being passed to the server.

[NiallP17]

It's the same code that is posted in the first comment.

[Rabbit]

I know. I want to see the SQL that is passed to the server, not the code. I don't want to see the code because that doesn't tell me what is passed to the server.

[NiallP17]

Isn't the SQL in the code?

[Rabbit]

Part of the SQL is in the code. The full SQL is what I want to see.

[NiallP17]

I must be missing some then because that's all I have. I'm new to this so I'm not 100% sure how to word it all. What exactly am I missing?

[Rabbit]

x = "world"
string = "bob" + x

My question is: What's in string?

This is the answer you're giving me:

string = "bob" + x

The answer I'm looking for is:

helloworld

I need to know what's in the SQL, not the code that builds the SQL. I already have the code.

[NiallP17]

Do you mean what values I am trying to store? Because I get the values from 2 text boxes and the StaffID from a session variable I set up.

[Rabbit]

The values help, the actual SQL is better.

[NiallP17]

The values are from 2 text boxes, the user enters the title of their problem in the title field and the description of their problem in the description text box. These values are then stored in the variables Title and Description

[Rabbit]

I need the actual values that you tried when the code failed...

[NiallP17]

Title was 'Wifi not working' description was 'The wifi is not working'

[Rabbit]

Is that with or without the quote?

[NiallP17]

Without the quotes on both.

[Rabbit]

Then I will need to see the SQL. It's most likely formatted incorrectly.

[NiallP17]

Do you mean what's in the sub when the button is clicked as the code above is just for the function?

[Rabbit]

I mean the value in the variable just before it is submitted to the SQL Server.

[NiallP17]

Both variables are empty until the user inputs values from the text boxes and when they click the button it stores them in said variables.

[Frinavale]

The most obvious thing that jumps out at me is that you are attempting to use the ExecuteReader method but you should be using the ExecuteNonQuery method to do inserts and updates.

You should also be using the OleDbCommand.Parameters property to pass user-provided values into your query. There are a lot of reasons to use parameters and I recommend that you research this topic.


For example (this code is not tested and is meant to be a guideline for you to understand what I am talking about):

'!!!Before this code is executed you should validate the user input!!!
  Dim userID As Integer = CType(Session.Item("UserID"), String)
  Dim title As String = txtRequestTitle.Text
  Dim description As String = txtProblemDescription.Text
 
  Dim connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Helpdesk\Test\Database.accdb;Persist Security Info=True"
  Dim queryString = "INSERT INTO Request (StaffID, Title, ProblemDescription) VALUES (?, ?, ?)"
  Dim numberOfRowsAffected As Integer = 0
 
  Using connection As New OleDbConnection(connectionString)
    connection.Open()
    Dim command As New OleDbCommand(queryString, connection)
    command.Parameters.Add(userID)
    command.Parameters.Add(title)
    command.Parameters.Add(description)
    numberOfRowsAffected = command.ExecuteNonQuery()
  End Using 
' you can use the numberOfRowsAffected  
' to determine if your insert worked correctly

-Frinny