What is wrong with this insert statement?

method · April 23rd, 2005, 04:46 AM

well i got an aspx page that supposed to insert some data in sql server db and it generates this sql statment but it give error in writing to sql server 2000:

Server Error in '/asp' Application.
--------------------------------------------------------------------------------

The name 'me' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The name 'me' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Note: me is the value for name feild that i typed in the form

I be happy if some one help me how to correct it. Thanks
Here is the corde part that supposed to generate the insert statment :

<%@ Page Language="VB" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script Language="VB" Option="Explicit" runat="server">

Sub Page_Load(Src as object, E as EventArgs)
' Hide Insert Confirmation Message
pnlConfirm.Visible = False
End Sub

Sub btnSave_OnClick(Src as object, E as EventArgs)
Dim dtDateTimeInput as DateTime
Dim strSQL as String
Dim objConnection as SqlConnection
Dim objCommand as SqlCommand

If Page.IsValid Then
' Save the Data to the DB

' Check Values - The validators do most of this, but I was
' too lazy to come up with (ie. write) a custom validator for
' the Date/Time field.
Try
validtxtDateTimeField.Text = ""
dtDateTimeInput = CDate(txtDateTimeField.Text)
Catch Exp As Exception
'Response.Write(Exp)
validtxtDateTimeField.Text = "Please enter a valid Date/Time value."

Exit Sub
End Try

' Build our SQL String
strSQL = ""
strSQL = strSQL & "INSERT INTO PLAYERS "
strSQL = strSQL & "(PLAYERNO,NAME,INIIALS,BIRTH_DATE,************,JOINED,STRE E T,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & txtIntegerField1.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtTextField1.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtTextField2.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtDateTimeField.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtTextField3.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtIntegerField2.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtTextField4.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtIntegerField3.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtIntegerField4.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtTextField5.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtIntegerField5.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtIntegerField6.Text
'strSQL = strSQL & ", "

'strSQL = strSQL & "'" & dtDateTimeInput & "'"
strSQL = strSQL & ");"
Response.Write(strSQL)

' Set up our connection.
objConnection = New SqlConnection("Data Source=(local);" _
& "Initial Catalog=teniss2;User Id=web;Password=web;" _
& "Connect Timeout=15;Network Library=dbmssocn;")

objCommand = New SqlCommand(strSQL, objConnection)

objCommand.Connection.Open()
objCommand.ExecuteNonQuery()
objCommand.Connection.Close()

' Display Confirmation Message:
lblSQL.Text = strSQL
pnlConfirm.Visible = True
End If
End Sub

</script>

method · April 23rd, 2005, 04:47 AM

Here is the rest of the code :

<html>
<head>
<title>ASP.NET Form to Database Sample from ASP 101</title>
</head>
<body>

<form runat="server">

<table border="0">
<tr>
<td align="right"><strong>Player Number Field:</strong></td>
<td align="left">
<asp:TextBox id="txtIntegerField1" runat="server" />
<asp:RequiredFieldValidator id="validtxtIntegerFieldReq1"
ControlToValidate="txtIntegerField1"
Display="Dynamic"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
<asp:RangeValidator id="validtxtIntegerFieldRange1"
ControlToValidate="txtIntegerField1"
Display="Dynamic"
MinimumValue="-32768"
MaximumValue="32767"
Type="Integer"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>
<tr>
<td align="right"><strong>NAME Text Field:</strong></td>
<td align="left">
<asp:TextBox id="txtTextField1" maxlength="10" runat="server" />
<asp:RequiredFieldValidator id="validtxtTextField1"
ControlToValidate="txtTextField1"
Display="Dynamic"
Text="Please enter some text."
ForeColor="#FF0000"
runat="server"
/>
</asp:RequiredFieldValidator>
</td>
</tr>

<tr>
<td align="right"><strong>INITIALS Text Field:</strong></td>
<td align="left">
<asp:TextBox id="txtTextField2" maxlength="10" runat="server" />
<asp:RequiredFieldValidator id="validtxtTextField2"
ControlToValidate="txtTextField2"
Display="Dynamic"
Text="Please enter some text."
ForeColor="#FF0000"
runat="server"
/>
</asp:RequiredFieldValidator>
</td>
</tr>

<tr>
<td align="right"><strong>BIRTH_DATE Date/Time Field:</strong></td>
<td align="left">
<asp:TextBox id="txtDateTimeField" runat="server" />
<asp:Label id="validtxtDateTimeField"
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>

<tr>
<td align="right"><strong>************ Text Field:</strong></td>
<td align="left">
<asp:TextBox id="txtTextField3" maxlength="10" runat="server" />
<asp:RequiredFieldValidator id="validtxtTextField3"
ControlToValidate="txtTextField3"
Display="Dynamic"
Text="Please enter some text."
ForeColor="#FF0000"
runat="server"
/>
</asp:RequiredFieldValidator>
</td>
</tr>

