Going nuts...a sql insert statement error...how to debug from here?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Kathy Burke
    #1

    Going nuts...a sql insert statement error...how to debug from here?

    Ugh. I'm using the following in an asp.net. I get an Syntax Error in
    INSERT INTO Statement on line Cmd1.ExecuteNon Query().

    I've made all my database fields text (just to eliminate that as a
    potential problem). I changed all my variables in the insert statement
    to text to test as shown below. Still getting the error! I've checked
    again and again my db table and field names. Could someone please tell
    me where to go from here??? THANKS, Kathy

    Dim Conn1 As New OleDbConnection () ' conn string set globally
    Dim Adapter As OleDbDataAdapte r
    Dim Cmd1 As OleDbCommand
    Dim strSQL1 As String

    strSQL1 = "INSERT INTO tblAnomaly (Date, WorkOrder, Customer, Line,
    Station, Assy, SerialNo, User, Item, Type, Problem, ProblemDesc,
    Comments, LineStop) VALUES ('08/10/03', '11233', 'XYZ', '07',
    'Station1', '865-7446-03BA', '222', 'burkek', 'ToolA not used',
    'Problem--Line Stop', 'Material Discrepancy', 'Does not meet spec',
    'none', 'true')"

    Conn1 = New OleDbConnection (strConn)
    Cmd1 = New OleDbCommand(st rSQL1, Conn1)
    Conn1.Open()
    Cmd1.ExecuteNon Query()
    Conn1.Close()


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Tags: None
    • Steve Letford
      #2
      Re: Going nuts...a sql insert statement error...how to debug from here?

      Without knowing your error message its a bit of a guess.

      But I notice that one of your column names is 'Date' this is a reserved word
      and may be causing issues.

      Steve

      "Kathy Burke" <kathyburke40@a ttbi.com> wrote in message
      news:e6F%23dZ5X DHA.2020@TK2MSF TNGP10.phx.gbl. ..[color=blue]
      > Ugh. I'm using the following in an asp.net. I get an Syntax Error in
      > INSERT INTO Statement on line Cmd1.ExecuteNon Query().
      >
      > I've made all my database fields text (just to eliminate that as a
      > potential problem). I changed all my variables in the insert statement
      > to text to test as shown below. Still getting the error! I've checked
      > again and again my db table and field names. Could someone please tell
      > me where to go from here??? THANKS, Kathy
      >
      > Dim Conn1 As New OleDbConnection () ' conn string set globally
      > Dim Adapter As OleDbDataAdapte r
      > Dim Cmd1 As OleDbCommand
      > Dim strSQL1 As String
      >
      > strSQL1 = "INSERT INTO tblAnomaly (Date, WorkOrder, Customer, Line,
      > Station, Assy, SerialNo, User, Item, Type, Problem, ProblemDesc,
      > Comments, LineStop) VALUES ('08/10/03', '11233', 'XYZ', '07',
      > 'Station1', '865-7446-03BA', '222', 'burkek', 'ToolA not used',
      > 'Problem--Line Stop', 'Material Discrepancy', 'Does not meet spec',
      > 'none', 'true')"
      >
      > Conn1 = New OleDbConnection (strConn)
      > Cmd1 = New OleDbCommand(st rSQL1, Conn1)
      > Conn1.Open()
      > Cmd1.ExecuteNon Query()
      > Conn1.Close()
      >
      >
      > *** Sent via Developersdex http://www.developersdex.com ***
      > Don't just participate in USENET...get rewarded for it![/color]


        Comment

        • PJ
          #3
          Re: Going nuts...a sql insert statement error...how to debug from here?

          query analyzer and similar db clients are your friends. just set a
          breakpoint before you execute the query and spit out the sql string. cut
          and paste the sql string to your db client and attempt running the query
          from there.

          "Kathy Burke" <kathyburke40@a ttbi.com> wrote in message
          news:e6F%23dZ5X DHA.2020@TK2MSF TNGP10.phx.gbl. ..[color=blue]
          > Ugh. I'm using the following in an asp.net. I get an Syntax Error in
          > INSERT INTO Statement on line Cmd1.ExecuteNon Query().
          >
          > I've made all my database fields text (just to eliminate that as a
          > potential problem). I changed all my variables in the insert statement
          > to text to test as shown below. Still getting the error! I've checked
          > again and again my db table and field names. Could someone please tell
          > me where to go from here??? THANKS, Kathy
          >
          > Dim Conn1 As New OleDbConnection () ' conn string set globally
          > Dim Adapter As OleDbDataAdapte r
          > Dim Cmd1 As OleDbCommand
          > Dim strSQL1 As String
          >
          > strSQL1 = "INSERT INTO tblAnomaly (Date, WorkOrder, Customer, Line,
          > Station, Assy, SerialNo, User, Item, Type, Problem, ProblemDesc,
          > Comments, LineStop) VALUES ('08/10/03', '11233', 'XYZ', '07',
          > 'Station1', '865-7446-03BA', '222', 'burkek', 'ToolA not used',
          > 'Problem--Line Stop', 'Material Discrepancy', 'Does not meet spec',
          > 'none', 'true')"
          >
          > Conn1 = New OleDbConnection (strConn)
          > Cmd1 = New OleDbCommand(st rSQL1, Conn1)
          > Conn1.Open()
          > Cmd1.ExecuteNon Query()
          > Conn1.Close()
          >
          >
          > *** Sent via Developersdex http://www.developersdex.com ***
          > Don't just participate in USENET...get rewarded for it![/color]


            Comment

            • Kathy Burke
              #4
              Re: Going nuts...a sql insert statement error...how to debug from here?

              Ugh. Changed the Date field to DateEntered...j ust to be safe. Then I
              copied the string verbatim into an Access query and ran
              it...successful ly...several times.

              The error I get in vb.net is [OleDbException (0x80040e14): Syntax error
              in INSERT INTO statement.]

              After the string worked directly in an Access query, I copied it back to
              the strSQL1 statement and tried again. SAME ERROR EVERY TIME. Where does
              one go from here?

              Any further suggestions...o r workaround? Is there a string length limit
              in vb.net for an insert statement that I don't know about?

              Thanks again.

              Kathy

              *** Sent via Developersdex http://www.developersdex.com ***
              Don't just participate in USENET...get rewarded for it!

                Comment

                • Kathy Burke
                  #5
                  Re: Going nuts...a sql insert statement error...how to debug from here?

                  Went back and did an insert by adding each field on...the problem was
                  the User field name...changed that and works great. It's always the
                  simple things. I'm going now to memorize all the KEYWORDS!

                  Thanks again for replying.

                  Kathy

                  *** Sent via Developersdex http://www.developersdex.com ***
                  Don't just participate in USENET...get rewarded for it!

                    Comment

                    • Kevin Spencer
                      #6
                      Re: Going nuts...a sql insert statement error...how to debug from here?

                      You can save yourself the trouble of memorizing all the keywords by putting
                      square brackets around your table and column names. Example:

                      SELECT [Date] FROM [User]

                      The square brackets indicate that the value enclosed is a database object,
                      not a reserved word.

                      --
                      HTH,

                      Kevin Spencer
                      Microsoft MVP
                      ..Net Developer
                      429 Too Many Requests
                      http://www.takempis.com

                      Complex things are made up of
                      lots of simple things.

                      "Kathy Burke" <kathyburke40@a ttbi.com> wrote in message
                      news:u9ngNiBYDH A.1784@TK2MSFTN GP09.phx.gbl...[color=blue]
                      > Went back and did an insert by adding each field on...the problem was
                      > the User field name...changed that and works great. It's always the
                      > simple things. I'm going now to memorize all the KEYWORDS!
                      >
                      > Thanks again for replying.
                      >
                      > Kathy
                      >
                      > *** Sent via Developersdex http://www.developersdex.com ***
                      > Don't just participate in USENET...get rewarded for it![/color]


                        Comment

                        • Munsifali Rashid
                          #7
                          Re: Going nuts...a sql insert statement error...how to debug from here?

                          You can use keywords, by wrapping them in square brackets. E.g. INSERT INTO
                          tbl_test ([User], [Date]) VALUES ('SomeUser', '13 August 2003'). But, it's
                          generally not recommended. Usually causes more hassle than it's worth...

                          Just thought I'd mention it :)

                          Mun




                          "Kathy Burke" <kathyburke40@a ttbi.com> wrote in message
                          news:u9ngNiBYDH A.1784@TK2MSFTN GP09.phx.gbl...[color=blue]
                          > Went back and did an insert by adding each field on...the problem was
                          > the User field name...changed that and works great. It's always the
                          > simple things. I'm going now to memorize all the KEYWORDS!
                          >
                          > Thanks again for replying.
                          >
                          > Kathy
                          >
                          > *** Sent via Developersdex http://www.developersdex.com ***
                          > Don't just participate in USENET...get rewarded for it![/color]


                            Comment

                            Previous template Next
                            Working...