Basically i make use of FormView in Web Developer Express Edition to create and store information to the database ....
what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament .....
this value would be stored within a session string in global condition that allowed any page able to access it .....
HI,
I want to store unicode data and retrieve them using C#.
How can I do so?
Data is saved but can not retrive. The data displayed as (??????).
Could Anybody Help me.
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
Hi group,
I don't know what I should try next, all of my tries have been without results in this case. I just wanted to create a temporary table, then insert one row into it and then use this row to compare if this row exists in another table or not. This is my script:
------------------------
CREATE PROCEDURE imp_Tippimport (@TGName char(33)) AS
BEGIN
CREATE TABLE #tTippgeber (
TGName2 char(33)
)
INSERT INTO #tTippgeber (TGName2)
VALUES (@TGName)
*** see below ***
IF NOT exists (select TGName FROM Tippgeber WHERE TGName = @TGName)
INSERT INTO Tippgeber SELECT TGName2 FROM #tTippgeber
/* SELECT * INTO Tippgeber FROM #tTippgeber */
ELSE
UPDATE Tippgeber SET
TGName = t.TGName2
FROM #tTippgeber AS t, Tippgeber
WHERE TGName = t.TGName2
END
GO
I also created a cursor and used a fetch in to a veriable in the line with the stars to see the value of TGName2 but it was NULL.
Any ideas ?!
Thanks for any help !!
Sascha
I want retrieve data from properties field in prifile table in aspnetdb.mdf for multi users,what do i ?
View 1 Replies View RelatedThe problem field is of type ntext, and the length is 16. We've tried changing it to varchar of length of 8000, and this didn't help either. Text datatype didn't work either.When the user pastes a word document or enters more than 11 lines of text, the field in the database goes plank.Here's the subroutine for the Insert. We will change it to a SPROC, but not in the very near future:'Build the Update StringDim strSQLWPSave As StringDim strRQW As String = Request.QueryString("WPPID")Dim strTO As String = TO.Text.ToString()Dim strWS As String = WkScope.Text.ToString()Dim strSD As String = StartDate.Text.ToString()Dim strED As String = EndDate.Text.ToString()Dim strAss As String = Assump.Text.ToString()Dim strBOE As String = BOE.Text.ToString()Dim strConf As String = ConfRat.Text.ToString()Dim str76 As String = Combo76.SelectedItem.Value.ToString()Dim strRDB As String = RevDrivenBy.Text.ToString()Dim strSigComm As String = SigComments.Text.ToString()strSQLWPSave = "Update WP_General_Info SET "strSQLWPSave &= "[TO] = '" & strTO & "', [WkScope] = '" & strWS & "', "strSQLWPSave &= "[Start Date] = '" & strSD & "', [End Date] = '" & strED & "', "strSQLWPSave &= "Assump = '" & strAss & "', BOE = '" & strBOE & "', "strSQLWPSave &= "[ConfRat] = '" & strConf & "', "strSQLWPSave &= "[WPMgrConf] = '" & str76 & "', "strSQLWPSave &= "[Rev Driven By] = '" & strRDB & "', "strSQLWPSave &= "[SigComments] = '" & strSigComm & "' "strSQLWPSave &= "WHERE WP_PId= '" & strRQW & "' "sCon1.Open()Dim cmdSave As New SqlCommand(strSQLWPSave, sCon1)'Try to open DB and execute UpdateTry'cmdDGB.ExecuteNonQuery()Dim NumberUpdated As IntegerNumberUpdated = cmdSave.ExecuteNonQuery'lblStatus.Text = NumberUpdated.ToString'lblStatus.Text &= " Record(s) Updated."Catch ex As ExceptionlblStatus.Text = "2323 Error Updating WP_G_I: "lblStatus.Text &= ex.MessageFinally'If Not (conDGB Is Nothing) Then' conDGB.Close()'End IfEnd TrysCon1.Close() I'm guessing I need to go the route of changing the data type to an image in order to allow users to to copy/paste from word documents. Do I need to go the BLOB route? I've tried to look for KB articles, but everything talks about .bmp or .jpg files.Thanks!
View 5 Replies View RelatedI am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
View 5 Replies View Related
Is possible to get the name of the store procedure that i'm executing ?
I need to get the name of the procedure inside the procedure being executed, to insert this value on a log file...
hello all, I have a multi line textbox and want to store the content of the textbox to a database. I want to preserve the linefeeds so that I display the message as it is typed by retrieving it. Can anyone please explain how to store the linefeeds to the database. Once i store the linefeeds to the database, I can restore them using the Replace("","<br/>) method. But I can't figure out how to store the 's in the database in the first place. Thanx in advance
View 2 Replies View Relatedhi,
i would like to store images in my database and retirve them aswell. i have looked at examples on the net but i am finding it hard to follow and undertsand them, can anyone please give me adivse on how i can do this. i have alrady created my table in my database which has 4 fields pictureID, pictureContent, pictureType and pictureSize but i dont know where to go on from here. i want to do it in vb aswell.
please any help and advise would be much appreciated as i am stuck,
thank you
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.
The item is added into the db but the output param is not modified.
Here is the code:
SP
CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc )
select @intItemID = SCOPE_IDENTITY()GO
I have tried in the last line of the SP
select @intItemID = SCOPE_IDENTITY()
select @intItemID = @@IDENTITY
select @intItemID = max(itemid) from RR_Item
but nothing seems to work.
I'm calling the store procedure as follows from asp.net:
Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int)
intItemID.Direction = ParameterDirection.Output
SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID)
MsgBox(intItemID.Value.ToString)
Any help would be appreciated.
Thanks
i have a table called tblpictures which look something like this..
filename|ID
----------
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3
is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..
hi gurus,
how to get " using bcp
e.g
select '"'+name+'"' from sysobjects
results
"name"
"name1"
I want to get the same using bcp ,so that i can populate into a file
something like
set @cmd='BCP "select '"' +name +'"' ,uid from sysobjects" QUERYOUT "' + 'vrs.txt' + '" -w -T -S -t , '
-- Executing the BCP Command
EXEC MASTER..XP_CMDSHELL @Cmd
I€™ve inherited a project from one of the guys on our team who will be out sick for a while. He developed two for marshaling data between System.Drawing.Image and System.Byte(). He€™s storing the byte array data in a database image field.
I€™ve retrieved the byte array data from his database image fields and have successfully converted them to images using his ConvertByteArrayToImage method below. I have also converted and image to a byte array with his ConvertImageToByteArray method below and succfully stored the data in a database image field. However, when I retrieve the byte array data that I stored in the database the last line in his ConvertByteArrayToImage method throws an exception (Parameter is not valid). I€™ve not been able to find a working copy of his code that€™s storing the byte array data. Does anyone see anything I€™m overlooking?
Imports System.Drawing
Imports System.IO
Public Sub InsertImage(ByVal pFilename As String)
Try
Dim lImage As Image
Dim lBA() As Byte
Dim lSQL As String
Dim lQuery As Alcon.SQLServer.Database.clsQuery
Dim lParameters As New Alcon.SQLServer.Database.clsParameters
lImage = Image.FromFile(pFilename)
ConvertImageToByteArray(lImage, lBA)
' Initialization
lQuery = New Alcon.SQLServer.Database.clsQuery(mConnection)
lSQL = ""
lSQL += "INSERT INTO [TBL_PCL_LENS_DATA]("
lSQL += "[SerialNumber], "
lSQL += "[ProcessedDate], "
lSQL += "[CartonLabelImage]) "
lSQL += "VALUES ("
lSQL += "@SerialNumber, "
lSQL += "@ProcessedDate, "
lSQL += "@CartonLabelImage "
lSQL += ")"
lParameters.Add("@SerialNumber", SqlDbType.VarChar, ParameterDirection.Input, mSerialNumber)
lParameters.Add("@ProcessedDate", SqlDbType.DateTime, ParameterDirection.Input, Now)
lParameters.Add("@CartonLabelImage", SqlDbType.Image, ParameterDirection.Input, lBA)
' Execute query
lQuery.Execute(lSQL, lParameters)
Catch ex As Exception
Throw
End Try
End Sub
ConvertByteArrayToImage(ByVal pBA() As Byte, ByRef pImage As Image)
Try
' Declaration
Dim lMS As MemoryStream
' Initialization
lMS = New MemoryStream(pBA, 0, pBA.Length)
lMS.Write(pBA, 0, pBA.Length)
lMS.Position = 0
' Create image
pImage = Image.FromStream(lMS, True)
Catch ex As Exception
Throw
End Try
End Sub
ConvertImageToByteArray(ByVal pImage As Image, ByRef pBA() As Byte)
Try
' Declaration
Dim lBM As Bitmap
Dim lBR As BinaryReader
Dim lMS As New MemoryStream
' Initialization
lBM = New Bitmap(pImage)
lBM.Save(lMS, Imaging.ImageFormat.Png)
lBR = New BinaryReader(lMS)
' Create byte array
pBA = lBR.ReadBytes(lMS.Length)
Catch ex As Exception
Throw
End Try
End Sub
hi all
i deleted 8 fields from a table and i need to retrieve them back, is there a way to do that.
please help it is very urgent
Hello,
i need a select statement that will retrieve only the 1st 15 words of a text field.
is it possible?
Thanks.
hello to experts :)
I'm stuck and I'm getting an error message
well the following code show access a store precedure ( on MS SQL 7 ) which the sp it self does work fine (using query analizer )
if I use the following code is asp.net I'll get error
BC30451: Name 'CommandType' is not declared.
on
Line 64: objCmd.CommandType = CommandType.StoredProcedure
please, please have look as it is quite urgent.
many thanks in advance
Meghoo
------------------------------------------------------------------------------------
Function non935(ByVal city As String, ByVal feeEar As String, ByVal year As String) As System.Data.DataSet
Dim connectionString As String = "server='csfirm03'; user id='sa'; password='testfirm'; database='ken_live';pooling=false"
Dim mysqlconn As new System.Data.SqlClient.SqlConnection(connectionString)
Dim myDataReader As System.Data.SqlClient.SqlDataReader
Dim SQL As String = "sp_non935"
Dim objCmd As New System.Data.SqlClient.SqlCommand(SQL, mySqlConn)
objCmd.CommandType = CommandType.StoredProcedure
Dim objParam As System.Data.SqlClient.SqlParameter
objParam = objCmd.Parameters.Add("@city", SqlDbType.VarChar)
objParam.Value = @city
objParam = objCmd.Parameters.Add("@trec", SqlDbType.Int)
objParam.Value = @feeEar
objParam = objCmd.Parameters.Add("@year", SqlDbType.VarChar)
objParam.Value = @year
mySqlConn.Open()
myDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
'Do stuff with the data reader
myDataReader.Close()
mySqlConn.Close()
myDataReader = Nothing
mySqlConn = Nothing
Return dataSet
End Function
Sub year_SelectedIndexChanged(sender As Object, e As EventArgs)
datagrid2.datasource=non935(city.SelectedItem.Value,trec.text,year.SelectedItem.Value)
datagrid2.databind()
End Sub
How can I use a store procedure in a asp.net. I have been trying many ways but it shows an error : "Invalid attempt to read when no data is present." when I am trying to read the data reader
My store procedure just return one value.
StoreProcedure:
create procedure sp_recordstudent
@studentid integer
as
if exists (select * from tbl_student where int_studentid = @studentid)
select strregistro = 'true'
else
select strregistro = 'false'
go
ASP.net
Dim Conn As New SqlConnection("server='(local)';database='BSF';trusted_connection=true")
Conn.open()
Dim Comm as SqlCommand = Conn.createcommand()
Comm.connection = conn
Dim Trans as SQLTransaction
Dim Query as String = ""
Dim DRStudent as SQLDataReader
'Verify whether the student exists
Query = "execute sp_recordstudent @studentid = " & intSId
Comm = New SQLCommand(query,conn)
'Comm.commandtext =
DRStudent = comm.executereader()
blstudentexist = DRStudent.getstring(o)
DRStudent.close()
I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)
dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00
dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)
[code]....
Hello!
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
S102 | Def of S102
Tab2
DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
FROM Tab2
WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers
Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SubjectCodeDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DepartmentReturn]
GO
CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
SELECT 'S100', 'Definition of Code S100' UNION ALL
SELECT 'S101', 'Definition of Code S101' UNION ALL
SELECT 'S102', 'Definition of Code S102' UNION ALL
SELECT 'S103', 'Definition of Code S103' UNION ALL
SELECT 'S104', 'Definition of Code S104' UNION ALL
SELECT 'S105', 'Definition of Code S105'
GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
SELECT 1,'P100','S100,S101,S104' UNION ALL
SELECT 1,'P101','S102,S103' UNION ALL
SELECT 1,'P102','S100,S101,S105' UNION ALL
SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
SELECT 2,'P103','S103,S104,S105' UNION ALL
SELECT 3,'P100','S100,S102,S104'
GO
SELECT SubjectCode ,Definition
FROM SubjectCodeDefinition
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
FROM DepartmentReturn
WHERE DeptID = 1 AND PurposeCode = 'P102')
i am working on a uni project based on orkut and is having dfficulty in storing and retrieving images..
can anybody please guide me...
thanx
Can i call tigger in store procdure and vice versa??
if yes how it ?????????
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
View 2 Replies View RelatedIn Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
Hi,
does anyone know a way, using native SQL, to store a result of a query in an image field of a certain table.
The case is we have a selfmade replication to communicate with several SQL servers in stores, this replication is over a telephone line. So we collect all the data using SQL statements and store them in a separate table as an image field. This is done know through a Delphi application that streams the resultset to a image field.
Thanks in advance,
I need some store procedure script , from two table i want to pickup matching field and insert into main table ,give some sample store procedure coding...
If 1st table as cheque no and 2nd table as cheque no - matching then data would be inserted in to main table using two table...
I am creating a document management systems using asp. I have beenresearching the different ways of handling the documents such as using thefile system and storing the path in the db, and actually storing thedocument in the db. I like the idea of storing it in the database muchbetter because I can allow users to manage documents themselves (I alreadyhave the code in place to do it if I decide), having a central system withthe ability to add my own document properties by adding fields to the table,security, and backups. I have found that most think it is better to storethe path due to performance issues and the rate the db can grow. I havelooked at our current system in access and we have a total of 4400 documents(of which probably 25% are in the database but don't actually exist anymorein the file system, one hangup about the file system) since 1988. Thiscomes to about 300 documents added each year. The other thing is the issuewith the size of the db. I don't see a whole lot of difference with thisissue because it is going to take up space in your file system too, althoughthe file system may be more efficient at storing them. I would say that 95%of our docs are under 1 mb in size and done in ms word.The last thing is using full-text search capabilities in SQL Server. I needto be able to search the contents of the field.Is there other issues around storing documents in the db to consider besidesthe above?
View 1 Replies View RelatedHello,
In my application, I have a master table that stores information about some other user tables. These other tables are all of different types, that is, the number and type of columns need not match across the user tables.
From the application perspective, logically each of these user tables is nothing but a column field within the master table.
I have seen references about "table" data type in SQL Server. It is possible to create a table that actually has a table data type as its field?
Thank you in advance for your help.
Regards,
Peter
Hi - I'm using Session variables to store information (sort of webshop).
The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.
I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?
I'm using SQL Server 2000.
Thanks for any help,
Mark
hi can anyone tell how to use the image field and add an image in a database. i'm using visual studio web developer express edition 2008 and i want people who visit my website to be able to see the table and the images associated with some of the rows in the table
Thanx Taryn
I have a 'notes' field. Entries in there are time-stamped as well. so lines
looks sort of below...
01/01/2001 10:00 am
Called him. Sent him some info.
03/01/2001 2:00 pm
Nice guy. Fun talking
etc
Is there a SQL function/way by which I can grab entries from 'notes' field by date ?
Thanks a lot in advance.
Derek
hi all,
i am having a problem. In my table i have defined a logtime field as a datatype datetime. Now i want to query the table on a particular date which is not possilbe i supposed. so how can i break the datetime field into date field. Is there any method.... I don't want to create the table once again.
manish
I need to expand the size of one column which has been defined as varchar(32) to varchar(50).Is this possible?Already there are many old records in the table,in what way it will effect the old records?Any help is appreciated.
Thanks!!