hello,
i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)
should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.
nicholas.gadacz
Hey all!
Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?
Any thoughts?
In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?
Thanks for any suggestions!
Jim Work
I have a field which was varchar before but I changed it to text.
But i can't write in it enough text as i wish. This field is important becouse it holds SQL senences which i parse latter in my application and than i execute it.
it says <Long Text> and i can't enter any more characters.
Ok, to start I will say I am a novice so detailed solutions are much appreciated. I believe I am on the right track. Here is what I am trying to accomplish. I have a page that uses an xml driven google map. When you click on the map marker, the info bubble displays brief information about a community and a link specified from the xml file. I have specified the links in the xml file to pass a query string variable ex: /community.aspx?name=uniqueCommunityNameHere. I tested this by simply placing a label on the community.aspx page that restated the "name" value - works fine (first commented out line of Page_Load). Now I need to complete my VB function that pulls the data record from the database - based on the parameter input from the query string - when the page loads. So if the user clicks the link on the marker for community1, it will populate community.aspx with info about community1 from the database... if they click community7 it will display that community info etc.
Data Table: "Community" Data Fields: ID, Name, Description, Address, City, State, Phone, SalesRep.
ASPX Page Design Elements: lblID, lblName, lblDesc, lblAddress.... etc.
Here is my start... I will need help with the sql statement for sure - how do I create a where clause from the query string parameter passed in?
Thanks so much for your help!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load'My initial test to see that the query string is working lblName.Text = Request("name").ToString()
' Get the community info when loading community.aspx for the user selected communityIf Not IsPostBack ThenLoadCommunityInfo(Request("name").ToString())End IfEnd SubPrivate Sub LoadCommunityInfo(ByVal community)' Define data objectsDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReader
' Read the connection string from Web.configDim connectionString As String = ConfigurationManager.ConnectionStrings("CONHTConnectionString").ConnectionString' Initialize connectionconn = New SqlConnection(connectionString)' Create commandcomm = New SqlCommand("SELECT * FROM Community WHERE Name = " & community, conn)' Enclose database code in Try-Catch-FinallyTry' Open the connectionconn.Open()' Execute the commandreader = comm.ExecuteReader()' Populate the list of community information -> Make the labels.text = the database fields --> lblName.text = table-Community field-"Name"lblName.Text = "Name"lblDesc.Text = "Description"....' Close the readerreader.Close()Catch' Display error messagedbErrorLabel.Text = "Error loading the community information!<br />"Finally' Close the connectionconn.Close()End TryEnd Sub
How to do some procedural updates based on a record string. What I wish to do.
If the value (of type string) in my column contains the ‘/’ character I wish to ‘do something’
If the value (of my string) in my column contains the ‘-‘ character I wish to ‘do something else’.
Assume my column contains both ‘/’ and ‘-‘ in the same table.column, but not within the same record.
The data in question is in
tbl_Quotes.tLastDateValue
I’ve found I can use something like this to search my string.
SELECT
Count(CHARINDEX('/',tbl_Quotes.tLastDateValue))
FROM tbl_Quotes
WHERE
CHARINDEX('/',tbl_Quotes.tLastDateValue)>0
And similar for ‘-‘ records.
I’m thinking something like this could be used as the test.
How do I put this into play, I’m thinking of an IF or CASE but not sure how to best test and which program flow to use in SQL.
Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)
DECLARE CursorSelect CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
[code]....
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
Hi,
I have three tables -
Table A
Col1...Col2
1.....A
2....B
Table B
Col3...Col4
1.....X
2....Y
TableC
Col1...Col3....Col5...Coln
1........1...........H....Y.
A&B serve as the parent table for C. A&B are the dimension tables and C is the fact table. How do I assign the PK and FK in EM?
Thanks
Consider the following:
Sub regUser(s as Object, e as EventArgs)
If IsValid Then
Dim stuTable as String
Dim connStr as String
stuTable = "mytable"
connStr = "myConnectionInfo"
'check to see that student num is in the db
Dim connect as SqlConnection
Dim strSelect as String
Dim cmdSelect as SqlCommand
Dim strDbResult as SqlDataReader
connect = New SqlConnection(connStr)
strSelect = "SELECT stu_num, stu_fname, stu_lname FROM " + stuTable + " WHERE stu_num=@stuNum"
cmdSelect = New SqlCommand(strSelect,connect)
cmdSelect.Parameters.Add("@stuNum", txtStdNum.text)
connect.Open()
strDbResult = cmdSelect.ExecuteReader()
Dim stuFName as String
Dim stuLName as String
Dim stuEmail as String
Dim strRandom as String
Dim strPassword as String
Dim i as Integer
Dim charIndex as String
Dim stuMailMessage as MailMessage
Dim strHTMLBody as String
'declare stuFname, stuLname, stuEmail
stuFName = strDbResult("stu_fname")
stuLName = strDbResult("stu_lname")
stuEmail = txtStdEmail.text
'more code follows ....
In my DB I have a table with the columns stu_num, stu_fname and stu_lname. Each of these columns contains rows with data. However, the proceeding code gives me this error: Invalid attempt to read when no data is present (line 58 --> stuFName = strDbResult("stu_fname") ).
What am I doing wrong here? How do I assign the stu_fname in the DB to the page level variable stuFName?
As a little aside how do I say "If no record was found matching stuNum Then" ... ??
Sorry, I'm a .NET beginner ...
Hi,
Is it possible to assign the column value to a user defined variable?
Hi ,
I have a typical scenario here where I am trying to assign a variable a value using another variable, for that I am using two execute SQL task , the fisrt one assigns the first variable a value and then the second one uses the first variable to evaluate and then assigns a value to another variable. The first execute sql task works fine , however the second one fails with the following error
"failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "
Here is the query I am using to assign the value in my second execute sql task
select (CASE
WHEN Sum(col1) <> 0 AND ? = 'M' THEN 'M'
WHEN Sum(col2) <> 0 AND ? >= 'T' THEN 'T'
WHEN Sum(col3) <> 0 AND ? >= 'R' THEN 'R'
ELSE 'S'
END) as Current
FROM dbo.TABLE1
I am passing the variable by mapping the parameters and then assigning the value using a single row result set.
Can someone please tell me where am I going wrong?
Thanks
Hi,
I have a sqldatasource which returns the result I want, but I need to assign it to a label or text box. Is there an easy way of doing this? I attempted it using this code:
PropertyFriendIDLabel.Text = PropUserIdSqlDataSource
Thanks
Hello,
I am using SessionParameters within a sqldatasource control, which is the datasource for a formview control.
The Session["PoolID"] has a value which is '0000009485'.<InsertParameters><asp:SessionParameter SessionField="PoolID" Name="pool_id" Size="10" Type="String" ConvertEmptyStringToNull="true" />
</InsertParameters>But when I click 'Save' I get the message 'Cannot insert the value null into column 'pool_id'. Column does not allow nulls. Insert fails'.Do i need to specify something like DefaultValue = <%Session["PoolID"] %>?Thank you in advance for your help.RajanP.S. How can I avoid double line spacing while writing a post? Thanks.
hi all
can we assign two foreign key to one table .
I’ve reconfigured Microsoft’s IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
Dim strSQL as String = "Select orderID, modelNumber from orderDetails" & _
"where CustomerID = " & User.Identity.Name & _
"And orderid = (SELECT MAX(orderid)FROM orderDetails" & _
"where CustomerID = " & User.Identity.Name & ")"
What I would like to do is assign specific values to variables based off of the above query. For example:
Dim orderItem as String = (all of the modelNumbers from the query)
Dim orderIdItem as String = (all of the orderIDs from the query)
How do I do this?? Any help is much appreciated! Thanks in advance.
Ron
Hi,
I want to give 'Execute' permission for all the sp to a particular user.
Any straight way of doing this then to go to each sp and give him permission for every sp.
TIA.
i am trying on this but hit error
Declare @var int
Set @var = select max(value) from xxx
anyone can help me?
Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it
When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.
If you need more clarification, post here and let me know.
Thanks in Advance,
Aaron Hawn (aaron@ionzion.net)
Hello everyone. I wanted to learn to assign and change a schema names used in my table. I dont want a dbo. in my tables. I want to give a personal name like the adventureworks database has. (Such as sales.salesdetails, humanresources.employee and all)
> So how i do create a table with a schema name sales
and
> How do i change my for example dbo.employee to sales.employee ??
I tried
Create table sales.employee
{
statements
}
but it doesnt work...
Please reply ASAP
Thanks
into the world of programming now :)
Hi
I am using SQL SERVER 2000. Until now my application used the default user "sa", but now the illigal access to my database make me move to a more secure login.
i am new to this concept.
i need to create a login, which i am successful in creating, but my problem is
I need to allow only this user to access my database and no other user should login my database.
please can any one explain how to do this.
its very urgent.
regards
James Alvin
Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.
So, I declare a variable:
DECLARE @variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.
I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.
Thanks in advance for any help
I have the following problem:
in a data flow, if inserting new records, there are columns that take some default values. These default values are kept in a table in case the user wants to change them some day. Def. values could not be assigned at a table level because there's another dataflow that populates the same table, but the rules for the default values are different.
Since I want to extract these values only if there is at least one new row, I'm not fond of the idea to use Execute SQL Task (to save the default values in a variable) before the actual Data Flow. What are my options in getting these values in a Data Flow right before inserting? Thank you for the help.
Hi,
I would like to assign the value of a variable to a column name of a table. For eg:
declare @sam varchar(100)
set @sam = 'Insert'
create table #temp(Sample varchar(100) not null)
insert into #temp(Sample) Values(@sam)
drop table #temp
I would like to generate a table whose column name should be generated dynamically. The value of @sam that is "Insert "should be set to the column name. Is there a way to assign like this. Can anyone please help me in achieving this.
Thanks,
Sana.
Hi,
1 20031012121212 200 (recordtype, CreationDateTime(YYYYMMDDHHMISS), Rec_Count) -- Header records
2 ABCD, XYZ, 9999, 999999999, 1234 ---- Detailed Record
2 ABCD, XYZ, 9999, 999999999, 1234 ---- Detailed Record
For the above given sample data I am having two outputs at Condition Split (based on the recordtype). I want to store the 1st record datetime value into a variable and then I want to use that variable value into 2nd and 3rd row.
Basically, detailed records would be stored into the database not the header record. Is there any way I can use the variable while doing processing for 2nd and 3rd records.
Please suggest me.
Regards,
Hi,
I have a parent SSIS package that executes various subpackages. Each of the subpackages contain variables that are required for their successful execution, e.g. one has a variable of datetime datatype and a variable of varchar datatype.
This date will essentially change with every running of the package as it specifies the date that additional data has been added to the back-end SQL Server 2005 database.
I can't find anything in the expressions of the Execute Package Task that would allow me to pass these variables into the package.
Can anyone advise?
Thanks,
Paul
i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus. I am wanting to pass the Membership.GetUser.ProviderUserKey.ToString() to the asp:parameter but I cant get it to work.So How do I pass a variable to a stored proc parameter using a bound data grid.I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.please helpjim
View 2 Replies View RelatedI'm trying to assign a datasource to a gmap control in 2.0 so that i can get pushpins in the map for all the locations in the sql db. I've enclosed my code below as well as a link to the site that provides the control and their sample. Can anyone tell me why it won't show the pins? What am I doing wrong?
Thanks.
My Code (sqllocations is a datasource):
GMap1.DataSource = sqllocations;
GMap1.DataBind();
Their site: http://en.googlemaps.subgurim.net/ejemplos/ejemplo_991000_DataSource.aspx
Their example (part of their explanation is not english, sorry):
List<DataSourceField> fields = new List<DataSourceField>(); fields.Add(new DataSourceField(45, 2, "hola"));fields.Add(new DataSourceField(46, 3));// Lo comentamos porque al utilizar la clase DataSourceField, // pero si fuera un dataset o cualquier otra cosa, // le deberíamos dar los nombres correspondientes// GMap1.DataLatField = "lat";// GMap1.DataLngField = "lng";// GMap1.DataGInfoWindowTextField = "gInfoWindowText";GMap1.DataSource = fields;GMap1.DataBind();
In the following stored procedure I would like to test whether @TopCategoryID is null. If so I would like to insert the @CategoryID value into the @TopCategoryID value. So, once @CategoryID recieves the @@IDENTITY value, how do I enter this same value into @TopCategoryID as well???
CREATE PROCEDURE sp_CT_InsertCategory
@Namevarchar(50),
@Description varchar(250),
@topCategory bit,
@ParentCategoryID int,
@TopCategoryID int,
@CategoryID int OUTPUT
AS
DECLARE @CurrID int
IF @CurrID IS NULL
BEGIN
INSERT
INTO CT_Category
(CategoryName, CategoryDescription, topcategory, parentcategoryid, topcategoryid)
VALUES
(@Name, @Description, @topcategory, @ParentCategoryID, @TopCategoryID)
SET @CategoryID = @@IDENTITY
IF @@ERROR > 0
BEGIN
RAISERROR ('Insert of Category failed', 16, 1)
RETURN 99
END
END
ELSE
BEGIN
SET @CategoryID = -1
END
GO
Thanks in advance
Hello all,
for a project I am trying to implement PayPal processing for orders
public void CheckOut(Object s, EventArgs e) { String cartBusiness = "0413086@chester.ac.uk"; String cartProduct; int cartQuantity = 1; Decimal cartCost; int itemNumber = 1; SqlConnection objConn3; SqlCommand objCmd3; SqlDataReader objRdr3; objConn3 = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); objCmd3 = new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3); objConn3.Open(); objRdr3 = objCmd3.ExecuteReader(); cartProduct = "Cheese"; cartCost = 1; objRdr3.Close(); objConn3.Close(); cartBusiness = Server.UrlEncode(cartBusiness); String strPayPal = "https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness; strPayPal += "&item_name_" + itemNumber + "=" + cartProduct; strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity; strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost); Response.Redirect(strPayPal);
Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.
So I want cartProduct = Title and cartCost = Price from the SQL query.
How do I do this?
Thanks
We have changed NT Administration Password. Now how to reassign the new password setting for sql server service account. As right now all schedule jobs are getting failed & needs to be executed manually.
Thanks in Advance
Manoj
Group,Is there a way to assign nullability on a column when using a select into?I've tried some of the usual things like coalsce, isnull, and cast. Sincethe new table gets definition from the source table or can be somewhatadjusted with cast is there a way to cast a not null? In the example belowhow can I select into causing tableone_new..col2 to be not null. Wetypically must use an alter statement after the select into but this seemsinefficient.Thanks,Raycreate table tableone (col1 int not null,col2 int )goinsert tableone values (1, 1)insert tableone values (2, 2)insert tableone values (3, 3)goselectcol1,col2into tableone_newfrom tableonegoexec sp_help tableone_newgodrop table tableonegodrop table tableone_newgo
View 3 Replies View RelatedHiI am creating new SQL Server Tables using SQL Server 2005. I have setprimary key to the tables .But I do not know how to assign Foreign keyto the tables .I need to do some joins later and that is why I have toput Foreign key to the table . The Primary key is visible and can beassigned easily .But How do I assign foreign key .Thanks*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View Related