"bruce lawson" <brucedeb930@earthlink.nospam> wrote in message
news:eBX_e.5679$vw6.4144@newsread1.news.atl.earthlink.net...
> Hi, I'm fairly new at SQL query language and am running an example program
> from a text in VisualBasic where I want to just insert a new row in an
> existing table, but I keep getting a syntax error in the INSERT INTO
> statement. Here's the program flow and below that, the error message
> caught in the exception line by the debugger. I've stared at the INSERT
> line a few hours I think (or so it seems) and can't find anything wrong.
> It works fine in MS Access. There is one extra ID column not included, but
> I read in MSN library that null values are by default entered. Any help is
> greatly appreciated.
>
>   Dim strSQL As String = "INSERT INT0 tblUsers " & _
>            "(FirstName,LastName,Address,City,State,Zip,Phone) " & _
>            "VALUES ('Eva','Smith','POBox
> 43','Brandywine','MO','35413','805-555-6393')"
>
>        ExecuteStatement(strSQL)  ****see function below****
>
>        FillDataGrid()
>
>    End Sub
>
>    Function ExecuteStatement(ByVal strSQL)
>        Dim objCmd As New OleDbCommand(strSQL, Conn)
>
>        Try
>            objCmd.Connection.Open()
>            objCmd.ExecuteNonQuery()
>
>        Catch ex As Exception
>
>            lblMessage.Text = "Error updating the database."
>
>        End Try
>
>        objCmd.Connection.Close()
>    End Function
>
> End Class
>
> ****** Here's the error message from debugger  at Catch ex as Exception
> line *******
>
> - ex {System.Data.OleDb.OleDbException} System.Exception
>
> Message "Syntax error in INSERT INTO statement." String
> Source "Microsoft JET Database Engine" String
>
> + objCmd {System.Data.OleDb.OleDbCommand} System.Data.OleDb.OleDbCommand
> + strSQL "INSERT INT0 tblUsers
> (FirstName,LastName,Address,City,State,Zip,Phone) VALUES
> ('Eva','Smith','POBox 43','Brandywine','MO','35413','805-555-6393')"
> {String} Object
>
>
>
>
>

"Mike Labosh" <mlab***@hotmail.com> wrote in message
news:ObkNDbTxFHA.2228@TK2MSFTNGP11.phx.gbl...
>> Hi, I'm fairly new at SQL query language and am running an example
>> program from a text in VisualBasic where I want to just insert a new row
>> in an existing table, but I keep getting a syntax error in the INSERT
>> INTO statement. Here's the program flow and below that, the error message
>> caught in the exception line by the debugger. I've stared at the INSERT
>> line a few hours I think (or so it seems) and can't find anything wrong.
>> It works fine in MS Access. There is one extra ID column not included,
>> but I read in MSN library that null values are by default entered. Any
>> help is greatly appreciated.
>
> I see a few things happening here that you might want to look at:
>
> 1.  Dim strSQL As String = "INSERT INT0 tblUsers " & _
>    Looks like you say INT0 (zero) instead of INTO (letter O)
>
> 2.  Function ExecuteStatement(ByVal strSQL)
>    Inside that function, you're not declaring a connection.  Simply
> referencing your command's Connection property, does not give you a live
> connection.  You have to create an instance of a Connection and feed it a
> connection string.
>
> 3.  Dim objCmd As New OleDbCommand(strSQL, Conn)
>    If you are really talking to a SQL Server, you need to use the
> SqlDataProvider, not the OleDbDataProvider.
>
> 4.  Function ExecuteStatement(ByVal strSQL)
>    This is not neccesarily an error, but you made it a function, but did
> not declare a scope or tell it to return anything.  You didn't declare
> your strSQL Parameter with a data type either.  That's sloppy, in my
> opinion.
>
> Try this:
>
> Imports System.Data
> Imports System.Data.SqlClient
>
> Public Class MyClass
>
>    Private Sub doMyStuff()
>>   Dim strSQL As String = "INSERT INT0 tblUsers " & _
>>            "(FirstName,LastName,Address,City,State,Zip,Phone) " & _
>>            "VALUES ('Eva','Smith','POBox
>> 43','Brandywine','MO','35413','805-555-6393')"
>
>        Dim sql As String = _
>            "INSERT INTO tblUsers " & _
>            "    (FirstName, LastName, Address, City, State, Zip, Phone) "
> & _
>            "VALUES " & _
>            "    ('Eva', 'Smith', 'POBox 43', 'Brandywine', 'MO', " & _
>            "'35413','805-555-6393')"
>
>            ExecuteStatement(sql)
>
>    End Sub
>
>    Public Sub ExecuteStatement(ByVal sql As String)
>
>        Dim cnstr As String = _
>            "Data Source = ThisServer; " & _
>            "Initial Catalog = ThisDatabase; " & _
>            "Persist Security Info = SSPD;"
>
>        Dim cn As New SqlConnection(cnstr)
>        Dim cm As New SqlCommand(sql, cn)
>
>       Try
>            cn.Open()
>            cm.ExecuteNonQuery()
>        Catch sqlEx As SqlException
>            MessageBox.Show(sqlEx.Message)
>        Catch exc As Exception
>            MessageBox.Show(exc.Message)
>        Finally
>            If cn.State = ConnectionState.Open Then cn.Close()
>        End Try
>
>    End Sub
>
> End Class
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
>
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>