|
Subject:
|
What is wrong with this insert statement?
|
|
Posted By:
|
method
|
Post Date:
|
4/23/2005 4:46:38 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,SEX,JOINED,STREE 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>
|
|
Reply By:
|
method
|
Reply Date:
|
4/23/2005 4:47:20 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>SEX 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>
|
|
Reply By:
|
Imar
|
Reply Date:
|
4/23/2005 5:50:14 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
|
|
Reply By:
|
method
|
Reply Date:
|
4/23/2005 12:16:56 PM
|
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,SEX,JOINED,STREE 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!
|
|
Reply By:
|
Imar
|
Reply Date:
|
4/24/2005 6:10:54 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?
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
4/24/2005 7:13:47 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)
|
|
Reply By:
|
method
|
Reply Date:
|
4/24/2005 1:34:53 PM
|
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
|
|
Reply By:
|
Prashant.k.m
|
Reply Date:
|
4/25/2005 1:23:44 AM
|
quote:
strSQL = strSQL & "INSERT INTO PLAYERS "
strSQL = strSQL & "(PLAYERNO,NAME,INIIALS,BIRTH_DATE,SEX,JOINED,STREE 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
|
|
Reply By:
|
method
|
Reply Date:
|
4/25/2005 4:55:08 AM
|
quote:
Originally posted by Prashant.k.m
quote:
strSQL = strSQL & "INSERT INTO PLAYERS "
strSQL = strSQL & "(PLAYERNO,NAME,INIIALS,BIRTH_DATE,SEX,JOINED,STREE 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,SEX,JOINED,STREET,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
|
|
Reply By:
|
Prashant.k.m
|
Reply Date:
|
4/25/2005 5:15:00 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.
|
|
Reply By:
|
method
|
Reply Date:
|
4/25/2005 5:40:03 AM
|
quote:
Originally posted by Prashant.k.m
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.
Many many thank to u . well i inserted the following data and it worked wel(http://i5.photobucket.com/albums/y180/method007/form.gif). Could u tell me where i can increase the site of these fields? In the form or in the sql server 2000 ?Thanks
[IMG]http://i5.photobucket.com/albums/y180/method007/form.gif[/IMG]
|
|
Reply By:
|
Prashant.k.m
|
Reply Date:
|
4/25/2005 5:55:52 AM
|
Set the MaxLength Property of the textboxes to the corresponding field width in the DB table. So you will not get any such errors more as the user will not be able to enter more no of charecters.
Now you can enter the data and find out which field is short of width.
If you need more width add that to the DB table field as well as increase the maxlength of the textbox
This should solve the whole problem
|
|
Reply By:
|
method
|
Reply Date:
|
4/27/2005 1:50:48 PM
|
Many thanks for u reply. If i want to user access 2000 db. What part need to be changed ?Thanks
|
|
Reply By:
|
Prashant.k.m
|
Reply Date:
|
4/27/2005 11:46:44 PM
|
TO convert to access 2000 db, you should use System.Data.OleDb intsead of System.Data.SqlClient. Thus every thing starting with Sql will start with OleDb (OleDbCommand,OleDbConnection...).
Also enclose date in #.
Regards
Prashant
|
