Hi,
I would like to display the TABLE_NAME variable where the table.ID =@ID
So far I got here:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME.ID = @ID)
but the last part AND (TABLE_NAME.ID = @ID) does not work.
Thanks,
dg
Hi guys,
I want to insert record to the table, the table name is a variable. I was tried like this,
declare @Table_Name as varchar(255)
declare @MM as varchar(2)
declare @YY as varchar(4)
set @MM = month(getdate())
set @YY = year(getdate())
set @Table_Name = 'tb_XXXX_' + @MM + @YY
INSERT INTO @Table_Name (column1,....) VALUES (@Column1,...)
If I print the @Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message
'Must declare the sclar variable @Table_Name'
How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.
Arun.
I need to pass a table_Name to a SELECT statement. This doesn't work.
DECLARE @variable
SET @variable
SELECT *
FROM @variable
Bill
I have a cursor looping through a list of tables that I want totruncate and then do a bulk insert into, Is this possible in SQLServer 2000 or do script each table individually.Cheers,Adam
View 2 Replies View RelatedHi,
In create table statements we use database_name.table_name.
Eg :
create table my_database.test_table(sno int .....)
What is the meaning of this ? Is that, the table is created in the specified database_name ?
Please clarify.
Thanks,
Sam
HI,
I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-
CREATE PROCEDURE MY_PROC
AS
DECLARE FETCHER CURSOR SCROLL
FOR
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
OPEN FETCHER
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FETCHER
CLOSE FETCHER
WHICH IS WORKING FINE WHEN I EXECUTE :-
EXEC MY_PROC
BUT I WANT TO PASS BOTH TABLE_NAMES AND COL_NAMES AS PARAMETERS :-
LIKE :-
EXEC MY_PROC [TABLE_NAME],[COL_NAME{1.....n}]
SO THAT I CAN CHANGE BOTH THE TABLE_NAME AND COL_NAME
HOW TO DO THIS ?
tHIS IS ONE MORE PROBLEM THAT I AM FACING.PLS HELP
tHANKS.
Hello. I've been searching for a couple of hours online and can't quite seem to find what I'm looking for. I've found plenty of database administration programs, but that's not what I need.
I'm looking for a program (or online service or whatever) that has the ability to basically take any database I choose and display the information within on a web page in which others can access. Such as, if my company wants to take on a few other company's databases and have them log in to our server through the web to display information from their (and only their) database dynamically on the web, then this program allows me to set all of that up. Information could be formatted in tables, charts, etc., whatever the program is capable of. Is there such a thing and where can I find more information?
Thanks for everyone's help. :-)
I've always bound data to a dataset...how do I display data without a dataSet? This is how i usually display data... SqlConnection myConnection = new SqlConnection(conn); string sqlQuery = "SELECT * FROM questions"; SqlDataAdapter myCommand = new SqlDataAdapter(sqlQuery, myConnection); DataSet ds = new DataSet(); myCommand.Fill(ds, "questions"); MyDataGrid.DataSource = ds.Tables["questions"].DefaultView; MyDataGrid.DataBind(); <%# DataBinder.Eval(Container.DataItem, "answer3")%>
View 1 Replies View RelatedI have a web page called homepage.aspx created in Visual Studio 2005 using VB. It contains some hyperlinks on the left side. I have a database created in SQL Server 2005. I need to add, delete, edit, update data in the database directly from this same page. How can I achieve this?. i.e is it possible to, say, have a form on a section of this same web page that would allow me to directly manipulate/modify and update the database? (If you may give code, you may use abstract names for the database, tables and column and/or field names-I will understand). Many thanks in advance.
View 3 Replies View RelatedHi,
How can I extract an XML from my data base and display it on reporting services?
I want to make a list of team (it's already do) and a link to a report wich display the XML
But I don't know how can I display this XML into a table.
Database Design:
[Table=FootTeam]
_ id : bigint
_ nameTeam : nvarchar
_ playerInfo : xml
DataBase Table
1 | "Mancherter" | "<team><player><name>Mick All</name><postion>Defense</postion></player><player><name>Pete Kolo</name><postion>Atack</postion></player></team>"
2 | "Liverpool" | "<team><player><name>John Back</name><postion>Defense</postion></player><player><name>Alex Pol</name><postion>Atack</postion></player></team>"
Example of a XML:
<team>
<player>
<name>Mick All</name>
<postion>Defense</postion>
</player>
<player>
<name>Pete Kolo</name>
<postion>Atack</postion>
</player>
</team>
I'm trying to do a search but it seems like it couldnt get the text in my text book and run the command. Can anyone help me here?Function searchBook(ByVal booking_id As Integer) As System.Data.DataSet
Dim search As Integer = txtSearch.Text
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Speedo'"Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [booking].* FROM [booking] WHERE [booking].[booking_id] LIKE '%1%'"Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnectionDim dbParam_booking_id As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_booking_id.ParameterName = "@booking_id"
dbParam_booking_id.Value = booking_id
dbParam_booking_id.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_booking_id)Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommandDim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)Return dataSet
End Function
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
' If Not Page.IsPostBack Then
'Dim search As String = txtSearch.Text
'MsgBox(search)
If ddSearchCat.SelectedValue = "booking" ThenMsgBox("you have selected booking")
GVSearch.DataSource = searchBook(txtSearch.Text)
GVSearch.DataBind()
ElseIf ddSearchCat.SelectedValue = "Log" ThenMsgBox("you have selected Log")
GVSearch.DataSource = searchLog(txtSearch.Text)
GVSearch.DataBind()
ElseIf ddSearchCat.SelectedValue = "UserName" ThenMsgBox("you have selected UserName")
GVSearch.DataSource = searchUserName(txtSearch.Text)MsgBox("HELLO IN GRID")
GVSearch.DataBind()
GVSearch.Visible = TrueMsgBox("out of grid")
End If
'End If
End Sub
I have a webform that I am wanting to display data from a database. Right now I am using the gridview but for future purposes when it comes to maintenance what would be the easiest way to do updates? For example, if I add a new record into the database I would like the webform to automatically update itself to show all the records including the new record I added. Do I need to use a "table" and somehow connect it to a database? Do any stored procedures need to be created? Suggestions/ideas/codes help help help would be verrrry much appreciated!!! Also I am using MS Server 2003 and C# as the programming language. Thank you!!!!
View 9 Replies View RelatedI have a tabe in my sql server 2005 database which contains a long description and I want to display the first 100 charactors on my web page but not sure how to do this I can only display the whole description. I'm not sure if I need to write the query to the database so it only brings the first 100 charactors back or query the whole description and then take the first 100 charactors in asp.net and display that can any one help
View 3 Replies View RelatedI have the following sql that retrieves the file from the database:
SqlConnection connection = new SqlConnection (ConnectionString);
SqlCommand command = new SqlCommand ("get_document", connection);
command.CommandType = CommandType.StoredProcedure;
What do I need to do to actually display this pdf file?
I am using the osql utility to access my sql server express edition.
My problem is that i don't know how many database is in the sql server so can any teach me how to list out all the name of the database in the sql server using a sql statment?
Oh, and can anyone teach me how i can attach and detach a database in the sql server using sql statment.
Thanks for the help...
Hi,
I searched in the previous posting but can't find any or miss them. We have a few databases that users can run reports from, i.e. production, training & development. Is there any way, say put it in either in the header or footer, to display from which database that the report are running from? Sometimes they got confused whether the data is current or like few days old.
Any input is very much appreciated. Thanks in advance ..
Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn
Below is code for inserting data into the database that I know works, so I thought if I replace "Insert" with "Select" I could retrieve a specific row from my database and present it in a label or text box but nothing happened. How can I retrieve a row or rows from a database using vb code and display it in a textbox? I'm using visual web developer 2008 which is similar to 2005.
Thank you.
Dim UserDatasource As New SqlDataSourceUserDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ToString()
UserDatasource.InsertCommandType = SqlDataSourceCommandType.Text
UserDatasource.InsertCommand = "insert into table1 (Username, comments, points,totalpoints, ipaddress, datatimestamp) values (@Username, @comments, @points,@totalpoints, @ipaddress, @datatimestamp)"
UserDatasource.InsertParameters.Add("username", Context.User.Identity.Name)
UserDatasource.InsertParameters.Add("comments", txtSearch.Text)UserDatasource.InsertParameters.Add("points", points)
UserDatasource.InsertParameters.Add("ipaddress", Request.UserHostAddress)UserDatasource.InsertParameters.Add("datatimestamp", DateTime.Now())
Dim rowaffected As Integer = 0
Try
rowaffected = UserDatasource.Insert()MsgBox("Thanks for the post", MsgBoxStyle.OkOnly, "Post Executed")
txtSearch.Text = ""Catch ex As Exception
MsgBox("Please sign in or sign up to post comment", MsgBoxStyle.OkOnly, "Login Error")
End Try
after i login, i should be able to see the login person's profile, and edit it. anyone know how to do this?currently i am using grid view to display the profile, and created a store procedure in database ALTER PROCEDURE [dbo].[select_customer_by_login] -- Add the parameters for the stored procedure here(@Username nvarchar(50))AS SELECT C.CustomerName, C.TFN, C.Address, C.City, C.State, C.PostCode, C.Phone From dbo.Login L, dbo.Customer C Where L.CustomerID = C.CustomerID and L.UserID=@Username; @Username is the login account when people login. the error output is Procedure or Function 'select_customer_by_login' expects parameter
'@Username', which was not supplied.
In our organization we have fixed two weeks menu. On our intranet i have database entries with two weeeks menu without dates. I want first six entries to appear in one week and next six entries to appear in another week. How can i achieve this with SQL query.
View 17 Replies View Related
hi friends
i am using visual studio 2005. i need one combo box which will display two values. one is "Designation" i.e.("clerk,accountant,manager,officer,shopkeeper) and another "Designation ID" i.e.(1,2,3,4,5 and respectively) i made table of "Designation Master" in sql server 2000 including field of "Designation ID, Designation". manually i am inserting id and designation in that table.
what i want exactly i have one customer form which contain a "combobox" for "Designation" column i achieved "Designation ID" and "Designation" in that combo box also but when i will go for save i want that i will store only "Designation ID" i don't want to store "Designation" in my "Designation" column which is in "Customer table" i am attaching code which store "Designation ID" and "Designation" in customer table but i want only "Designation ID" only
bold line which is difficult for me to inserting only "designationId" in customer table.
Inventory_Master.cntData.Open()
cmdstr = "Select DesigID,Designation from Designation_Master"
cmd = New SqlCommand(cmdstr, Inventory_Master.cntData)
cdrCustomer = cmd.ExecuteReader()
If cdrCustomer.HasRows = True Then
While cdrCustomer.Read()
cmddesig = cdrCustomer.Item(0).ToString.PadLeft(1) + cdrCustomer.Item(1).ToString.PadLeft(25)
cbodesignation.Items.Add(cmddesig)
End While
Else
End If
Inventory_Master.cntData.Close()
Inventory_Master.cntData.Open()
cmd = New SqlCommand("insert CUSTOMER(Membership_No,Member_Initial,Member_Name,Member_Birthdate,Gender,Res_Telno,Mobile_No,Res_Addr1,Res_Addr2,Res_City,Res_Pincode,Landmark,Company_Name,Comp_Addr1,Comp_Addr2,Comp_Telno,Comp_City,Comp_Pincode,Designation,Credit_Limit,Income_Mthly,Email_ID,Note) values (@Membership_No,@Member_Initial,@Member_Name,@Member_Birthdate,@Gender,@Res_Telno,@Mobile_No,@Res_Addr1,@Res_Addr2,@Res_City,@Res_Pincode,@Landmark,@Company_Name,@Comp_Addr1,@Comp_Addr2,@Comp_Telno,@Comp_City,@Comp_Pincode,@Designation,Convert(money,@creditlimit),@Income_Mthly,@Email_ID,@Note)", Inventory_Master.cntData)
cmd.Parameters.AddWithValue("@Membership_No", txtmebno.Text)
cmd.Parameters.AddWithValue("@Member_Initial", cbomebname.Text)
cmd.Parameters.AddWithValue("@Member_Name", txtmebname.Text)
cmd.Parameters.AddWithValue("@Member_Birthdate", dtime.Value)
cmd.Parameters.AddWithValue("@Gender", cbogender.Text)
cmd.Parameters.AddWithValue("@Res_Telno", txttelno.Text)
cmd.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text)
cmd.Parameters.AddWithValue("@Res_Addr1", txtresaddr1.Text)
cmd.Parameters.AddWithValue("@Res_Addr2", txtresaddr2.Text)
cmd.Parameters.AddWithValue("@Res_City", txtrescity.Text)
cmd.Parameters.AddWithValue("@Res_Pincode", txtrespin.Text)
cmd.Parameters.AddWithValue("@Landmark", txtlandmark.Text)
cmd.Parameters.AddWithValue("@Company_Name", txtcompname.Text)
cmd.Parameters.AddWithValue("@Comp_Addr1", txtcompaddr1.Text)
cmd.Parameters.AddWithValue("@Comp_Addr2", txtcompaddr2.Text)
cmd.Parameters.AddWithValue("@Comp_Telno", txtcomptelno.Text)
cmd.Parameters.AddWithValue("@Comp_City", txtcompcity.Text)
cmd.Parameters.AddWithValue("@Comp_Pincode", txtcomppin.Text)
cmd.Parameters.AddWithValue("@Designation", cbodesignation.Text.PadLeft(1, CChar(CStr(1)))) [ help me for inserting designation in this line]
cmd.Parameters.AddWithValue("@creditlimit", txtcreditlimit.Text)
cmd.Parameters.AddWithValue("@Income_Mthly", txtincomemthly.Text)
cmd.Parameters.AddWithValue("@Email_ID", txtemailid.Text)
cmd.Parameters.AddWithValue("@Note", rtxtNote.Text)
cmd.ExecuteNonQuery()
Inventory_Master.cntData.Close()
MsgBox("RECORD SAVED")
Hi
In SQL Server 2000 & 2005
How to Display list of Tables & S.Ps & Views in a Database?
Please advise
Thanks
I would like to display a portion of report where there is data or no data
There is data subreport  display  Â
   Product Name Latex Gloves Â
   Product ID   Â
