retrieve Datetime: form

Showing posts with label form. Show all posts

Monday, March 26, 2012

retriving gender from database in a radio button

i am storing gender in the database.
i want to retrive it in one of the radiobuttons for male and female already present on the form . how can i?

related radio button should be highlighted while clicking on the button........

Shubhada

Wednesday, March 21, 2012

retrieving specific page(number of rows) form table

hi,

i need SP that receive 2 integers ,@.NUM_ROWS and @.PAGE_NUMBER,
and return the rows in that page.
for example:

SP(4,2) will return 4 rows in page number 2 .

So if i have table with 9 rows i will get rows 5-8,
the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.

i have to assume that rows can be deleted form that table.
thanksYou have to have a (preferably unique) column or set of columns to order the data consistently each call. Do you have an incrementing identity field or datetime stamp?|||i have the PK of the table, but i have to assume some records have been deleted.
so i can not assume i have perfectly order column|||Not necessary.
declare @.NUM_ROWS int
declare @.PAGE_NUMBER int

select [YourTable].*
from [YourTable]
inner join --PageRows
(select [PKey],
count(*) as RowNum
from [YourTable]
inner join [YourTable] Ordinal on [YourTable].[PKey] >= Ordinal.[PKey]
having count(*) between (@.PAGE_NUMBER * @.NUM_ROWS) + 1 and (@.PAGE_NUMBER + 1) * @.NUM_ROWS) PageRows
on [YourTable].[Pkey] = PageRows.Pkey

Monday, March 12, 2012

Retrieving Binary Image from SQL

i am trying to retrieve an image blob stored in SQL to a web matrix form (using VB.NET). I am totaly new to this and i got the code below from a site but it doesn't see to work....

<%@. Page Language="VB" %>

<%@. import Namespace="System.Data" %>

<%@. import Namespace="System.IO" %>

<%@. import Namespace="System.Drawing.Image" %>

<%@. import Namespace="System.Drawing.Bitmap" %>

<%@. import Namespace="System.Drawing.Graphics" %>

<%@. import Namespace="System.Web.UI.WebControls" %>

<%@. import Namespace="System.Data.Odbc" %>

<%@. import Namespace="System.Data.SqlClient" %>

Private Function GetImageFromDB(ByVal ImageID As Integer) As System.Drawing.Image

' Get the Connection string

Dim strConnection As String

strConnection = "server='(local)'; trusted_connection=true; database='mydatabasename'"

Dim conn As New SqlConnection(strConnection)

Dim sqlCommand As String

Dim strImage As String

Dim image As System.Drawing.Image

Try

sqlCommand = "SELECT ImageField FROM ImageTable WHERE MyImageId = " + ImageID.ToString

Dim cmd As New SqlCommand(sqlCommand)

cmd.Connection = conn

conn.Open()

Dim dr As SqlDataReader

dr = cmd.ExecuteReader()

While (dr.Read())

Dim byt As Byte()

byt = dr.Item(strImage)

Dim bmp As New System.Drawing.Bitmap(New System.IO.MemoryStream(byt))

image = bmp

End While

Catch ex As Exception

' Log Error

Response.Write(ex.Message)

Finally

If conn.State = ConnectionState.Open Then

conn.Close()

End If

End Try

Return image

End Function

Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs)

If Not IsPostBack Then

GetImageFromDB(2111)

End If

End Sub

</script>

<html>

<head>

</head>

<body>

<asp:Image id="BrandSignImg" runat="server"></asp:Image>

</form></body></html>

You can not send two different content types with a single http request. ie Html and Image data.
You will need a HttpHandler for serving images out, or you can use a special .aspx page, here is a link to get your started.
http://www.sadeveloper.net/Forums/ShowPost.aspx?PostID=52636
bill

Wednesday, March 7, 2012

Retrieve Values from SQL Server into Winforms

Hi all,

I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this? I have followed this question at http://www.vbforums.com/showthread.php?t=357605 ,but I havent had an answer to my question yet. Please help, I am stuck and can't go on with my application until I have figured this out.