<tr>
<td align="right"><strong>JOINED Field:</strong></td>
<td align="left">
<asp:TextBox id="txtIntegerField2" runat="server" />
<asp:RequiredFieldValidator id="validtxtIntegerFieldReq2"
ControlToValidate="txtIntegerField2"
Display="Dynamic"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
<asp:RangeValidator id="validtxtIntegerFieldRange2"
ControlToValidate="txtIntegerField2"
Display="Dynamic"
MinimumValue="-32768"
MaximumValue="32767"
Type="Integer"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>

<tr>
<td align="right"><strong>STREET Text Field:</strong></td>
<td align="left">
<asp:TextBox id="txtTextField4" maxlength="10" runat="server" />
<asp:RequiredFieldValidator id="validtxtTextField4"
ControlToValidate="txtTextField4"
Display="Dynamic"
Text="Please enter some text."
ForeColor="#FF0000"
runat="server"
/>
</asp:RequiredFieldValidator>
</td>
</tr>

<tr>
<td align="right"><strong>HOUSENO Field:</strong></td>
<td align="left">
<asp:TextBox id="txtIntegerField3" runat="server" />
<asp:RequiredFieldValidator id="validtxtIntegerFieldReq3"
ControlToValidate="txtIntegerField3"
Display="Dynamic"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
<asp:RangeValidator id="validtxtIntegerFieldRange3"
ControlToValidate="txtIntegerField3"
Display="Dynamic"
MinimumValue="-32768"
MaximumValue="32767"
Type="Integer"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>

<tr>
<td align="right"><strong>POSTCODE Field:</strong></td>
<td align="left">
<asp:TextBox id="txtIntegerField4" runat="server" />
<asp:RequiredFieldValidator id="validtxtIntegerFieldReq4"
ControlToValidate="txtIntegerField4"
Display="Dynamic"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
<asp:RangeValidator id="validtxtIntegerFieldRange4"
ControlToValidate="txtIntegerField4"
Display="Dynamic"
MinimumValue="-32768"
MaximumValue="32767"
Type="Integer"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>
<tr>
<td align="right"><strong>Town Text Field:</strong></td>
<td align="left">
<asp:TextBox id="txtTextField5" maxlength="10" runat="server" />
<asp:RequiredFieldValidator id="validtxtTextField5"
ControlToValidate="txtTextField5"
Display="Dynamic"
Text="Please enter some text."
ForeColor="#FF0000"
runat="server"
/>
</asp:RequiredFieldValidator>
</td>
</tr>

<tr>
<td align="right"><strong>PHONENO Field:</strong></td>
<td align="left">
<asp:TextBox id="txtIntegerField5" runat="server" />
<asp:RequiredFieldValidator id="validtxtIntegerFieldReq5"
ControlToValidate="txtIntegerField5"
Display="Dynamic"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
<asp:RangeValidator id="validtxtIntegerFieldRange5"
ControlToValidate="txtIntegerField5"
Display="Dynamic"
MinimumValue="-32768"
MaximumValue="32767"
Type="Integer"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>
<tr>
<td align="right"><strong>LEAGUENO Field:</strong></td>
<td align="left">
<asp:TextBox id="txtIntegerField6" runat="server" />
<asp:RequiredFieldValidator id="validtxtIntegerFieldReq6"
ControlToValidate="txtIntegerField6"
Display="Dynamic"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
<asp:RangeValidator id="validtxtIntegerFieldRange6"
ControlToValidate="txtIntegerField6"
Display="Dynamic"
MinimumValue="-32768"
MaximumValue="32767"
Type="Integer"
Text="Please enter a number between -32768 and 32767."
ForeColor="#FF0000"
runat="server"
/>
</td>
</tr>
<tr>
<td> </td>
<td><asp:Button id="btnSave" runat="server"
OnClick="btnSave_OnClick"
Text="Save To Database"
/>
</td>
</tr>
</table>

<asp:Panel id="pnlConfirm" runat="server">
<h2>Thanks for submitting your information to us!</h2>

<p>
<strong>The resulting SQL statement was:</strong>
<pre><asp:Label id="lblSQL" runat="server" /></pre>
</p>
</asp:Panel>

<p>

<a href="db_count.aspx">next page</a>.
</p>

</form>

</body>
</html>

Imar · April 23rd, 2005, 05:50 AM

Hi there,

