I having a strange problem, my code is as below:
cmd.connection = cnn
cmd.commandtext = "select count(*) from member"
dim i as integer
i = cInt(cmd.executescalar)
However, what the result tat i get is "&H0" ! When I use the same query in sqlquery, it did show out the result as "11".
I have no idea abt wat is goin on, can anyone gv me some guide ? Thanks.
Hi
View 7 Replies View RelatedHi,
I have a question. I have a standard query that retrieves the list of users from a database. The query uses "exist" statement and inside it I have another smaller query. The smaller query which used withing exist statement is called numerous times during the query execution. I am wondering if I can speed things up by storing result of a smaller query in a table rather than asking DB to run it again and again. The query takes over a minute to run and I am trying to find a way of speeding it up.
Thanks a lot
Serge
This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .
alter proc r (@id INT)
as
BEGIN
DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @in VARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)
SET @input = 'AWCA,GCS,IHP,Aetna'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input
select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in
select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end
Print @list
EXECUTE ('SELECT Label FROM repricingsystemtype WHERE Label Not IN (' + @list + ')')
END
Hi,
I am transferring data from msaccess to sql2005 using foreach loop. I have a table called Students and a column in it called State.
I want to store the value of the State in a variable in the foreach loop. I am using Dataflowtask to transfer rows from access to sql. what component can i use inside dft to store the value of State in a variable.
Plz its urgent
thanku.
Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.
View 2 Replies View RelatedHere is my objective:
As part of auditing of our application, I want to write the values of all column in one row out to an audit table both before and after an update to see what the changes were.
I know I can SELECT * FROM tableName FOR XML AUTO to generate a single XML 'column', but I cannot figure out how to capture that output and store it.
According to MS, you cannot use FOR XML in a subselect, or fill a TEXT variable with the results of such (which, IMHO really diminishes the usefulness).
I want to stream out the results of one entire row and store that in ONE column of an audit table. XML seems like the easiest way if not for this limitation.
A better way?
Thanks.
I have image type col.I'm trying to do the following,DECLARE @Data varbinary(16)SET @Data = (select imageCol from Table1 where id=3)As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,========================================Server: Msg 279, Level 16, State 3, Line 2The text, ntext, and image data types are invalid in this subquery or aggregate expression.========================================Is there anyway to store image datatype value to a variable?Cheers.
View 2 Replies View RelatedI want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?
example:
@variable varchar(1000)
SELECT *
FROM table
WHERE @variable
(The @variable would reflect the where clause string)
Thanks!
Hi there, I should prefix this message by saying, I am brand new to SQL so please be gentle
What I am doing seems pretty standard and I'd like some advice on the best way to proceed.
I am simply trying to add some customer information to my database. For this I have set up two tables. One for the Customer name, title, etc and then a related table for the address which is referenced in the previous table by a AddressID.
In the address table the AddressID uses an Identity. So what I'd like to do (which may not be the best way, if so tell me) is write a stored procedure to accept all of the customer information then INSERT a new entry into the Addresses table, then use DBCC to retrieve the current IDENTITY value and then add the remaining customer info into the Customers table using the IDENTITY value retrieved for the AddressID.
Now is that a total bass-ackwards way of doing this?
Thanks for the help!
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
Help - I am having a moment!
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
r/ Anthony
Hi,
Does anyone know a way to store the ConnectionString of an OLEDB connection in a variable? I want to see exactly what values are in there because the step is failing with an error at that point. Here is what I'm looking for:
Data Source=;Initial Catalog=;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Thanks,
Phil
Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)
declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output
How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable. I need to check whether count > 0 or not in stored procedure.I have used below query.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
I have a few flat files that will be retrieved from some SFTP server. One of the flat file will act as a terminal file where it will specify the total number of records expected in each other the flat file.
Data in the terminal.txt
FileName TotalRecords
File1 1000
File2 1500
File3 2000
So, before transforming the data from the flat file sources into the target destination, i wish to do a row count checking for each of the flat file source to make sure that the number of records in the flat file source is tally with the number of records specify in the terminal.txt file. I'm able to get the number of records in each of the flat file by using the RowCount component but don't know how to get the data out from the terminal.txt file in order to make a rowcount comparison.
Can any1 help me on this? Or is there any other way we can do to make sure that the flat file source is alright before proceeding with the data transformation task?
Thanks!
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
View 7 Replies View RelatedI have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id
This doesn't work as expected as @xml doesn't get any value assigned or rather I would say
EXEC @xml = StoredProc1 @Id
outputs the entire xml whereas it should just save the xml in a variable.
I have one package through for each loop container I am loading all flat files into target table and its working fine.Now I my requirement is to capture the .txt file and loading into sqlserver table.Please check below file name.
CCCLOC_DDDLOC_LOC_20151203_240000_trigger.txt
from above file name I have to derived below detail.I have variable @File_Name which is used to stored the file name at rune time.Now I want to derived 5 values from the file name as below
Clientname=CCCLOC
Partnername=DDDLOC
Entity=LOC
Creationdate=20151203
Creationtime=240000
how to write expression for above situation.
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
View 2 Replies View Relatedprivate void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;
SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}
i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.
and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
what is the problem?
Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString)
Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn)
With Cmd.Parameters
.Add(New MySqlParameter("?CurrentPage",thisPage))
End With
Conn.Open()
Exists = Cmd.ExecuteScalar()
End Using
End Using Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%index.aspx%') AND Enabled=1 This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself. Does anybody have any ideas?
The following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.
Any ideas on what I might be doing wrong... ?
Howdie y'all!
I'm trying to do an executescalar() on the next stored procudure...
SELECT COUNT(*) FROM tblUsers WHERE UserEmail = @UserEmail;
Strangely enough I get SqlServer exception that tells me there's a syntax error.
Is there something I'm overseeing?
Cheers,
Wes
Hi all
I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.
'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:
...
<asp:TemplateField HeaderText="Client" SortExpression="c_id">
<ItemTemplate>
<asp:Label ID="lblClient" runat="server" Text='<%#GetClient(Eval("c_id")) %>' />
</ItemTemplate>
</asp:TemplateField>
...
This then calls: GetClientName - Which is as follows.
...
Public Function GetClientName(ByVal ClientID)
Dim ScalarValue As String = ""
Dim myConnection As New SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")
Dim myCommand As New SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @ClientID", myConnection)
myCommand.Parameters.Add("@ClientID", SqlDbType.Int)
myCommand.Parameters("@ClientID").Value = ClientID
Try
myConnection.Open()
ScalarValue = myCommand.ExecuteScalar
Catch ex As Exception
Console.Write(ex.Message)
End Try
If ScalarValue > "" Then
Return ScalarValue.ToString
Else
Return "<span style='color: #CCCCCC'>- NULL -</span>"
End If
End Function
...
This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.
Any help in this matter would be great and really appreciated. Thanks.
David
Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?
View 1 Replies View Relatedi have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)
my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"
what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!
Hey folks, I was just learning how to work with ScopeIdentity and I found out I needed somthing called executescalar. Things seem to be working really well, and I think I did it write, except I'm having one strange little oddity. Everytime my page writes to my databases, it writes rows. I'm really curious to know what I did wrong. Any ideas? Thank you as always :-)
Public Sub newoptic(ByVal sender As Object, ByVal e As System.EventArgs) Handles newpostBTN.Click
If newpostTXTBX.Text = "" Then
Exit Sub
End If
Dim mySQL As String = "insert into msg_topics (topic_title, topic_forum_id) values (@topicTITLE, @forumID); Select Scope_Identity()"Dim myConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("DBconnect"))
Dim cmd As New SqlCommand(mySQL, myConn)cmd.Parameters.AddWithValue("@topicTITLE", newposttitleTXTBX.Text)
cmd.Parameters.AddWithValue("@forumID", HDN_topic_forum_ID_LBL.Text)
myConn.Open()Dim topic_ID_holder As Integer = cmd.ExecuteScalar()
HDN_topic_id_holder_LBL.Text = (topic_ID_holder)
cmd.ExecuteNonQuery()
myConn.Close()
End Sub
Oh, one more quick question if whoever responds knows the answer. Why do I need a semi-colon here "@forumID); Select" ? The websites I learned about this from didn't explain that, and I've never used a semicolon in my select statements before, so I figured there must be something special about it.
Thank you
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
View 2 Replies View Related