Thanks alot in advance

Rudi Groenewald

You can use various SQL Server Functions for this (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp).

Here's an example:

select serverproperty('ProductVersion') as ProductVersion
serverproperty('Edition') as Edition;

On my computer this returns:

Product Version Edition
-
9.00.1116 Express Edition

Hope this helps,
Josh Lindenmuth

Retrieve the ID of the record just added

I would like to retreive the identity field value of a record that was just added to the table.

In other words, I have a form and on submission, if it is a new record, I would like to know the identity value that the SQL Server has assigned it.

This may be overkill, but here is my code to process the form:

Protected Sub processForm(ByVal thisID As String, ByVal myAction As String)
Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
sqlConn.Open()
Dim sql As String
Select Case myAction
Case "save"
If thisID > 0 Then
sql = "update INCIDENT set " & _
"RegionID = @.RegionID, " & _
"DistrictID = @.DistrictID, " & _
"DateReported = @.DateReported, " & _
..CODE...
"WHERE IncidentID = " & myIncidentID
Else
sql = "insert into INCIDENT(" & _
"RegionID, " & _
"DistrictID, " & _
"DateReported, " & _
...CODE...
") " & _
"values(" & _
"@.RegionID, " & _
"@.DistrictID, " & _
"@.DateReported, " & _
...CODE...
")"
End If
Case "delete"
sql = "delete from INCIDENT where IncidentID = " & myIncidentID
Case Else
End Select

Dim sqlComm As New SqlCommand(sql, sqlConn)
sqlComm.Parameters.Add(New SqlParameter("@.RegionID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@.DistrictID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@.DateReported", SqlDbType.NVarChar))
...CODE...

sqlComm.Parameters.Item("@.RegionID").Value = ddRegionID.SelectedValue
sqlComm.Parameters.Item("@.DistrictID").Value = ddDistrictID.SelectedValue
sqlComm.Parameters.Item("@.DateReported").Value = db.handleDate(txtDateReported.SelectedDate)
...CODE...

Dim myError As Int16 = sqlComm.ExecuteNonQuery
'Response.Redirect("incident.aspx?id=" & )
End Sub

The response.redirect at the end of the sub is where I would like to put the identity field value.

This has been a popular question over the past 2 days.Check out these posts, you should find what you need:415207 and416141

____________________________________________________________________

Updated hyperlinks on January 9, 2006

|||Thanks! That did it.|||

I have the same problem but those posts are no longer there. Are you able to help me out?

I couldn't work it out so I created a date/time field called StartDate for when each record is entered. I was trying to use that to grab the particular eventID.

Dim EventID =""

tbEventIDTest.Text =""

Dim EventDataSource1AsNew SqlDataSource()

EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString

EventDataSource1.SelectCommandType = SqlDataSourceCommandType.Text

EventDataSource1.SelectCommand ="SELECT EventID FROM Event "WHERE ([StartDate] = @.StartDate)"

EventID = EventDataSource1.SelectParameters.Item(EventID)

tbEventIDTest.Text = EventID

Thanks, any help will be appreciated.

|||

Call SCOPE_IDENTITY() to retrieve Identity.

Take a look atRetrieving Identity or Autonumber Valuesfor sample code.

|||thanks alot|||

gevans:
I have the same problem but those posts are no longer there.

Sorry I'm so late to reply. I've now updated that post so that the hyperlinks work; the old links only worked with the older version of the software used here.

Saturday, February 25, 2012

Retrieve Identity Field Value from a stored procedure into a form

OK first time poster, so hello everyone in advance. Right i'm sure this is a simple problem to solve, but I'm just getting the hang of SQL 2000. What I've got is a form where I input the values and then through a procedure these values are inserted into a table. That's fine. However I now need to open a subform which is linked by the ID field created through the first procedure. How do I retrieve that value back into the form??

The procedure code is

CREATE PROCEDURE InsertFamilyDetails

@.CarerIDvarChar(6),
@.FamilyNamevarChar(30),
@.Address1varChar(30),
@.Address2varChar(30),
@.Address3varChar(30),
@.PostCodeMain varChar(4),
@.PostCodeSubvarChar(30),
@.PhoneNovarChar(16),
@.LocalTransport varChar(200),
@.Leisure varChar(200),
@.School varChar(200),
@.RulesvarChar(250),
@.Resultint OUTPUT

DECLARE @.FamilyID int;

BEGIN TRANSACTION

-- Insert New Family

SELECT @.FamilyID=@.@.Identity

INSERT INTO tblWfsFamilyDetails

(intCarerID,txtFamilyName,txtAddress1,txtAddress2, txtAddress3,txtPostCodeMain,txtPostCodeSub,txtPhon eNo,memoLocalTransport, memoLeisure, memoSchool,memoRules)

VALUES

(@.CarerID,@.FamilyName,@.Address1,@.Address2,@.Address 3,@.PostCodeMain,@.PostCodeSub,@.PhoneNo,@.LocalTransp ort,@.Leisure,@.School,@.Rules)

IF @.RESULT<1

BEGIN

ROLLBACK TRANSACTION;

RETURN -1

END

SET @.RESULT=1

COMMIT TRANSACTION
GO

Whereas, the VB Code is

Private Sub cmdInsert_Click()
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insertFamilyDetails"

' FieldNames Assigned to FormControls

cmd.Parameters.Append cmd.CreateParameter("CarerID", adInteger, adParamInput, 6, txtCarerID)
cmd.Parameters.Append cmd.CreateParameter("FamilyName", adVarChar, adParamInput, 30, txtFamilyName)
cmd.Parameters.Append cmd.CreateParameter("Address1", adVarChar, adParamInput, 30, txtAddress1)
cmd.Parameters.Append cmd.CreateParameter("Address2", adVarChar, adParamInput, 30, txtAddress2)
cmd.Parameters.Append cmd.CreateParameter("Address3", adVarChar, adParamInput, 30, txtAddress3)
cmd.Parameters.Append cmd.CreateParameter("PostCodeMain", adVarChar, adParamInput, 4, txtPostCodeMain)
cmd.Parameters.Append cmd.CreateParameter("PostCodeSub", adVarChar, adParamInput, 4, txtPostCodeSub)
cmd.Parameters.Append cmd.CreateParameter("PhoneNo", adVarChar, adParamInput, 12, txtPhoneNo)
cmd.Parameters.Append cmd.CreateParameter("LocalTransport", adVarChar, adParamInput, 200, memoLocalTransport)
cmd.Parameters.Append cmd.CreateParameter("Leisure", adVarChar, adParamInput, 200, memoLeisure)
cmd.Parameters.Append cmd.CreateParameter("School", adVarChar, adParamInput, 200, memoSchool)
cmd.Parameters.Append cmd.CreateParameter("Rules", adVarChar, adParamInput, 200, memoRules)
cmd.Parameters.Append cmd.CreateParameter("Result", adInteger, adParamOutput) ' OutPut Returns Result Parameter a Value

cmd.Execute

Res = cmd("Result")

If (Res = 1) Then
MsgBox "Family Inserted Successfully", , "Insert Family"
End If

Set cmd.ActiveConnection = Nothing

End Sub

Apologise for wasting your time if this something obvious, but like I say I'm learning.pretty simple - in your stored procedure immediately after the line values(....) do this: set @.retval = @.@.IDENTITY

that will automatically put the new ID into the variable @.retval. Then you can do
Set @.RESULT = @.retval|||

Quote:

Originally Posted by scripto

pretty simple - in your stored procedure immediately after the line values(....) do this: set @.retval = @.@.IDENTITY

that will automatically put the new ID into the variable @.retval. Then you can do
Set @.RESULT = @.retval

scope_identity() function is a better choice than @.@.IDENTITY as there might be some triggers defined on tblWfsFamilyDetails table.

retrieve Datetime