i am running this script
SELECT dbo.SHOVEL_A.HOLEID, dbo.Shovel_Hole.HOLEID AS shovel_id, dbo.SHOVEL_A.PROJECTCODE, dbo.Shovel_Hole.Shotname,
dbo.Shovel_Hole.B_FEET, dbo.Shovel_Hole.S_FEET, SQRT(square(dbo.SHOVEL_A.EAST - dbo.Shovel_Hole.EAST)
+ square(dbo.SHOVEL_A.NORTH - dbo.Shovel_Hole.NORTH)) AS distance_total
FROM dbo.SHOVEL_A FULL OUTER JOIN
dbo.Shovel_Hole ON dbo.SHOVEL_A.POLYGONO = dbo.Shovel_Hole.Shotname AND distance_total <= dbo.Shovel_Hole.B_FEET
GROUP BY dbo.SHOVEL_A.HOLEID, dbo.Shovel_Hole.HOLEID, dbo.Shovel_Hole.Shotname, dbo.Shovel_Hole.B_FEET, dbo.Shovel_Hole.S_FEET,
dbo.SHOVEL_A.PROJECTCODE, SQRT(square(dbo.SHOVEL_A.EAST - dbo.Shovel_Hole.EAST)
+ square(dbo.SHOVEL_A.NORTH - dbo.Shovel_Hole.NORTH))
HAVING (dbo.SHOVEL_A.PROJECTCODE = 'PRODL') AND (dbo.Shovel_Hole.Shotname = '20040524029')
but i have a result with the message that my colum name distance_total is wrong, so how can i fix that?
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150 SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!
We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.
Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.
Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?
Hi All, I have a column called TIER in my database which is a long string. There are piece of these strings that are separated by spaces like this: 'A3A00 A2B00 B1A00 C2C06 C3A06 C5A00 D2C00 G6B00 M2B00 M3B00 P7A00 T2A00 G4C00 G3C00 T5A06' How do I select the last 2 characters of the piece of string when the first 2 chacters are M2? I have the following: This is retruning me all the rows that have 'M2'. declare @code varchar (2)set @code = 'M2'select tier from companywhere tier like '%'+@code+'___ %' Any help is appreciated. Thank you in advance.
HI, how can i drop the identity on a column from the table. I have no access to the sql enterprise manager, i have to do it thru query analyser only. regards, varadish
i have a problem, i have a query which does a search based on a parameter, thing is i want to order them according to how relevant the results are such as 5 for beingan exact match, 0 for being no match. using a series of liek statements.
so that the most relevnat results are displayed at the top.
how do i achieve this - im a bit confused about this.
I have about 5 statements like the update below, depending on the PID different columns will be update "C2005, G2005,E2005...."
I would like to use 1 update statement in stead of 5 to update all columns below are 2 original update statements and my attempt at when then update. Note a different column is updated depending on the PID.
If when then isnt possible, any other suggestions are welcomed. Thanks UPDATE #Sec SET C2005 = Pos.USD / 1000 FROM #Sec INNER JOIN Pos ON #Sec.ID = Pos.ID WHERE (Pos.PID = 'B')
UPDATE #Sec SET G2005 = Pos.USD / 1000 FROM #Sec INNER JOIN Pos ON #Sec.ID = Pos.ID WHERE (Pos.PID = 'G')
UPDATE #Sec WHEN (Pos.PID = 'C') THEN SET C2005 = Pos.USD / 1000 end WHEN (Pos.PID = 'G') THEN SET G2005 = Pos.USD / 1000 end WHEN (Pos.PID = 'E') THEN SET E2005 = Pos.USD / 1000 end FROM #Sec INNER JOIN Pos ON #Sec.ID = Pos.ID
Im Working with stored procedure. How can i compare Columns with specific values. I want to get the greater values of those column and inserted it to other columns. i want something like these CASE WHEN a> b,c,d THEN a WHEN b> a,c,d THEN b WHEN c> a,b,d THEN c WHEN d> a,d,c THEN d
is there any ways to implement this? i got an error.. thanks please help..
SELECT @initial = CASE WHEN type = 1 THEN SUM(amount) END, @incomings = CASE WHEN type = 2 THEN SUM(amount) END, @outgoings = CASE WHEN type = 3 THEN SUM(amount) END, FROM Transactions WHERE date = '05/14/2006' AND STATION = 'apuyinc' GROUP BY type, amount
What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where The incoming transactions is type 2, outgoing transactions is type 3
In SQL7, if you add a column to a table, is it necessary to recompile all stored procs that reference that table? Under 6.5, this caused problems at our site unless the Procs were 'refreshed'. Is this only necessary where select * is used? Thanks
Hi ,I have this querypaprojnumber is varcharpatx500 is textpalineitemseq is intselect Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601wherepa02101.pabillnoteidx=pa01601.pabillnoteidx group bypaprojnumber,patx500it throws this errorServer: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.Thanks a lot for your help.AJ
THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column. "Cannot insert the value NULL into column UID column does not allow" Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed. Is this common? THe source and destination tables are in the same databes.
Hi all,We're selecting data from our database, FirstName, LastName, MobileNumber etc.We're using the detaials view function to return it in a table upon selection. However all of the variables are returned as they are in the database, ie: without spaces. We tried putting in spaces by selecting "AS what ever", but MSSQL does not seem to like spaces.Any ideas?Thanks
Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam
I have a complex view in my sql 2005 database. The view returns a column that could be null (as the result of a left outer join). The coulmn that is returned is an integer. Everything works fine if I run the view from SQL 2005 Management Studio. My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable. Has anybody seen this behaviour before? Any help appreciated. Regards, Paul.
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image, 2 (SELECT Location 3 FROM Location_Table 4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID, 5 (SELECT TypeOfProperty 6 FROM Type_Table 7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty 8 FROM Property_Table 9 WHERE (TypeID = @TypeID OR 10 TypeID IS NULL) AND (LocationID = @LocationID OR 11 LocationID IS NULL) AND (Price >= @MinPrice OR 12 Price IS NULL) AND (PriceMax <= @MaxPrice OR 13 PriceMax IS NULL)
SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ "MONTH(Some_Date) >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" This is NOT: SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ Month >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" it says - Invalid column name 'Month'
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting Alter Table Employees Add Constraint CK_SNN Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _ "S (@UserID, @UserName, @UserPassword, @Name, @Email)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userID.ParameterName = "@UserID" dbParam_userID.Value = userID dbParam_userID.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_userID) Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userName.ParameterName = "@UserName" dbParam_userName.Value = userName dbParam_userName.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userName) Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userPassword.ParameterName = "@UserPassword" dbParam_userPassword.Value = userPassword dbParam_userPassword.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userPassword) Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_name.ParameterName = "@Name" dbParam_name.Value = name dbParam_name.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_name) Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_email.ParameterName = "@Email" dbParam_email.Value = email dbParam_email.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
Return rowsAffected End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Message.Text = "Register Successed, click on the link WebCam for login"
Else Message.Text = "Failure" End If End Sub
and here is the error I receive when I try to open this register.aspx page:
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: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs) Line 53: Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Line 55: Message.Text = "Register Successed, click on the link WebCam for login" Line 56:
Hi please lok at this SP I have written and point where am I commiting mistake. The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails] ( @ProductCode Int = null )
AS
DECLARE @sqlString AS nvarchar(2000) SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode, Master.PROD_DESC_TEXT AS ProductName, Detail.PiecesPerBox, Detail.Price FROM cp_nafta_dws.PRODUCT AS Master INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM' BEGIN IF NOT (@ProductCode = NULL) BEGIN SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode END END
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. 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: Line 1: Incorrect syntax near '='.Source Error:
