Syntax error in INSERT Statement : The Official Microsoft ASP.NET Forums

Reply

Syntax error in INSERT Statement

Dec 20, 2011 06:22 PM|LINK

Can someone help me figure out where the syntax error is?

Dim giver_id As Integer = 0 : Dim giver_family_code As Integer = 0
            Dim rec_id As Integer = 0 : Dim rec_family_code As Integer = 0
   SqlString = "INSERT INTO tblmain ([year_id], [Giver], [Recipient], [familynumdraw], [familynumdrawee], [update_time]) " + _
                    " VALUES ('" + Session("year") + "'," + giver_id + "," + rec_id + ", " + _
                    "" + giver_family_code + "," + rec_family_code + "," + "#" + Now.AddHours(-1).ToString + "#" + " "

Data Types:

year id = string; giver = number; recipient = number; familynumdraw = number; failynumdrawee = num; update_time = date/time

Edited by tvb2727 on Dec 20, 2011 06:22 PM
Moved by XIII on Dec 20, 2011 06:28 PM
Moved by bbcompent1 on Dec 21, 2011 10:34 AM

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 06:33 PM|LINK

using (SqlConnection conn = new SqlConnection(connectionString))
{
        string statement = "insert into tblmain (year_id, giver, recipient, familynumdraw, familynumdrawee, update_time)" +
                                     "values (@year_id, @recipient, @familynumdraw, @familynumdrawee, @update_time";
        SqlCommand cmd = new SqlCommand(statement, conn);
        cmd.Parameters.AddWithValue("@year_id", Session["year"].ToString());
        cmd.Parameters.AddWithValue("@giver", giver_id);
        cmd.Parameters.AddWithValue("@recipient", rec_id);
        cmd.Parameters.AddWithValue("@familynumdraw", giver_family_code);
        cmd.Parameters.AddWithValue("@familynumdrawee", rec_family_code);
        cmd.Parameters.AddWithValue("@update_time", DateTime.Now.AddHours(-1));
        cmd.ExecuteNonQuery();
}

i dunno but lets clean up the code - you can convert to VB in the link below

i think it's your session("year")

what is the hash char "#" after the datetime.now.addhours???

also your update_time column is a datetime yet you did a ToString()

Please post your code for us to help!!
Mark Answered if it helps - Good luck!
Cheers!
Design And Align
- Rob

Edited by robwscott on Dec 20, 2011 06:34 PM
Edited by robwscott on Dec 20, 2011 06:35 PM

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 06:48 PM|LINK

I tried the following and converted it to my Access db, but have the following error:

12/20/2011 1:44:38 PM----> System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
   at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
   at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
   at System.Data.OleDb.OleDbConnectionString..ctor(String connectionString, Boolean validate)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.OleDb.OleDbConnection.ConnectionString_Set(String value)
   at System.Data.OleDb.OleDbConnection.set_ConnectionString(String value)
   at System.Data.OleDb.OleDbConnection..ctor(String connectionString)
   at ASP.adduser_aspx.btnAddGR_Click1(Object sender, EventArgs e) in C:\cl2011\adduser.aspx:line 279


My code:

        Using conn As New Data.OleDb.OleDbConnection(mainconnection.ToString)
                SqlString = "insert into tblmain (year_id, giver, recipient, familynumdraw, familynumdrawee, update_time) " & _
                "values (@year_id, @recipient, @familynumdraw, @familynumdrawee, @update_time"
                Dim cmd As New Data.OleDb.OleDbCommand(SqlString, conn)
                cmd.Parameters.AddWithValue("@year_id", Session("year").ToString())
                cmd.Parameters.AddWithValue("@giver", giver_id)
                cmd.Parameters.AddWithValue("@recipient", rec_id)
                cmd.Parameters.AddWithValue("@familynumdraw", giver_family_code)
                cmd.Parameters.AddWithValue("@familynumdrawee", rec_family_code)
                cmd.Parameters.AddWithValue("@update_time", DateTime.Now.AddHours(-1))
                cmd.ExecuteNonQuery()
            End Using

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 06:57 PM|LINK

I think it's my connection string that's causing the issue. Not sure how to fix it though?

Connection string is:

Dim mainconnection As New Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringSQL").ConnectionString)

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 07:00 PM|LINK

your connection string is wrong - name "myConnectionString" to whatever it is in your web.config

string connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

using (SqlConnection conn = new SqlConnection(connectionString))  // substitute your OleDb
{

}

also i noticed an error on my part - put a ")" before the quotes on the end

SqlString = "insert into tblmain (year_id, giver, recipient, familynumdraw, familynumdrawee, update_time) " & _ 
                "values (@year_id, @recipient, @familynumdraw, @familynumdrawee, @update_time)" <--- add the )

Please post your code for us to help!!
Mark Answered if it helps - Good luck!
Cheers!
Design And Align
- Rob

