Hi Xperts,
I am trying to insert some record using an Insert Into statement using command object in .net. First I am checking the availability of username using a dataset. If not available, then I am trying to insert the record into the table. But every time I am getting the following error:
Syntax error in INSERT INTO statement
I have a sample statement as:
INSERT INTO ForumMember (Title, FirstName, LastName, RegDate, Username, Password, Email, MemberStatus, IncludeSignature, NotifyDefault, PostCount, LastLoginDate, LastPostDate, Signature) VALUES ('Mr.', 'sankar', 'sengupta', '4/13/2004', 'sankar3', 'sankar3', '[email protected]', 3, 0, 0, 0, , , 'Mr. sankar sengupta')
I don't understand why this is happening. Here are my files:
=========================== register.aspx ============================
<%@ Page Language="VB"%>
<%@ Import namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Register TagPrefix="e_business_forum" TagName="Header" Src="Pagelet\header.ascx" %>
<%@ Register TagPrefix="connection" TagName="connect" Src="Pagelet\connection_string.ascx" %>
<%@ Register TagPrefix="e_business_forum" TagName="Footer" Src="Pagelet\footer.ascx" %>
<script runat="server">
Public Sub btnRegister_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim myDS As New DataSet
    'Dim myConn As OleDbConnection
    Dim myCommand As OleDbDataAdapter
    Dim strConnString As String = ctlConnectStrings.JetConnectionString
    Dim strSQL As String
    Dim strText As String
    Dim MyDate As Date
    MyDate = Today ' Return current system date.
    'Check the length of the password
    lblshortpassword.Text = ""
    If txtpassword.Text.Length < 5 Then
        lblshortpassword.Text = "Password must be at least 5 character long!"
        Exit Sub
    End If
    'Check if the username is being already taken by someone
    Dim objConn As New OleDbConnection (strConnString)
    strSQL = "Select * From ForumMember Where Username='" & txtusername.Text & "'"
    lblerrmsg.Text = ""
    ' Populate the Dataset from the SQL statement
    ' Start Connection to ADO source
    'myConn = New OleDbConnection (strConnString)
    ' Run the Sql statement and get the Data into the DataAdapter
    myCommand = New OleDbDataAdapter(strSQL, objConn)
    ' Fill the DataSet and name the table
    myCommand.Fill(myDS, "Register")
    If myDS.Tables("Register").Rows.Count > 0 Then
        ' username already taken
        lblerrmsg.Text="Username already taken!"
        Exit Sub
    Else
        ' Insert user
        'Prepare SQL statement
        strSQL = "INSERT INTO ForumMember (Title, FirstName, LastName, RegDate, Username, Password, Email, MemberStatus, IncludeSignature, NotifyDefault, PostCount, LastLoginDate, LastPostDate, Signature) VALUES ('" & listtitle.SelectedItem.Value & "', '" & txtfname.Text & "', '" & txtlname.Text & "', '" & MyDate & "', '" & txtusername.Text & "', '" & txtpassword.Text & "', '" & txtemail.Text & "', 3, 0, 0, 0, , , '" & listtitle.SelectedItem.Value & " " & txtfname.Text & " " & txtlname.Text & "')"
        'strSQL = "INSERT INTO Test(Username,Password) VALUES('" & txtusername.Text & "','" & txtpassword.Text & "')"
        Dim dbComm As New OleDbCommand(strSQL, objConn)
        Try
            objConn.Open()
            dbComm.ExecuteNonQuery()
        Catch ex As Exception
            Response.Write(ex.Message)
            Response.Write(strSQL)
            Response.End
        Finally
            If objConn.State = ConnectionState.Open Then
              objConn.Close()
            End If
            'Prepare successtext
            strText = "Thank you for your interrest in e-business forum. The e-business forum is scheduled to launch shortly. As soon as it starts operating, you will be notified via email. Please wait as you will be redirected to the forum home page."
            lblerrmsg.Text = strText
        End Try
    End If
    'Server.Transfer("thankyou.aspx?ThankyouText=" & strText)
