I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.
This is what I have:
<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @Password) AND (users.username = @Username)"
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query. Can Someone tell me where is the problem? Thanks
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
I use select @@identity to return @@identity from my store procedure,but I could not retrive it from my Visual basic code, like variable=oRS.fields.item(0).value, it always says item can not be found....
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Hi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically? <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
Hello, inside of my SP i want to execute another SP, something like: EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns). Columns it returns:QResult , Threads , Answers SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID ? or how do you get it? Patrick
I have a table called Image1 and i have stored the image in SQL server 2005 with a feiled called picture
table name ---- Image1 field-- picture (data type image) please let me know the code step by step code how to Retrive Images from SqlServer in ASP .NET webpage, please help me ....
hai everybody!!!! am developing one application in visual 2005..and sql server. so how to write the coding for searching one record from a database table according to the id..and to display it
hai everybody!!!!! am working wirh visual 2005 and sql server()asp.net).....so i need the coding in VB for searching one record from a database table according to the id..and want to display it.....
This is DataSet retriving code from IBUYSPY Events Module.
public DataSet GetEvents(int moduleId) {
// Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlDataAdapter myCommand = new SqlDataAdapter("GetEvents", myConnection);
// Mark the Command as a SPROC myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC SqlParameter parameterModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4); parameterModuleId.Value = moduleId; myCommand.SelectCommand.Parameters.Add(parameterModuleId);
// Create and Fill the DataSet DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet);
// Return the DataSet return myDataSet; }
My Question is
1) Is this ideal way to retrive data? 2) Will it return connection back to pool?
Hi, I am new to using SQL. Currently, I'm using the following statemens to retrive a specific record from my MS Access DB via VB.net.
SELECT * FROM table_name WHERE Field_Name = Criteria
Can someone please tell me, after selecting this record, If I want to go to the FIRST, or NEXT or PREVIOUS of the record just retrived or the LAST record. Can someone please tell me how can write the SQL statment to achieve this ?
Code Block SELECT tce.TimeCardID, tce.TimeCardExpenseID, tc.DateCreated, e.LoginID, e.FirstName + ' ' + e.LastName AS FullName, tce.ExpenseAmount, tce.ExpenseDescription, op.ProjectName, op.ProjectDescription, ec.ExpenseCode FROM OPS_TimeCards tc JOIN OPS_Employees e ON e.EmployeeID = tc.EmployeeID JOIN OPS_TimeCardExpenses tce ON tc.TimeCardID = tce.TimeCardID Join OPS_Projects op ON op.ProjectID = tce.ProjectID Join OPS_ExpenseCodes ec ON ec.ExpenseCodeID = tce.ExpenseCodeID WHERE e.LoginID = 'jross' ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID 1 2 1 3 1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
hi I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem?????? I am really looking forward your answers private void retrive() { public void a() { SqlConnection connection = new SqlConnection("Some Connection string"); SqlCommand command = new SqlCommand("Select * from temp", connection); // Writes the BLOB to a file FileStream stream; // Streams the BLOB to the FileStream object. BinaryWriter writer; // Size of the BLOB buffer. int bufferSize = 50; // The BLOB byte[] buffer to be filled by GetBytes. byte[] outByte = new byte[bufferSize]; // The bytes returned from GetBytes. long retval; // The starting position in the BLOB output. long startIndex = 0; // Open the connection and read data into the DataReader. connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess); while (reader.Read()) { // Create a file to hold the output. stream = new FileStream( "C:\file.txt", FileMode.OpenOrCreate, FileAccess.Write); writer = new BinaryWriter(stream); // Reset the starting byte for the new BLOB. startIndex = 0; // Read bytes into outByte[] and retain the number of bytes returned. retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize); // Continue while there are bytes beyond the size of the buffer. while (retval == bufferSize) { writer.Write(outByte); writer.Flush(); // Reposition start index to end of last buffer and fill buffer. startIndex += bufferSize; retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize); } // Write the remaining buffer. if (retval != 0) writer.Write(outByte, 0, (int)retval - 1); writer.Flush(); // Close the output file. writer.Close(); stream.Close(); } // Close the reader and the connection. reader.Close(); connection.Close(); } }
hai In my web application i want to bind data from sql 2005 to ultrawebgrid, when i use source code dim cmdselect as sqlcommand dim cmdstring as string cmdstring="select name, datefieldname from tablename" cmdselect=new sqlcommand(cmdstring,connectionstring) connectionstring.open() ultrawebgrid1.datasource=cmdselect.executenonquery() ultrawebgrid1.databind() connectionstring.close() ----- when i execute above coding i am geting date and time displayed there, but i want to display date alone --my datefield datatype is datetime - i am using sql 2005,(vs2005-vb/asp.net) please help me thanks in advance
I have a locally installed MS SQL server 2000. I am trying to retrieve the contents of the table onto a datagrid. But when I use the user name and password, It gives the following errorSystem.Data.SqlClient.SqlException: Login failed for user 'aroop'. Reason: Not associated with a trusted SQL Server connection. string connectionString = "server=(local); uid=aroop; pwd=abcdef; database=mydb"; string commandString = "select * from mytable"; SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); dgEmployees.DataSource = dataSet; dgEmployees.DataBind();Can someone please let me know what to do about this?
hello dear friends I am trying to save a binary file to database with the following code: public static void memorystreamToDb() { MemoryStream mst = new MemoryStream(); UnicodeEncoding u = new UnicodeEncoding(); string Textn = "Test"; byte[] b = u.GetBytes(Textn); mst.Write(b ,0,Textn.Length ); BinaryReader reader = new BinaryReader(mst); byte[] file = reader.ReadBytes((int)mst.Length); using (SqlConnection connection = new SqlConnection("Some Connection String")) { SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection); command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file; connection.Open(); command.ExecuteNonQuery(); } reader.Close(); mst.Close(); } or with the other method from a real file (not memory stream) public static void Addfile(string path) { CommonMethods_class k = new CommonMethods_class(); byte[] file = GetFile(path); using (SqlConnection connection = new SqlConnection(k.Get_connection_string())) { SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection); command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file; connection.Open(); command.ExecuteNonQuery(); } } and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want. looking forward your points thank you in advance
I am using FileUpload method in tools box and i want to store the uploaded image into database. but when debuging it will shows an error like: Operand type clash: sql_variant is incompatible with image this is the code for "upload" button.Protected Sub uploadbtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadbtn.Click If FileUpload1.HasFile Then TryFileUpload1.SaveAs("C: emp" & FileUpload1.FileName) Label1.Text = "File name: " & FileUpload1.PostedFile.FileName & "<br>" & "File Size: " & FileUpload1.PostedFile.ContentLength & "kb<br>" & "Content Type: " & FileUpload1.PostedFile.ContentTypeCatch ex As Exception Label1.Text = "ERROR: " & ex.Message.ToString End Try Else Label1.Text = "You have not specified a file" End If Try SqlDataSource1.Insert() Label2.Text = "picture is saved"Catch ex As Exception Label2.Text = ex.Message.ToString End Try End Sub
