Database Display/manipulation
May 24, 2007
I 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
ADVERTISEMENT
May 4, 2004
Hi everyone!
I recently found out that using ADO to connect to the SQL server without mentionning the data source; then, by sending a query with the following syntax: dbname.owner.tablename.columnname... an implicit connection to the data source (database) is performed automatically. This way of connecting allowed me to manipulate accross two or more databases thing that is necessary in my project.
My question is:
1- Do anyone have any bad experience and/or negative consequence to such connectivity (memory consumption, unexpected disconnection, ...)? Please consider a very high frequency of manipulation since we are dealing here with a 24/24 hour operational site.
2- Are there any alternative solutions for cross database manipulation (select, insert, update, delete)?
Thanks
View 2 Replies
View Related
Jan 9, 2008
Hi
I'm used to DTS but new to SSIS. What's a good reference/tutorial that deals with transforming columns of data (from a flat file) from one format to another when uploading into SQL2005? Typically columns of data have "" around the values and spaces that I want to remove.
Presumably in SSIS I need the following:
A Data flow task containing:
Flat file source
Derived or Copy Column?
OLE DB Destination
Is this on the right track?
Thanks
Gerry
View 1 Replies
View Related
Mar 6, 2006
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. :-)
View 14 Replies
View Related
Oct 10, 2006
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 Related
Dec 19, 2006
Hi, I would like to display the TABLE_NAME variable where the table.ID =@IDSo 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
View 10 Replies
View Related
May 21, 2008
Hi,
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>
View 2 Replies
View Related
Feb 23, 2008
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
View 3 Replies
View Related
Mar 19, 2008
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 Related
Mar 28, 2008
I 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 Related
Sep 28, 2004
I 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?
View 1 Replies
View Related
Oct 13, 2006
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...
View 3 Replies
View Related
Apr 11, 2008
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 ..
View 5 Replies
View Related
Mar 12, 2007
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
View 1 Replies
View Related
Jan 21, 2008
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
View 2 Replies
View Related
May 25, 2008
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.
View 9 Replies
View Related
Oct 4, 2013
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
Jan 24, 2008
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")
View 1 Replies
View Related
Aug 16, 2007
Hi
In SQL Server 2000 & 2005
How to Display list of Tables & S.Ps & Views in a Database?
Please advise
Thanks
View 4 Replies
View Related
Apr 30, 2015
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
View 3 Replies
View Related
Sep 22, 2007
(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
View 6 Replies
View Related
Mar 7, 2008
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.
View 1 Replies
View Related
Apr 6, 2015
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] .....
View 2 Replies
View Related
May 29, 2008
col1 col2 col3
1IN NULL
2FOR 1
3Small 4
4World 1
5Hj 1
6NJ 1
7Welcome 1
8So 4
9We 1
10Are 1
11Resp 3
12OIJIOJPOJPJO7
13OIJOIJ 1
14LKJ 2
15IJ 1
Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1IN NULL
2FOR1
2nd set would look something like this
3Small 4
4World1
5Hj 1
6NJ 1
7Welcome1
3rd set
8So4
9We1
10Are1
4th Set
12OIJIOJPOJPJO7
13OIJOIJ1
and the last set
14LKJ2
15IJ1
Any help is appreciated!!
View 1 Replies
View Related
Jan 25, 2007
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
View 1 Replies
View Related
Jan 31, 2008
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
View 3 Replies
View Related
Mar 1, 2007
Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.
I loop through that data to load a check list which also works wonderfully.
two questions based on that.
1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.
2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)
sorry for the fairly basic question. i appreciate the help.
Justin
View 2 Replies
View Related
Apr 2, 2001
I am totaly confused please help
I am trying to change a tring of 7 characters of the format "XXYZZZZ" to be
"XX0YZZZZ" I wonder if any body has any idea
Also how can get an out of a dattime field in the format of DDMMYYYY and converted into text.
You help is highly appreciated
View 1 Replies
View Related
May 15, 2001
Hi!
I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:
CANADA - Toronto
United States- ARIZONA
France
Argentina
United States (USA)- ARIZONA
........
........
The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:
Server: Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
QUERY
-----
select
Left(
Destinations.[Destination Name],
charindex("-", Destinations.[Destination Name])-1
)
as test
into temp2
from destinations
CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).
View 1 Replies
View Related
Feb 3, 2003
Hi,
I have a table that has about 12 fields and 700000 records. My client is going to send me 4 fields out of the 12 fields (1 of them will be primary key) in a text format. What is the best way I can get them updated in the table?
Please help!!
Thanks!!
View 1 Replies
View Related
Nov 30, 2004
I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname
crab,mike,Allen
Lota Weilly,Eric,M
My script to do this looks like
update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (longname, patindex( '%,%' , longname) + 1, patindex( '% %', longname)-patindex( '%,%' , longname)),
middle_name = substring (longname, patindex( '% %', longname) + 1, len(longname)-patindex( '%,%' , longname))
My problem is that some people actually have 2 last names, not hyphenated, but 2. Whenever I have 2 names I get the following error
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
It seems to be related to the first name, I can comment out that update and it works
Thanks
Thanks
View 11 Replies
View Related
Jul 24, 2007
Hi All,
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
Any help is appreciated.
Thanks.
View 2 Replies
View Related
Nov 13, 2005
Field1 = Dominguez Public Transport Division 03 9320 4326
how do i remove these strings in T-SQL
Field1 = Dominguez
Field2 = Public Transport Division
Field3 = 03 9320 4326
View 3 Replies
View Related