End Sub
</script>
<connection:connect id="ctlConnectStrings" runat="server"/>
<e_business_forum:Header runat="server"/>
<table width="100%" border="0" cellspacing="0" cellpadding="0" summary="main table">
  <tr>
    <td width="14%" background="images/bot1.gif"><img src="images/bot1.gif" alt="left"></td>
    <td width="76%" align="center" valign="top">
    <br><br>

    <table width="85%" border="1" cellspacing="0" cellpadding="0" bordercolordark="#FFFFFF" bordercolorlight="#666666" summary="Form outer table">
        <tr class="reversebig" bgcolor="#999999">
          <td align="center" valign="top">User Registration</td>
        </tr>
        <tr>
        <td class="smalldisptext">&nbsp;<strong>Note:</strong> Field(s) marked&nbsp;<img src="images/required.gif" alt="Required field"> are compulsory.</td>
        </tr>
        <tr>
          <td>

          <table width="100%" border="0" cellspacing="2" cellpadding="2" summary="registration table">
              <form method="POST" enctype="application/x-www-form-urlencoded" name="frmregister" id="frmregister" runat="server">
                  <tr class="errortext" align="left">
                <td colspan="2"><asp:label ID="lblerrmsg" runat="server"></asp:label></td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>Title:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%">
                <asp:dropdownlist ID="listtitle" CssClass="droplisttext" runat="server">
                <asp:listitem Text="Mr." Value="Mr."></asp:listitem>
                <asp:listitem Text="Ms." Value="Ms."></asp:listitem>
                <asp:listitem Text="Dr." Value="Dr."></asp:listitem>
                </asp:dropdownlist></td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%">&nbsp;</td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>First Name:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%" class="smallerrortext"><asp:textbox ID="txtfname" CssClass="mediumtextboxtext" MaxLength="20" runat="server" />&nbsp;<asp:RequiredFieldValidator ID="ValidateFname" EnableClientScript="true" Display="Dynamic" ErrorMessage="* Required Field!" ControlToValidate="txtfname" runat="server"></asp:RequiredFieldValidator>
                    </td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%">&nbsp;</td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>Last Name:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%" class="smallerrortext"><asp:textbox ID="txtlname" CssClass="mediumtextboxtext" MaxLength="20" runat="server" />&nbsp;<asp:RequiredFieldValidator ID="ValidateLname" EnableClientScript="true" Display="Dynamic" ErrorMessage="* Required Field!" ControlToValidate="txtlname" runat="server"></asp:RequiredFieldValidator></td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%">&nbsp;</td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>Email Address:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%" class="smallerrortext"><asp:textbox ID="txtemail" CssClass="mediumtextboxtext" MaxLength="50" runat="server" />&nbsp;<asp:RequiredFieldValidator ID="ValidateEmail" EnableClientScript="true" Display="Dynamic" ErrorMessage="* Required Field!" ControlToValidate="txtemail" runat="server"></asp:RequiredFieldValidator></td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%"><asp:RegularExpressionValidator id="CheckEmailAddress" runat="server" ControlToValidate="txtemail" ErrorMessage="You must enter a valid email address!" ValidationExpression="\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator></td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>Username:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%" class="smallerrortext"><asp:textbox ID="txtusername" CssClass="mediumtextboxtext" MaxLength="8" runat="server" />&nbsp;<asp:RequiredFieldValidator ID="ValidateUsername" EnableClientScript="true" Display="Dynamic" ErrorMessage="* Required Field!" ControlToValidate="txtusername" runat="server"></asp:RequiredFieldValidator></td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%">&nbsp;</td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>Password:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%" class="smallerrortext"><asp:textbox ID="txtpassword" CssClass="mediumtextboxtext" MaxLength="8" TextMode="Password" runat="server" />&nbsp;<asp:RequiredFieldValidator ID="ValidatePassword" EnableClientScript="true" Display="Dynamic" ErrorMessage="* Required Field!" ControlToValidate="txtpassword" runat="server"></asp:RequiredFieldValidator></td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%"><asp:label ID="lblshortpassword" CssClass="smallerrortext" runat="server"></asp:label></td>
              </tr>
              <tr align="left" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td width="30%"><strong>Confirm Password:</strong>&nbsp;<img src="images/required.gif" alt="Required field"></td>
                <td width="70%" class="smallerrortext"><asp:textbox ID="txtconfpassword" CssClass="mediumtextboxtext" MaxLength="8" TextMode="Password" runat="server" />&nbsp;<asp:RequiredFieldValidator ID="ValidateConfPassword" EnableClientScript="true" Display="Dynamic" ErrorMessage="* Required Field!" ControlToValidate="txtconfpassword" runat="server"></asp:RequiredFieldValidator></td>
              </tr>
              <tr class="smallerrortext">
                <td width="30%">&nbsp;</td>
                <td width="70%"><asp:CompareValidator ID="ComparePasswords" EnableClientScript="true" Display="Dynamic" ErrorMessage="The password values do not match!" ControlToValidate="txtconfpassword" ControlToCompare="txtpassword" runat="server"></asp:CompareValidator></td>
              </tr>
              <tr align="center" valign="middle" class="disptext" bgcolor="#CCCCCC">
                <td colspan="2"><asp:button ID='btnRegister' CssClass='buttonflat' Text='Register' onclick="btnRegister_Click" runat="server" /></td>
              </tr></form>
            </table>
          </td>
        </tr>
      </table>

    <br><br></td>
  </tr>
