Wednesday, March 7, 2012

Freetext box + SQL

I have a vb page, which is a simple front end so I can edit text from anywhere.

The person who is going to use it knows no html, and so I am trying to get freetextbox to work.

I am getting the following error:
Server Error in '/' Application.
------------------------

ExecuteNonQuery: CommandText property has not been initialized
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.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized

Source Error:

Line 81:
Line 82: mySelectCmd.Connection.Open()
Line 83: mySelectCmd.ExecuteNonQuery()
Line 84: MyConnString.Close()
Line 85:

The pages code is below:


<%@. Page Language="VB" Debug="True" validateRequest="False"%>
<%@. Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer
dim e_title, e_text, e_section_no as string
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text
mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left">Word</td>
 <td width="15%" align="left">Title</td>
 <td width="70%" align="left">Text</td>
 <td width="10%" align="left">Section Number</td>
 </tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?src=images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
 <td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?src=images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
 <td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html
You do this:
myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)

Note you set myExecuteQuery, but you then pass mySelectQuery to the SqlCommand constructor. I strongly suggest using Option Explicit On, sinc ein this case I think it would have caught your error.|||Stupid mistake!

anyways, made that change and now get this error

Must declare the variable '@.title'.
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: Must declare the variable '@.title'.

Any idea's?

<code>
<%@. Page Language="VB" Debug="True" validateRequest="False"%>
<%@. Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
dim myExecuteCmd As SqlCommand

Sub Page_Load(Sender As Object, E As EventArgs)

If Not Page.IsPostBack Then
ReadMyData()
end if
end sub

Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")

Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable

dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))

mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()

dr = dt.NewRow()

dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")

dt.Rows.Add(dr)

End While
Finally
myReader.Close()
MyConnString.Close()
End Try

Pubs.DataSource = DT
Pubs.Databind()

End Sub

Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub

Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub

Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)

MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")

myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)

dim e_id as integer
dim e_title, e_text, e_section_no as string

e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text

mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()

pubs.EditItemIndex = -1

ReadMyData()

End Sub
</script>
<html>
<head>
<title>Untitled Document</title
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left">Word</td>
<td width="15%" align="left">Title</td>
<td width="70%" align="left">Text</td>
<td width="10%" align="left">Section Number</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?src=images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
<td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?src=images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
<td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html>
</code|||You need to set @.title, etc. parameters.

Here is some information on parameters|||Thanks for that, made those changes, but now getting an error as below, which is strange as the code is similar to what I used to add entries

Sorry to be a pain!

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'Text' is not a member of 'String'.

Source Error:

Line 74: myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
Line 75: myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
Line 76: myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
Line 77: myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
Line 78: myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)


<%@. Page Language="VB" Debug="True" validateRequest="False"%>
<%@. Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
dim myExecuteCmd As SqlCommand
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
dim e_id as integer
dim e_title, e_text, e_section_no as string
myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), e_TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), e_FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), e_TextBox).Text
myExecuteCmd.Connection.Open()
myExecuteCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left">Word</td>
 <td width="15%" align="left">Title</td>
 <td width="70%" align="left">Text</td>
 <td width="10%" align="left">Section Number</td>
 </tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?src=images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
 <td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?src=images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
 <td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html
|||Well, what do YOU think that error means?

Look at this code:


dim e_title, e_text, e_section_no as string
myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)

e_title is a string, and so, look at the docs for string. Do you see a .Text property of string? No, you do not. So, the error message was telling you EXACTLY what the problem is. You can just pass e_Title, since that is a string and that is what you are passing as the @.Title.

Posted by justifylzxw at 5:59 AM
Labels: , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)