Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2005, 04:46 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default What is wrong with this insert statement?

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>


 
Old April 23rd, 2005, 04:47 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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>&nbsp;</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>


 
Old April 23rd, 2005, 05:50 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
 
Old April 23rd, 2005, 12:16 PM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

 
Old April 24th, 2005, 06:10 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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?
 
Old April 24th, 2005, 07:13 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

  • 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)
 
Old April 24th, 2005, 01:34 PM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 25th, 2005, 01:23 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 
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








 
Old April 25th, 2005, 04:55 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 25th, 2005, 05:15 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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.











Similar Threads
Thread Thread Starter Forum Replies Last Post
What is wrong with following Insert Query? [email protected] VB.NET 2002/2003 Basics 1 September 19th, 2006 04:41 PM
Where is wrong of my alert statement? Edward King Javascript How-To 1 May 28th, 2005 05:12 AM
What's wrong with this statement ? levinll SQL Language 4 April 29th, 2004 08:41 PM
What's wrong with this SQL Statement dc925 ADO.NET 4 January 23rd, 2004 11:27 AM
What's wrong with my SQL statement? kaz VS.NET 2002/2003 1 December 11th, 2003 09:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.