This is quite a lot of code for others to scan, looking for an error somewhere. Can you please try to limit stuff like that to the relevant parts (of course that's only possible when you can actually determine what is relevant and what not)?

Anyway, I don't see how this code is related to the error you posted. I see no reference to "Me" anywhere in your code. And what's the result of Response.Write(strSQL)??

Imar

method · April 23rd, 2005, 12:16 PM

Quote:

quote:Originally posted by Imar
Hi there,

This is quite a lot of code for others to scan, looking for an error somewhere. Can you please try to limit stuff like that to the relevant parts (of course that's only possible when you can actually determine what is relevant and what not)?

Anyway, I don't see how this code is related to the error you posted. I see no reference to "Me" anywhere in your code. And what's the result of Response.Write(strSQL)??

Imar

well the 'me' is the input value that i enter in name text box in the
form. I think the problem is some where in :

' Build our SQL String
strSQL = ""
strSQL = strSQL & "INSERT INTO PLAYERS "
strSQL = strSQL & "(PLAYERNO,NAME,INIIALS,BIRTH_DATE,************,JOINED,STRE E T,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & txtIntegerField1.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtTextField1.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtTextField2.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtDateTimeField.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtTextField3.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtIntegerField2.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtTextField4.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtIntegerField3.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtIntegerField4.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtTextField5.Text
strSQL = strSQL & ", "
strSQL = strSQL & "'" & txtIntegerField5.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtIntegerField6.Text
'strSQL = strSQL & ", "

but i do not know how to fix it!! Unfortunetly the Response.Write(strSQL)does not output on the page so i can see what is wrong with sql statement!

Imar · April 24th, 2005, 06:10 AM

Try changing the Response.Write to "writing" to a label. Add a label in your page, and then fill its Text property:

Label1.Text = strSQL

You should comment out the rest of the code so it doesn't run and throw an error.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Everybody's Changing by Keane (Track 5 from the album: Hopes and Fears) What's This?

joefawcett · April 24th, 2005, 07:13 AM

Are you sure the PLAYERNO needs quotes around it, what type is it in the database?
Are you sure that the NAME field doesn't require quotes? What type is it in the database?
NAME is often a reserved word, maybe put brackets around it => [NAME]
Is INIIALS really the name of a field?

You should be using stored procedures for this type of data entry. It's easier to debug and much cleaner. You then don't have to worry about quotes around strings, quotes within strings and SQL injection attacks.

--

Joe (Microsoft MVP - XML)

method · April 24th, 2005, 01:34 PM

Quote:

quote:Originally posted by joefawcett

Are you sure the PLAYERNO needs quotes around it, what type is it in the database?
Are you sure that the NAME field doesn't require quotes? What type is it in the database?
NAME is often a reserved word, maybe put brackets around it => [NAME]
Is INIIALS really the name of a field?

You should be using stored procedures for this type of data entry. It's easier to debug and much cleaner. You then don't have to worry about quotes around strings, quotes within strings and SQL injection attacks.

--

Joe (Microsoft MVP - XML)

well i am trying to input values from a form to sql server 2000 db.Well i do not have much experience with stored procedure i be happy if u show me how . thanks

Prashant.k.m · April 25th, 2005, 01:23 AM

Quote:

quote:strSQL = strSQL & "INSERT INTO PLAYERS "

Quote:

strSQL = strSQL & "(PLAYERNO,NAME,INIIALS,BIRTH_DATE,************,JOINED,STRE E T,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & txtIntegerField1.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtTextField1.Text

You have not enclosed the txtTextField1.Text in quotes (Name).

To avoid these type of errors use always SqlCommand object with parameters.

To debug such things paste sql to query analyser and run, you will get the exact error. (you can extract the sql in runtime using response.write or the quick watch window)

If still the error persists post the tabel design

method · April 25th, 2005, 04:55 AM

Quote:

quote:Originally posted by Prashant.k.m

Quote:

quote:strSQL = strSQL & "INSERT INTO PLAYERS "

Quote:

strSQL = strSQL & "(PLAYERNO,NAME,INIIALS,BIRTH_DATE,************,JOINED,STRE E T,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO) " & vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & txtIntegerField1.Text & "'"
strSQL = strSQL & ", "
strSQL = strSQL & txtTextField1.Text

You have not enclosed the txtTextField1.Text in quotes (Name).

To avoid these type of errors use always SqlCommand object with parameters.

To debug such things paste sql to query analyser and run, you will get the exact error. (you can extract the sql in runtime using response.write or the quick watch window)

If still the error persists post the tabel design

Many thanks to you reply . Well i tried like this :

' Build our SQL String
    strSQL = ""
    strSQL = strSQL & "INSERT INTO PLAYERS "
    strSQL = strSQL & "(PLAYERNO,NAME,INITIALS,BIRTH_DATE,************,JOINED,STR EET,HOUSENO,POSTCODE,TOWN,PHONENO,LEAGUENO) " & vbCrLf
    strSQL = strSQL & "VALUES ("
    strSQL = strSQL & "'" & txtIntegerField1.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtTextField1.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtTextField2.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtDateTimeField.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtTextField3.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtIntegerField2.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtTextField4.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtIntegerField3.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtIntegerField4.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtTextField5.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtIntegerField5.Text & "'"
    strSQL = strSQL & ", "
    strSQL = strSQL & "'" & txtIntegerField6.Text & "'"
    'strSQL = strSQL & ", "

    'strSQL = strSQL & "'" & dtDateTimeInput & "'"
    strSQL = strSQL & ");"
    Response.Write(strSQL)

but i got error like this!:
Server Error in '/asp' Application.
--------------------------------------------------------------------------------

String or binary data would be truncated. The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

i be happy to get some help in fixing this. Thanks

Prashant.k.m · April 25th, 2005, 05:15 AM

Your data is larger in some string field,

ie, your field width may be 100 and you are trying to insert a string with 101 or greater charecters.