Two Simple (I HOPE) SQL Insert() Follow Up Questions:
Apr 28, 2007
1. Let's say I have a SqlDataSource named Fred. Say I have a string variable, call it Barney. If I call Fred.Insert(Barney) HOW Does the insert command GET the Barney value and pass it to the SQL Insers string?
2. Lets say also in this same insert, I want the user Lets say Batty, who is logged in, to have her username automatically inserted into the table along with her Barney value. What is the method to get a hold of user name that is magically floating around in the session somewhere?
All said and done, I'd love for it to work like this: "INSERT INTO [BedRock] ([UserName], [Character] ) VALUES (SomethingThatResolvesToSignedInUserName, SomehtingThatResolvesToStringValueofVariable)
Table Bedrock
UserName Character
BettyRubble 'Bam-Bam'
THANKS inadvance fro ANY Help
Dan-Simple and sleepless-Harper
Greetings! I am writing a proof of concept ASP.net application for my employer and I have run into a couple perplexing issues. First off, In my Page_Load event I have the following code. txOTHrs.Attributes["onBlur"] = "return calculateTotal(this)"; The onblur event handler is coded as follows function calculateTotal(tb) { var regHrs = document.getElementById ("txRegHrs").value; var otHrs = document.getElementById("txOtHrs").value; var rate = document.getElementById("txRate").value; var regAmt = rate * regHrs; var otAmt= rate * ( otHrs * 1.5 ); var total = otAmt + regAmt; document.getElementById("txTotal").value = total; } Everything seems to work fine. The onBlur event is handled and, as I expect the value in the txTotal text box is updated correctly. The problem occurs when I try to access that value in the codebehind page. When ever I look at txTotal.Text in the debugger it is always blank. This has left me scratching my head as I can clearly see a value on the web page. Have I missed some step where I have to notify the server that a value in the control has changed ?
My second question is, I hope a very easy one to answer. In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?
Any help anyone can provide is greatly appreciated!!
I was looking for a way to "globally" set my FROM database value. Is this possible?, or does the "FROM" syntax always require the database name? I would like to just state my table names in the FROM statement
BEFORE Example: select T1.id, FROM PT.ptuser.tblRequest T1, where etc....
Can the "PT.ptuser" be declared somehow 1 time so my SQL code could look like...
AFTER Example: select T1.id, FROM tblRequest T1, where etc....
hope this makes sense.... I'm trying to run SQL via Microsoft Query
HELP PLEASE! 1) Is it true that INSERT, UPDATE, and DELETE queries can be carried out on only ONE table at a time?2) I have a newly created database which is empty, and has four tables related with FK's. How can I begin to enter data, if i can only do an INSERT or UPDATE on one table. When i do a SELECT query, i don't even get an empty results set to modify. But i think that's an old fashion technique anyway. And if i can only INSERT VALUES into one table at a time, that violates the relational integrity. PLEASE HELP!DanThe only thing I can think of is the de-enforce the Relationships Constraint... Then, one at a time, fill each table's 1st record with fictious data, and then enforce the Relationships again, hoping that the identity columns will match up.
When I'm designing an Integration Services package, the drag and drop doesn't work. It did at one time but doesn't now. What did I change??? Driving me nuts!!!
Also, this is a simple package. I'm exporting 6 colums from a view (db --> csv). I only want the first initial from the middle name. What data flow transformation object do I use? I know it's not the data conversion because that concerns the column type.
I am a PHP/Delphi MySQL programmer for some years, now I am going to create some code in MS SQL and VB. So I downloaded and registered MS SQL 2005 Server Express Edition and some tools like SQL Server Studio Management Express, and VB 2005 Express.
As I have some knowledge of MySQL design and programming, I have installed MS software, rather without problems. Then, using Studio Management I created new database, new table in it with some four fields (int, varchars and smallint). Then from the same tool I opened table and inserted some data, and closed this application.
Next I opened Visual Basic and in Database Explorer I set up connection to my SQL database indicating the file it is stored in (path*.mdf). I ordered not to copy data to new file but to use original one.
Then in Data Sources I created new data source based on previously set connection. In the end I dragged and dropped my table from Data Sources into my new Form of my new Windows Application, nice grid with panel appeared. So I ran the app, I tried to put some data and then after trying to save (using floppy disk icon on panel) i got error: Update requires a valid UpdateCommand when passed DataRow collection with modified rows. which appears in event handler for saving in line i've indicated.
Me.Validate() Me.LudzieBindingSource.EndEdit() Me.LudzieTableAdapter.Update(Me.TestDataSet.ludzie ) ' <-- here highlights
I tried then to add to ID field identity specification, nothing changed. Then in query editor of management console I put some code: exec sp_configure 'user instances enabled', 1. Reconfigure.
As some sources mentioned. I restarted Service. Nothing Changed. So this is my problem. What to change.
I use Win XP Pro SP2.
And next one question, even more important for me is the way I connect to database. I found that in Visual Basic Creating new connection in Database Explorer I indicate the file on my local drive!!! And for example if I try to open my table alongside in Management Studio Express and in my App in VB and the error raises Cannot open user default database. Login failed. Login failed for user 'FS7120Mm227'. which is not a surprise for me as in VB i am using file which cannot be shared.
In MySQL I simply put address, port and so on and I could use my database in Delphi, PHP, and another management tools with no hassle. Is it possible in VB? Or it only can be used to exclusive using database?
Please inform me if such questions shall be directed to VB forum, I am not sure, and thank you for _any_ answer.
MS goes right way giving people great possibility to use and even sell small apps using their Express Editions of SQL and VB, but it is still too confusing for people who encouraged start using their apps and have such basic problems.
One Table 3 Columns: StudentName, ExamSubject, FinalScore
A class with 40 students Each student took the 3 exams and have scores. All student took the same 3 exams.
120 rows total.
Task One: Top Score in each subject Task Two: Top 3 student name in each subject Task Three: Top 3 total score student in the class Task Four: Avg. Score for each student
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ? Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ? What are the major disadvantages of using CLR stored procedures instead of T-SQL?
I'm using Bulk Insert for the first time and have a question.I'm getting an error message about a field being truncated:Bulk insert data conversion error (truncation) for row 2, column 12(Depleted)The data type for the "Depleted" column is Char(1). Looking at thefile, there is indeed only one character in the column. I'm not surehow to fix this, or what I can do about it. Any suggestions would beappreciated.Thanks!JenniferThe table:CREATE TABLE [dbo].[parSalesDetailTemp] ([parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[Before] [int] NOT NULL ,[Quantity] [int] NOT NULL ,[After] [int] NOT NULL ,[Promo] [money] NOT NULL ,[PromoBefore] [money] NOT NULL ,[ItemPrice] [money] NOT NULL ,[PromoAfter] [money] NOT NULL ,[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[UnitNumber] [int] NOT NULL ,[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON[PRIMARY]GOThe SQL:BULK INSERT parSalesDetailTempFROM '\wbhq.comdfsdviDataIntTLDFilesExtractSalesD tl.csv'WITH (FIELDTERMINATOR =',')The file contents (1st few rows):8032753,37312006,0,1,0,0,0,4.39,0,"WB-ML",2,N8032753,37312007,0,1,0,0,0,4.39,0,"WB-ML",2,N8032753,37312008,0,2,0,0,0,.00,0,"ML-M-COK",2,N
I have two questions about bulk insert. The first is why can't I use it in a stored procedure with a parameter to give the FROM filename?
Second, I tried using a bulk insert statement interactively and it executes with no errors, but says 0 row(s) affected. If I paste one of the lines from the csv file into a query window and apply all the right single quotes in a regular insert statement it inserts fine.
I am doing a system for my project.I have three tables and they are 1. Hote Table which contains the hotelID as the primary key and other attributes of a hotel in the table 2.Room Table which contains the roomType of the hotels.The table conist the rmId as the primary key and the hId asthe foreign key from the hotel table.The table consist of an attribute standard price of the hotel roomType standard price. 3.The third table is the room type priceList table where all the prices of the roomsType is inserted inside. For e.g HotelID| RMTypeID | Price | dateStart | dateEnd 1 1 200 28 jan 07 30 jan 07 1 1 300 28 dec 06 30th dec 07 1 2 400 28 jan 07 30 jan 07 How do i get the the current price List of the hotel.I tried various sql statement and i am unable to fix ? If there are not latest price of the room Type i have to display the standard price.Do you have any idea how can i do this ?How can i display them in a dataList/datagrid/repeater as i have to show all the room types in the hotel. If my database has some ammendments in my database,please do suggest.Thank you for your help.
How to use a dsn-less connection from DTS?Source: MySQLDestination: SQL Server 7.0I have the ADODB.connection code but unsure where and how to use it.Pls advise.TIA!Bob
I have been programming access databases for 8 years.Well today I find out that I need to know SQL.HUH LOLwell I don't have a clue as to how people even enter data intoa sql database or how to manipulate or retrieve data.Can someone please recommend a book to get me started?thank you very much for any and all help with this.
Hi allI had some info on setting db owners and chaining so I can execute a dtsrun(many thanks to Dan for that)I have taken the comments on board and done some more research on theinternet, but I'm still struggling.I am testing this on my personal edition, but when I runEXEC sp_dboption 'my dbname', 'db chaining', trueit tells me that the SP doesn't accept the option db chaining.I ran select @@version and I have SP3 on where I believe this option wasimplentedcan anyone give me any further pointers.many thanks for any helpAndy
Hi, how do I perform a simple insert command? I dont need the SQL syntax, just how do i do it using VS2005. using SqlDataSource, for some reason, after writing the insert sentence, the "next" button remains gray. i am using VS2005 and sql server.
I am trying to do a simple insert 3 items into 3 colms the table has 4 the 4th is an identity with a seed of 1
I am getting this error that i need an explicit val for the key
TABLE SCRIPT :
GO /****** Object: Table [dbo].[Company] Script Date: 03/30/2006 17:48:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Company]( [idCompany] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Abreviation] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Sol] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ( [idCompany] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF --SHOULDNT NEED THIS : --SET IDENTITY_INSERT dbo.Company ON
INSERT INTO COMPANY VALUES('AmericanExpressCheck','Amex','ax')
Msg 545, Level 16, State 1, Line 2 Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
INSERT INTO archivetitles (title_id, title, type, pub_id) SELECT title_id, title, type, pub_id FROM titles WHERE (pub_id = '0766')
How would I modify this if the table archivetitles had columns: titleid, title, newcolumn, type, pub_id and I wanted the newcolumn value to be null, but I still wanted to select the values from titles?
I had only 40 mb of disk space in the c drive. could that be the reason. if so, how can I turn on the server and get into win NT and make the server up and running. I hope someone can reply as soon as possible
Hi All, I am facing a problem in my personal edition of sql server 2000. my this edition is hung after some time due to error "the schedular 0 appear to be hung in sql server 2000" and next error is "the schedular 1 appear to be hung in sql server 2000" i found the solution of this error i run sp4, after that this error wont appear to me in the error log , but the other problem occur which is "your server is configure for 8 concurrent process but currently 166 process running so the performance would be degrade" and after that i am not able to log into the server. Due to this concurrent process problem i want to go for sql server 2000 Enterprise edition. so what stragery should i follow for upgradation of server. any other alternative of this problem
Hello,I just started with ms sql. I need to insert data from some textboxes into ms sql 2000 database.I can't get it work.I'm trying this:"INSERT INTO tblUplate (ID_Uplata, JMB, IDOsnovaUplate, Iznos, Datum) Values('', ' + txtJMBUplate.Text + ', ' + ddVrstaPrihoda.SelectedItem + ', ' + txtIznos.Text + ', ' + DateTimePicker1.Value + ')"Is this wrong? Should I use + or & (like I used to use with MS Access)?Thanks
I have a textbox with the id of txtName it is a name text box. and I have a submit button. How do i get the value of the text in the text box to popluate a new row of data in my Sql Server database.
------------------------------- Events ------------------------------- ID | E_EventName -------------------------------
------------------------------- Photos ------------------------------- ID | P_EventID | P_Filename -------------------------------
Given an event like "2005 cookout", and a file like "bob.jpg", I'd like to insert the event id based on the event name and the filename into Photos. How can I do that?
Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.
i want an unique identifier for each row.
heres my stored procedure:
CREATE PROCEDURE composeMessage -- Add the parameters for the stored procedure here @username varchar(24), @sender varchar(24), @date dateTime, @subject varchar(255), @message varchar(2500)
AS BEGIN
insert into Messages( "Username", "Sender", "Date", "Subject", "Message" ) values ( @username, @sender, @date, @subject, @message ) END GO
heres my sqlcreate table:
USE [Messenger] GO /****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Messages]( [Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL, [Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL, [Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL, [Date] [datetime] NOT NULL, [ID] [int] NOT NULL, CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
As primary keycan't be null, what do i put for primary key for my insert to work?
I have a query with 2 subqueries, and no error message is reported, but, my problem is that the 2 subqueries do not follow the GROUP BY rule and show the total instead of by vendor...
Code: SELECT Table1.agents AS Vendor , Count(Table1.carS) AS Car_Sold , Sum(Table1.carP) AS Car_Price , Count(Table1.busS) AS MortBus_Sold , Sum(Table1.busP) AS busPRice
I'm trying to wrap my head around inserting values into a table w/ SQLDataSource. What I'm trying to do is have a form where users can submit their feedback and their responses would get put into a table on the backend. It would store not only their comments, but their userid, the url that they were on last, and which area they're from. The trouble is, I have no idea if I'm coding this correctly. On the submit Here is the form: <form id="form1" runat="server"> <asp:SqlDataSource runat="server" ID="feedbackSQL" InsertCommand="insertfeedback_sp" InsertCommandType="StoredProcedure" connectionstring="<%$ ConnectionStrings:Test%>"> <InsertParameters> <asp:Parameter Name="ID" Type="Int16" /> <asp:Parameter Name="Region" Type="String" /> <asp:Parameter Name="Querystring" Type="String" /> <asp:FormParameter Name="Comments" FormField="Comment" /> </InsertParameters> </asp:SqlDataSource> <div> <asp:TextBox ID="Comment" runat="server" Height="200px" Width="350px"></asp:TextBox><br /> <asp:Button ID="Submit" OnClientClick="btn_Submit" runat="server" BackColor="White" Font-Names="Arial" Font-Size="X-Small" Text="Submit" /> <asp:Button ID="Cancel" runat="server" BackColor="White" Font-Names="Arial" Font-Size="X-Small" Text="Cancel" OnClientClick='javascript: window.close()' /> <asp:Button ID="Clear" runat="server" BackColor="White" Font-Names="Arial" Font-Size="X-Small" Text="Clear" OnClientClick='javascript: ClearBox(Comment)' /> </div> </form>Here is the backend: protected void btn_Submit(object sender, EventArgs e) { feedbackSQL.InsertParameters["ID"].DefaultValue = UserId ; feedbackSQL.InsertParameters["Region"].DefaultValue = Region; feedbackSQL.InsertParameters["Comments"].DefaultValue = Comment.Text.ToString(); feedbackSQL.InsertParameters["Querystring"].DefaultValue = URL.ToString(); }When I test it out. Nothing shows up in my database table. I also have another problem, ID is stored in my table as an int, but when I try to set it's value in btn_Submit and try to compile it, I get a "cannont convert 'int' to 'string'" error. Am I missing something here? Any help would be much appreciated, thanks :)
Hi all, I really can't figure out what i'm doing wrong here, and I'd appreciate some help if possible please! SqlDataSource5.InsertParameters("ntuser").DefaultValue = ntuser SqlDataSource5.InsertParameters("datetime").DefaultValue = DateTime.Now SqlDataSource5.Insert()Above is my code to insert into my SQL database a varchar NT user name, and the current date. The Parameters are defined in the HTML as : <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:Test EnvironmentConnectionString %>" InsertCommand="usp_insert_hit" InsertCommandType="StoredProcedure" SelectCommand="usp_select_hits" SelectCommandType="StoredProcedure" UpdateCommand="usp_insert_hit" UpdateCommandType="StoredProcedure"> <InsertParameters> <asp:Parameter Name="ntuser" Type="String" /> <asp:Parameter Name="datetime" Type="DateTime" /> </InsertParameters></asp:SqlDataSource> My SQL Database is set up to have a table called "Hits" which has three colums, a hit_id (Autocounter, primary key), ntuser (varchar), datetime (datetime) When the application runs it stops at runtime and highlights the SQLDataSource5.Insert() line, and I can't figure out why - can anyone else tell why please? thanks.
