We help IT Professionals succeed at work.

SQL Query for Internal Messaging System  in ASP.NET

1,088 Views
Last Modified: 2012-08-13
I am building a Internal Messaging System. I have one Table called UserProfiles,  another table called tblMessage.
In the Userprofiles table are the field for UserName  and various other fields.
In the tblMessage table are fields  Messagefrom, MessageTo, Message, Prevmessage, Subject,  date,   etc. etc.

The user will type in the UserName of the  person they are sending the message to .  THey should be able to correspond back and forth with a record maintaines within the table.  
I am attaching the code for the query that I currently have however am getting a error:
Input string was not in a correct format.
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.FormatException: Input string was not in a correct format.

When I tested the Query I get the following error:
Column tblMessage.MessageId  is invalid  in the select list because it is not contained in either an aggregate function or the GROUP BY Clause.

I am not sure what the error is trying to say.
Any help preparing this query is greatly appreciated.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:SecurityString %>" 
            DeleteCommand="DELETE FROM [tblMessage] WHERE [MessageId] = @MessageId" 
            InsertCommand="INSERT INTO [tblMessage] ([MessageFrom], [MessageTo], [Message], [prevMessage], [Subject], [date], [Checked], [deletedbySender], [deletedbyRecipient], [IP]) VALUES (@MessageFrom, @MessageTo, @Message, @prevMessage, @Subject, @date, @Checked, @deletedbySender, @deletedbyRecipient, @IP)" 
            SelectCommand="SELECT tblMessage.MessageId, tblMessage.MessageFrom, tblMessage.MessageTo, tblMessage.Message, tblMessage.prevMessage, tblMessage.Subject, tblMessage.date, tblMessage.Checked, tblMessage.deletedbySender, tblMessage.deletedbyRecipient, tblMessage.IP, UserProfiles.UserId, UserProfiles.UserName, UserProfiles.firstname FROM tblMessage INNER JOIN UserProfiles ON UserProfiles.UserName = tblMessage.MessageTo GROUP BY UserProfiles.Messages" 
            UpdateCommand="UPDATE [tblMessage] SET [MessageFrom] = @MessageFrom, [MessageTo] = @MessageTo, [Message] = @Message, [prevMessage] = @prevMessage, [Subject] = @Subject, [date] = @date, [Checked] = @Checked, [deletedbySender] = @deletedbySender, [deletedbyRecipient] = @deletedbyRecipient, [IP] = @IP WHERE [MessageId] = @MessageId">
            <DeleteParameters>
                <asp:Parameter Name="MessageId" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="MessageFrom" Type="Int32" />
                <asp:Parameter Name="MessageTo" Type="Int32" />
                <asp:Parameter Name="Message" Type="String" />
                <asp:Parameter Name="prevMessage" Type="String" />
                <asp:Parameter Name="Subject" Type="String" />
                <asp:Parameter Name="date" Type="DateTime" />
                <asp:Parameter Name="Checked" Type="Byte" />
                <asp:Parameter Name="deletedbySender" Type="Byte" />
                <asp:Parameter Name="deletedbyRecipient" Type="Byte" />
                <asp:Parameter Name="IP" Type="String" />
                <asp:Parameter Name="MessageId" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="MessageFrom" Type="Int32" />
                <asp:Parameter Name="MessageTo" Type="Int32" />
                <asp:Parameter Name="Message" Type="String" />
                <asp:Parameter Name="prevMessage" Type="String" />
                <asp:Parameter Name="Subject" Type="String" />
                <asp:Parameter Name="date" Type="DateTime" />
                <asp:Parameter Name="Checked" Type="Byte" />
                <asp:Parameter Name="deletedbySender" Type="Byte" />
                <asp:Parameter Name="deletedbyRecipient" Type="Byte" />
                <asp:Parameter Name="IP" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>

Open in new window

Comment
Watch Question

          SelectCommand="SELECT tblMessage.MessageId, tblMessage.MessageFrom, tblMessage.MessageTo, tblMessage.Message, tblMessage.prevMessage, tblMessage.Subject, tblMessage.date, tblMessage.Checked, tblMessage.deletedbySender, tblMessage.deletedbyRecipient, tblMessage.IP, UserProfiles.UserId, UserProfiles.UserName, UserProfiles.firstname FROM tblMessage INNER JOIN UserProfiles ON UserProfiles.UserName = tblMessage.MessageTo GROUP BY UserProfiles.Messages"

should be

           SelectCommand="SELECT tblMessage.MessageId, tblMessage.MessageFrom, tblMessage.MessageTo, tblMessage.Message, tblMessage.prevMessage, tblMessage.Subject, tblMessage.date, tblMessage.Checked, tblMessage.deletedbySender, tblMessage.deletedbyRecipient, tblMessage.IP, UserProfiles.UserId, UserProfiles.UserName, UserProfiles.firstname FROM tblMessage INNER JOIN UserProfiles ON UserProfiles.UserName = tblMessage.MessageTo "
or    

SelectCommand="SELECT DISTINCT tblMessage.MessageId, tblMessage.MessageFrom, tblMessage.MessageTo, tblMessage.Message, tblMessage.prevMessage, tblMessage.Subject, tblMessage.date, tblMessage.Checked, tblMessage.deletedbySender, tblMessage.deletedbyRecipient, tblMessage.IP, UserProfiles.UserId, UserProfiles.UserName, UserProfiles.firstname FROM tblMessage INNER JOIN UserProfiles ON UserProfiles.UserName = tblMessage.MessageTo "

Author

Commented:
Good Morning thanks for the help on the query.  I made the changes as you suggested and am getting a error message when I test the query.  The following is the error message as well as how the table is structured.  I may have structured the table incorrectly.
Thanks

Incorrect syntax near 'SelectCommand'
The identifier that starts with 'SELECT Distinct tblMessage.MessageId, tblMessage.MessageFrom, tblMessage.MessageTo, tblmessage.Message, tblMessage.prevMessage,'is too long. Maximum length is 128

MessageId  int  (KEY)
MessageFrom  int
MessageTo  int
Message varchar(1500)
prevMessage  varchar(500)
Subject   varchar(50)
date     smalldatetime
Checked  tinyint
deletedbysender  tinyint
deletedbyRecipient   tinyint
IP   varchar(15)
Please provide also the structure from this table
hit enter to fast:

Please provide also the structure from this table UserProfiles
In mean while try this query:
SELECT m.MessageId, m.MessageFrom, m.MessageTo, m.Message, m.prevMessage, m.Subject, m.date, m.Checked, 
m.deletedbySender, m.deletedbyRecipient, m.IP, u.UserId, u.UserName, u.firstname 
FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo 

Open in new window

Check also your parameters, they should not include apostrophe '

Author

Commented:
Hello
The structure of the UserProfiles table is basic.  All  columns ara nvarchar except for the UserId which is uniqueidentifier.   There is no Key in the UserProfiles table.
I just tested the last query you sent and there is no error.  I am now going to try it in the page to see how well it works.

Author

Commented:
Hello when running the page i am getting a error.  I am attaching the error message.  I am using a ASP.NET formview to input the information.  I dont know if i should setup textboxes and use a commanf on the submit button better to input the information into the table?
Input string was not in a correct format. 
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.FormatException: Input string was not in a correct format.
 
Source Error: 
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Open in new window

Well, the instert statement should be run after all the fields on the form are inputed.

Author

Commented:
Hello that is probably the problem i shouldnt be using that ASP.NET form view.  I am going to redo the page using text boxes and a Insert statement on the command button.  I will use the query that you indicated and see what happens.  Thanks for having pacience with me.

Author

Commented:
Good morning I have created a onclick action for the submit button using the last query you sent me.   Looks like we are very close  to having is right but there is still showing a error in the code.  I am sending the code for the Submit button.
Thanks for your ongoing assistance.
Protected Sub SndMessage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SndMessage.Click
        If Not Page.IsValid Then Exit Sub
 
        ' Determine the currently logged on user's UserId
        Dim currentUser As MembershipUser = Membership.GetUser()
        Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid)
 
        ' Insert a new record 
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("SecurityString").ConnectionString
        Dim insertSql As String = "INSERT INTO tblMessage(m.MessageId, m.MessageFrom, m.MessageTo, m.Message, m.prevMessage, m.Subject, m.date, m.Checked, m.deletedbySender, m.deletedbyRecipient, m.IP, u.UserId, u.UserName, u.firstname)"
            FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo 
 
        Using myConnection As New SqlConnection(connectionString)
            myConnection.Open()
            Dim myCommand As New SqlCommand(insertSql, myConnection)
            myCommand.Parameters.AddWithValue("@MessageTo", MessageTo.Text.Trim())
            myCommand.Parameters.AddWithValue("@Subject", Subject.Text.Trim())
            myCommand.Parameters.AddWithValue("@Message", Message.Text.Trim())
            myCommand.Parameters.AddWithValue("@UserId", currentUserId)
            myCommand.ExecuteNonQuery()
            myConnection.Close()
        End Using
 
        ' "Reset" the Subject and Body TextBoxes
        Message.Text = String.Empty
    End Sub

Open in new window

Please provide the error message.
This is wrong :

Dim insertSql As String = "INSERT INTO tblMessage(m.MessageId, m.MessageFrom, m.MessageTo, m.Message, m.prevMessage, m.Subject, m.date, m.Checked, m.deletedbySender, m.deletedbyRecipient, m.IP, u.UserId, u.UserName, u.firstname)"
            FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo
Try this one

Dim insertSql As String = "INSERT INTO [tblMessage] ([MessageFrom], [MessageTo], [Message], [prevMessage], [Subject], [date], [Checked], [deletedbySender], [deletedbyRecipient], [IP]) VALUES (@MessageFrom, @MessageTo, @Message, @prevMessage, @Subject, @date, @Checked, @deletedbySender, @deletedbyRecipient, @IP)"
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Sorry for that I should have sent the error message.  I am attaching  the error message.
Basicly there is a blue squigley line under =  FROM tblMessage m

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: The compiler failed with error code 1.
 
 
 