</table>
</td>
  </tr>
</table>
<e_business_forum:Footer runat="server"/>
======================== header.ascx =============================
<html>
<head>
<title>Title text</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../styles/style.css" type="text/css">
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="100%" border="0" cellspacing="0" cellpadding="0" summary="header table">
  <tr bgcolor="#000000">
    <td width="40" align="left" valign="top">&nbsp;</td>
    <td align="left" valign="top"><img src="images/titlesmall.gif" alt="GETTING TO TRANSFORMATION" width="215" height="105"></td>
    <td align="right" valign="bottom"><img src="images/some image name" alt="Some text" width="253" height="26"></td>
    <td width="60">&nbsp;</td>
  </tr>
</table>
======================== footer.ascx ===============================
<table width="100%" border="0" cellspacing="0" cellpadding="0" summary="footer">
  <tr bgcolor="#000000">
    <td class="reversetext">&nbsp;</td>
  </tr>
</table>
</body>
</html>
========================== connection_string.ascx ===================
<%@Control Language="VB" %>
<script language="VB" runat="server">
    Public ReadOnly Property JetConnectionString() As String
      Get
        Return ConfigurationSettings.AppSettings("JetDatabase") _
               & Request.PhysicalApplicationPath _
               & "database\database_name.mdb"
      End Get
    End Property
</script>
================================================== ====================
Can any one pls help why the heck this error is coming?


Sankar Sengupta
Striving for the BEST

__________________
Sankar Sengupta
Striving for the BEST

Does the database folder have write permissions for the ASPNET user?

Hello,

You need to account for when the user enters text with a ' in it. This will throw off your SQL statement. Where you assume text may have this, you need to use the replace method to change ' into ''.

Brian

Hey,

You have two fields that aren't being passed anything, which appear as: ..., , , .... I don't think you can do that. You either have to not supply those fields in the INSERT INTO clause, or pass NULL into those fields, when doing dynamic SQL like that (..., NULL, NULL, ...).

Brian

Hi Brian, I've tried that (using NULL) also. The interresting part is the SQL Statement run okay when trying from the Access query builder utility, but generates this error when embeded in ASP.NET code. I am really confused. Must be some simple thing I am missing, not getting what it is.
Sankar Sengupta
Striving for the BEST

Yes, the database folder is having the necessary permissions. Infact, I am able to enter data in one other page, but don't know what is happening in this case.
Sankar Sengupta
Striving for the BEST

But in this particular case, I am not passing any single quote inside the SQL Statement, am I? So, this should work well... but this is not working also.
Sankar Sengupta
Striving for the BEST

Hello,

How did you try putting in null? That should work because it's SQL that you are executing. I would think that the ", , " is still giving you problems. For testing sake, try hard-coding the values into the SQL string and execute that.

Brian

Actually, I believe the problem lies with one of the field names in the database table. Password is a reserved ODBC function call keyword, and thus using it as a field name in an INSERT INTO statement results in the error message you observed.

Avoid the problem by:

1. Renaming the field in the database and in the code.
--OR--
2. Surrounding the password field name in the INSERT statement with brackets, as in
strSQL = "INSERT INTO ForumMember (Title, FirstName, LastName, RegDate, Username, [Password], Email, ...

This is one reason why using a prefix naming convention is so practical. Prefacing every field name with something like 'fld' will help avoid other common reserved keywords, like First, Last, User, Password, Date, etc. For a complete list of keywords, you may want to http://msdn.microsoft.com/library/de...ra-rz_9oj7.asp

Yeah, I've seen that error before, I didn't even notice the field... Good catch.