Hi I want to update the grid which in turn will load data to a few tables: the main table and it's lookup tables, I want to write a stored procedure that will check if the data from lookup table already exists and if not it will insert it
but I am only starting to get to know stored procedure structure
could you give me some advice on how to write such procedure I would be veeeery gratefull
I have gridview bound with SqlDataSource control. One of the grid columns is a dropdown list which is populated programatically, (the gridview has template column with edittemplate field with dropdown list), dropdown Value contains of course collection of IDs.Please tell me how to declare UpdateCommand which will update the row with the dropdown list. The problem is with parameter regarding the column with dropdown list. When i declare this parameter and bind it to SelectedValue property of the dropdownlist from edittemplate tag - it doesnt work, because datasource control cannot see this dropdown list. So, how am i supposed to declare the ID of the row being updated in <updateParameters>? Thanks in advance!
View 4 Replies View RelatedI have a database which is used for the asp.net login control and i use the same database for my website work too. In this database there are asp.net created tables for login controls and the tables that i have created for the website. Now when i add a user to the website, data is added in the asp.net created tables (like aspnet_membership, aspnet_users). I want to add some of the data that is added to these tables into the tables that i have created. Is there a way i can do this?
View 4 Replies View Related
Hello,
I have a ta ble that has 2 columns(licenseplatenumber,vehicletype) and a second table that has 20 or more columns but has(licenseplatenumber,vehicletype) too. The first table got changed once because vehicletype changed from central office for each licenseplatenumber, and i was wondering if there is a way to update the second table with the data from the first table with one or two statements, and not one-by-one. there are alot of data to be changed.
thank you in advance..
Hi all,
Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables? If yes- what would be the query structure for that? Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it
Thank you in advance.
Hi All,
My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:
Code Snippet
CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy
AS
SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_
UNION ALL
SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_
UNION ALL
SELECT clmSectors, clmLeft, clmRight FROM tblSectors_
UNION ALL
SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_
Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
So I have 2 tables for products in the system
products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+
productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
what datafield shoud i put in my database if its a checkbox option? coz in my datagrid i added a checkedbox so that i can easily manipulate it.but when i run it. it produces error. and doesnt recognize the checkbox column.it said that there none in the datasource.but i dont know what should i put in the database! pls help!thanks
View 1 Replies View RelatedI've created a Data Set with a connection string to my sql database however when i go to create queries and select 'generate update, delete and insert statements' it doesn't do it. I'm wondering why and how do i get this fixed since i don't know how to code it myself.
Thank you.
I need to display data from a sql server 2000 db on an asp web page and I need a vertical split. Does anyone have any solutions? I really can't afford to spend much/any money so freeware/shareware or coding suggestions is what I am interested in.
Thanks!
I want to update a data in grid view
I have three columns in grid view
First two column are shift id and date these two column are taken from table 1
The third column is shift column this column are taken from table 2
Now I want to update a data where shift id and shift column are same
table 2 have these column
shift
shiftid
and table 1 has these column
shift id,
date
I have the following code: Imports System
Imports System.Data.SqlClient
Namespace TestWeb
Partial Class araging
Inherits System.Web.UI.Page
Private _dt As DataTable
Private _connstr As String
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim _connstr As New SqlConnection(ConfigurationManager.ConnectionStrings("ESQLConn").ConnectionString)
Dim sqlCommand As SqlCommand = New SqlCommand("csp_rpt_ack_form", _connstr)
sqlCommand.CommandType = CommandType.StoredProcedure
Dim objaDapter As New SqlDataAdapter(sqlCommand)
objaDapter.SelectCommand.Parameters("@v_order_doc_no").Value = 272
Dim ds As New DataSet
objaDapter.Fill(ds)
mainGrid.DataSource = ds
mainGrid.DataBind()
Response.Write(_connstr)
End Sub
End Class
End Namespace
I am trying to pass a value to the parameter that it contains. What Im I doing wrong? I keep on getting the following message."An SqlParameter with ParameterName '@v_order_doc_no' is not contained by this SqlParameterCollection." Thanks guys!
I try to find the way to create editable data grid (a view from MySQL database) in a web page. So, user can edit, add, or delete from one page instead of having switch edit, insert pages, one field in the view only. Any script I need to write or any special server behavior I can use?
Thanks a lot any one can help with.
Ann
I have a table which stores all prices for companies daily trading. Companies ABC & XYZ have information available for what was the high & low values for a DateID.
CREATE TABLE [dbo].[TestGrid]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](200) NOT NULL,
[DateID] [int] NOT NULL,
[High] [float] NOT NULL,
[Low] [float] NOT NULL
[code]...
What I'm trying to do is turn the DateID into columns and then as a additional change those columns represent the actual day of the Date ID How would i know 20121201 is equal to say Monday and 02 is Tuesday?
END RESULT:
NAME TYPE MONDAY TUESDAY WEDNESDAY
ABC HIGH 0.5 0.6 1
ABC LOW 0.1 1.5 0.6
I have table in sql server for Employee, it has(id, Employee, director),so the director field is id of the director's name, so the director is employee and has director also, only the first director hasn't director( one Employee has not director);now, i want to use datagridviewer in c# for display as this table (id, employee, director), so the first record will be the (id, employee, ' ');i use this query:"select emp.ID ,emp.Name ,dir.Name as'director' from table1 emp, table1 dir where (emp.director=dir.ID OR dir.director IS NULL)"but when i display the datagridviewer , it don't display the first employee(first director) which hasn't director.
View 6 Replies View RelatedHas anyone ever looked at the way the grid data viewer sorts it data when
clicking on a column header? If you click on a column header, something
happens to the sorting of the data in the viewer, but it's not always clear
to me _what_ is happening. It appears the data is sorted ascending on the
column you clicked on. If you click again the sort order seems to inverse to
descending. However, if you look closely, it turns out that the data is not
always sorted correctly, especially when you click on an integer valued
column.
I found this when doing a demo on the AdventureWorks demo extracting data
from the SalesOrderDetail table. If you sort on the OrderQty column, data is
correctly sorted in ascending quantities. However, if you click the column
again, orders with an order quantity of 2 are displayed on top (while there
are orders with a much higher order quantity) and if you scroll down the
list, you notice that there is no clear sorting anymore. The same happens
with other columns.
Is this supposed to work as I would expect it to do or is there a logical
explanation for the behaviour I see?
--
Best regards,
Hans Geurtsen
Docent Kenniscentrum
Info Support
De Smalle Zijde 39
3903 LM Veenendaal
The Netherlands
www.infosupport.nl
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field.
Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource.
If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query.
Can anyone help.
I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
--------------------------------------------------------------------------------------------------
Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015
--------------------------------------------------------------------------------------------------
Laptop | Tom | Tom | Tom | Avail | Avail
Projector | Avail | Avail | Avail | Avail | Bob
Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
Hi
We are building a near real time application in Windows Forms, that stores data in SQL Compact Edition.
The client receives messages via UDP socket and performs insert / update operations to the db accordingly, and the requirement is to update a data grid with the changes.
We tried querying the db and receive a ResultsSet which is sensitive and scrollable, and bind a ResultSetView to the Grid. But, since the updates are made from another thread, the grid is not being refreshed.
What is the best approach for achieving this?
Thanks in advance,
Guy Burstein
http://blogs.microsoft.co.il/blogs/bursteg
i am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
I am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
Role Loans Payments Transactions Accounts Customer Emplo
Database Admin SUID SUID SUID SUID SUID SUID
Branch Manager SUI SUI SUI SUI SUI SUI
Internal Auditor S S S S S S
Loan Officer SUID SUI SUI S S
Tellers S S S S SU
Customers U
hi,i have an sql database on my server on the world wide web.i can make a connection to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas?
View 2 Replies View RelatedI have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db.
ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is:
select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user)
I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net.
Many thanks
Consider i have 2 database namely database1 and database2 . both the databases are in the same server
database1 has a following tables
1.the table "class" has following fields and many more fields. consider 50 fields but for the use of example i have given only three fields name and sample values. class studentname rollno (table name - class) 8th std aaaaa 100
2. the table "Fees" has the following fields and many more fields. consider 25 field but for the use of example i have gievn only 2 fields name and sample values rollno fees (table name - fees) 100 50000 101 25000
Now i have created the following tables in database2 the table "class" has the following fields only, class studentname rollnothe table "fees" has the following fields only. rollno fess
Question ?please let me know if there is any tool or method . to transfer values from database1 to databse2
now i want to update all the three tables with the help of asp.net in the table PhoneExtraFieldAliasalias should be replased by the inputed values say in filed1 = 'date' so now filed1 should behave like as date column and if no data is inputed then field should be 'null' and it picks up its value from phoneextra and phonemst and all the task are performed in one form of asp.net in phoneextra nad phoneextrafieldalias tables has phoneid is comman.first it takes data from phoneextrafiledalias then its value from phoneextra CREATE TABLE [dbo].[PhoneExtra] ([PhoneID] [varchar] (12) NOT NULL ,
[Field1] [varchar] (50) NULL ,[Field2] [varchar] (50) NULL ,[Field3] [varchar] (50) NULL ,[Field4] [varchar] (50) NULL ,[Field5] [varchar] (50) NULL ,[Field6] [varchar] (50) NULL ,[Field7] [varchar] (50) NULL ,[Field8] [varchar] (50) NULL ,[Field9] [varchar] (50) NULL ,[Field10] [varchar] (50) NULL ,[Field11] [varchar] (50) NULL ,[Field12] [varchar] (50) NULL ,[Field13] [varchar] (50) NULL ,[Field14] [varchar] (50) NULL ,[Field15] [varchar] (50) NULL ,[Field16] [varchar] (50) NULL ,[Field17] [varchar] (50) NULL ,[Field18] [varchar] (50) NULL ,[Field19] [varchar] (50) NULL ,[Field20] [varchar] (50) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneExtraFieldAlias] ([CampaignID] [varchar] (20) NOT NULL ,[Field1] [varchar] (50) NULL ,[Field2] [varchar] (50) NULL ,[Field3] [varchar] (50) NULL ,[Field4] [varchar] (50) NULL ,[Field5] [varchar] (50) NULL ,[Field6] [varchar] (50) NULL ,[Field7] [varchar] (50) NULL ,[Field8] [varchar] (50) NULL ,[Field9] [varchar] (50) NULL ,[Field10] [varchar] (50) NULL ,[Field11] [varchar] (50) NULL ,[Field12] [varchar] (50) NULL ,[Field13] [varchar] (50) NULL ,[Field14] [varchar] (50) NULL ,[Field15] [varchar] (50) NULL ,[Field16] [varchar] (50) NULL ,[Field17] [varchar] (50) NULL ,[Field18] [varchar] (50) NULL ,[Field19] [varchar] (50) NULL ,[Field20] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneMst] ([PhoneId] [varchar] (12) NOT NULL ,[PhoneNo] [varchar] (50) NOT NULL ,[Name] [varchar] (50) NULL ,[Sex] [char] (1) NULL ,[Company] [varchar] (100) NULL ,[Address] [varchar] (150) NULL ,[City] [varchar] (50) NULL ,[State] [varchar] (50) NULL ,[Zip] [varchar] (50) NULL ,[Country] [varchar] (50) NULL ,[Email] [varchar] (60) NULL ,[Website] [varchar] (50) NULL ,[Fax] [varchar] (50) NULL ,[EntryOn] [datetime] NULL ,[Remarks] [varchar] (10) NULL ) ON [PRIMARY]GO
I have a webform contains a list of documents and information about them. What would be the best way to update any changes back to the database where I would need to log each change into an audit table as well.
For example:document changedA YesB NoC Yes
I would need to take this information and update the documents table with the changes from A and C, but ALSO would need TWO entries in my audit log table. One for A and one for C. Is there a quick way to do this or will I need to use loops and/or cursors? I'm trying to find a way that won't kill my performance. Thank you.
Even if indexes are good, isn't there still a performance issue with updates to a very fat table (333 columns and almost 8030 bytes, with over 100,000 rows). We are looking at 300 users updating this table 400-500 times an hour. I had no input on the table design, but I can complain. All the updates are sql, not sp's
View 2 Replies View RelatedHello I have two tables users and Private I want to be able to view, update, delete info from the two tables from one screen
scenario user logs in to system system stores userID and transfers userID to new table this user id will be used by sql to load only that users details (UserID is PK for users and FK for private) the user will be able to update some of their info. as an admin they will use the same procedure except they will be able to see all members details and add new members using update or delete. I have tried to use a data Sqladaptor to do this but the info will display it will not allow delete update. If I load both tables into separate Sqladaptors they are not in sync anyone have any suggestions
thanks
M
I have two tables:
table 1
-------
code
description
colour
size
active
table 2
-------
code
location
sales_region
active
How can I, using SQL Server 2005, update both tables so that
colour = red
location = Wisconsin
size = medium
active = yes
where code = AL1
please?
table 1 has code as the primary key. table 2 has no index.
Okay, here is my issue:I have an access program that tracks the location of certain items.When the items are moved their record will be added with transferinformation.So, there are two tables: tblContents and tblTransfertblContents holds all the relevant information about each item as wellas a flag for transferred items and tblTransfer holds all thetransferred item information.My problem is: How do I update tblTransfer when an item in tblContentsgets flagged true?btw, this is using a .asp front end to interact with the database.
View 1 Replies View RelatedI'm writing a sproc to update a table based on parameters being passed into it. Here is the sproc:
Code Block
CREATE PROCEDURE [dbo].[UpdateLicense]
@VendorId int,
@PoId int,
@LicenseTypeId int,
@LicenseUserId int,
@LocationId int,
@LicenseStartDate smalldatetime,
@DaysAllowed int,
@SerialNum varchar(50),
@ActivationKey varchar(50),
@MaxUsers int,
@Comments varchar(1000),
@LicenseId int
AS
BEGIN
UPDATE license
SET
vendor_id = @VendorId,
po_id = @PoId,
license_type_id = @LicenseTypeId,
lic_user_id = @LicenseUserId,
location_id = @LocationId,
lic_start_date = @LicenseStartDate,
days_allowed = @DaysAllowed,
serial_num = @SerialNum,
activation_key = @ActivationKey,
max_users = @MaxUsers,
comments = @Comments
WHERE license_id = @License_id;
END
When I try to compile this I get the following error:
Error Message
Msg 137, Level 15, State 2, Procedure UpdateLicense, Line 35
Must declare the scalar variable "@License_id".
My first question is what line is line 1? Does the line count include every line, even comments? Becuase, if so, then line 35 is "location_id = @LocationId," which makes no sense to me given the error message about the scalar variable. I am probably missing a comma or something but I cant see it. Anyone?
Hi,
Is it possible to update two tables with the same stored procedure. I want to update the tables Member an Login using the same SP. If not is it possible to use two stored procedures but the same SQLconnection with two command statements?
e
USE [AdminDB]
ALTER PROCEDURE [dbo].[swim_insert_ipnumber]
@member int,
@mbrFirstName nvarchar(15),
@mbrLastName nvarchar(15),
@mbrEmail nvarchar(15),
@lgnIPnumber int
AS
BEGIN
update Member,, Login
set Member.mbrFirstName = @mbrFirstName,
Member.mbrLastName = @mbrLastName,
Member.mbrEmail = @mbrEmail,
Login.lgnIPnumber = @lgnIPnumber
where mbrMemberNumber = @member
END