CODEWITHSUNDEEP
sqlvb.netms-access
Skathix
Skathix

Reputation: 278

VB.Net SQL Insert into Using Windows Forms Data

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?

Upvotes: 0

Views: 1353

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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.

Upvotes: 3

Related Questions