In a nutshell, I am selecting values from a checked box column in a grid view. After checking the boxes I have a function that concatenates each customer number into a string called @CusList to use a parameter for a stored procedure Public Function SelectCusList()
Dim CusShow As String
Dim CusStr As String
Dim CusList As String
CusStr = ""
[Code]...
The store procedure which give me the information I need is:
USE [UCSMIS]
GO
SET ANSI_NULLS ON
[Code].....
When I try to add it to my report it gives an error, is it because of the temp table and how should I go about it?
I have a small question about stored procedures and the DateTime2 datatype in SQL Server 2008.
I got several stored procedures using the DateTime2 datatype and I want to insert the date 0001.01.01 00:00:00 but this won't work with VB.net and I cannot find the reason why.
I use this code fragment:
Dim sqlStatement As New SqlClient.SqlCommand
Dim sqlTransaction As SqlClient.SqlTransaction
sqlStatement.CommandType = CommandType.StoredProcedure
[Code]....
to call the stored procedures from my program (the stored procedure is a simple INSERT statement nothing else and works fine with actual dates). But when I enter the date 0001.01.01 00:00:00 it always comes up with the error that I cannot insert dates before '01.01.1753' when I want to execute the stored procedure.
Now I already know that the DateTime2 datatype in SQL Server should support this.
So my question is it possible that this is a driver problem and updating the SQLClient would solve this, or is this a general problem and I can finally stop searching and just use 1753.01.01.
project, but have decided to take another direction on it, after much and continued struggling.I started out trying to use data binding, but was advised to use straight SQL statements in the code. So what I'm trying to do now: I have 2 forms, frm1 and frm2. frm1 has a combobox, which is showing all the data fine from a table, and an OK button. Upon clicking on this button, I need frm2 to load, correctly populating its textboxes and comboboxes with the corresponding data, based on the selection made on frm1. This is what I have so far:
View 14 RepliesI know there are the 'Wizards' to help me, but I need the code to access the Table Manager, the data set and so on, create them in my project and access the tables or stored procedures
View 2 RepliesCan't seem to figure out why this is not working.
Dim Myspcmd As New SqlCommand("spDataImport", myConnection)
Myspcmd.CommandType = CommandType.StoredProcedure
Myspcmd.Parameters.Add(New SqlParameter("@FileName", Data.SqlDbType.NVarChar, 100)).Direction = ParameterDirection.Input
Myspcmd.Parameters("@FileName").Value = "Import_File.dtsx"
myConnection.Open()
[Code]...
I am confused about how to pass parameters to a stored procedure. The stored procedure is written in sql server 2005 and the code is in vb.net.
I have included the sub that I am working on. I know that my use of parameters is wrong.
Public Sub AddRecipe()
Using myDBconnection As New SqlConnection(setConnstring)
Using SQLcmd As New SqlCommand()
[Code]....
This may not be the place to ask this, if not, you can move this post to the SQL forum but I have an application that I'm building which pulls dates from a database. The start value is stored as payPeriodStart date and I add 7 days to get the end date. What I need to know is, is it possible to store those values and pass them to a Stored Procedure? Currently my stored procedures have "static" dates in them ie:
where dateadd (n, Timestamp, '12/31/1899') - ([LoggedIn]/1000)/60/1440+1 Between '10/3/2010' and '10/10/2010'
and I'd like those values to be
Between payPeriodStartDate and payPeriodEndDate.
I have an application that I'm building which pulls dates from a database. The start value is stored as payPeriodStart date and I add 7 days to get the end date. What I need to know is, is it possible to store those values and pass them to a Stored Procedure? Currently my stored procedures have "static" dates in them ie:
[Code]...
I am struggling with trying to pass values to a stored procedure, I am creating new records on my table by the values are not passing to the table.Here is my persistance class.
Public Function createDMRIssue(ByVal ItemNumber As String, ByVal IssueStatus As String, _
ByVal ProblemDescription As String, ByVal p_AuditResults As String, _
ByVal DMRDate As String, ByVal p_IssueCategory As String, _
ByVal p_DMRInitiator As String, ByVal p_QtyUnacceptable As Integer, _
ByVal p_ProductType As String, ByVal p_OrderNumberm_dmrissue As String)
Me.Connection()
[Code]...
I have an application that I'm building which pulls dates from a database. The start value is stored as payPeriodStart date and I add 7 days to get the end date. What I need to know is, is it possible to store those values and pass them to a Stored Procedure? Currently my stored procedures have "static" dates in them i.e.:
where dateadd (n, Timestamp, '12/31/1899') - ([LoggedIn]/1000)/60/1440+1 Between '10/3/2010' and '10/10/2010'
Here is my code for the form that will be passing the variables payPeriodStartDate and payPeriodEndDate to the stored procedure:
Code:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
"dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday]" & _
"from dbo.payroll" & _
[Code] .....
And lastly since I'm calling those variables payPeriodStartDate and payPeriodEndDate, I assume that my sql query will have to change as follows:
where dateadd (n, Timestamp, '12/31/1899') - ([LoggedIn]/1000)/60/1440+1 Between payPeriodStartDate and payPeriodEndDate
Is that correct?
I need to update a table with values from a listbox. I am trying to create as many parameters as there are items in the list, my code below, but how do i name them differently? so that they dont' overwrite each other?
For Each item As ListItem In ris
Dim pID As New SqlParameter("@userid", SqlDbType.Int)
pID.Value = objFormat.CheckSQL(item.Value)
[Code]....
and on the SQL side, is it possible to write an update statement, that will take a dynamic number of parameters?
so for example, i need to update multiple users with the same value in the "active" field...
I would like to create a stored procedure that will call any webservice by passing to the stored procedure the webservice name/url and its parameters. I would like this instead of adding web refference each webservice one by one.
View 7 RepliesI have three items in my checkbox list, what I want to do is for the user to select either one of them in any combination and pass these values to my sql server stored procedure which then returns data according to the values passed to it in the where clause and binds this data to a gridview. However, at the moment in my sp I have three I parameters, in the case of the user selecting one item from the list not enough arguments will have been passed to the procedure. I'm not sure how to go about doing this
View 2 RepliesI am trying to pass some declared variables to a stored procedure. Here is my
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
[Code].....
I have been creating some asp.net sites that connect to an AS400 for data and I use a SQLDataSource for this with the following connection string (changed for security reasons). Now I need to create a winform application in VB.NET and am wondering how I would do this? I have only used MSSQL in winforms and use the sqldatareader for Select statements and execute non-query for others. I need to call some stored procedures on the AS400 passing parameters. I was hoping to do this by code like I do with MSSQL rather than drop in controls to bind to.
Provider=IBMDASQL.DataSource.1;Data Source=192.168.100.2;Password=xxxxx;User ID=xxxxx;Initial Catalog=S12345;Default Collection=AABBCC
my issue could be found simple but I'm founding it difficults in regard of dealing with array concept in oracle and vb.netI will start by showing to you a code done in oracle side1-this script should be executed in hr schema2-create a table and call it job_testas
create table job_test as
select * from jobs
where 1=2
[code].....
i have a stored procedure to insert data into table create procedure insertdata
[Code]...
I have a radgrid bound to a SqlDataSource that includes a hidden, readonly column that stores a pk. I want to pass the value bound to that column to a stored procedure for Updates but the default behavior when the column is readonly is not to pass the parameter to the sqlDataSource. My question is whether there is a way to pass that value without having to go into the code behind. Here is a snippet of my asp markup.
<telerik:RadGrid ID="rgEmployees" runat="server" AutoGenerateColumns="False"
DataSourceID="sdsEmployees" GridLines="None" AllowAutomaticUpdates="true" AllowAutomaticDeletes="true">
[Code]....
If I set readonly="false" on the It works fine but then the user can edit the primary key value which is not desired. I know work arounds in code behind but is there any way to do it straight in the markup?
VB Imports System.Data.SqlClient Public Class frmMain
[Code]...
I have three ways of adding parameters to my query commented out above. The all seem to return 1781 records (which is all of the records) and will not filter out to what the UserName variable equals which should reduce it to 32 rows.
I use a Console.WriteLine to show that the UserName is indeed my username. But it seems to not be getting passed to the query properly. I tested the Stored Procedure before even starting in on the program, so I know it functions properly. I just don't know what I'm missing from a VB.NET perspective to get the same results into my DataTable.
I am using the following stored procedure to call period numbers to match them to teachers.We are writing an attendance taking application where upon selecting the teacher's name from a dropdown menu, the "period" dropdown menu populates. For some reason we are not getting the period data to populate.
Stored Procedure:
ALTER PROCEDURE spGetPeriod @Period varchar(10)
AS
SELECT Period
FROM tmTeacher
[Code]...
So I want to erase the guid @value from the database and retrieve the return value from my stored procedure [code]object name 'value' not valid..Net SqlClient Data Provider0.
View 4 RepliesSTORED PROCEDURE
ALTER Procedure [dbo].[usp_validatecard](
@CLUBCARD1 nvarchar(50),
@STATUS nvarchar(50))[code]....
What I am trying to do is if the clubcard parameter is found and status is active I would like the label to read 'card is good' also if the clubcard parameter is found however is inactive then the label should read 'card is good but not active' last if all those fail just have the label read 'card not in system'As of right now this code does not work I thought I could just call the stored procedure and exec it and it would print the statements depending on what passes however it is erroring out. When I debug it the error I get is under the cmd.parameters.add is
Item In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
I want to execute a sql server stored procedure in vb using a button. The stored procedure as one variable that is read from a text file.I got it to read the variable from the text file but don't know how to execute the stored procedure.
View 1 RepliesThis is my Stored Procedure Code to get the customer mobile value:[code]I was try to get this value into lblGetCustMobile (Label Control) using this [code]but nothing happened, can any one make this code get the mobile NO. into lblGetCustMobile?
View 3 RepliesI am using vs 2008 doing a winforms app in vb.net. I have a database with a single table, that has two columns, in it and a stored procedure. The stored procedure is supposed to take an input value, match that value against one column in the table and then return the corresponding value from the other column; except it doesnt.It returns 1 or 0
ALTER PROCEDURE dbo.getgamenumber(@outputnumber bigint OUTPUT,
@inputnumber bigint)
AS
SELECT @outputnumber = ggnumber[code]......
but I still dont get the value I expect.
This is a bit old-contents to be discussed but I need someone who can explain me how to create stored procedure in SQL Server for returning a value from procedure, example:[code]Then I need the name of its customer and the address instead.I need to make it as a stored procedure and show me how to execute it on VB.NET. Perhaps we assume that the name will be prompted to LABEL1.TEXT and the address will be prompted to LABEL2.TEXT. I've improved this SQL-Server Stored Procedure Using return but I have nothing to return after I execute it.[code]
View 1 RepliesCreated an XSD file that represents my table structure from my database. Created a class file to hold my functions such as the one below.
Code sample:
Public Function GetUser(ByVal UserID As String) As xsdUser.UserDataTable
Dim SqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString)
[Code]....
Now it seems odd to me to have two files doing what should be contained within just one file. I feel like I should have one class file called "User" and have member variables in there along with the class functions like the one above. But all the tutorials I see are using these data tables. On top of that I'm not sure how to handle the if statement at the end of the function. I don't think I would want to return a whole DataTable since I should only have one row, and I also don't know how I would handle an error if my function is supposed to return a data table. I suppose I could return an empty one and then if the table is empty in my code then I would handle it there, but this also seems messy.
Is this the common way of retrieving information from a stored procedure? Or is there a more up-to-date method that I'm not seeing in my search results?
I have a problem trying to link my my datagrid view to my sql server stored procedure. The way it should work is that when I select an item in a list from my combo box it returns a set of rows on the datagrid view specific only to that item This is the code on the server sideCREATE PROCEDURE spCompanySearch
[Code]...
The @comp is supposed to be each item in the combobox list but I don't know how to do it or how to go about it
I am using sql server 2008 as database server. I have created "AddTest" stored Procedure and using it in my app,
Dim cn As new SqlConnection(connStr)
Dim da As New SqlDataAdapter("Select * from dbo.Subjects", cn)
Dim ds As DataSet
[code]....