xxxx5678
 There NO data in the subReport
Â
  Product Name             Â
  Product ID  Â
(I moved this thread from datagrid area) I have a sql database that has individual records consisting of name, date, hours worked among other fields.Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date. My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.TABLE:dan 12/13/2012 12:00:00 AM9.123dan 12/14/2012 12:00:00 AM3.123123cara 12/12/2012 12:00:00 AM4.222cara 12/16/2012 12:00:00 AM3.3333cara 12/17/2012 12:00:00 AM2 CODE: Select distinct(name), (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday, (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Wednesday')as Wednesday, (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Thursday')as Thursday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday, (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Saturday')as Saturday,(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Totalfrom dbo.testtime YTgroup by date, name, hours RESULTS: cara NULL NULL NULL 4.222 NULL NULL NULL 4.222cara NULL 2 NULL NULL NULL NULL NULL 2cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123dan NULL NULL NULL NULL 9.123 NULL NULL 9.123 Like I said, I am SO close, I just need it to look like;NAME SUN MIN TU WED TH FR SA TOTAL cara 3.333 2 4.222 9.555
dan 9.125 3.125 12.5 TIAdan
Here is the Stored procedure
ALTER procedure [dbo].[ActAuditInfo](@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) output
)asbegindeclare @AuidtID as varchar(30)Select @IndustryName=Industry_Name from Industry whereIndustry.Ind_Id_PK =(Select Audit_Industry from Audits whereAd_ID_PK=@AuidtID)Select @CompanyName=Company_Name from Company whereCompany.Cmp_ID_PK =(Select Audit_Company from Audits whereAd_ID_PK=@AuidtID)Select @PlantName=Plant_Name from Plant where Plant.Pl_ID_PK=(Select Audit_Plant from Audits where Ad_ID_PK=@AuidtID)Select @GroupName=Groups_Name from Groups whereGroups.G_ID_PK =(Select Audit_Group from Audits whereAd_ID_PK=@AuidtID)Select @UserName=Login_Uname from RegistrationDetails whereRegistrationDetails.UID_PK =(Select Audit_Created_By fromAudits where Ad_ID_PK=@AuidtID)SELECT Ad_ID_PK, Audit_Name, @IndustryName, @CompanyName, @PlantName,@GroupName, Audit_Started_On, Audit_Scheduledto, @UserName FROMAudits where Audit_Status='Active'end
U can see here different parameters,my requirement is that iam havingID's of Industry,company,plant,group,username stored in a table calledPcra_Audits and i must display their related names in the front end.so this is the query iam using for that.
Data in the database:Commercial83312 2 2 2 1 1 InactiveHere u can see 2,2,2,1,1 these are the IDs ofindustry,company,plant,group and username and Commercial83312 is tehaudit ID.now i want to display this data in teh front end as i cannot displaythe IDs i am retrieving the names of the particular IDs from therelated tables.Like iam getting name of the IndustryID from Industry Table,in thesame way others too.when iam running this procedure iam getting the gridview blank.iam passing the output parameters:@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) outputinto the function in the frontend and iam calling that into the pageload method.please help me with this.
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
Hi all,
Can anyone give me a ready-made sample script to display a search reslut from a database?
My intention is to give a dynamic web page for the search in the BookStore website.
The result will show the following:-
First the picture of the book.
To the right of it, 'Title' field, below that, 'description' field
It should be created programatically as the contents are in the database.
I tried several ways, but could not succeed. I have the BookID, Title, BookPicture, and the Description fields
Thanking you in advance,
Tomy
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
ByVal strCompany As String, _
ByVal decSerial As Decimal, _
ByVal strSerialCode As String, _
ByVal strSerialScan As String, _
ByVal decMasterSerialNumber As Decimal, _
ByVal strCustomerPart As String, _
ByVal strTakataPart As String, _
ByVal strCustomerRanNo As String, _
ByVal strCustomerAbv As String, _
ByVal strDestinationAbv As String, _
ByVal decQty As Decimal, _
ByVal strCreatDate As String, _
ByVal decVoidSerialNo As Decimal, _
ByVal strProductionLineNo As String, _
ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe
Dim strResult As Integer = 0
Dim strErrorText As String = String.Empty
Dim dsData As New DataSet
Dim parameter(15) As OleDbParameter
If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
' Populate parameter collection
parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))
RunDB2Sp("FABLE.MAP", parameter, dsData)
If dsData.Tables.Count > 0 Then
dsData.Tables(0).TableName = "Supreeth"
Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here
SqlContext.Pipe.Send(bitresult)
SqlContext.Pipe.Send("No errors")
End If
Else
Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
'*********************************************
' Declare Variables
'*********************************************
Dim daAdaptor As OleDbDataAdapter
Dim cmdAS400 As OleDbCommand
'Dim dstestMe As New DataSet
Try
cmdAS400 = CreateCommand(strProcedure, parms)
daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set
daAdaptor.Fill(dsData)
Catch expError As OleDbException
daAdaptor = Nothing
Finally
daAdaptor = Nothing
cmdAS400.Dispose()
'Me.Close()
End Try
End Sub
Public Shared Function CreateParameter(ByVal name As String, _
ByVal type As OleDbType, _
ByVal size As Integer, _
ByVal direction As ParameterDirection, _
ByVal paramValue As Object) As OleDbParameter
Dim param As OleDbParameter = New OleDbParameter
param.ParameterName = name
param.OleDbType = type
param.Size = size
param.Direction = direction
param.Value = paramValue
Return param
End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
Dim CmdSAS400 As OleDbCommand
Dim parameter As OleDbParameter
Dim connAS400 As OleDbConnection
connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
connAS400.Open()
CmdSAS400 = connAS400.CreateCommand()
CmdSAS400.CommandText = strProcedure
CmdSAS400.CommandType = CommandType.StoredProcedure
CmdSAS400.Parameters.Clear()
'CmdAS400.CommandTimeout = intTimeOut
If (prams Is Nothing) Then
Else
For Each parameter In prams
CmdSAS400.Parameters.Add(parameter)
Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
Thanks
I have a database field which consists of a long string of values with some separator.
Ex:Â Injection^!@$#Medication
in my report i have to split the string as
Injection
Medication
till here i am able to display the results but in addition to that i have to display check box against these values like below
and this string can contain any no of values ex: if we have 4 values are separated within a string then i have to display 4 check boxes against 4 results.
This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1
Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:
As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:
http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon
I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.
Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter
'Consider using Me._adapter that is used already
CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()
'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then
MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If
Try
Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try
'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Dim rows As Integer
rows = 0
Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)
CN.Open()
rows = CMD.ExecuteNonQuery
If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Thanks for reviewing my code.
Technozoide
Hi All,
I am not sure whether this is the right place to post this question. But I am unable to figure out what is the best solution to retrieve and display an image in a html file(stored in varbinary(max) column). I have a list of images in the file and I am supposed to display them. Can anybody please let me know what is the best way to do this?
Thanks a lot!!
I am new to accessing sql stuff and asp.net I have some code in a .cs page that accesses a sql database and displays the information on a page from top to bottom (press releases: www.managewatch.com/press). I created this site but I used the code from the old site and just implemented it. I'd like to have the press releases display from newest on top to oldest on bottom. Is there a way I can do this? I have included the code in my cs page that does this: protected void Page_Load(object sender, EventArgs e) { // connect to db SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["xxxx"].ConnectionString); db.Open(); // Initialize Objects SqlCommand cmd = new SqlCommand(); cmd.Connection = db; SqlDataReader row; // COUNT PAGES ######################################## cmd.CommandText = "SELECT * FROM press"; row = cmd.ExecuteReader(); while (row.Read()) { Literal pressReleaseTitle = new Literal(); Literal pressReleaseBody = new Literal(); pressReleaseTitle.Text = "<div id='pressheader'>" + row["title"].ToString() + "</div>"; pressReleaseBody.Text = "<div id='pressbody'>" + row["content"].ToString() + "</div><br>"; lblPressReleases.Controls.Add(pressReleaseTitle); lblPressReleases.Controls.Add(pressReleaseBody); } row.Close(); db.Close(); } I can see what is happening here "cmd.CommandText = "SELECT * FROM press";" tells it to get all the info from "press" but I just don't get the " row = cmd.ExecuteReader();" part. Is this just grabbing one line of the database? what does this code do? Thanks!
View 5 Replies View RelatedHi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());