Hi, I have a Stored procedure doind an INSERT which then returns @@Identity.
I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..
Not sure where I am going wrong...
Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?
Many Thanks
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
View 2 Replies View RelatedThe query below should be inserting unique records in the PropertyItem table which only has propertyitem, propertyid, and itemid columns, all of which are PK's in other tables. I'm not doing the insert correctly b/c it's inserting 72 records instead of 24. I really just want to automatically insert the values once I've inserted in the other tables but I'm not sure how. Maybe On Update Cascade?
--PROPERTYITEM
INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId])
SELECT Property.propertyId, ITEM.ItemID
FROM ITEM RIGHT OUTER JOIN
miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN
Property ON property.adprintid = miamiherald.adprintid
WHERE validAD=1
Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
View 1 Replies View RelatedThe following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this.... I tried several alternatives that I found on the internet but none seem to work...
Thanks!
Dim objConn3 As SqlConnectionDim mySettings3 As New NameValueCollectionmySettings3 = AppSettingsDim strConn3 As StringstrConn3 = mySettings3("connString")objConn3 = New SqlConnection(strConn3)Dim strInsertPatient As StringDim cmdInsert As SqlCommandDim strddlSex As StringDim strddlPatientState As StringDim rowsAffected As Integer
strddlSex = ddlSex.SelectedItem.TextstrddlPatientState = ddlPatientState.SelectedItem.TextstrInsertPatient = "Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _"Today_Date_Year) Values (@UserID, @Accession, @FirstName, @MI, @LastName, @MedRecord, " & _"'" & strddlSex & "', @DOB, @Address1, @Address2, @City, @Suite , '" & strddlPatientState & "', " & _"@ZIP, @HomeTelephone, @OutsideNYC, @ClinicalImpression, @Today_Date_Month, @Today_Date_Day, " & _"@Today_Date_Year)SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"
cmdInsert = New SqlCommand(strInsertPatient, objConn3)
cmdInsert.Parameters.Add("@UserID", "Joe For Now")cmdInsert.Parameters.Add("@Accession", Accession.Text)cmdInsert.Parameters.Add("@LastName", LastName.Text)cmdInsert.Parameters.Add("@MI", MI.Text)cmdInsert.Parameters.Add("@FirstName", FirstName.Text)cmdInsert.Parameters.Add("@MedRecord", MedRecord.Text)cmdInsert.Parameters.Add("@ddlSex", strddlSex)cmdInsert.Parameters.Add("@DOB", DOB.Text)cmdInsert.Parameters.Add("@Address1", Address1.Text)cmdInsert.Parameters.Add("@Address2", Address2.Text)cmdInsert.Parameters.Add("@City", City.Text)cmdInsert.Parameters.Add("@Suite", Suite.Text)cmdInsert.Parameters.Add("@strddlPatientState", strddlPatientState)cmdInsert.Parameters.Add("@ZIP", zip.Text)cmdInsert.Parameters.Add("@HomeTelephone", Phone.Text)cmdInsert.Parameters.Add("@OutsideNYC", OutsideNYC.Text)cmdInsert.Parameters.Add("@ClinicalImpression", ClinicalImpression.Text)cmdInsert.Parameters.Add("@Today_Date_Month", Today_Date_Month.Text)cmdInsert.Parameters.Add("@Today_Date_Day", Today_Date_Day.Text)cmdInsert.Parameters.Add("@Today_Date_Year", Today_Date_Year.Text)
objConn3.Open()cmdInsert.ExecuteNonQuery()objConn3.Close()
I have tried many code sample but I am very stuck
The primary key of my database (SQL server 2005) table is a uniqueidentifier.
I am using the following code to insert a row into my table:
myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query
I need to retrieve the GUID that is automatically generated when the insert command is executed.
Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using
string _id = (string)myCommand.ExecuteScalar();
and I am still stuck. I will really appreciate it if someone can refer me to some code sample.
HELP
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
View 3 Replies View RelatedHi there. I looked through many other posts describing scope_identity but I am trying to achieve the same thing from the code behind. i.e. I need to some how call a method to execute the insert command and then return the ID so I can update other tables with this value.
I was going down the road of something like:
addnew as sqldatasource = new sqldatasource
addnew.insertcommand = "Insert into....; def @NewID as scope_identity"
addnew.insert()
The problem is I don't know how to add a output parameter using VB or how to retrieve it.
Any help would be much appreciated, this is doing my head in....
Doug.
How can I efficiently retrieve the top x managers within the top y regions within the top z states within the top q countries?
The only way I have been able to do this is to first find the top q countries, and store the list in an IN clause. Then for each of those, find the top z states and create another IN clause that contains the country+state concatenated. Then for each of those, find the top y regions and concatenate country+state+region. Then finally find the top x managers within this IN clause.
This works fine for a few hundred records, but once it reaches the thousands, it takes much too long. The final IN clause contains thousands of entries.
Isn't there a simpler way to approach this, especially with SQL Server 2005?
i am using a stored procedure, lets say spTemp, that calls sp_executesql within it. in the sp_executesql procedure i am passing parameters from the stored procedure 'spTemp'. i want to retrieve the result of the sp_executesql statement so as to perform further calculations in spTemp. is there a way of doing this?
ex:
create procedure spTemp
(
@abc int
)
as
declare @temp int
exec sp_executesql 'select @temp = Id from Product where @abc > 10'
...
how to get the value @temp from this statement?
I am now retrieving the PDF from the database and I am getting an error:
Error 1 'GetImages.GetImage(int)': not all code paths return a value
I have:
int imageid = Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]);
And...
private SqlDataReader GetImage(int imageid)
{
Sql Statement...
}
Could someone help please??
Hi
I've got a module that contains the following function.
Imports System.Data.SqlClient
Module SQL_Sprocs
Function ListUsers()
Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("DBConn"))
Dim cmd As SqlCommand = New SqlCommand("list_users", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
ListUsers = dr
dr.Close()
conn.Close()
End Function
End Module
I then want to call this function from my webform. So I'm using the following
Dim dr As SqlClient.SqlDataReader
dr = Timetracker.SQLProcs.ListUsers()
Do While dr.Read
Label1.Text &= dr("first_name") & " " & dr("last_name") & ", "
Loop
dr.Close()
But it's not working. I want to have a module that contains all my sprocs and be able to call them from the individual webpages.
What am I doing wrong?
Lbob
I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.
Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.
i have a table in MS SQL Server,i need to show two results at a time,and on click of a button nxt two results have to b shown,how do i accomplish this (i mean retrieving the results two at a time)thanx in advance
View 1 Replies View RelatedFaculty member writes:
"I meant to delete just one assignment, doing which was giving me difficulty.
I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that.
Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!"
My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs.
Then the server is backed up to tape shortly after the 5 pm full backup.
Obviously, I can't restore from the backup for this situation because it would hurt other data.
But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?)
I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this.
I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched.
That's exactly what I need to do now with MS SQL 2000.
As the faculty dude said, "HELP!"
And Thanks.
Hi,
I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server.
I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
Hi,
I have a giant SQL query that retrieves a bunch of information from a couple of tables for use on a page, and I would like some help on constructing the SQL to get exactly what I have in mind. I have a table - called scContacts - that contains a list of contacts with detailed information. The contacts in the table are regular sales contacts, sales reps, and sales managers. Another table - called scCompany - contains a list of companies with detailed information.
For each company, we have a certain sales manager (or user) pertaining to that company as well as potentially a certain rep. The problem is that the contact tied to the company, the sales manager tied to the company, and the rep tied to the company all come from the same table. Here is the base SQL code I've used to get everything but the sales rep's name and sales manager's name:
----- SQL CODE -----
SELECT DISTINCT
scContacts.ID,
scContacts.inheritCoAddr,
scContacts.fName,
scContacts.lName,
scContacts.contact,
scContacts.billToAddress1,
scContacts.billToAddress2,
scContacts.billToState,
scContacts.billToZip,
scContacts.billToCity,
scContacts.billToCounty,
scContacts.billToCountry,
scCompany.ID AS companyID,
scCompany.companyName,
scCompany.companyURL,
scCompany.billToAddress1 AS companyAddress1,
scCompany.billToAddress2 AS companyAddress2,
scCompany.billToCity AS companyCity,
scCompany.billToState AS companyState,
scCompany.billToZip AS companyZip,
scCompany.billToCounty AS companyCounty,
scCompany.billToCountry AS companyCountry,
scCompany.businessType,
scCompany.phoneExt,
scCompany.phoneNum,
scCompany.faxNum,
scCompany.minEmployees,
scCompany.maxEmployees,
scCompany.actionTypeMAX,
scCompany.actionDateMAX,
scCompany.actionTimeMAX,
scCompany.statusID,
scCompany.userID,
scCompany.repID
FROM
scCompany,
scContacts
INNER JOIN
scStatus ON
scStatus.ID = scCompany.statusID
WHERE
scgroupContacts.contactID = [insert cookie value]
AND
scgroups.ID = [insert cookie value]
AND
scCompany.ID = scContacts.companyID
AND
scCompany.groupID = scgroups.ID
----- END SQL CODE -----
As indicated right now, this SQL code will retrieve all of the contact and company information for a certain contact ID which comes from a cookie value (inserted in ASP). I want to get the [fName] and [lName] fields from scContacts table where the scContacts.ID = scCompany.userID, and I also want those same fields from scContacts table again where the scContacts.ID = scCompany.repID. It would be simplest and most efficient if I could do this all at once (and I'm sure it's possible). How would I change the SQL to bring in that information from the same table two more times, tying their ID's to ID's in the company table?
thanks,
mellamokb
bobby writes "I have a Table from which i get the ID of customers(Customers are people who have written an online exam).The date of completion of the exams are stored in the corresponding table with the name of the exam.There are more than one exams and the names of the exams are stored in an other table.Now my question is how to get the date of cpletion with the given Id from the first table?"
View 2 Replies View RelatedHi, I need some help. I have this query where i need to pull up students that have more than or equal to 3 absences in a row. Now I am getting all those students that are absent but not in a row. But i was wondering if there is a way to tell if a student was absent three days in a row . There is a date field that is being used to identify the day of the class and a status field that identifies if the student was absent or present. Please help someone.
Birju
i lost a table with lots of data - there's around 10,000 records inserted daily.
my problem was i only realized this morning (and i lost it last night) and i have the backups backing up each day every hour - so the backup had already overwritten it seems the db that was missing the table)
my backup type is set to append - so i though i could retrieve by date time but it only let me retrieve the backup at 11pm(tune if last backup) and no earlier time. is there anyway of getting the data from the 7 pm backup -- again it backsup each hour to the same file - set to append so i thought that meant i could choose a time but it doesn't seem to be working.
please advise.
insert into newTABLE(newCOLUMN)
Select
Case When [Column 11] like '%DOB%' Then
SubString( [Column 11],
CharIndex('DOB',[Column 11],1)+3,
Case When CharIndex(';',[Column 11],1)= 0 Then
CharIndex('.',[Column 11],1) Else CharIndex(';',[Column 11],1)
End-(CharIndex('DOB',[Column 11],1)+3)) Else '' End,[column 11]
from table
Following is da example of return result
through the image, we can found that some of them got ddmmyy and some got only year.Currently, i want to insert those record to newtable newcolumn with only those got propal date(unwanted for only year).What should i do following?
Hi AllBit of a situation so hopefully someone can advise.I've a client who has a sql 2000 database that *unfortunately* hasn'thad a backup procedure in place. Its been running for just over a yearand on Monday it got, well, screwed. They have a 50 gig ldf file.Yes, 50 gig. Is it possible to put in place a database maintenanceplan that will allow me to roll back to, say, last friday just from theldf file?Thoughts? Any advice would be welcome.MTIAMark
View 3 Replies View RelatedHi all,I have an application that creates a publication on the server, andhave multiple mobile devices creating annonymous subscriptions to thatpublications. I need to write a report that checks if each device havethe replication synchronized successfully. I can rundistribution.dbo.sp_MSenum_merge or look intodistribution.dbo.MSmerge_history to get at the data for _all_subscriptions to a given publication, but to look at a particularsubscription, I need to filter by either the subscriber_db or agent_idcolumn. The problem is, how do I get either one of these informationfrom the device? Or is there other way of retrieving the merge historyfor a particular device/annonymous subscription?Thanks in advance,Harold
View 1 Replies View RelatedHi,Is it possible to get metadata (i.e. descriptions of tables etc.) insql-server? In Oracle you can retrieve this information with tables likeall_objects, user_tables, user_views etc. For example, this query selectsthe owner of the table 'ret_ods_test' (in Oracle!):select ownerfrom all_objectswhere object_name = 'ret_ods_test'What's the equivalent in sql server?Thanks a lot.
View 2 Replies View RelatedDear fellows,
Up to the moment I've got enough knowledge for read data stored into .LDF files by dbcc log and so on. It's very useful and interesting. Now, I wonder how to retrieve the same information but on MDF files.
At first, I want to make sure that is not possible by mean of traditional methods (dbcc or something like that) I suppose so but I'd like to hear opinions regarding that.
Thanks in advance for any idea, though or further information.
Hello and thanks for taking a moment. I am trying to retrieve and display an image that is stored in SQL Server 2000. My aspx code is as follows:<HTML> <HEAD> <title>photoitem</title> <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> <asp:DataGrid id="DataGrid3" HorizontalAlign='Left' runat="server" AutoGenerateColumns="true" Visible="True"> <Columns> <asp:TemplateColumn HeaderText="Image"> <ItemTemplate> <asp:Image Width="150" Height="125" ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "InventoryItemPhoto")) %>' Runat=server /> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> </form> </body></HTML>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------My code behind file is below VS 2003 does not like my datareader. It says the following: 'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Items'If there are any suggestions as to what I am doing wrong I would appreciate the input. - Jasonusing System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Text;namespace ActionLinkAdHoc{ /// <summary> /// Summary description for photoitem. /// </summary> public class photoitem : System.Web.UI.Page { string connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; protected System.Web.UI.WebControls.DataGrid DataGrid3; private void Page_Load(object sender, System.EventArgs e) { // Get the querystring ID string item =Request.QueryString["ID"]; int ID=Convert.ToInt32(item); SqlConnection dbConn5 = new SqlConnection(connStr); SqlCommand sqlCom5 =new SqlCommand("sp4TWRetrieveItemPhotos"); sqlCom5.Connection = dbConn5; sqlCom5.CommandType = CommandType.StoredProcedure; sqlCom5.Parameters.Add("@ID", SqlDbType.Int); sqlCom5.Parameters["@ID"].Value =ID; dbConn5.Open(); SqlDataReader myDataReader; myDataReader = sqlCom5.ExecuteReader(CommandBehavior.CloseConnection); DataGrid3.DataSource = sqlCom5.ExecuteReader(); DataGrid3.DataBind(); while(myDataReader.Read()) { Response.ContentType = myDataReader.Items("JPEG"); Response.BinaryWrite(myDataReader.Items("InventoryItemPhoto")); } dbConn5.Close(); } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion }}
View 1 Replies View RelatedHello and thanks for taking a moment. I am trying to retrieve and display an image that is stored in SQL Server 2000. My aspx code is as follows:<HTML> <HEAD> <title>photoitem</title> <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> <asp:DataGrid id="DataGrid3" HorizontalAlign='Left' runat="server" AutoGenerateColumns="true" Visible="True"> <Columns> <asp:TemplateColumn HeaderText="Image"> <ItemTemplate> <asp:Image Width="150" Height="125" ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "InventoryItemPhoto")) %>' Runat=server /> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> </form> </body></HTML>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------My code behind file is below VS 2003 does not like my datareader. It says the following: 'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'Items'If there are any suggestions as to what I am doing wrong I would appreciate the input. - Jasonusing System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Text;namespace ActionLinkAdHoc{ /// <summary> /// Summary description for photoitem. /// </summary> public class photoitem : System.Web.UI.Page { string connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; protected System.Web.UI.WebControls.DataGrid DataGrid3; private void Page_Load(object sender, System.EventArgs e) { // Get the querystring ID string item =Request.QueryString["ID"]; int ID=Convert.ToInt32(item); SqlConnection dbConn5 = new SqlConnection(connStr); SqlCommand sqlCom5 =new SqlCommand("sp4TWRetrieveItemPhotos"); sqlCom5.Connection = dbConn5; sqlCom5.CommandType = CommandType.StoredProcedure; sqlCom5.Parameters.Add("@ID", SqlDbType.Int); sqlCom5.Parameters["@ID"].Value =ID; dbConn5.Open(); SqlDataReader myDataReader; myDataReader = sqlCom5.ExecuteReader(CommandBehavior.CloseConnection); DataGrid3.DataSource = sqlCom5.ExecuteReader(); DataGrid3.DataBind(); while(myDataReader.Read()) { Response.ContentType = myDataReader.Items("JPEG"); Response.BinaryWrite(myDataReader.Items("InventoryItemPhoto")); } dbConn5.Close(); } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion }}
View 1 Replies View RelatedHello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.
View 2 Replies View RelatedI am using several TextBox Controls, instead of a FormView, for inserting data into a Sql Database. The primary key (ID) is an integer which is automatically incremented by one at each insertion. At each insertion I need to retrieve the ID value of the newly inserted record. I have followed a suggestion from a help sample with this code:Imports System.Data.CommonImports System.Data.SqlClientImports System.DataPartial Class InsertInherits System.Web.UI.PageProtected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.ClickSqlDataSource1.Insert()End SubSub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)Dim InsertedKey As SqlParameterInsertedKey = New SqlParameter("@PK_GuestList", SqlDbType.Int)InsertedKey.Direction = ParameterDirection.Outpute.Command.Parameters.Add(InsertedKey)End SubSub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)Dim command As DbCommandcommand = e.CommandLabel1.Text = command.Parameters("@PK_GuestList").Value.ToString()End SubEnd ClassNo output appears on the Label1.If in the last code row I replace "@PK_GuestList" with the name of any TextBox used for inputting data, its content is correctly shown in Label1.Where is the problem?
View 7 Replies View Relatedhi all,
I have a table productprice which has the following feildsid price datecreated productname 1 12.00 13/05/2007 a1 2 23.00 14/05/2007 a13 24.00 15/05/2007 a14 56.00 13/05/2007 b15 34.00 18/05/2007 b16 23.00 21/05/2007 b17 11.00 12/02/2007 c1 8 78.00 12/03/2007 c2I
need to select the rows that are highlighted here.. ie the row that has
the max(datecreated) for all the productname in the table..
plz help thanks in advance..
Ok, again, I'm reasonably new to this. I've been trying to display an image stored in SQL in a ASP.NET page. Pretty simple stuff I would have thought. I've read countless examples of how to do this online, and many of them use the same method of displaying the image, but none seem to work for me. The problem seems to lie in the following line of code:
Dim imageData As Byte() = CByte(command.ExecuteScalar())Which always returns the error: Value of type 'Byte' cannot be converted to '1-dimensional array of Byte'.Here's the rest of my code, hope someone can help. It's doing my head in!
Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System.IOPartial Class _ProfileEditor
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Get the UserID of the currently logged on user Dim NTUserID As String = HttpContext.Current.User.Identity.Name.ToString
Session("UserID") = NTUserID
Dim Photo As Image = NothingDim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = "SELECT Photograph, ImageType FROM Users WHERE UserID = @UserID"command.Parameters.AddWithValue("@UserID", NTUserID)
connection.Open()Dim imageData As Byte() = CByte(command.ExecuteScalar())Dim memStream As New MemoryStream(Buffer)
Photo = Image.FromStream(memStream)
End Sub
End Class
Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.
So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).
How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?
TIA.
Dear Friends,
I have read many solution over the net, but since I am unable to utilize anyone according to my needs I am seeking help from you people.
I have a table imagedata in sql server 2005 having fields name and imageperson. Name is string and imageperson is Image field. I have successfully stored name of the person and his image in database.
I have populated the dataset from codebehind and bind it to the repeater.
By writing <%# DataBinder.Eval(Container.DataItem, "name")%>I am a able to retrieve the name of the person. But when I pass photodata as
<%#photogen((DataBinder.Eval(Container.DataItem, "imageperson")))%>
where photogen is function in code behind having structure
public void photogen(byte[] dataretrieved)
{
Response.BinaryWrite(datarerieved)
}
But it is giving error at <%#photogen((DataBinder.Eval(Container.DataItem, "imageperson")))%>
The best overloaded method match for '_Default.photogen(byte[])' has some invalid arguments
AND
Cannot convert object to byte[].
Can anyone please provide me working solution with code for aspx page and code behind.
Thanks and regards