Edited by robwscott on Dec 20, 2011 07:02 PM

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 07:09 PM|LINK

I fixed my connection string like you have above and get this error:

Error:
12/20/2011 2:05:45 PM----> System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
   at System.Data.OleDb.OleDbConnection.CheckStateOpen(String method)
   at System.Data.OleDb.OleDbCommand.ValidateConnection(String method)
   at System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Connection String: 
   Dim mainconnection2 As String = ConfigurationManager.ConnectionStrings("ConnectionStringSQL").ConnectionString

Code:
       Using conn As New Data.OleDb.OleDbConnection(mainconnection2)
                SqlString = "insert into tblmain (year_id, giver, recipient, familynumdraw, familynumdrawee, update_time) " & _
                "values (@year_id, @recipient, @familynumdraw, @familynumdrawee, @update_time"
                Dim cmd As New Data.OleDb.OleDbCommand(SqlString, conn)
                cmd.Parameters.AddWithValue("@year_id", Session("year").ToString())
                cmd.Parameters.AddWithValue("@giver", giver_id)
                cmd.Parameters.AddWithValue("@recipient", rec_id)
                cmd.Parameters.AddWithValue("@familynumdraw", giver_family_code)
                cmd.Parameters.AddWithValue("@familynumdrawee", rec_family_code)
                cmd.Parameters.AddWithValue("@update_time", DateTime.Now.AddHours(-1))
              
                cmd.ExecuteNonQuery()
            End Using

Edited by tvb2727 on Dec 20, 2011 07:09 PM
Edited by tvb2727 on Dec 20, 2011 07:10 PM
Edited by tvb2727 on Dec 20, 2011 07:11 PM
Edited by tvb2727 on Dec 20, 2011 07:11 PM
Edited by tvb2727 on Dec 20, 2011 07:11 PM

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 07:13 PM|LINK

robwscott




also i noticed an error on my part - put a ")" before the quotes on the end

SqlString = "insert into tblmain (year_id, giver, recipient, familynumdraw, familynumdrawee, update_time) " & _
                "values (@year_id, @recipient, @familynumdraw, @familynumdrawee, @update_time)" <--- add the )

Yes, this works now after adding the ), but I want to change it to what you've shown me since I know it's safer I believe.

Reply

Re: Syntax error in INSERT Statement

Dec 20, 2011 07:48 PM|LINK

I have it working now with changing the code around that you sent. Thanks again.

Dim date_insert As DateTime = Now
            If Session("where") = "cdrive" Then
                date_insert = Now.AddHours(-1)
            Else
                date_insert = Now
            End If
            
            Using conn As New Data.OleDb.OleDbConnection(mainconnection2)
                SqlString = "insert into tblmain (year_id, giver, recipient, familynumdraw, familynumdrawee, update_time) " & _
                "values (@year_id, @giver, @recipient, @familynumdraw, @familynumdrawee, @update_time)"
                Dim cmd As New Data.OleDb.OleDbCommand(SqlString, conn)
                cmd.Parameters.AddWithValue("@year_id", Session("year").ToString())
                cmd.Parameters.AddWithValue("@giver", giver_id.ToString)
                cmd.Parameters.AddWithValue("@recipient", rec_id.ToString)
                cmd.Parameters.AddWithValue("@familynumdraw", giver_family_code.ToString)
                cmd.Parameters.AddWithValue("@familynumdrawee", rec_family_code.ToString)
                cmd.Parameters.AddWithValue("@update_time", date_insert.ToString)
                conn.Open()
                cmd.ExecuteNonQuery() : conn.Close()
            End Using

Marked as answer by tvb2727 on Dec 20, 2011 07:51 PM

Reply

Re: Syntax error in INSERT Statement

Dec 21, 2011 07:46 AM|LINK

tvb2727

I have it working now with changing the code around that you sent. Thanks again.

One remark. I see you're passing date_insert,ToString to the last parameter. As you might have noticed, passing date_Insert, which has the value Now or Now.AddHours(-1) will cause an error too. That's because Now is also includes miliseconds, which cannot be used in Access. Instead of ToString, you better use ToOADate (an OleAutomation Date),

cmd.Parameters.AddWithValue("@update_time", date_insert.ToOADate())

http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

Also, another article explaining why you shoudl use parameterized queries:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

Syntax error in INSERT Statement RSS

8 replies

tvb2727

Syntax error in INSERT Statement

robwscott

Re: Syntax error in INSERT Statement

tvb2727

Re: Syntax error in INSERT Statement

tvb2727

Re: Syntax error in INSERT Statement

robwscott

Re: Syntax error in INSERT Statement

tvb2727

Re: Syntax error in INSERT Statement

tvb2727

Re: Syntax error in INSERT Statement

robwscott

tvb2727

Re: Syntax error in INSERT Statement

hans_v

Re: Syntax error in INSERT Statement

tvb2727

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft