I am a java developer having little bit knowledge in sql statements.
can any body guide me what is the effective way of using sql statement
to achieve the following goal.
here is the requirement.
1. There are 2 tables A & B.
2.The table 'B' may or maynot have an equivalent entry for the primary
key of table a.
3. select column1 (of A), column 2(of B) from table a and b.
4. now column2 should give a value, if there is a corresponding
entry in table b.other wise null.
5. at any time there may be 0 to 1 record in table b for the primary
of table a.
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status( ID varchar(50), Status varchar(50), EffectiveStartDate datetime, EffectiveEndDate datetime, Is_Current bit
Hi, I created a sqldatasource (sql server) and a gridview.In the aspx file, i can see the selectcommand: SelectCommand="select email from mytable" Now, i want to modify something in the selectcommand produced in the aspx file, in order to get the emails in clickable mode. I knwow i have to add a string like: <a hef="mailto:mailaddress">mailadress</a> So i tried this: SelectCommand="select '<a href="mailto:'+ email +'">' , email + '</a>' from email But this gives the error: "tag is not well formed".The problem is the " around mailto: Thanks Cl.
Here is an sql query but it keeps giving me an error of (Incorrect syntax near keywork ON) here is the sql, don't get why this doe not work when I have done it this way multiple times. select DISTINCT c.strSSN, p.sidstrNAME_IND, p.sidstrGR_ABBR_CODE, p.sidstrSSN_SM, p.sidstrST_ADDR, p.sidstrADDR_CITY, p.sidstrSTATES_US, p.sidstrZIP_CODE, p.sidstrPAY_GR, p.sidstrMARTL_STAT, p.sidstrNBR_EXEMPT, p.sidstrST_TAX_CODE, u.lsdbstrPHONE_NBR_HOME, case when sidstrTech_SVC_Code in ('M', 'R', 'S', 'T', 'U', 'Z') then case when sidstrACT_STAT_PROG in ('5', 'A', 'E', 'F', 'N', 'R', 'S', 'T') then 'AGR' else 'Tech' end else 'M-Day' end as status from tblAssignedPersonnel as c on ap.strssn = p.sidstrSSN_SM Inner Join cms.dbo.tblLSDB as u on u.lsdbstrSSN_SM = c.strSSN inner joincms.dbo.tblSidpers as p on ap.strSSN = p.sidstrSSN_SM where intUICID = (select intUICID from tblUIC where strUIC = '2' and intTaskForceID = '1') order by p.sidstrNAME_IND
I need to use IF-THEN in a SQL Select statement. Using Google I have found a couple of obscure references to this but nothing that I can use. Tried various combinations without luck. Can anyone point me to a good resource or supply a simple example of proper syntax? Thanks in advance for any help provided.
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ToString()); SqlCommand myCommand = new SqlCommand("SELECT (Deadline, Description, Headline, AddressField, OrganizationField, NameField, FileField, EmailField, CommentField) FROM RegistrationFormDB_Info WHERE (UserName = @UserName AND TournamentName = @TournamentName)", myConnection); SqlParameter myParam = new SqlParameter(); myParam.ParameterName = "@UserName"; myParam.Value = User.Identity.Name; myCommand.Parameters.Add(myParam); myParam = new SqlParameter(); myParam.ParameterName = "@TournamentName"; myParam.Value = Request.QueryString["TournamentName"]; myCommand.Parameters.Add(myParam); myConnection.Open(); SqlDataReader myReader = myCommand.ExecuteReader(); <====================== I continue to get this error and I can't figure out the problem! Anything helps. Thank you very much.
I have the following statement that I am trying to convert to a "like" statement in a SqlDataSource for a web application. I can't seem to get the syntax correct. Would someone be able to assist with this? Thanks! SELECT * FROM [Employees] WHERE ([LName] = @LName) ORDER BY [LName], [FName] Something like below. SELECT * FROM [Employees] WHERE ([LName] LIKE '@LName%') ORDER BY [LName], [FName]
SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblLooker,tblSubject,tblStop WHERE NOT (SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblSubject, tblLooker, tblStop WHERE tblSubject.Subject > tblLooker.Looker AND tblSubject.Subject < tblStop.Stop AND tblLooker.id = tblStop.id) Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near ')'. Can anyone tell me why this is not working? Thanks
What is the syntax for making a query using like, the below is my code <asp:SqlDataSource ID="Search" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString.ProviderName %>" SelectCommand="SELECT * FROM [Products]WHERE category LIKE %@category%">
<SelectParameters> <asp:QueryStringParameter Name="category" QueryStringField="category" Type="string" /> </SelectParameters> </asp:SqlDataSource> Its giving a syntax error but I dont know how to change it. Please advise, thanks!
Can someone tell me what is wrong with my syntax? I am getting several errors:
Incorrect syntax near keyword 'CASE' Incorrect syntax near keyword 'WHEN'
Incorrect syntax near '@Tablename'
CREATE PROCEDURE al_readcampsignup
@User_ID int,
@Pagenumber smallint,
@Tablename varchar(10)
AS
CASE @Mypagenumber WHEN 1 THEN SELECT Blah FROM tempcampsignup WHERE Camp_ID = @User_ID WHEN 2 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID WHEN 3 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID END
I inherited a query and I am getting an error of Unsupported literal in join in the INNER JOIN FRDM.dbo.MEMBER_SUBSC FRDM_dbo_MEMBER_SUBSC2
ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_CK = FRDM_dbo_MEMBER_SUBSC2.SBSB_CK AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX = '00')statement. Specifically the AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX = '00')part. SELECT frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.GRGR_ID AS 'group number'
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES XYZ Company ABC Limited Liability Company ZZZ Corporation KKK Inc. ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS Company Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES XYZ Company ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT * FROM [Names of Companies] WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
Hi,I'm new to SQL Server Programming, I work with ASP a lot, but latelyI've been trying to create Stored Procedures, etc. I'm having aproblem writing a simple IF statement.. I don't seem to understand whyit's giving me this error. I've search around on Google Groups, but Istill don't get it.=================USE msdbIF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')END=================My error is:Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'END'.Thanks for any help.
I am trying to take all the trips that equal a one and divide them by the total number of service calls and then multiple them by 100 to get the percentage.
I am receiving the following errror: "The value expression refers to the field 'SvcCallNumber'. Report item expressions can only refer to fields within the current dta set scope or, if inside an aggregate, the specified data set scope...
Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue: sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error: Incorrect syntax near the keyword 'Bulk'. Anyone have any ideas, thanks
Hi forum, im converting some code and have an issue with th following code!!! many thanks for good advice, Paul Line 55: SQLa = "UPDATE counted SET " & y & " = " & intClick & ", total = " & intTotal & " WHERE ID = " & RSpc.Fields("pc").ValueLine 56: Conn.Execute(SQLa) System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Dim SQLa As String
I have place a lblmessage.text = ex.Message to trace what happen and it show me a syntax error in update happen. I don't know whats wrong with my syntax. Can someone help me on this?
'set up connection dim conn as new oledbconnection _ ("provider = microsoft.jet.oledb.4.0;" & _ "data source = c:aspnetdataanking.mdb")
sub page_load(sender as object, e as eventargs) if not page.ispostback then filldatagrid() end if end sub
sub submit (sender as object, e as eventargs) 'insert new data dim i,j as integer dim params(7) as string dim strtext as string dim blngo as boolean = true
j = 0
for i = 0 to addpanel.controls.count -1 if addpanel.controls(i).gettype is _ gettype (textbox) then strtext = ctype(addpanel.controls(i), _ textbox).text if strtext <> "" then params(j) = strtext else blngo = false lblmessage.text = lblmessage.text & _ "you forgot to enter a value for " & _ addpanel.controls (i).id & "<p>" lblmessage.style ("forecolor")= "red" end if j=j+1 end if next
sub dgdata_edit (sender as object, e as datagridcommandeventargs) filldatagrid(e.item.itemindex) end sub
sub dgdata_delete (sender as object, e as datagridcommandeventargs) dim strsql as string = "DELETE FROM tblusers " & _ "WHERE userid = " & e.item.itemindex + 1
executestatement(strsql) filldatagrid() end sub
sub dgdata_update (sender as object, e as datagridcommandeventargs) if updatedatastore(e) then filldatagrid(-1) end if end sub
sub dgdata_cancel (sender as object, e as datagridcommandeventargs) filldatagrid(-1) end sub
sub dgdata_pageindexchanged (sender as object, e as datagridpagechangedeventargs) dgdata.databind() end sub
function updatedatastore (e as datagridcommandeventargs) as boolean dim i,j as integer dim params(7) as string dim strtext as string dim blngo as boolean = true
j = 0
for i =1 to e.item.cells.count - 3 strtext = ctype(e.item.cells(i).controls(0), _ textbox).text if strtext <> "" then params(j) = strtext blngo = true j= j+1 else blngo = false lblmessage.text = lblmessage.text & _ "you forgot to enter a value<p>" end if next
if not blngo then return false exit function end if
executestatement (strsql) return blngo end function
sub filldatagrid (optional editindex as integer = -1) ' open connection dim objcmd as new oledbcommand _ ("select * from tblusers", conn) dim objreader as oledbdatareader
try objcmd.connection.open () objreader = objcmd.executereader() catch ex as exception lblmessage.text = "error retrieving from the " & _ "database." end try
dgdata.datasource = objreader if not editindex.equals(nothing) then dgdata.edititemindex = editindex end if
Here is my insert statement: StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO patients_import_test "); sb.Append("(Referral_Number,Referral_Date,FullName,Patient_ien,DOB,FMP,SSN_LastFour,Race_Id,PCM,Age) "); sb.Append("VALUES(@rnum,@rdate,@fname,@patid,@birthDate,@fmp,@ssan,@race,@pcm,@age) "); sb.Append("WHERE Referral_Number NOT IN ( SELECT Referral_Number FROM patients_import_test )");I'm getting an "Incorrect syntax near the keyword 'WHERE'".If I remove the WHERE clause the INSERT statement work fine.
I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.
Error I'm getting: Incorrect syntax near the keyword 'AS'.
Here's where the error lies: CASE (SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID) WHEN False THEN 'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber ELSE vwEmp.SocialSecurityNumber END
Hi All -I am updating four values. What is the proper syntax to have thefollowing 4 update statements as one statement?set objRec = objDB.Execute("Update orientform set session = '" &strSession & "' where id = '" & strid & "'")set objRec = objDB.Execute("Update orientform set fname = '" & strfname& "' where id = '" & strid & "'")set objRec = objDB.Execute("Update orientform set gender = '" &strgender & "' where id = '" & strid & "'")set objRec = objDB.Execute("Update orientform set lname = '" & strlname& "' where id = '" & strid & "'")Thanks,Joey
Server Error in '/ys(Do Not Remove!!!)' Application.
Syntax error in INSERT INTO statement.
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.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Source Error:
Line 8: <Script runat="server"> Line 9: Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs) Line 10: SqlDataSource1.Insert() Line 11: End Sub ' InsertAuthorized Line 12: </Script> Source File: C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx Line: 10
I've created multiple select statement to pull in varies fields from a table and one of those fields is Fiscal_Year, however, after the select statement I'm adding a Where clause:
Public DBString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:VoicenetRTS FolderRTS ChargesAccountscosting.mdb" Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click Dim connstring As New OleDbConnection(DBString) connstring.Open() Dim searchstring As String = "SELECT * FROM Costings1" Dim da As New OleDbDataAdapter(searchstring, connstring) Dim DS As New DataSet() Dim dt As DataTable = DS.Tables("Costings1") da.FillSchema(DS, SchemaType.Source, "Costings1") da.Fill(DS, "Costings1") Dim cb As New OleDbCommandBuilder(da) da.InsertCommand = cb.GetInsertCommand da.UpdateCommand = cb.GetUpdateCommand Dim dr As DataRow = DS.Tables("Costings1").NewRow dr("Key") = TextBox1.Text dr("CODE") = TextBox2.Text dr("Element") = TextBox3.Text etc... DS.Tables("Costings1").Rows.Add(dr) da.Update(DS, "Costings1") <<<<<<<<<<<Syntax error in INSERT INTO statement.
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).
Hi, I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code: protected void ContinueButton_Click(object sender, EventArgs e) { //Get the ip address and put it into the customer table - (the instance of this user now exists) MembershipUser _membershipUser = Membership.GetUser(); Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog SqlDataSource customerDataSource = new SqlDataSource(); customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text; what next I've not got a clue as to what to write next. There is a try / catch statement after this using : rowsAffected = customerDataSource.Update(); which remains at 0 no matter what I try. Any help greatly appreciated.
Following is the stored procedure iam trying to create.Here i am trying to
First create a table with the table name passed as parameter Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows. Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.
This table would be further used with cursor. Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.
Hi All I'm having a bit of trouble with an sql statement being inserted into a database - here is the statement: string sql1; sql1 = "INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town,"; sql1 += " Postcode, Phone, DateOfBirth, email, PaymentAcctNo)"; sql1 += " VALUES ("; sql1 += "'" + TxtTitle.Text + "'," ; sql1 += "'" + TxtForename.Text + "'," ; sql1 += "'" + TxtSurname.Text + "'," ; sql1 += "'" + TxtHouseNo.Text + "',"; sql1 += "'" + TxtRoad.Text + "',"; sql1 += "'" + TxtTown.Text + "',"; sql1 += "'" + TxtPostcode.Text + "',"; sql1 += "'" + TxtPhone.Text + "',"; sql1 += "'" + TxtDob.Text + "',"; sql1 += "'" + TxtEmail.Text + "',"; sql1 += "'" + TxtPayAcc.Text + "')"; Which generates a statement like:INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town, Postcode, Phone, DateOfBirth, email, PaymentAcctNo) VALUES ('Mr','Test','Test','129','Test Road','Plymouth','PL5 1LL','07855786111','14/04/1930','mr@test.com','123456') I cannot for the life of me figure out what is wrong with this statement. I've ensured all the fields within the database have no validation (this is done within my ASP code) that would stop this statement being inserted. Line 158: dbCommand.Connection = conn;Line 159: conn.Open();Line 160: dbCommand.ExecuteNonQuery();Is the line that brings up the error - I presume this could be either an error in the statement or maybe some settings on the Database stopping the values being added. Any ideas which of this might be ? I'm not looking for someone to solve this for me, just a push in the right direction! Thanks!
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. 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 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