Show Detailed Compiler Output:
 
 
c:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE> "C:\Windows\Microsoft.NET\Framework\v3.5\vbc.exe" /t:library /utf8output /R:"C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll" /R:"C:\Windows\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll" /R:"C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll" /R:"C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\assembly\dl3\2faf1681\00ca2aca_f8e1c801\EO.Web.DLL" /R:"C:\Windows\assembly\GAC_MSIL\System.Xml.Linq\3.5.0.0__b77a5c561934e089\System.Xml.Linq.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Runtime.Serialization\3.0.0.0__b77a5c561934e089\System.Runtime.Serialization.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll" /R:"C:\Windows\assembly\GAC_MSIL\Telerik.Charting\2.0.3.0__d14f3dcc8e3e8763\Telerik.Charting.dll" /R:"C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\App_Web_wl5ffs3a.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.ServiceModel.Web\3.5.0.0__31bf3856ad364e35\System.ServiceModel.Web.dll" /R:"C:\Windows\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll" /R:"C:\Windows\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Web.Extensions\3.5.0.0__31bf3856ad364e35\System.Web.Extensions.dll" /R:"C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\App_Code.q0v2qolj.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Web.Mobile\2.0.0.0__b03f5f7f11d50a3a\System.Web.Mobile.dll" /R:"C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\assembly\dl3\6114997e\002fd209_2a02c701\AlbumOnNet.DLL" /R:"C:\Windows\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll" /R:"C:\Windows\assembly\GAC\EnvDTE80\8.0.0.0__b03f5f7f11d50a3a\EnvDTE80.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll" /R:"C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\assembly\dl3\8ba16ce5\00fa3d53_18ecc801\Telerik.Web.UI.DLL" /R:"C:\Windows\assembly\GAC_MSIL\System.Core\3.5.0.0__b77a5c561934e089\System.Core.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Data.DataSetExtensions\3.5.0.0__b77a5c561934e089\System.Data.DataSetExtensions.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Drawing.Design\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.Design.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.ServiceModel\3.0.0.0__b77a5c561934e089\System.ServiceModel.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.WorkflowServices\3.5.0.0__31bf3856ad364e35\System.WorkflowServices.dll" /R:"C:\Windows\assembly\GAC\stdole\7.0.3300.0__b03f5f7f11d50a3a\stdole.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.IdentityModel\3.0.0.0__b77a5c561934e089\System.IdentityModel.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll" /out:"C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\App_Web_sendmessage.aspx.3aa7c2b3.fyqoxlvo.dll" /D:DEBUG=1 /debug+ /define:_MYTYPE=\"Web\" /imports:Microsoft.VisualBasic,System,System.Collections,System.Collections.Generic,System.Collections.Specialized,System.Configuration,System.Text,System.Text.RegularExpressions,System.Linq,System.Xml.Linq,System.Web,System.Web.Caching,System.Web.SessionState,System.Web.Security,System.Web.Profile,System.Web.UI,System.Web.UI.WebControls,System.Web.UI.WebControls.WebParts,System.Web.UI.HtmlControls /warnaserror- /optionInfer+  "C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\App_Web_sendmessage.aspx.3aa7c2b3.fyqoxlvo.0.vb" "C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\App_Web_sendmessage.aspx.3aa7c2b3.fyqoxlvo.1.vb" "C:\Users\Laptop\AppData\Local\Temp\Temporary ASP.NET Files\new folder (3)\16d64cd5\c4a25828\App_Web_sendmessage.aspx.3aa7c2b3.fyqoxlvo.2.vb"
 
 
Microsoft (R) Visual Basic Compiler version 9.0.21022.8
Copyright (c) Microsoft Corporation.  All rights reserved.
 
C:\Users\Laptop\Desktop\New Folder (3)\Membership\Messaging\SendMessage.aspx.vb(27) : error BC30451: Name 'FROM' is not declared.
 
            FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo 
            ~~~~                                                                    
C:\Users\Laptop\Desktop\New Folder (3)\Membership\Messaging\SendMessage.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
 
            FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo 
                 ~~~~~~~~~~                                                         
C:\Users\Laptop\Desktop\New Folder (3)\Membership\Messaging\SendMessage.aspx.vb(27) : error BC30451: Name 'tblMessage' is not declared.
 
            FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo 
                 ~~~~~~~~~~                                                         
C:\Users\Laptop\Desktop\New Folder (3)\Membership\Messaging\SendMessage.aspx.vb(27) : error BC32017: Comma, ')', or a valid expression continuation expected.
 
            FROM tblMessage m INNER JOIN UserProfiles u ON u.UserName = m.MessageTo 
 

Open in new window

Author

Commented:
Everything works thanks for your assistance
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.