How To Loop Through This Sql Table And Display In Ms Access Listbox
Nov 9, 2006
I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.
So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.
Here is an example of the table:
USER ID TYPE PAYMENT
=====================
0001 CARD 150.00
0001 CASH 250.00
0002 CASH 175.00
If I have 0001 in the txtuserid textbox, I then want to display in
the listbox:
CARD 150.00
CASH 250.00
How would I do this?
thanks
View 1 Replies
ADVERTISEMENT
Oct 23, 2007
Hi everyone, not sure if a this topic has been covered yet (a have been looking all day), but as I am still very new to this, my problem is as follows:
In the Try .. Catch block below, data is posted from a form and the SqlCommand.ExecuteScalar() statement returns a Unique Job ID.
I am attempting to populate a subordinate table for qualifications which are selected from a ListBox, but rather than using qualification titles, I am using the values.
My problem is that only one value (the first) gets posted multiple times, when multiple values are selected.
Looking at the For Each loop in the inner Try Catch block, I am wondering whether there is some sort of Index pointer that needs to be incremented, in order to establish new values further down the list.
I have seen no evidence that this is the case, save for the fact that the value stalls on just the first.
Any help would be appreciated.
===== CODE ===
Try
C4LConnection.Open()
JobPostingID = SqlJobPost.ExecuteScalar()Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
Try
' Multiple Qualification EntriesSqlQualPost.Parameters.Add(New SqlParameter("@JobPostingID", SqlDbType.Int))
SqlQualPost.Parameters("@JobPostingID").Value = Int32.Parse(JobPostingID)SqlQualPost.Parameters.Add(New SqlParameter("@QualificationID", SqlDbType.Int))
SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
If Qualifications.SelectedIndex > -1 ThenFor Each Item In Qualifications.Items
If Item.Selected ThenResponse.Write("<br />SelectedItem Value: " & Qualifications.SelectedItem.Text)
QualPostingID = SqlQualPost.ExecuteScalar()SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
End If
Next
End IfCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Could not add qualifications <br />" & Exp.Message
End Try
failJobPost = FalseCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Error: could not post job to database <br />" & Exp.Message
Finally
C4LConnection.Close()
End Try
View 2 Replies
View Related
Nov 20, 2007
Hello everyone. I am using C#, and posted this in the C# forum, but was told to try here, as for some reason I just can't get this to work. Everyone was very helpful, but for some reason I keep getting a message that a value is not being given for one or more required parameters. This doesn't make any sense though, as I am specifying a value for all parameters...
Basically, I have a listBox that has several items in it. I want to update the database for each item that appears in the listBox. I can't seem to get this to work though, no matter what I try, and it is driving me nuts... lol
Here is a link to my original thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2349891&SiteID=1
Here is my code:
Code Block
string whereClause = "";
for (int i = 0; i < Panel1ListView1.Items.Count; i++)
{
if (i == 0)
{
whereClause = "ID = " + Panel1ListView1.Items[0].Text;
}
else
{
whereClause += " OR ID = " + Panel1ListView1.Items[i].Text;
}
}
try
{
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Environment.CurrentDirectory + @"DB.mdb;Jet OLEDB:Database Password=xxx;"))
{
OleDbCommand cmd;
conn.Open();
string command = "UPDATE [Table1] SET [Status] = @P1, [Name] = @P2, [Number] = @P3 WHERE " + whereClause;
cmd = new OleDbCommand(command, conn);
cmd.Parameters.Add("@P1", OleDbType.VarChar).Value = Panel1TextBox3.Text;
cmd.Parameters.Add("@P2", OleDbType.VarChar).Value = Panel1TextBox1.Text;
cmd.Parameters.Add("@P3", OleDbType.VarChar).Value = Panel1TextBox2.Text;
int i = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
I really appreciate any help anyone can give me. I REALLY want to get this working. I am open to any suggestions and will try anything.
Thank you very much for your help.
View 7 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Jan 22, 2008
One of my tables in the db contains the websites and the location of their link directory. i.e. domain.com/linkdirectory
I want to run a check against every website's robots.txt file to ensure that it doesn't look like this:
user-agent: *
Disallow: /linkdirectory/
so I need a program that does the following:
For each website in database (start of loop)
Does robots.txt exist on their site ?(run a remote check)
if it does
is their link directory blocked in robots.txt?
if so, then log their user id and website name into an array for later use
end if
endif
end loop
How would I do this in ASP.net 2.0?
View 3 Replies
View Related
Jan 31, 2008
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
ByVal strCompany As String, _
ByVal decSerial As Decimal, _
ByVal strSerialCode As String, _
ByVal strSerialScan As String, _
ByVal decMasterSerialNumber As Decimal, _
ByVal strCustomerPart As String, _
ByVal strTakataPart As String, _
ByVal strCustomerRanNo As String, _
ByVal strCustomerAbv As String, _
ByVal strDestinationAbv As String, _
ByVal decQty As Decimal, _
ByVal strCreatDate As String, _
ByVal decVoidSerialNo As Decimal, _
ByVal strProductionLineNo As String, _
ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe
Dim strResult As Integer = 0
Dim strErrorText As String = String.Empty
Dim dsData As New DataSet
Dim parameter(15) As OleDbParameter
If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
' Populate parameter collection
parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))
RunDB2Sp("FABLE.MAP", parameter, dsData)
If dsData.Tables.Count > 0 Then
dsData.Tables(0).TableName = "Supreeth"
Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here
SqlContext.Pipe.Send(bitresult)
SqlContext.Pipe.Send("No errors")
End If
Else
Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
'*********************************************
' Declare Variables
'*********************************************
Dim daAdaptor As OleDbDataAdapter
Dim cmdAS400 As OleDbCommand
'Dim dstestMe As New DataSet
Try
cmdAS400 = CreateCommand(strProcedure, parms)
daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set
daAdaptor.Fill(dsData)
Catch expError As OleDbException
daAdaptor = Nothing
Finally
daAdaptor = Nothing
cmdAS400.Dispose()
'Me.Close()
End Try
End Sub
Public Shared Function CreateParameter(ByVal name As String, _
ByVal type As OleDbType, _
ByVal size As Integer, _
ByVal direction As ParameterDirection, _
ByVal paramValue As Object) As OleDbParameter
Dim param As OleDbParameter = New OleDbParameter
param.ParameterName = name
param.OleDbType = type
param.Size = size
param.Direction = direction
param.Value = paramValue
Return param
End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
Dim CmdSAS400 As OleDbCommand
Dim parameter As OleDbParameter
Dim connAS400 As OleDbConnection
connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
connAS400.Open()
CmdSAS400 = connAS400.CreateCommand()
CmdSAS400.CommandText = strProcedure
CmdSAS400.CommandType = CommandType.StoredProcedure
CmdSAS400.Parameters.Clear()
'CmdAS400.CommandTimeout = intTimeOut
If (prams Is Nothing) Then
Else
For Each parameter In prams
CmdSAS400.Parameters.Add(parameter)
Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
Thanks
View 3 Replies
View Related
Nov 28, 2006
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies
View Related
Oct 10, 2007
I have nine type of buttons,
EnrollAmtBTM
PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button
this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION
THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation()
{
WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass();
string SQLCONDITION = "";string RUN_SQLCONDITION = "";
SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null)
{ValueSelected = new SessionValues();
Session.Add(Session_UserSPersonalData, ValueSelected);
}
else
{
ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);
}ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false;
Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode();
SelectedNode = AgencyTree.SelectedNode;
SelectedAgency = SelectedNode.Value.ToString();
Agencytxt.Text = SelectedAgency;
Agencytxt2.Text = SelectedAgency;
Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null;
try
{CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'");
}
catch
{
}DataTable TableSet = new DataTable();
TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows)
{if (IndexingValues == 0)
{SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
else
{SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
IndexingValues += 1;
}SQLCONDITION = SQLCONDITION + ")";
ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0)
{
Dataviewlisting.ActiveViewIndex += 1;
}
else
{
Dataviewlisting.ActiveViewIndex = 0;
}
SelectedAgency = SQLCONDITION;
ValueSelected.CONDITION = SelectedAgency;
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;
try
{
CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);
EnrollAmtBTM.Text = CollectionCount.ToString();
}
catch
{
}////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.
"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"
here is the functionpublic int CollectedFinaceDataCount(String SQLStatement)
{int DataCollection;
DataCollection = 0;
try
{
SQLCommandExe = FinanceConnection.CreateCommand();
SQLCommandExe.CommandType = CommandType.Text;
SQLCommandExe.CommandText = SQLStatement;
ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar();
DisconnectToDatabase();
}catch (Exception ex)
{Console.WriteLine("Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString());
}
return DataCollection;
}
So here mu requirement request is to display only the value fronm the view i have against the agency selected
Please help ASAP
Thanks
Santosh
View 8 Replies
View Related
Jan 18, 2007
Hi ALL,
Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container.
Please Note :
I have a folder containing .sql files. I have to dynamically loop through the files and send them as a File connection Folder to the Execute Sql Task.
When I run this Package I am getting the follwoing error :
[Execute SQL Task] Error: An error occurred with the following error message: "Access to the path 'C:ProjectsFuzzy Lookup DataFlow ExampleScripts' is denied.".
Also I have logged in to the machine as Administrator and to Sql Server with sa.
Please help.
Thanks.
Regards,
Salil
View 1 Replies
View Related
Nov 29, 2004
I got one table with 3 columns = Column1, Column2, Column3
Sample Table
Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0
Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0
Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0
The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner
Column1 | Column2 | New1 | New2|
Thanks
View 3 Replies
View Related
Jan 22, 2012
I have a table that stores a couple of tablenames in the same db.The tablenames in the table can change from time to time.Is it possible to loop through the tablenames in the table and run a query against each table name. I cannot hard code the table names in the query because they can change from time to time.
View 1 Replies
View Related
May 18, 2004
Is there a way to loop using a cursor in SQL-server so i can see if each columns of each tables that i loop through my DB have a specific string value and change it to something else, renaming the column if the match if correct.
any threads that i can read from or website..
thanx !!
View 3 Replies
View Related
Jun 4, 2008
I have a table called _phy_greenville. Its a table that was imported from an excel file. I need to take the values in this table, and pass them to the following stored procedure. This stored proc create the physician record correctly for me when doing one record at a time. What I need to do is pass ALL record in this table (_phy_greenville) into this stored proc. Is there anyway I can loop through, or do some sort of bulk insert?
I tried the following, but obviously this does not work
BEGIN TRANSACTION
EXEC pInsertPersonEX SELECT 1, 'Dr.', FirstName, LastName, Suffix, Email, CAST((LEFT(FirstName, 1) + LastName)as varbinary), 31, PrimarySpecialty, 'Student', REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''), NULL, 0, 0, NULL, Street, NULL, City, 41, Zip,3 FROM _Phy_Greenville
SELECT * FROM Person WHERE PersonOrganizationID = 31
ROLLBACK TRANSACTION
Here is the whole stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pInsertPersonEx]
(
@Active bit,
@PersonPrefix varchar(5),
@PersonFirstName varchar(50),
@PersonLastName varchar(50),
@PersonSuffix varchar(20),
@PersonEmail varchar(100),
@PersonPassword varchar(20),
@PersonOrganizationID int,
@PersonDepartment varchar(50),
@PersonTitle varchar(100),
@PersonPhone varchar(10),
@PersonFax varchar(10),
@PersonInfoRequested bit,
@PersonHCTrained bit = NULL,
@PersonHCTrainedDate DateTime = NULL,
@AddressAddress1 varchar(100),
@AddressAddress2 varchar(100),
@AddressCity varchar(100),
@StateId int,
@AddressPostalCode varchar(10),
--@DepartmentID int,
@RoleID int
)
as
IF @PersonHCTrainedDate = '01/05/1900' SET @PersonHCTrainedDate = NULL
set nocount on
declare @PersonId int,
@AddressTypeID int
insertPerson
(
Active,
PersonPrefix,
PersonFirstName,
PersonLastName,
PersonSuffix,
PersonEmail,
PersonPassword,
PersonOrganizationID,
PersonDepartment,
PersonRegistrationDate,
PersonLicenseAgreement,
PersonTitle,
PersonPhone,
PersonFax,
PersonInfoRequested,
PersonHCTrained,
PersonHCTrainedDate
)
values
(
@Active,
@PersonPrefix,
@PersonFirstName,
@PersonLastName,
@PersonSuffix,
@PersonEmail,
convert(varbinary,@PersonPassword),
@PersonOrganizationID,
@PersonDepartment,
getdate(),
0,/* TODO need to get this from form */
@PersonTitle,
@PersonPhone,
@PersonFax,
@PersonInfoRequested,
@PersonHCTrained,
@PersonHCTrainedDate
)
set @PersonID = IDENT_CURRENT('Person')
/* look up the default address type */
select@AddressTypeID = AddressTypeID
fromAddressType
whereAddressTypeDisplayName = 'Work'
execpInsertPersonAddress
@AddressAddress1,
@AddressAddress2,
@AddressCity,
@AddressPostalCode,
@StateID,
@PersonID,
@AddressTypeID,
1/* this proc always inserts the default address */
/* - Schema change. Department now a varchar
execpInsertPersonDepartment
@PersonID,
@DepartmentID,
1/* this proc always inserts the default department */
*/
execpInsertPersonRole
@PersonID,
@RoleID
INSERT INTO tblHospitalsCoordinated
(
intPersonID
,intHospitalID
,dtmCreatedDate
,dtmModifiedDate
,strModifiedBy
)
VALUES
(
@PersonID
,@PersonOrganizationID
,GetDate()
,GetDate()
,''
)
select @PersonID
View 4 Replies
View Related
Apr 30, 2007
I am wandering how to "Properly do this" Without doing a dynamic SP. How do I do a search with the multiple listbox data. What do I pass the stored procedure?
SELECT ID, LAST_NAME || ', ' || FIRST_NAME AS FULLNAMEFROM BIT_USER1WHERE (TYPE_ID = 1) OR (TYPE_ID = 3) OR (TYPE_ID = 4) OR (TYPE_ID = 5) OR (BLDG_ID = 1) OR (BLDG_ID = 2)ORDER BY LAST_NAME, FIRST_NAME
View 4 Replies
View Related
Feb 20, 2008
I have a table of CategoryIDs and I want to increment through it passing each categoryID as a parameter to a stored procedure that returns the TOP 1 row, and build a new table from the the result. I do not know how to pass the categoryID into the stored procedure. I do not know how to begin building the new table.
public DataTable GetContractorCats() { // Generates table of all Contractor Category IDs no parameters SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings ["ConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("GetContractorCatIDs", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "ContractorCats"); return ds.Tables["ContractorCats"]; } catch { throw new ApplicationException("Data error"); } }protected void Top1EachCategory(){ // need to build a new table row by row using a stored procedure that finds the top result from a database query int RowIncrement; RowIncrement = 0; DataTable dt1 = GetContractorCats(); foreach (DataRow row in dt1.Rows) { I need to pass value of dt1 table ["CG_ID"] as a parameter to Stored Procedure called "GetTop1" Run the stored procedure which returns a single row if the CG_ID is found and use its field values to build each row in a new table. The stored procedure returns C_Name, Category_Name, C_Email, C_RCode and C_City
RowIncrement++; }
}
View 1 Replies
View Related
Sep 10, 2012
I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.
The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL
DROP TABLE #tmpCustomerAmount
CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,
[Code] ......
View 3 Replies
View Related
Mar 12, 2008
I'd like to loop through a database and get all the user table names and insert them into another table. What's the best way to do this without using a cursor?
This gives me the last table only.
declare @table_name char(50)
select @table_name = object_name(id) from sysindexes where indid =0
INSERT INTO holding.dbo.tbl_inputfiles
(IP_File, IP_Act, IP_Import)
Values (@table_name,'Y','ADV')
View 7 Replies
View Related
Aug 25, 2015
I was writing a query to get the age and the retirement year for all the employees.And thought of using while loop so that I don't have to write IF conditions or case statements for all the ages.
I'm using the AdventureWorks2012 database.And the actual table looks like this.
SELECT * FROM HumanResources.Employee
*NOTE:- These tables are not the complete tables.
BusinessEntityID JobTitle BirthDate MaritalStatus Gender
1 Chief Executive Officer 1963-03-02 S M
2 Vice President of Engineering 1965-09-01 S F
3 Engineering Manager 1968-12-13 M M
4 Senior Tool Designer 1969-01-23 S M
[Code] ...
And after I wrote the query to get the age and the retirement year of all the employees I got 70 tables for all the ages from 30 to 70. As the starting age is 30 and the last age is 70 in the table.So,I just want to know how I can settle all the tables into a single table as a sinle result and not as multiple results.
The query for age and retirement year....
DECLARE @Counter INT
DECLARE @Duration INT
DECLARE @Result DATE
SET @Counter=(SELECT MIN(DATEDIFF(YY,BirthDate,GETDATE()))FROM HumanResources.Employee)
SET @Duration=30
[Code] .....
And the result tables.
BusinessEntityID JobTitle BirthDate AGE MaritalStatus Gender Retirement Year69
Production Technician - WC60 1985-05-07 30 S M 2045-08-25 22:36:38.160115
Production Technician - WC50 1985-07-01 30 S F 2045-08-25 22:36:38.160133
Production Technician - WC40 1985-02-04 30 S M 2045-08-25 22:36:38.160144
[Code] ....
And it goes like this for 70 times. So just want to know how I can merge those 70 tables into a single table.
View 2 Replies
View Related
Nov 12, 2007
Hi. I want to use a FilterExpression in my sqldatasource that verify if a value is in a listbox with several values. How can I do that? Thanks
View 3 Replies
View Related
Feb 10, 2004
Sorry if this is to basic but I am just starting out. Any help is appreciated.
Basically I am attempting to populate a listbox with items from a MSSQL DB so the user can select either one or multiple items in that listbox to search on.
View 1 Replies
View Related
Feb 18, 2015
I am trying to loop through a table which has table metadata and create a:
- 'STORED PROCEDURE'
- This will SELECT all the data in the table and add a hashed column at the end
- Each table has a unique ID
-
Instead of a cursor would there be a set-based approach to achieving this?
METADATA TABLE :
IDTableNameColumnNameHashByteCalculation
111dbo.TableAColA CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColBCAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColCISNULL(ColC,'NA') + '|' +
111dbo.TableAColDISNULL(ColD,'NA') + '|' +
222dbo.TableBColAACAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' +
222dbo.TableBColBBISNULL(ColBB,'NA') + '|' +
222dbo.TableBColCCISNULL(ColCC,'NA') + '|' +
From the above data I want to generate:
SELECT
ColA
,ColB
,ColC
,ColD
, (CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
[Code] ....
View 2 Replies
View Related
Aug 21, 2007
hello forum friends,
i need to display the database table in ASP
page.how it is possible,can anyone explain me
with code.
Regards
Prathap
View 1 Replies
View Related
May 17, 2008
Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?
View 4 Replies
View Related
Sep 20, 2013
I want to make a SP to update table Product with information I get from table Orderdetail.
Create Procedure UpdateVoorraad
§OrderId (int)
As
Select ProductId, Tal From Orderdetail where OrderId = @OrderId
-- this query get info from table orderdetail : ProductId (integer) and Tal (smallint)
-- Tal = Number of Products
-- Here I want to loop through the query above
-- and for each record in the query I want to update
-- table Product.
Update Product Set Product.Voorraad = Product.Voorraad - Tal where ProductId = ProductId
To do this must I make a create a tempory table, store the query result in the table loop through the table and update table product, or can I try to create a function without a temporary table.
View 3 Replies
View Related
Feb 6, 2015
I wanted to insert values in columns as explained in below ex.
I am having a table that contains Column1,Column2,Column3,......,Column10.
Inside my for loop, i am getting Column1 value then Column2 then Column3 values and so on till Column10.
My requirement is that on each iteration,I wanted to insert value of Column1 in field Column1, value of Column2 in field Column2 and so on.
View 3 Replies
View Related
Feb 19, 2015
I want to, for each month of the year 2014 say, to create a loop that will enter data into a table.
Right now I have:
Select [Member Number],
sum(case when [Receipt Date]='2014/01/01' then Amount else 0 end) as [Rec 2014/01/01]
From [Receipts Table]
Group by [Member Number]
Insert into [Receipts 2014/01/01]
[Code] ....
Instead I would just like to do something like…
Declare i date
For i=2014/01/01 to 2014/12/01
Select [Member Number],
sum(case when [Receipt Date]=i then Amount else 0 end) as [Rec +i]
From [Receipts Table]
Group by [Member Number]
Insert into [Receipts + i]
Don’t know if this is at all possible?
View 2 Replies
View Related
Sep 21, 2005
Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.
View 4 Replies
View Related
Jul 20, 2005
Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.
View 1 Replies
View Related
May 1, 2007
I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this?
View 9 Replies
View Related
Nov 15, 2015
I have two tables i have to update table2 using table1 without using while loop.
example given below.
Table1
rid
id
amt
firdate
lastdate
1
1
500
[code]....
View 7 Replies
View Related
Jul 31, 2007
Hello,
Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.
View 5 Replies
View Related
Jun 20, 2007
Hi everyone, having some problems
Basically, using ASP.NET 2.0 and here is my problem,
Get data from table
Put into array
Split where there is a +
remove +'s
assign to listbox to give a list of everything in that table
The + split the courses, so in my Order table I have A + B + C etc and I want all of the different options in a list box (note different records have different entries it isn't always a b c)
I am testing my code, here is what I got
Public Sub TitleChange(ByVal Sender As Object, ByVal E As EventArgs) Try Dim DBConn As SqlConnection Dim DSPageData As DataSet = New DataSet() Dim VarTxtAOE As String DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=XXX") 'Dim DBDataAdapter As SqlDataAdapter DBDataAdapter = New SqlDataAdapter("Select AOE FROM TBL_Role WHERE Title = @ddlTitle", DBConn) DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar) DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue DBDataAdapter.Fill(DSPageData, "Courses") 'Need to find out what this rows business is about whats the number about? and am I doing it correct? 'txtAOE.Text = DSPageData.Tables("Courses").Rows(0).Item("AOE") 'txtAOE.Items.Add(New ListItem(DSPageData.Tables(0).Rows(0).Item("AOE"))) VarTxtAOE = DSPageData.Tables("Courses").Rows(0).Item("AOE") Dim VarArray() As String = VarTxtAOE.Split("+") Response.Write(VarArray()) txtAOE.DataSource = VarArray() txtAOE.DataBind() 'Response.Write(test) 'ListBox1.DataSource = test 'Response.Write(VarArray) Catch TheException As Exception lblerror.Text = "Error occurred: " & TheException.ToString() End Try End Sub My response.Write works correctly, but my list box doesn't, also I don't want to say which bit of the array like I have done using 1, I just want to display the whole array in my list box. I am not worrying about removing the +'s at the minute, just splitting my data and putting each section into a listbox
Maybe I am going about this the wrong way, but I have been trying a lot of different things and its hard to find any help
Thanks
Chris
View 9 Replies
View Related
Aug 30, 2005
I need to use the list of items from a multiselect listbox in a parameter of a query's IN clause.
Example:
I assemble the list from the listbox and set the paramter value so @CityList = 'London','Paris','Rome' .... etc.
A sample SQL would be:
SELECT * FROM Customers
WHERE City IN (@CityList)
However, the SQL will not work. It seems the whole string is put in
another set of single quotes by the compiler so it's treated as one
string literal instead of a list.
Is there a way around this?
View 4 Replies
View Related
