I keep getting the error "Invalid attempt to read when no data is present" when trying to query a table in my SQL DB. I have checked and rechecked and the table name and column names within it are spelled correctly. There are only three records in the database, they all have data in them, and the code in Country.Text precisely matches the data in the Country field in one of the records.
It's worth mentioning that when I use Visual Studio 2005's little direct SQL query tool to build and run the following SQL statement that it works properly:
SELECT Country, WordForState
FROM data_Countries
WHERE (Country = N'Jordan')
I am perplexed. Any ideas anybody...here is the code...?
Dim SelectSQL_Countries As String
SelectSQL_Countries = "SELECT * FROM data_Countries "
SelectSQL_Countries &= "WHERE Country='" & Country.Text & "'"Dim con_Countries As New SqlConnection(ConfigurationManager.ConnectionStrings("MySiteMainDB").ConnectionString)
Dim cmd_Countries As New SqlCommand(SelectSQL_Countries, con_Countries)Dim reader_Countries As SqlDataReader
I am using MVWD 2005 Express and Sql Server 2005 Express. I am using SqlDataSource control and GridView control to disply a ata table, and let user to input data into the database through dropdownlist and TextBox. When I run the application I always get exception:"Conversion failed when converting character string to smalldatetime data type." I could not find anything wrong. Could anybody out there help me find out what's wrong with my code. Much appreciate it.Here is the code:<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"DataSourceID="SqlDataSource2" DataKeyNames="DocumentID"><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /><asp:BoundField DataField="DocumentName" HeaderText="DocumentName" ReadOnly="True"SortExpression="DocumentName" /><asp:BoundField DataField="DateInput" HeaderText="Date" SortExpression="DateInput" /><asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" /><asp:BoundField DataField="DocumentCategory" HeaderText="Category" SortExpression="DocumentCategory" /><asp:CheckBoxField DataField="TopDoc" HeaderText="TopDoc" SortExpression="TopDoc" /></Columns><EmptyDataTemplate>There is no data to be displayed!</EmptyDataTemplate></asp:GridView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" DeleteCommand="DELETE FROM [Documents] WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"InsertCommand="INSERT INTO [Documents] ([DocumentName], [DateInput], [Comments], [DocumentCategory], [TopDoc]) VALUES (@DocumentName, @DateInput, @Comments, @DocumentCategory, @TopDoc)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Documents]"UpdateCommand="UPDATE [Documents] SET [DocumentName] = @DocumentName, [DateInput] = @DateInput, [Comments] = @Comments, [DocumentCategory] = @DocumentCategory, [TopDoc] = @TopDoc WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"><DeleteParameters><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></DeleteParameters><UpdateParameters><asp:Parameter Name="DocumentName" Type="String" /><asp:Parameter Name="DateInput" Type="DateTime" /><asp:Parameter Name="Comments" Type="String" /><asp:Parameter Name="DocumentCategory" Type="Int32" /><asp:Parameter Name="TopDoc" Type="Boolean" /><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></UpdateParameters><InsertParameters><asp:ControlParameter Name="Comments" Type="String" ControlID="TextBox2" /><asp:ControlParameter Name="DocumentCategory" Type="Int32" ControlID="DropDownList1" PropertyName="SelectedValue" /><asp:ControlParameter Name="TopDoc" Type="Boolean" ControlID="RadioButton1" PropertyName="Checked" /></InsertParameters></asp:SqlDataSource>Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.ClickDim savePath As String = "C: empuploads"Dim fileName As String = FileUpload1.FileName Dim dt As DateTime = DateTime.Now SqlDataSource2.InsertParameters.Add("DocumentName", fileName) SqlDataSource2.InsertParameters.Add("DateInput", dt)SqlDataSource2.Insert()If (FileUpload1.HasFile) ThensavePath += FileUpload1.FileName FileUpload1.SaveAs(savePath) Label1.Text = "Your file was uploaded successfully."DisplayFileContents(FileUpload1.PostedFile)ElseLabel1.Text = "You did not specify a file to upload."End IfEnd Sub
Hi im new and new to coding when i run the following code it gives me the following error can someone please help me and tell me where i am going wrong; "Invalid attempt to read when no data is present. " using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;
public partial class dclogin : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) {
I want to print out the table name and record count whenever a table has record(s), but the statement: Print 'TableName: '+@TableName +' Row Count: '+CAST(@RCTR AS varchar) never gets executed.
Any help is appreciated.
****************************************** SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int
SET @TableName = '' SET @RCTR = 0
WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ANDQUOTENAME(TABLE_NAME) > @TableName ANDOBJECTPROPERTY( OBJECT_ID(QUOTENAME(TABLE_NAME) ), 'IsMSShipped') = 0 )
SET @SQL = ( 'select count(*) from ' + @TableName ) EXEC (@SQL) SET @RCTR = @@ROWCOUNT IF @RCTR > 0 Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar) END *********************************
SELECT * FROM BOOK_CUSTOMER WHERE STATE='FL' OR STATE='NJ' AND REFERRED=NULL;
Im sorry for posting this, but the results just arent working out. Im trying to display user information if the customer lives in florida or new jersey and they cant have a referring user. I tried the above combination and managed to only list the people from fl and got one user who was referred. I also tried:
SELECT * FROM BOOK_CUSTOMER WHERE REFERRED=NULL AND STATE='FL' OR STATE='NJ';
And I only get people from nj and also get one referred user. Can anyone point out what im doing wrong?
I want to print out the table name and record count whenever a table has record(s), but the statement: Print 'TableName: '+@TableName +' Row Count: '+CAST(@RCTR AS varchar) never gets executed.
Any help is appreciated.
****************************************** SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int
SET @TableName = '' SET @RCTR = 0
WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ANDQUOTENAME(TABLE_NAME) > @TableName ANDOBJECTPROPERTY( OBJECT_ID(QUOTENAME(TABLE_NAME) ), 'IsMSShipped') = 0 )
SET @SQL = ( 'select count(*) from ' + @TableName ) EXEC (@SQL) SET @RCTR = @@ROWCOUNT IF @RCTR > 0 Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar) END *********************************
Here is part of the code: CREATE table Fornecedor ( codintCONSTRAINT RI_79 PRIMARY KEY, NCint, nomeVARCHAR(100), telefoneint, moradaVARCHAR(100), CONSTRAINT RI_78 CHECK(cod>0), CONSTRAINT RI_80 CHECK(telefone>0), CONSTRAINT RI_81 CHECK(telefone is not null), CONSTRAINT RI_83 CHECK(morada is not null), CONSTRAINT RI_85 CHECK(nome is not null), CONSTRAINT RI_86 UNIQUE(nome), CONSTRAINT RI_87 CHECK(NC>0), CONSTRAINT RI_88 CHECK(NC is not null), CONSTRAINT RI_89 UNIQUE(NC) );
CREATE table Encomenda ( codintCONSTRAINT Fornecedor_não_existe_I FOREIGN KEY REFERENCES Fornecedor(cod), número_ordemint, lojaintCONSTRAINT Loja_não_existe_IV FOREIGN KEY REFERENCES Loja(loja), data_pedidoCHAR(8), data_previstaCHAR(8), data_entregaCHAR(8), CONSTRAINT RI_117_72 PRIMARY KEY(cod,número_ordem), CONSTRAINT RI_71 CHECK(número_ordem>0), CONSTRAINT RI_74 CHECK(data_pedido is not null), CONSTRAINT RI_76 CHECK(data_prevista is not null), CONSTRAINT RI_118 CHECK(loja is not null) );
Returns me this error when I try to create Encomenda_Disco: There are no primary or candidate keys in the referenced table 'Encomenda' that match the referencing column list in the foreign key 'Encomenda_não_existe_I'.
I have checked this code for more than an hour, and I'm starting to get prety anoyed with this. Can someone be kind enough to point me the error? :)
select au_lname, au_fname,au_id from authors where 100 =all (select royaltyper from titleauthor where titleauthor.au_id = authors.au_id) This query uses database pubs. Why this code returns some au_id which is not in table titleauthor? for example 527-72-3246 is not in titleauthor.
how to find the names of all authors who earn 100 percent royalty on all his/her books?
Hi AllI keep getting back VINET and not Lilas...can someone point me in theright direction?Thanks a lotDECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><CustomerID>VINET </CustomerID><CustomerID>Lilas</CustomerID></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/ROOT',2)WITH (CustomerID varchar(100) )
c:documents and settings910287my documentsvisual studio projects eport project1FCAT READING LEVELS.rdl The color expression for the textbox €˜textbox8€™ contains an error: [BC30201] Expression expected.
Below is my connection code.. Public Class clsConnect Public Shared Function getConnection () AS SQLConnection Dim strConn As String = "Data Source=PPS_SERVER;" & _ "Initial Catalog=eCommunity;User ID=sa;Password=pps" Dim DBConn As SqlConnection DBConn = New SqlConnection(strConn) DBConn.Open() Return DBConn End Function
However when i run the web this error was appeared. what possibility of this problem? SQL Server does not exist or access denied. 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: SQL Server does not exist or access denied.Source Error:
Line 12: DBConn = New SqlConnection(strConn) Line 13: Line 14: DBConn.Open() Line 15: Line 16: return DBConn
CREATE PROCEDURE Page4 ( @startRowIndex int, @maximumRows int ) AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that
-- Get the first employeeID for our page of records SET ROWCOUNT @startRowIndex SELECT @first_id = CAST(DATE as int) FROM TOPIC
-- Now, set the row count to MaximumRows and get -- all records >= @first_id SET ROWCOUNT @maximumRows
SELECT * FROM TOPIC WHERE CAST(DATE as int) >= @first_id
SET ROWCOUNT 0
GO
I have the date field clustered DESC
The code always starts at the same place but maximum rows always adjusts to my input unlike the startrowindex. What am I doing wrong?
select sub1.*, case sub1.mdiff when sub1.mdiff<12 then 1 else 0 end as flag
error msg by query analyser Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '<'. Server: Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'as'.
I am running this piece of code to execute a package programatically and Yet I get Failure. What could be Wrong with this. If so can anyone tell me How I can debug in SSIS.
Hi,I have code on a page to update one table and insert info into another, but I cant make it work. I am new to coding and i think there are many mistakes. Please can someone pick out a few that need to be changed for the page to work.Code:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "ConnectionString"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable WHERE productID='@productID' = " + TextBox1.Text + command.ExecuteNonQuery(); return true; con.Close();} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "ConnectionString"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT Transactions SET Usersname = @UserName" + "; INSERT Transactions SET Itemid WHERE productID='@productID' = @productID; INSERT Transactions SET itemname = @Itemsname" + "; INSERT Transactions SET Date WHERE productID='@productID' = " + DateTime.Now.ToString() + "; INSERT Transactions SET Qty WHERE productID='@productID' = " + TextBox1.Text; command.Parameters.Add("@UserName", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); return true; con.Close(); }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) ); ExecuteInsert(Int32.Parse);} Current error message is:The best overloaded method match for 'detailproview.ExecuteInsert(string)' has some invalid argumentsto Line 74: ExecuteInsert(Int32.Parse); Thanks if someone can help!Jon
Hi, just learning SQL2000.I have this code: mSQL = "UPDATE Contactpersonen SET Naam=? WHERE ContactpersoonID=?" Command = New SqlClient.SqlCommand(mSQL, C_CP) Command.Parameters.Add("Naam", txtNaam.Text) Command.Parameters.Add("CPID", SqlDbType.UniqueIdentifier).Value = m_CP_IDwhere m_CP_ID is defined as a GUID in: Public Property m_CP_ID() As Guid Get If Not viewstate("m_CP_ID") Is Nothing Then Return viewstate("m_CP_ID") End If 'Return 0 End Get Set(ByVal Value As Guid) viewstate("m_CP_ID") = Value End Set End PropertyWhen running the app I got this error:Server Error in '/4D' Application.-------------------------------------------------------------------------------- Line 1: Incorrect syntax near 'Naam'. Line 1: Incorrect syntax near '?'. (points to mSQL above)I know it has something to do with the GUID, but I cannot guess what.Help is appreciated, Ger.
Hi, I have the following code: Dim RowLoopIndex As Integer DsFuncties.Clear() A_Functies.Fill(DsFuncties) For RowLoopIndex = 0 To (DsFuncties.Tables("Functies").Rows.Count - 1) If DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("FunctieID") = Func_ID Then Func_naam = (DsFuncties.Tables("Functies").Rows(RowLoopIndex).Item("Functienaam")) DDL_Functie.SelectedIndex = RowLoopIndex + 1 Exit For End If NextFunc_ID has been declared as follows: Public Property Func_ID() As Guid Get If Not viewstate("Func_ID") Is Nothing Then Return viewstate("Func_ID") End If End Get Set(ByVal Value As Guid) viewstate("Func_ID") = Value End Set End PropertyOn the red line I got the following tooltip (error):Operator '=' is not defined for types 'System.Object' and 'System.Guid'.How can I define '=' and eg. '&' for type System.Guid ???? Or another solution ??Help is appreciated, Ger.
2�the PhotoManager's class is : public static List GetPhotos(int AlbumID) { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString)) { using (SqlCommand command = new SqlCommand("GetPhotos", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID)); command.Parameters.Add(new SqlParameter("@IsPublic", 1)); connection.Open(); List list = new List(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) {
3�photo.cs is using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Drawing.Drawing2D; using System.Drawing.Imaging; using System.IO; using System.Web; public class Photo {
private int _id; private int _albumid; private string _caption; private string _location;
private int _ViewCount;
private DateTime _LastModified; private int _AlbumCategoryID;
public int PhotoID { get { return _id; } } public int AlbumID { get { return _albumid; } } public string Caption { get { return _caption; } } public string Location { get { return _location; } }
public int ViewCount { get { return _ViewCount; } }
public DateTime LastModified { get { return _LastModified; } }
public int AlbumCategoryID { get { return _AlbumCategoryID; } }
4�the StoredProcedure "GetPhotos" is : ALTER PROCEDURE [dbo].[GetPhotos] @AlbumID int, @IsPublic bitAS SELECT [Photos].[AlbumID],[Photos].[PhotoID],[Photos].[Caption],[Photos]. [Location],[Photos].[ViewCount],[Photos].[LastModified], [Albums].[AlbumCategoryID] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [Photos].[AlbumID] = @AlbumID AND ([Albums].[IsPublic] =@IsPublic OR [Albums].[IsPublic] = 1) ORDER BY [Photos].[Caption] ASC
In the "Example: Detecting a Poison Message" section, it reads: This Transact-SQL example shows a simple, stateless service that includes logic for handling poison messages. Before the stored procedure receives a message, the procedure saves the transaction. When the procedure cannot process a message, the procedure rolls the transaction back to the save point. The partial rollback returns the message to the queue while continuing to hold a lock on the conversation group for the message.
oConn = New SqlClient.SqlConnection oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;" oCmd = New SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "TestStdInfo" 'parameters block oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString()) oParam1.Direction = ParameterDirection.Input oParam1.SqlDbType = SqlDbType.Int oCmd.Parameters.Add(oParam1) oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text)) oParam2.Direction = ParameterDirection.Input oParam2.SqlDbType = SqlDbType.DateTime oCmd.Parameters.Add(oParam2) oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text) oParam3.Direction = ParameterDirection.Input oParam3.SqlDbType = SqlDbType.VarChar oCmd.Parameters.Add(oParam3) oConn.Open() daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn) dsStudent = New DataSet daStudent.Fill(dsStudent) 'This line is highlighted when error happens oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
Can anyone please tell me what is wrong with this query: rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") In DreamWeaver the bit in bold is greyed out, why? rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") Thanks Joe
I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?
SELECT DISTINCT MtgeBroker, COUNT(CreatedDate) AS [Title Orders for Current Month]
FROM RECalendar
WHERE ( RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness') AND RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974') ) AND ( MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE())) AND YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE())) ) GROUP BY MtgeBroker
string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString; SqlConnection con = new SqlConnection(ConnectionString);
Finally, the database has a table called poll, including the fields int lagID Pk string lagnamn int votes
What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but it won't update the database. I hope someone will see where I am going wrong here.
I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:
If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.
For instance, this code:
sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g " sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName" Set rs = conn.Execute(sql) Do While Not rs.EOF lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")" rs.MoveNext Loop Set rs = Nothing
could show the following: 2441-John Doe (10) 2441-John Doe (11) if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.
select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS , SUM(inv_numb, freight_val) FROM OPCSAHH WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND LESS THEN = 2003 AND PREPAID COLLECT FLAG = 'C'
What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.
THe following query generates, Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'if'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ',' errors.
Can anyone tell me what I am missing?
EXECUTE sp_MSforeachtable ' select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user) from ? as o where not exists(select * from users u where (u.users_id = o.rn_create_user) )';