I am getting an error with the INSERT command from a form

[leroi green]

hey all,

I'm using a form to throw info straight into a database. i know it isn't the best way but i'm new and all other implementations have confused me (i've been reading and watching tutorials for 2 weeks) but anyway i'm going to take the info straight from the form.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class frmClassAdd

    Dim m_intClassId As Integer = 0

#Region " Form Controls & Events "

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        If ValidateData() <> True Then Exit Sub
        Call AddClassRecord()
        Call AddClass2db()

        Me.Close()

    End Sub

#End Region 'Form Controls & Events
    'Contains submit and cancel buttons


#Region " Form Subs & Functions "

    Private Sub AddClassRecord()

        Dim row As DataRow = Nothing

        row = g_dsGradeList.Classes.NewRow
        row("ClassId") = nudClassId.Value
        row("ClassName") = txtClassName.Text.Trim
        row("Department") = txtDepartment.Text.Trim
        g_dsGradeList.Classes.Rows.Add(row)

    End Sub

    Private Sub AddClass2db()
        'this block below is to add the record straight into the database when
        'the user inserts the validated info

        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim ra As Integer 'integer holds the number of records inserted

        Dim ClassId As String
        Dim ClassName As String
        Dim Department As String

        'you need to provide password for sql server 
        myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ein05\Documents\dbGradeListApplication.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

        myConnection.Open()
        myCommand = new SqlCommand INSERT INTO tblClasses (ClassId,ClassName,Department)VALUES "('" + nudClassId.Value + "," +txtClassName.Text + "," + txtDepartment.Text "')";

        ra = myCommand.ExecuteNonQuery()

        MessageBox.Show("New Row Inserted" & ra)

        myConnection.Close()

    End Sub

but the only problem i'm coming into is where the line

myCommand = new SqlCommand INSERT INTO tblClasses (ClassId,ClassName,Department)VALUES "('" + nudClassId.Value + "," +txtClassName.Text + "," + txtDepartment.Text "')";

comes into play.

the error states that
End of statement expected.

i've been overlooking this over and over but i can't find my error.

help?

[MumtazKhan]

Hi -
You Can Use This Query

myCommand = new SqlCommand INSERT INTO tblClasses (ClassId,ClassName,Department)VALUES "('" + nudClassId.Value _+ "," +txtClassName.Text + "," + _txtDepartment.Text "')";myCommand = new SqlCommand INSERT _INTO tblClasses (ClassId,ClassName,Department)VALUES "('" + _nudClassId.Value + "," +txtClassName.Text + "," + _txtDepartment.Text "')";

[kvprajapati]

Two ways to go:

1. Form a query,

