locked
Syntax error in INSERT Statement RRS feed

  • Question

  • User-1188570427 posted

    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
    Tuesday, December 20, 2011 2:22 PM

Answers

  • User-1188570427 posted

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 20, 2011 3:48 PM

All replies

  • User617492218 posted
    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()

     

    Tuesday, December 20, 2011 2:33 PM
  • User-1188570427 posted

    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
    Tuesday, December 20, 2011 2:48 PM
  • User-1188570427 posted

    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)
    Tuesday, December 20, 2011 2:57 PM
  • User617492218 posted

    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 )
    Tuesday, December 20, 2011 3:00 PM
  • User-1188570427 posted

    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
    Tuesday, December 20, 2011 3:09 PM
  • User-1188570427 posted

    
    

    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. 

    Tuesday, December 20, 2011 3:13 PM
  • User-1188570427 posted

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 20, 2011 3:48 PM
  • User-1199946673 posted

    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

    Wednesday, December 21, 2011 3:46 AM