0

Alright, I have picked up everything I know for VB.Net from trial and error. I have built an SQL string that works in Access and tried to implement it, however it doesn't seem to work in my program. I totally accept that I don't have a firm grasp on it, so what am I doing wrong? This particular form just needs to take the text from textboxes in a Windows form and insert them into a database.

    Dim insertSql As String = "INSERT INTO StudentTable VALUES ('" + BadgeNoTextBox.Text + "','" + FirstNameTextBox.Text + "','" + LastNameTextBox.Text + "','" + SAPSIDTextBox.Text + "','" + EmailTextBox.Text + "'.'" + PhoneTextBox.Text + "','" + CollegeComboBox.Text + "')"
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\Users\larsennicholasg\Documents\Visual Studio   2012\Projects\SSCLogin\SSCLogin\My Project\SSCStudent.mdb"""
    Dim da As New OleDbDataAdapter(insertSql, conn)

    If (da.Update(ds)) Then
        MessageBox.Show("Success")
    Else
        MessageBox.Show("Fail")
    End If

Any ideas?

Improve this question
4
  • As a side note I'd be happy to look into books or a good Vb.net -> sql reference site, I've looked but can't seem to settle on one that is consistent. Commented Aug 23, 2015 at 23:33
  • 1
    You forgot to execute your query da.ExecuteNonQuery(); Commented Aug 23, 2015 at 23:38
  • Have a look at this using parameters instead stackoverflow.com/questions/21961951/… Commented Aug 23, 2015 at 23:40
  • Please expand on "doesn't seem to work" Commented Aug 23, 2015 at 23:43

1 Answer 1

Reset to default
3

Try this:

Dim insertSql As String = "INSERT INTO StudentTable VALUES (?, ?, ?, ?, ?, ?, ?)"
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\Users\larsennicholasg\Documents\Visual Studio   2012\Projects\SSCLogin\SSCLogin\My Project\SSCStudent.mdb"""
Using conn As New OleDbConnection(connStr), _
      cmd As New OleDbCommand(insertSql, conn)

    ''# I had to guess at types and lengths here. 
    ''# Adjust this to use actual types and lengths in your database
    cmd.Parameters.Add("?", OleDbType.Integer).Value = CInt(BadgeNoTextBox.Text)
    cmd.Parameters.Add("?", OleDbType.VarWChar, 20).Value = FirstNameTextBox.Text
    cmd.Parameters.Add("?", OleDbType.VarWChar, 20).Value = LastNameTextBox.Text
    cmd.Parameters.Add("?", OleDbType.Integer).Value = CInt(SAPSIDTextBox.Text)
    cmd.Parameters.Add("?", OleDbType.VarWChar, 50).Value = EmailTextBox.Text
    cmd.Parameters.Add("?", OleDbType.VarChar, 20).Value = PhoneTextBox.Text
    cmd.Parameters.Add("?", OleDbType.VarWChar, 35).Value = CollegeComboBox.Text

    conn.Open()
    cmd.ExecuteNonQuery()
End Using

The use of query parameters rather than string substitution is important. What you had was crazy-vulnerable to sql injection attacks.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

The ?'s are variable names? What are they for?
They're just placeholders for the query parameters. Some providers use named parameters and match the value to the parameter in the query by name, but OleDb matches up the parameter to placeholder by the order in which they appear in the query and Parameters collection, so you just use a ?.
Do I need one for EVERY parameter in the table? (The first column is an autonumber value)
No, skip autonumber columns.
Alright here's the new code, but I'm getting a "Additional information: Number of query values and destination fields are not the same." Error
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.