hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
Dim objConn As New SqlConnection Dim objCmd As New SqlCommand Dim Value As String = EventCmb.SelectedItem.ToString() objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True" Try objConn.Open() objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter objCmd.Parameters.AddWithValue("@Value", Value) ' Add the @value withthe actual value that should be put. This makes it securer Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned. If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then ContactLbl.Text = RetVal.ToString() Else ' noting was returned End If Catch ex As Exception Throw ex Finally objConn.Close() End Try There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() " Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int." It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!
I'm attempting to copy data from an Epicore server with multiple company databases to another server. All this data will be placed in a single table on the receiving server with the addition of an database identifier, so that the receiving department will know, to which company the data belongs. I have code that will dynamically create the SQL and then execute it using EXEC (@SQL), and this works just fine acrosss multiple databases. I've read in my Transact-SQL Programming book that using EXEC() is not limited to 255 characters, but it still appears that my dynamically created SQL is being shortened. The insert statement and the select statement list each column, and here is that code:
I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.
Thanks in advance, Anne
begin Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date) select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date from TEAM3B_PULL_TOTAL_TST A where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST) and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST) and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST) and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE) end
epost char(60) default NULL, i must be a @ value in it! any ides?
kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!
losenord char(60) default NULL, verlosenord char(60) default NULL, How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!
i've two column... One for msg another for msgLastposted
in label control view msg(last visitor)....... that msg stored in my db.
Example Table Data --------------------------------------------- MSg|MsgLastPosed
How edit forum |2007-03-12 10:50:25.747 How to Value Change|2007-03-12 10:56:36.373 Sql Command|2007-03-12 11:00:25.047 User Control|2007-03-12 11:02:10.793 How I can uninstall|2007-03-12 13:07:51.233 -----------------------------------------------
In table have many record..
label control display msg based on last visitor time.. after that again msg changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..
:This is a segement of VBscript for checking lastest Backup in SQLServer 2000: 'temporary variables to useDim sTemp1Dim i, sTemp2 'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2 end If If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve
I am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
Hi, I will give someone a script that creates a database using : create database mydatabase my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737 go use mosaikDB737
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[FileListInput]( [FileName] [char](50) NULL ) ON [PRIMARY] END
use master select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb') select * from mosaikDB737.dbo.DBlistOutput
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this. If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems FROM Table1 Inner join on table1.assettag = table2.assettag where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1 SET query1.ScheduledItems = True Where query1.Status = Scrubbed
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:
select * from Table where ... and Official in (... problem area ...)
What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.
Any ideas? I don't want to use a dynamic query. Thanks,
Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman (pkey smallint primary key, fkey char(1), updateddatetime)
insert into #blindman (pkey, fkey, updated) select1, 'A', '1/1/2006' UNION select2, 'B', '1/1/2006' UNION select3, 'A', '1/2/2006' UNION select4, 'B', '1/2/2006' UNION select5, 'A', '1/4/2006' UNION select6, 'B', '1/2/2006' UNION select7, 'A', '1/3/2006' UNION select8, 'B', '1/3/2006' UNION select9, 'A', '1/5/2006' UNION select10, 'B', '1/5/2006'
drop table #blindman Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key. The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value. Here is the desired output for the sample data:pkey fkey updated nextpkey ------ ---- ---------- -------- 1 A 2006-01-01 3 3 A 2006-01-02 7 7 A 2006-01-03 5 5 A 2006-01-04 9 2 B 2006-01-01 4 4 B 2006-01-02 6 6 B 2006-01-02 8 8 B 2006-01-03 10 Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value. Ideally, I want this as a VIEW.
ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:
What is the total weight of different colored parts?
ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:
Select sum pweight from parts where distinct pcolor
looks horribly wrong so any help is appreciated. here is another one:
What colored part has total weight greater than 8 units?
select pcolor from parts where sum pweight > 8
??? i dunno lol.
there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?
Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.
Is it possible to grant permissions without specifying the username,passwd
I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:
Hi!If you like to spend some moments on my code examples,please look at it - usage is free (-:http://www.codeproject.com/useritems/TSQL_coding.aspFeedback welcome.GreetingsBjorn
Hi. I'm looking at a problem and I can't find any solution short ofcoding.I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any ofthe 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's saythat-bucket A has 100 PINS-bucket B has 300 PINS-bucket C has 600 PINS-bucket D has 200 PINS-bucket E has 500 PINS-bucket F has 350 PINS-bucket G has 700 PINSI need to know, for each PIN, the number of buckets (tables) itbelongs to, and which ones, i.e:- PIN 1 belongs to A, C, D, so it belongs to 3 buckets- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets- PIN 3 belongs to A, so it belongs to 1 bucket- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets- PIN 5 belongs to ..., so it belongs to 0 bucketsetc, etcWhat would be the simplest way to achieve that, please ?Thank you very muchAlex.
Hi List,I am searching franticly for a solution (or the procedure) to settingthe coding of a new DB to UTF-8. I can find no setting in the ServerManager, during creation of the DB, to influence this. Can someoneplease show me the way? Thanks--Shawn
This question probably overlaps a few different topic areas.
As I will be required to work with both Oracle and SQL Server I will be in a difficult position with SSIS(due to it's change in distribution).
Therefore I am having to look at alternatives.
With coding a can open a text file and parse it reasonably to my satisfaction. However getting the data into the database is incredibly slow.
I am using an Insert into for each line, which I am sure everone will shake their head over. This seems to be pretty slow even using transactions.
Is there any scope in using data tables or have the read on one thread and write on another.
Other than that is there an Oracle equivalent of SSIS which comes (probably get shot for asking that on a microsoft web site, but would probably get shot if I asked on Oracle forums as well).
In the past we had reasonable results in outputting to csv and then doing some sort of bulk insert, messy and irritating though this may be.
Any ideas on this area will be gratefully accepted.
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
GLOBAL STRING Line1_BatchID GLOBAL STRING Line1_CompletedDate GLOBAL INT Line1_CompletedQty GLOBAL STRING Line1_CompletedTime GLOBAL STRING Line1_ExpiryDate GLOBAL INT Line1_Qty GLOBAL STRING Line1_OrderID
GLOBAL INT Line1_BatchID_ID GLOBAL INT Line1_CompletedDate_ID GLOBAL INT Line1_CompletedQty_ID GLOBAL INT Line1_CompletedTime_ID GLOBAL INT Line1_ExpiryDate_ID GLOBAL INT Line1_OrderStatus_ID
GLOBAL STRING Line2_BatchID GLOBAL STRING Line2_CompletedDate GLOBAL INT Line2_CompletedQty GLOBAL STRING Line2_CompletedTime GLOBAL STRING Line2_ExpiryDate GLOBAL STRING Line2_OrderStatus GLOBAL INT Line2_Qty GLOBAL STRING Line2_OrderID
GLOBAL INT Line2_BatchID_ID GLOBAL INT Line2_CompletedDate_ID GLOBAL INT Line2_CompletedQty_ID GLOBAL INT Line2_CompletedTime_ID GLOBAL INT Line2_ExpiryDate_ID GLOBAL INT Line2_OrderStatus_ID
GLOBAL STRING Line3_BatchID GLOBAL STRING Line3_CompletedDate GLOBAL INT Line3_CompletedQty GLOBAL STRING Line3_CompletedTime GLOBAL STRING Line3_ExpiryDate GLOBAL STRING Line3_OrderStatus GLOBAL INT Line3_Qty GLOBAL STRING Line3_OrderID
GLOBAL INT Line3_BatchID_ID GLOBAL INT Line3_CompletedDate_ID GLOBAL INT Line3_CompletedQty_ID GLOBAL INT Line3_CompletedTime_ID GLOBAL INT Line3_ExpiryDate_ID GLOBAL INT Line3_OrderStatus_ID
GLOBAL STRING result GLOBAL STRING result2
FUNCTION getOrderDetail(STRING Product_ID) INT counter = 0 INT statusSQL, sqlResult; STRING Sql1 STRING Sql2 STRING Sql3
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')" sqlResult = SQLExec(statusSQL, Sql1); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF result <> "" THEN result = result + "," END result = result + SQLGetField(statusSQL, "SetId") END END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID" sqlResult = SQLExec(statusSQL, Sql2); IF sqlResult = 0 THEN //If SQL Success result = "" IF SQLNext(statusSQL) = 0 THEN //Select first SetId result = SQLGetField(statusSQL, "SetId") END END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field" sqlResult = SQLExec(statusSQL, Sql3); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF Product_ID = "Biscuit" THEN IF counter < 9 THEN IF counter = 0 THEN Line1_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line1_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line1_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line1_Qty = SQLGetField(statusSQL , "DataValue") END END END
IF Product_ID = "ChocolateBiscuit" THEN IF counter = 0 THEN Line2_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line2_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line2_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line2_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "PeanutButterBiscuit" THEN IF counter = 0 THEN Line3_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line3_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line3_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line3_Qty = SQLGetField(statusSQL , "DataValue") END END
counter = counter + 1 END END
END SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateBatchID(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID) END IF Product_ID = "ChocolateBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID) END IF Product_ID = "PeanutButterBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID) END
sqlResult = SQLExec(statusSQL, Sql1); END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateCompletedQty(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID) END IF Product_ID = "ChocolateBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID) END IF Product_ID = "PeanutButterBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID) END
sqlResult = SQLExec(statusSQL, Sql1); END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateCompletedOrder(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID) sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID) sqlResult = SQLExec(statusSQL, Sql1); END END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
INT FUNCTION getLine1_Qty() RETURN Line1_Qty END
INT FUNCTION getLine1_CompletedQty() RETURN Line1_CompletedQty END
STRING FUNCTION getLine1_OrderID() RETURN Line1_OrderID END
STRING FUNCTION getLine1_BatchID() RETURN Line1_BatchID END
INT FUNCTION getLine2_Qty() RETURN Line2_Qty END
INT FUNCTION getLine2_CompletedQty() RETURN Line2_CompletedQty END
STRING FUNCTION getLine2_OrderID() RETURN Line2_OrderID END
STRING FUNCTION getLine2_BatchID() RETURN Line2_BatchID END
INT FUNCTION getLine3_Qty() RETURN Line3_Qty END
INT FUNCTION getLine3_CompletedQty() RETURN Line3_CompletedQty END
STRING FUNCTION getLine3_OrderID() RETURN Line3_OrderID END
STRING FUNCTION getLine3_BatchID() RETURN Line3_BatchID END
FUNCTION dbTest()
INT statusSQL, sqlResult; INT counter = 0 STRING Sql1 STRING Sql2 STRING Sql3 STRING Product_ID = "Biscuit" result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')" sqlResult = SQLExec(statusSQL, Sql1); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF result <> "" THEN result = result + "," END result = result + SQLGetField(statusSQL, "SetId") END END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))" sqlResult = SQLExec(statusSQL, Sql2); IF sqlResult = 0 THEN //If SQL Success result = "" IF SQLNext(statusSQL) = 0 THEN result = SQLGetField(statusSQL, "SetId") END END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field" sqlResult = SQLExec(statusSQL, Sql3); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF Product_ID = "Biscuit" THEN IF counter = 0 THEN Line1_BatchID_ID = SQLGetField(statusSQL , "ID") Line1_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line1_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line1_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line1_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "ChocolateBiscuit" THEN IF counter = 0 THEN Line2_BatchID_ID = SQLGetField(statusSQL , "ID") Line2_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line2_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line2_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line2_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "PeanutButterBiscuit" THEN IF counter = 0 THEN Line3_BatchID_ID = SQLGetField(statusSQL , "ID") Line3_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line3_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line3_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line3_Qty = SQLGetField(statusSQL , "DataValue") END END
counter = counter + 1 END END
END SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
I have a table where I am needing to concatenate several columns. Some of the columns might have NULL values, so I need to concatenate only those fields where there is data and not concatenate the NULLs.
For Example:
My columns are ID, Effdt, keyword01, keyword02, keyword03, keyword04, keyword05, keyword06, keyword07, etc.
I need to concatenate all of the keyword columns into one field (keyword01 + '/' + keyword02 + '/' +keyword03 + '/' +...). However, keyword06 and on might be NULL (there is never an instance where, say, keyword02 has data, keyword03 is NULL, and keyword04 has data. If a keyword field, greater than keyword01 (it always has data in it), has data in it... then every keyword field before it does also.)
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE"; execute SQL statement "select * from table A where tableA.field B = "ABCDE"; For each record from the result of the above SQL statement, "select * from tableB where tableB.field C=tableA.fieldX" if the record can be found in tableB, "update tableB set tableB.fieldD=something"; if the record cannot be found in tableB, "insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
I'm currently working on an application that needs to store address information. I figured a database of some sort would be the perfect thing to use.
The thing is: The application will work locally, there will be no server running on a network
There shouldn't be any login and password protection for the db When I later on install the application on a computer, a fresh and clean db should be created within the applications folde
How can I create and access a db like that using c# and MS SQL Server?
Any hints, tips, example code, links...anything?
Many thanks in advance!!!
PS. I've been working some with visual c# express and recently installed SQL Server & Manager. I've previusly made some php web pages using MySQL but I´m a total noob when it comes to working with SQL and c# (= I'm stuck!).
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference? Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is successful, my return value is coming back correctly (to whatever i set it)....but if there is an error such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP. It comes back blank. (The literature I've read says that processing should continue in the SP, so you can perform error processing...is that right?)
I set the return var in my ASP as: objCommand.Parameters.Append objCommand.CreateParameter("return",_ adInteger,adParamReturnValue,4) and read it back as: strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah if @@error = 0 return(100) else return(200)
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database. The database will be replicated to an central web server. That will say one web server and x numbers of laptops that has same GPS program. :) All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
