I’m using VB.NET to extract data from an Access database, simple enough. However, I type in the following line:
Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]>=" & IP_Address And "[EndIPNo]<=" & IP_Address
It doesn’t work.
If I type in:
Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]>=" & IP_Address
The above sql line works.
I now realize SQL commands is very sensitive.
FIELD NAMES:
quote:StartIP |EndIP|StartIPNo|EndIPNo|CountryCode
5.0.0.0 |5.163.66.79|83886080|94585423|ZA
5.163.66.80|5.163.66.95|94585424|94585439|SE
5.163.66.96|5.255.255.255|94585440|100663295|ZA
If the user types in a value, I want it to check the values between the StartIPNo and EndIPNo and return the CountryCode.
Clean vista install, Clean full office 2007plus install Clean visual studio 2008pro install Many failed sql 2005dev installed, did SKUUPGRADE-1 Now have office2007 Smallbiz, VS2008pro, SqlExpress and sql 2005dev installed. Note outlook 2007 error on first run after sql2005dev. Seams to have broken and created a new sql connection, don€™t think its the original. but that€™s not the current issue ! web admin page error, could not connect to db I did regsql.exe from the net dir and the web admin work and the db is created for roles and such in vs 2008 via web admin page and mssql. Managed to create db in apps data folder and modify connection in server explorer. Added table to aspx page from apps folder and it runs via f5, but errors under localhost. €œCannot open user default database. Login failed.€? €œLogin failed for user 'NT AUTHORITYNETWORK SERVICE'€? Have seen some reference to iis permissions, but not sure what to change? Is it a file level permission, a virtual permission Works in vs2008 using f5 run! http://localhost:50115/vs2008/ Can work with apps folder db file in vs 2008 server explorer Had to modify db connection back to sqlexpress in server explorer Can work with db in sql management studio, have three local instances; Pc Pcsqlexpress Pcsmallbiz Can display db data in aspx page via f5 , but not localhost unless vs2008 is not running!!! Have not been able to make vs2008, Sql2005dev, sqlexpress all work together using iis in vista via localhost and vs2008 f5 at same time?? Can€™t do anything with db in solutions explorer, but can work in server explorer if I modify connection back to sqlexpress Can€™t display data aspx page via localhost http://localhost/vs2008/ Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITYNETWORK SERVICE' Localhost works in iis via virtual directory if I remove db grid in page or vs is not running Could someone test and tell how to use all of these together? What is going to happen if I get it to work local and then try to upload to shared host? <connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="Server=wabpc;Database=VS2008test;Integrated Security=true" providerName="System.Data.SqlClient" /> <add connectionString="Server=SQLEXPRESS;Database=Database.mdf;Integrated Security=true" name="sqlexpress" />
</connectionStrings> If I close vs2008 and the management studio, localhost works and displays the db info!! Open vs2008, modify connection back to sqlexpress and the aspx page breaks via localhost Close vs2008 and local host works Open vs2008 and get a error 25 in server explorer connection string invalid, but f5 works after the offline clears. I change db connection back to sqlexpress to work with db in server explorer! That breaks localhost, but f5 works
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
i have Smart Device with following Configuration :
Processor: ARMv4i OS: Window CE 5.0
I have installed SQL CE 3.0 on my Device.
When I try to make new database on my devcie using Qurey Analyser 3.0 , it gives "ISQLW Provider Failed... ". When I try Deploye SQL CE 3.0 Database Application using F5 in VS2005 , it give an error "Can€™t find P/Invoke DLL sqlceme30.dll".Plz tell me How can i resolve that problem.
As the title says I stupidly deleted the BuiltinAdministrator group now can€™t login. Is there anyway get back in? I did not setup the server up so I€™m unsure what the SA password is. As a last resort could I rebuild the Master database and then over write if backup?
Sorry that I have to ask again, because the question is a FAQ but I can€™t find the €œData Mining Web Controls Project€? I have to deploy. I downloaded and installed the first five examples from the page http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
But no one contains the expected project. Could somebody help me please? I can€™t start with my project because I can€™t use the Viewers.
This query should return results, minus any rows that have a UserId in a Filter table (which is just two columns, one the userId and the other a filteredUserId that the user has chosen to block)
alter procedure sp_wm_GetAds
@userid int
as select a.*, dbo.GetAge(a.bday, GETDATE()) age from wm_user a where hasimage=1 and a.userid not in (select userid from wm_filter where userid=@userid and filteredUserId=a.userid)
VB.NET hitting MySQL Express using a sqldataadapter
Here is my Sql statement
SELECT FormName, FieldSeq, FieldTitle, FieldType, FieldLength, DecimalPlaces, CodeList, CodeAdd, CodeMask, TableName, AlternateTableName, FieldName, FieldRequired, CodeTableName, CanEdit, ToolTip, ImportantColor FROM FormFieldInfo WHERE (FormName = 'frmPolicy') AND (FieldSeq < 1000) AND (FieldName <> 'Split1') AND (FieldName <> 'Split2') AND (FieldName <> 'Split3') AND (FieldName <> 'Split4') ORDER BY FieldSeq
This works great it gets the rows that i want except for there are 4 rows in which the FieldName value is Null and these are also being excluded. Can someone tell me why?
I have tried multiple variations on this select statement and either it doesn't exclude these rows or it excludes these and the nulls.
I have the sql statement given below. I want to sort by agency name, except, I want the agency a person belongs to at the top. The text "DHS" is stuffed through code. AgAbbreviation is varchar(4). AgName is varchar(60). This never works. The row containing "DHS" is never at the top. Please help. Thanks
SELECT AgAbbreviation, AgName FROM Agency ORDER BY CASE WHEN RTRIM(AgAbbreviation) = ' DHS' THEN 0 ELSE 1 END, AgName
can someone tell me why this sql statement doesnt work?
SQL = "SELECT (Count(department_id) as 'totals' FROM nonconformance WHERE department_id = '7'),(Count(department_id) as 'totals2' FROM nonconformance WHERE department_id = '1') FROM nonconformance"
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?
i viewed the delete statement with the execusion plan and this is what i saw.
delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
the following is my TSQL command of a job(after passing databaseName, I don't want multiple steps, just with 1 step) --=================================== BACKUP DATABASE ABC To Backup_ABC_1 with init, name = 'Backup of 1(M, W, F ) Job_ABC_1Mon' go backup log ABC with truncate_only go print 'shrinkdatabase begin-- ' dbcc checkdb(ABC) go dbcc shrinkdatabase(ABC, 10)
Issue: with 3 'go' inside the whole line, execution failed. after replace 'go' with 'begin' and 'end' for each sql statemet, the execution works. Someone could explain why 'go' as a batch does not work in this situation?
also, is this a good practice to 1. truncate_only 2. dbcc checkdb 3. shrinkdatabase after a daily backup?
If any of you can tell me how the following SQL SELECT statement works , I will be forever grateful! What I mean is, if anyone has actually seen an SQL statement like this and knows what it does, I would be eternally grateful, because I have been writing SQL for over 10 years, and not only do I have no clue how this monstrosity works (and I assure you that it does work), I have never seen anything quite like it. The primary purpose of the code is to sort the chapel messages (mp3s) for my institutions website, but it moonlights for NASA as any code they deem necessary to perform the most complicated computerized tasks known to mankind.
Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com
Hello everybody I have problem with CASE statement. Here is select it
Code Block select mev.Id ,mev.MetaElementId ,mev.ElementValue ,mev.DocumentId ,me.ElementTypeId ,castedValue = case when me.ElementTypeId =3 then cast(mev.ElementValue as integer) when me.ElementTypeId =4 then cast(mev.ElementValue as datetime) end from dbo.tbMetaElementValue mev inner join dbo.tbMetaElement me on mev.MetaElementId = me.Id where mev.MetaElementId =7
it returns
Id MetaElementId ElementValue DocumentId ElementTypeId castedValue
49 7 2006 28 3 6/30/1905 0:00
53 7 2004 30 3 6/28/1905 0:00
61 7 2006 36 3 6/30/1905 0:00
67 7 2005 38 3 6/29/1905 0:00
70 7 2004 39 3 6/28/1905 0:00
105 7 2003 63 3 6/27/1905 0:00
166 7 2006 109 3 6/30/1905 0:00
195 7 2005 129 3 6/29/1905 0:00
220 7 2005 150 3 6/29/1905 0:00
223 7 2006 151 3 6/30/1905 0:00
As you can see it should return castedValue as integer but it cast to datetime which is wrong. If I commented line
Code Block when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)
it casts everything normal, but as soon as it has more than one condition in CASE it will choose anything but not right casting
Looks like I am missing something really fundamental. Any help is apreciated !
Can€™t understand the Query execution interval, for every one criteria it takes extra 60 seconds. Help please?,
Query A
SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL
AND T2.sd='20060817') AND sd='20060817'
--AND STOCKNAME like 'A%'
ORDER BY D_CUM desc;
The above query take 2 seconds to return result
Query B
SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL
AND T2.sd='20060817') AND sd='20060817' AND STOCKNAME like 'A%' ORDER BY D_CUM desc
This query same as above but " AND STOCKNAME like 'A%'" Criteria only added but it takes 01 minute 43 seconds to return result. why it is like that where i need to adjust the query i have no idea. please help
In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:
Merge TST.dbo.LockCombination AS LKC1 USING (select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId from [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?
Hi, I was handed an old application written for sql05, it has this (kind of) statement: Dim CommandText As String = "SELECT c.name FROM db1 c LEFT JOIN "
CommandText += "db2.dbo.Users u ON u.iKey = c.key "
CommandText += "WHERE u.cUserName = '" & User & "'"
As you can see, there are two databases, db1 and db2 and this worked fine in sql05, but when I convert each of the databases to sqlexpress, I get this error: Invalid object name 'db2.dbo.Users'.
So how can I make this work in sqlexpress? Are queries across databases not allowed?
Hello to all, i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me. I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418: declare @IDM int; declare @IDO varchar(8000); set @IDM = 3418; set @IDO = '3430' select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 )) but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM)) I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks I use MS SQL 2005 Server Management Stadio Express Thanks a million and Best regards Sha
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
hello it seems lack of c# skills is getting the better of me here...iv been trying to get this to work for hours now without success. I have just started working in c# so i am a beginner :) all i want to do it query the database, check to see if a title exists, if yes then populate textbox saying 'title in use' but if the title doesnt exists then continue executing rest of code to add the title. You'll notice iv tried to use string variables and sqlDataAdapter but i cant get these to work either...please help so far i have this that keeps giving me the error that my button click is undefined: protected void bookButton_Click(object sender, EventArgs e){ //string title;try{SqlConnection conn1 = new SqlConnection(); conn1.ConnectionString = "Data Source=Gemma-PC\SQLEXPRESS;" +"Initial Catalog=SoSym;" + "Integrated Security=SSPI;";SqlCommand findTitle = new SqlCommand("SELECT title FROM Publication WHERE title='" + titleTextBox.Text + "';", conn1);
conn1.Open(); findTitle.ExecuteNonQuery(); /* DataSet myDataSetTitle = new DataSet("PublicationTitle"); myDataSetTitle.Clear(); myDataAdapterTitle.Fill(myDataSetTitle);//myDataSet contains results from above SELECT statement title = myDataSetTitle.Tables[0].Rows[0]["title"].ToString();*/ }catch(Exception ex) {TitleInvalidMessage.Text = "Title Not Accepted: This title is already in use by another publication" +ex.Message; } //title does not exist in table so continue and add to database //rest of code here
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code. Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message. 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: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error: Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 1 <asp:sqldatasource runat="server"></asp:sqldatasource> 2 <%@ Page Language="C#"%> 3 using System; 4 using System.Data; 5 using System.Collections; 6 using System.Data.SqlClient; 7 8 <script runat=server> 9 10 protected void Page_Load(object o, EventArgs e) 11 { 12 ObjectDataSource dsa; // This works no problems from the compiler here 13 SqlDataSource ds; // This works no problems from the compiler 14 15 if (IsPostBack) 16 { 17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text)) 18 { 19 instructions.Text = "Congratulations, your authenticated!"; 20 instructions.ForeColor = System.Drawing.Color.Red; 21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'"; 23 } 24 else 25 { 26 instructions.Text = "Please try again!"; 27 instructions.ForeColor = System.Drawing.Color.Red; 28 } 29 } 30 31 } 32 33 bool AuthenticateUser(string username, string password) 34 { 35 // Authentication code goes here 36 37 }
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