myCommand = new SqlCommand("INSERT INTO tblClasses  (ClassId,ClassName,Department) VALUES  "('"  & nudClassId.Value  & "','"  & txtClassName.Text  &  "','" & txtDepartment.Text  & "')")
myCommand.Connection=myConnection

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()


'Note:
'Wrap non-numeric (string or date) fields using single quote.

2. Use Parameterized query (Recommend method),

myCommand = new SqlCommand("INSERT INTO tblClasses   (ClassId,ClassName,Department) VALUES  (@classid,@classname,@department)")
myCommand.Connection=myConnection

myCommand.Parameters.Add(new SqlParameter("@classid",SqlDbType.VarChar,20)).Value= nudClassId.value
myCommand.Parameters.Add(new SqlParameter("@classname",SqlDbType.VarChar,30)).Value= txtClassName.Text
myCommand.Parameters.Add(new SqlParameter("@department",SqlDbType.VarChar,20)).Value= txtDepartment.Text

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

[leroi green]

with these queries... am i replacing my "myCommand" line, or am i removing my "myCommand" line and placing this code you showed me somewhere else?

Two ways to go:

1. Form a query,

myCommand = new SqlCommand("INSERT INTO tblClasses  (ClassId,ClassName,Department) VALUES  "('"  & nudClassId.Value  & "','"  & txtClassName.Text  &  "','" & txtDepartment.Text  & "')")
myCommand.Connection=myConnection

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()


'Note:
'Wrap non-numeric (string or date) fields using single quote.

2. Use Parameterized query (Recommend method),

myCommand = new SqlCommand("INSERT INTO tblClasses   (ClassId,ClassName,Department) VALUES  (@classid,@classname,@department)")
myCommand.Connection=myConnection

myCommand.Parameters.Add(new SqlParameter("@classid",SqlDbType.VarChar,20)).Value= nudClassId.value
myCommand.Parameters.Add(new SqlParameter("@classname",SqlDbType.VarChar,30)).Value= txtClassName.Text
myCommand.Parameters.Add(new SqlParameter("@department",SqlDbType.VarChar,20)).Value= txtDepartment.Text

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

[leroi green]

okay adatapost,

i chose option #2 that you showed me.

this is what i have now... VS isn't fussing with the coding anymore but it isn't sending records to the db.

am i placing and/or implementing your code correctly?

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class frmClassAdd

    Dim m_intClassId As Integer = 0

#Region " Form Controls & Events "

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        If ValidateData() <> True Then Exit Sub
        Call AddClass2db()
        Call AddClassRecord()

        Me.Close()

    End Sub

#End Region 'Form Controls & Events
    'Contains submit and cancel buttons


#Region " Form Subs & Functions "

    Private Sub AddClassRecord()

        Dim row As DataRow = Nothing

        row = g_dsGradeList.Classes.NewRow
        row("ClassId") = nudClassId.Value
        row("ClassName") = txtClassName.Text.Trim
        row("Department") = txtDepartment.Text.Trim
        g_dsGradeList.Classes.Rows.Add(row)

    End Sub

    Private Sub AddClass2db()
        'this block below is to add the record straight into the database when
        'the user inserts the validated info

        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand

        ' Dim ClassId As String
        'Dim ClassName As String
        'Dim Department As String

        'you need to provide password for sql server 
        myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ein05\Documents\dbGradeListApplication.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

        myCommand = New SqlCommand("INSERT INTO tblClasses   (ClassId,ClassName,Department) VALUES  (@ClassId,@ClassName,@Department)")
        myCommand.Connection = myConnection

        myCommand.Parameters.Add(New SqlParameter("@ClassId", SqlDbType.NChar, 10)).Value = nudClassId.Value
        myCommand.Parameters.Add(New SqlParameter("@ClassName", SqlDbType.NVarChar, 50)).Value = txtClassName.Text
        myCommand.Parameters.Add(New SqlParameter("@Department", SqlDbType.NVarChar, 50)).Value = txtDepartment.Text

        myConnection.Open()
        myCommand.ExecuteNonQuery()
        myConnection.Close()

    End Sub

[leroi green]

OKAY GUYS

i did some reading and got some help and listened to ya'll and finally got this done.

the below code is what worked for me. i hope it helps anyone who may be in my same position.

Private Sub AddClass2db()  'this block below is to add the record straight into the 
        'database when the user inserts the validated info

        'sql Connection Variables
        Dim myConnection As SqlConnection
        Dim strInsert As String = ""

        'Sql Commands
        Dim myCommand As SqlCommand

        'no password needed, using MS Auth 
        myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ein05\Documents\Visual Studio 2008\Projects\GradeListApplication2Update\GradeListApplication2\dbGradeListApplication.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        myConnection.Open()

        'the below order must be maintained to tell the program:
        '1) what the strInsert will insert
        '2) then turn the strInsert into a Command
        '3) Execute the Command

        strInsert = "Insert Into tblClasses (ClassId, ClassName, Department) VALUES ( " & CInt(nudClassId.Value) & ", '" & txtClassName.Text.Trim & "', '" & txtDepartment.Text & "')"
        myCommand = New SqlCommand(strInsert, myConnection)
        myCommand.ExecuteNonQuery()

        MessageBox.Show("New Row Inserted")

        myConnection.Close()

    End Sub