cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows
for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}
How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
command.CommandText = “SELECT UserName from Users WHERE UserID = “ = userID
Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can’t get it out. Should I be using a dataSet for this operation?
The rest of the code so far:
SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add("Table", "Users");
dataSet = new DataSet(); dataAdapter.Fill(dataSet);
In my BLL I have a method that adds a new row to a table in the database... [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public bool AddContact(string firstname, string lastname, string middleinit, bool active, Guid uid, bool newsletter) { ContactsDAL.tblContactsDataTable contacts = new ContactsDAL.tblContactsDataTable(); ContactsDAL.tblContactsRow contact = contacts.NewtblContactsRow(); contact.FirstName = firstname; contact.LastName = lastname; contact.MiddleName = middleinit; contact.Active = active; contact.UserID = uid; contact.Newletter = newsletter; contacts.AddtblContactsRow(contact); int rowsAffected = Adapter.Update(contact); return rowsAffected == 1; } The primary key in this table is a BigInt set as an identity column....How do I capture the value of the primary key that gets created when the new row is added?
Hi,I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error.....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).Can anyone see anything obvious.Cheers. >>Calling routine:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadSystem.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")hsc = CType(Activator.GetObject(GetType(IHelpSC), _"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)Dim drEdit As DataRowDim intRow As Integer = CInt(Request.QueryString("item"))strDiscipline = Request.QueryString("discipline")drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote methodstrRecord = drEdit.Item(0)txtLogged.Text = drEdit(1)txtEngineer.Text = drEdit.Item(3)End SubRemote Class Function:Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRowstrDiscipline = Trim(discipline)Dim cmdSelect As SqlCommand = sqlcnn.CreateCommandDim drResult As DataRowDim strQuery As String = "SELECT * FROM " & strDiscipline & _" WHERE CallID=" & idcmdSelect.CommandType = CommandType.TextcmdSelect.CommandText = strQuerysqlda = New SqlDataAdaptersqlda.SelectCommand = cmdSelectds = New DataSetsqlda.Fill(ds, "Results")drResult = ds.Tables(0).Rows(0)Return drResultEnd Function
I have in SQL Table1 the following values in Field1:
ABC ABc AbC abc aBc abC Select Field1 From Table1 Where Field1 = "AbC"
I get all six records, not just the one record (AbC) that I want to retrieve. How do I tell the select statement to be case sensitive? my asp.net statement is given below DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'")[0]; i m using datarowso i need a helpi have quary but i don;t know how can use DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'where CONVERT(binary(5),Userid)=CONVERT(binary(5),'" + txtUserId.Text + "')'" )[0]; this give me error "Where condiction need operator"waiting for reply
Hi. I am new to ADO.NET and I can't seem to figure out how to populate a row that is a layer lower than the instantiated row. I am populating and XML file called Users.xml:
and, i am using the below code to access and populate the rows based on the users registration input:
Code:
Dim NewLogin As Data.DataRow = LoginDS.Tables(0).NewRow
I am able to access all the rows that are one layer into the xml file with the following code:
Code:
NewLogin("someNode") = _someNode.Text
but, how do i populate nodes that are "further down" such as "birthDay". Do i have to reinstantiate NewLogin as ...Tables(1).NewRow? I have tried various forms of this and it doesnt work. Suggestions appreciated... thanks.
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table - call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable and update it the row is in the database but in vb the datarow primary key is not filled in! anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
I will attempt to explain my situation more clearly.
I need to get data from a data source using a DataFlow Task (which pushes the DataSet into a Variable) and process the data row by row in a ForNext (ADO Enumerated) Container. I don't want to map the columns into variables because the number of columns and the data types vary considerably and I want to make my package as "generic" as possible.
Is there a way, in Script, to read the current row of the ForEach Container from into an ADO DataRow so that thie names and values of each column can be accessed?
I know how to read the entire DataSet object from a Variable into an ADO DataSet and iterate through the rows in the normal way but this doesn't suit my purpose. It would be really useful if there was a way to do somehing similar with the current DataRow in the ForEach Container.
To explain what I am doing, the idea is to use the Column Names and Values for each row to construct an xml fragement, store it in a string Variable and (in the next step) use the Web Services Task to call a Web Method with the xml fragment (from the Variable) as one of the inputs.
A less attarctive alernative would be to use a Scipt outside a ForEach Container and loop through the rows of teh DataTable as descibed above and perhaps call the Web Service Task from teh Script. The proble is that I don't know how to do this either and it woudl be much "neater" anyway to use the ForEach Container.
Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...?? ===================================== DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW' DECLARE @SEP AS NVARCHAR(5)SET @SEP = ',' WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN -- do the rest END
I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks!
Hi All, I have this code below:Dim a As Integer = 0 While a <= myArray.Length() Conn.Open() Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @hp", Conn) UpdateCmd.Parameters.Add("@hp", SqlDbType.VarChar, 50).Value = myArray(a) UpdateCmd.ExecuteNonQuery() a = a + 1 Conn.Close() End While BUt, When I run it, I receive error "Prepared statement '(@hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @hp, which was not supplied. " DOes anyone have the solution??? Thanks....
Hello, how can I use an array in a WHERE clause? If I had an array saystring[] NamesArray = new string[] {"Tom", "***", "Harry"} How would I do this:string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray" Thanks in advance,Louis
I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub
How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.
I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks
For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views?
select enc_id,labcnt,order_name,date_due
reate FUNCTION fn_GET_ARRAY_VALUE( @DELIMITER VARCHAR(100), @STRING VARCHAR(1000), @ARRAY_POSITION INT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @CURRENT_POSITION INT DECLARE @VALUE VARCHAR(8000) SET @CURRENT_POSITION = 0
WHILE @CURRENT_POSITION<@ARRAY_POSITION AND CHARINDEX(@DELIMITER,@STRING,0)>0 BEGIN SET @STRING = SUBSTRING(@STRING, CHARINDEX(@DELIMITER, @STRING, 0) +LEN(@DELIMITER), LEN(@STRING) -CHARINDEX(@DELIMITER, @STRING, 0) + LEN(@DELIMITER) ) SET @CURRENT_POSITION = @CURRENT_POSITION + 1 END
IF CHARINDEX(@DELIMITER,@STRING,0)=0 SET @VALUE = @STRING ELSE SET @VALUE = SUBSTRING(@STRING, 0, CHARINDEX(@DELIMITER, @STRING, 0) )
My Issue is the next thing: I have a table filled with contracts I have Stoc that checks if contract has expired If I start the Stoc I must give it the contractnumber with it like: SP_expired 8
This works fine but now I want that the Stoc checks all the contracts by just starting the Stoc.
I don't really know how to do this, I have thinking about using an array. But don't have an idea where to start....
Please excuse me if this is the wrong forum. I hope your combined genius will be able to point me in the right direction.
I have an ASP page that accepts any SQL query. I want to create a table based on the result set so the number of columns can grow/shrink. But I don't know how. Can anyone help me?
My ASP Code shows how I create a table with only 8 fields.
<% Dim objConn dim SQLstmt Dim objRS Dim SQLqry Dim UID Dim RecordArray Dim I
hi guys im having some problems with arrays as parameter what i want to do is pass a array of comma separated values i.e 12,24,36 and use the in(@parameter) but it doesnt work
i've found some solutions, like ALTER PROCEDURE [dbo].[GetLawyers] ( @LawyerTypeIDs VARCHAR(100) AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS] WHERE [PARCELAS] IN (' + @LawyerTypeIDs + ')' EXECUTE (@SQL)
but in my case doesnt work.
here is a part of my code SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cod_banco ORDER BY coeficiente asc) AS RecID FROM tb_valor_bancos where (1 = CASE WHEN @TABELA IS NULL THEN 1 ELSE CASE WHEN TABELA = @TABELA THEN 1 ELSE 0 END END ) AND (1 = CASE WHEN @PARCELAS IS NULL THEN 1 ELSE CASE WHEN PARCELAS in(@PARCELAS) THEN 1 ELSE 0 END END ) ...
i know there is a way to pass the array into a table but it doesnt work too
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
POHDR;JAKARTA;St.1DTL;1. ;00248337;8996006855701 ;083;041;002; 7,812.50; 312,500.00; 284,091.00; 8,875.00 first row is the PO header and the second is the detail. This data was collected using Export Import Wizard from flatfile and put it in one column. I want to split the header and the detail into different table and map each column separated by ; to a proper field. For this work I should check every column separated by ; with CHARINDEX function but I have to write the TSQL as much as the count of ;. It would be better if we can use array to simplify the code.
Can we use array in Transact SQL ? since I have no clue referring to SQLBOL. Thanks in advance.
Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?
Hi, I want to populate the array with a single column values from database(sqlserver 2000)Ex. name ageaaaa 23bbbb 43cccc 18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala
Using the SqlDataReader, I am retrieveing records from a SQL Server 2005 DB table. Since I am using the SqlDataReader to retrieve the records from the DB, I have to use the Read method of the SqlDataReader like this:Dim sqlReader As SqlDataReaderWhile(sqlReader.Read) Response.Write(sqlReader.GetValue(0) & "<br>") Response.Write(sqlReader.GetValue(1) & "<br>") Response.Write(sqlReader.GetValue(2) & "<br>")End WhileThe records retrieved can only be accessed inside the While loop. I want to access the records outside the While loop as well. Is there anyway by which I can do this, maybe by populating the recordset in an array variable & then using it outside the While loop?
