"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
>
>
>
>
>

> 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')"

"Mike Labosh" <mlabosh@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
>