SqlDataAdapter && SqlParameter
Mar 26, 2007
Hi I am writing an app in flash which needs to hook up to MS SQL via asp.
I need the code below to pass the var (ptodaysDate) to the sql statement. I can hard code it in and it works great but I really need to pass the var from flash.
Pulling my hair out here, not much left to go.
Any help greatly appreciated.
----------------------------------------------
[WebMethod]
public Schedule[] getSchedule(int ptodaysDate)
{
SqlDataAdapter adpt =
new SqlDataAdapter("SELECT scheduleID, roomName, eventType,unitName,groupName,staffName,staffName2,theDate,theEnd FROM tb_schedule Where theDate >= @rtodaysDate", connString);
SqlParameter rtodaysDate = new SqlParameter("@rtodaysDate", ptodaysDate);
DataSet ds = new DataSet();
ArrayList al = new ArrayList();
adpt.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
Schedule obj = new Schedule();
obj.scheduleID = (int)row["scheduleID"];
obj.roomName = (string)row["roomName"];
obj.eventType = (string)row["eventType"];
obj.unitName = (string)row["unitName"];
obj.groupName = (string)row["groupName"];
obj.staffName = (string)row["staffName"];
obj.staffName2 = (string)row["staffName2"];
obj.theDate = (string)row["theDate"];
obj.theEnd = (string)row["theEnd"];
al.Add(obj);
}
Schedule[] outArray = (Schedule[])al.ToArray(typeof(Schedule));
return outArray;
}
public class Schedule
{
public int scheduleID;
public string roomName;
public string eventType;
public string unitName;
public string groupName;
public string staffName;
public string staffName2;
public string theDate;
public string theEnd;
}
View 2 Replies
ADVERTISEMENT
Jun 16, 2006
I'm using parameters to record into database like:
SqlParameter paramId_empresa = new SqlParameter("@Id_empresa", SqlDbType.Int, 4);
paramId_empresa.Value = idEmpresa;
myCommand.Parameters.Add(paramId_empresa);
And set sql server field to allow null values and default value as (null)
Trouble is I'm getting an Input string was not in a correct format. when the field is blank
Line 182:Line 183: myConnection.Open();Line 184: myCommand.ExecuteNonQuery();Line 185: myConnection.Close();Line 186: }
Thanks a lot for any help
View 1 Replies
View Related
Jun 20, 2006
I am unable to set the value of my SqlParameter unless it is to a string.sqlCmd.Parameters.Add('@myParameter', SqlDbType.Bit).Value := false;
Setting it to false or 0 results in error: "Incompatible types: 'Object' and 'Boolean' (or 'Integer')"
I've tried to cast them as objects and that didn't work. I don't understand why something like 'false' (as a string) will.
FYI: I'm using Delphi.NET and hating it.
View 1 Replies
View Related
Feb 13, 2007
Im trying to execute the following:mySqlCmd.Parameters.Add("@Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID); However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value? Thanks
View 1 Replies
View Related
Feb 21, 2008
The FULL System.Data.SqlDbType reference seems unnecessary? Is there something I am doing wrong or something that should be added to Web.Config? If I try ONLY ",SqlDbType," it will not compile?
MY CODE: SqlParameter AName;
AName = myCommand.Parameters.Add("@AName",System.Data.SqlDbType.VarChar,50);
View 2 Replies
View Related
Jul 10, 2004
Hi,
I am getting the following error:
The SqlParameter with ParameterName '@pk' is already contained by another SqlParameterCollection
If I could work out what code to post I would, but I can say that I am managing my Sql data in my code by caching small arrays of SqlParameter objects as pulled from the database. If I need to update the DB I change the cached SqlParameter and re-insert it. If I perform a SELECT I check the cache first to see if I already have the SqlParameter.
However, currently, I am experiencing the above error when performing a select, then later an update, followed by another update.
Would I be correct in saying that a SqlParameter object can only be used once for a database operation and should be discarded? Would I be correct if I said that the SqlCommand object should be discarded? I am barking up the wrong tree entirely?
Distressed in DBLand,
Matt.
View 2 Replies
View Related
Aug 5, 2004
Is it possible to have a SqlParameter setup as an Output, and get it to return @@Identity without using a stored procedure?
I have an INSERT statement, written as TEXT in my code (at the time being, I cannot create stored procedures). I'm trying to find out how to return the created IDENTITY that was generated.
Can someone please explain to me how this works?
Thanks.
View 1 Replies
View Related
Nov 28, 2007
hi
I am creating a stroed procedure which suppose to update an image column of the given row. I have created the same procedures before which worked fine with texts and numbers but not with Images.
this procedure get table name,row Id, column name of the image, and image data. like:
Create PROCEDURE [dbo].[spImageUpd]
(
@TableName varchar(100),
@Id int,
@ImageColName nvarchar(100),
@ImageData image
)
begin
??????
end
I tryed to use
set @sql='update '+ @TableName+ ' set '+@ImageColName + '=' + @ImageDt' + ' where Id='+ cast(@Id as nvarchar(10))
EXEC sp_executesql
@query = @sql,
@params = N'@ImageDt">N'@ImageDt Image',
@ImageDt = @ImageData
but sp_executesql just accept text as params. is it possible to fix this problem?
thank you in advance
regards
View 3 Replies
View Related
Aug 2, 2006
I am just learning ASP.net, been reading over data grids but I am having a tough time understanding what the parameter line would be used for. I have read the MSDN regarding parameters, they seem to make sense. But the whole System.Byte(0) I cannot understand.
sqlUpdateCommand1 = new SqlCommand();
sqlUpdateCommand1.Parameters.Add(new SqlParameter("@Original_SSN", SqlDbType.VarChar, 11, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "SSN", DataRowVersion.Original, null));
Any pointers or tutorials regarding this is much appreciated.
Thanks,
Rishi Dhupar
View 1 Replies
View Related
Feb 6, 2005
Below I have pasted the error message, hidden form fields, function code and even the stored procedure code. I'd be most grateful if anyone can show me the error of my ways!
Thanks,
theSpike
I'm calling a stored procedure and besides several varchar fields, I'm passing two integer values (employeeID and businessID). These values come from hidden fields on a form and it's the businessID that throws the format error. I call the same stored procedure for both insert and update (the SP evaluates employeeID to decide btwn insert/update) and the insert works perfectly, but the update throws the format exception error. The only difference is that the employeeID is 0 (zero) for an insert and > 0 when it's updating. I strikes me as particularly bizzare that the line that throws the exception is the businessID, not the employeeID because the businessID is the same value for both insert and update. It's the employeeID that is subject to change.
The error:
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 147: //businessID
Line 148: SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);
Line 149: businessID.Value = Convert.ToInt32(hdnBusinessID.Value);
Line 150: businessID.Direction = ParameterDirection.Input;
Line 151: theCommand.Parameters.Add(businessID);
I'm calling the SAME function for the insert and update, the same hiddent form fields exist
<input name="hdnBusinessID" id="hdnBusinessID" type="hidden" value="1" />
<input name="hdnEmployeeID" id="hdnEmployeeID" type="hidden" value="7" />
Here's the function that get's called:
void btnAddEmployee_Click(object sender, EventArgs e) {
int intNewEmployeeID;
string connectionstring = ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand theCommand = new SqlCommand();
theCommand.CommandText = "AMS_EmployeeSave";
theCommand.Connection = new SqlConnection(connectionstring);
theCommand.CommandType = CommandType.StoredProcedure;
// Add parameters to SqlCommand
//employeeID
SqlParameter empID = new SqlParameter("@employeeID",SqlDbType.Int,4);
empID.Value = Convert.ToInt32(hdnEmployeeID.Value);
empID.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(empID);
//businessID
SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);
businessID.Value = Convert.ToInt32(hdnBusinessID.Value);
businessID.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(businessID);
//title
SqlParameter title = new SqlParameter("@title",SqlDbType.Char,5);
title.Value = txtTitle.Text.ToString();
title.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(title);
//firstname
SqlParameter firstName = new SqlParameter("@firstName",SqlDbType.VarChar,25);
firstName.Value = txtFirstName.Text.ToString();
firstName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(firstName);
//middleName
SqlParameter middleName = new SqlParameter("@middleName",SqlDbType.VarChar,15);
middleName.Value = txtMiddleName.Text.ToString();
middleName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(middleName);
//LastName
SqlParameter lastName = new SqlParameter("@lastName",SqlDbType.VarChar,25);
lastName.Value = txtLastName.Text.ToString();
lastName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(lastName);
//suffix
SqlParameter suffix = new SqlParameter("@suffix",SqlDbType.Char,5);
suffix.Value = txtSuffix.Text.ToString();
suffix.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(suffix);
//email
SqlParameter email = new SqlParameter("@email",SqlDbType.VarChar,80);
email.Value = txtEmail.Text.ToString();
email.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(email);
//password
SqlParameter password = new SqlParameter("@password",SqlDbType.Char,20);
password.Value = txtPassword.Text.ToString();
password.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(password);
//newEmployeeID output parameter
SqlParameter newEmployeeID = new SqlParameter("@newEmployeeID",SqlDbType.Int,4);
newEmployeeID.Direction = ParameterDirection.Output;
theCommand.Parameters.Add(newEmployeeID);
theCommand.Connection.Open();
theCommand.ExecuteNonQuery();
intNewEmployeeID = Convert.ToInt16(theCommand.Parameters["@newEmployeeID"].Value);
theCommand.Connection.Close();
if(intNewEmployeeID == -1){
lblSaveResult.Text = "There was a problem saving the employee information";
}
else if(hdnEmployeeID.Value == "0"){
lblSaveResult.Text = "New employee successfully added";
}
else{
lblSaveResult.Text = "Employee successfully updated";
}
lblSaveResult.Visible = true;
}
STORED PROCEDURE
CREATE PROCEDURE [dbo].[xxxx]
(@employeeID int,
@businessID int,
@title char(5),
@firstName varchar(25),
@middleName varchar(15),
@lastName varchar(25),
@suffix char(5),
@email varchar(80),
@password char(20),
@newEmployeeID int output)
AS
declare @alreadyExists int
-- passed in ID = 0 means they're adding a new employee so do insert
if @employeeID = 0
begin
select @alreadyExists = count(*) from dbo.AMS_employee where emp_email = @email
if @alreadyExists = 0
begin
insert into AMS_employee (emp_businessID, emp_title, emp_firstName, emp_middleName, emp_lastName, emp_suffix, emp_email, emp_password)
values (@businessID, @title, @firstName, @middleName, @lastName, @suffix, @email, @password)
select @newEmployeeID = @@identity
end
else -- there is already an employee with that email address
select @newEmployeeID = -1
end
-- passed in ID > 0 means they're updating existing employee so do update
else if @employeeID > 0
begin
update AMS_employee set
emp_title = @title,
emp_firstName = @firstName,
emp_middleName = @middleName,
emp_lastName = @lastName,
emp_suffix = @suffix,
emp_email = @email
where emp_ID = @employeeID
select @newEmployeeID = @employeeID
-- only update the password if it one was provided
if len(rtrim(@password)) > 0
begin
update AMS_employee set emp_password = @password where emp_ID = @employeeID
end
end
GO
View 2 Replies
View Related
Mar 29, 2007
Hi,
i don't know if this is the right forum to post to, but here i go:
I'm having a problem...
I need to delete several records having as criteria their pk.
The deletion is made using SqlParameter in the code and a stored procedure on server.
as far as i know, i can do this, either using a IN list:
- build and pass the list of id's from code to the sp:
delete from tbl where attachment.id in (1,2,3)
OR
- execute a simple delete several times.
So far i tried first option, but i get errors in converting the list to ints; that is what the sql engine says.
I prefer this option because i think is faster and requires less roundtrips to the sql server.
My questions are:
1. how do i build a sane IN list?
2. is it worth to use a delete in a loop? the records that must be deleted are 5 at most, per job.
1 job can have [0..5] attachments.
Thank you,
Daniel
View 2 Replies
View Related
Aug 2, 2006
We've got an app that is in final testing so we can't go around forcing SqlDbType.VarChar. Is there any way to make SqlParameter default to varchar if you don't explicitly set a type for a string value? It's bad for our db index usage because they're all varchar and nvarchar forces a convert_implicit messing up performance.
View 1 Replies
View Related
Mar 23, 2007
Stored procedure:1 ALTER PROCEDURE [dbo].[insert_DagVerslag]
2 -- Add the parameters for the stored procedure here
3 @serverID int,
4 @datum datetime,
5 @ID int OUTPUT
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11 -- Insert statements for procedure here
12 BEGIN TRANSACTION
13 INSERT Log ( serverID, datum)
14 VALUES( @serverID, @datum)
15 COMMIT TRANSACTION
16 SET @ID = @@identity
17 END
18
Method who is calling the stored procedure and causes the error1 public void AddDagVerslag(Historiek historiek)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslag";
5 SqlCommand oCmd = new SqlCommand(strSql, oConn);
6 oCmd.CommandType = CommandType.StoredProcedure;
7 oCmd.Parameters.Add(new SqlParameter("@serverID", SqlDbType.Int)).Value = historiek.ServerID;
8 oCmd.Parameters.Add(new SqlParameter("@datum", SqlDbType.DateTime)).Value = historiek.Datum;
9 oCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
10
11 oCmd.Parameters["@ID"].Direction = ParameterDirection.Output;
12
13 try
14 {
15 oConn.Open();
16 int rowsAffected = oCmd.ExecuteNonQuery();
17 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek");
18 oCmd.Parameters.Clear();
19 historiek.HistoriekID = System.Convert.ToInt32(oCmd.Parameters["@ID"].Value);
20
21 foreach (HistoriekDetail hDetail in historiek.LstHistoriekDetails)
22 {
23 AddDagVerslagCategorie(historiek.HistoriekID, hDetail);
24 }
25 }
26 catch (Exception ex)
27 {
28 throw new ApplicationException("Fout toevoegen historiek : " + ex.Message);
29 }
30 finally
31 {
32 if (oConn.State == ConnectionState.Open) oConn.Close();
33 }
34 }
Whats going wrong, i've added the ID parameter my ParameterCollection... so why cant it be found
View 5 Replies
View Related
Sep 19, 2007
Hello All,
I'm having a problem tracking down why my app is not instantiating a parameter in a sqldatasource, when I'm expecting it to do so.
When the app fails, this info is displayed:
Server Error in '/' Application.--------------------------------------------------------------------------------
An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.
Source Error:
Line 30: Private Sub SqlDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.DeletingLine 31: e.Command.Parameters.Item("@createdby").Value = CType(Membership.GetUser.ProviderUserKey, Guid)Line 32: Line 33: End Sub
Source File: ...filename... Line: 31
Stack Trace:
[IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.] System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) +713105 System.Data.Common.DbParameterCollection.get_Item(String parameterName) +7 diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:Documents and SettingsAlbertMy DocumentsVisual Studio 2005Projectsdiabetesdiabetesdiabetes
eading.aspx.vb:31 System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) +114 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +682 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +75 System.Web.UI.WebControls.FormView.HandleDelete(String commandArg) +839 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +556 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Here's the datasource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DiabetesOne %>" DeleteCommand="reading_delete" DeleteCommandType="StoredProcedure" > <DeleteParameters> <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /> <asp:Parameter Name="rid" Type="Int32" /> <asp:Parameter Name="createdby" Type="Object" /> </DeleteParameters> </asp:SqlDataSource>
Here's the beginning of the stored procedure showing the parameters:
CREATE PROCEDURE [dbo].[reading_delete] @rid int,@createdby sql_variantASset nocount on;begin try...the procedural code...
Here's debugger capture, pointing to same issue:
System.IndexOutOfRangeException was unhandled by user code Message="An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection." Source="System.Data" StackTrace: at System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) at System.Data.Common.DbParameterCollection.get_Item(String parameterName) at diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:... at System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) at System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback)
I've inspected the sqldatasourcecommandeventargs command.parameters in the deleting method during debugging and could not find the parameter in the array, although I "think" it should be because it's explicitly stated in the sqldatasource parameters declarations. I see two parameters in the array, @RETURN_VALUE and @RID, both of which are expected and coincide with the declaration in the stored procedure and the sqldatasource.
View 2 Replies
View Related
Mar 27, 2007
Hi,What I am trying to do is to get the new ID for every record is inserted into a table. I have a For...Each statement that does the loop and using the sqldatasource to so the insert. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ myConnectionString%>">
<InsertParameters>
<asp:Parameter Name="NewID" Type="int16" Direction="output" />
</InsertParameters>
</asp:SqlDataSource> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sqlSelect As String
Session("Waste_Profile_Num") = 2
Session("Waste_Profile_Num2") = 5
sqlSelect = "SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Waste_Profile_Num = " & Session("Waste_Profile_Num").ToString() & ")"
SqlDataSource1.SelectCommand = sqlSelect
Dim dv As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)
For Each dr As Data.DataRow In dv.Table.Rows
sqlSelect = "INSERT INTO Component_Profile (Waste_Profile_Num, Range, Concentration, Component_ID) "
sqlSelect &= "VALUES (" & Session("Waste_Profile_Num2").ToString() & ", @Range, @Concentration, @Component_ID); SELECT @NewID = @@Identity"
SqlDataSource1.InsertCommand = sqlSelect
'SqlDataSource1.InsertParameters.Add("Waste_Profile_Num", Session("Waste_Profile_Num2").ToString())
SqlDataSource1.InsertParameters.Add("Range", dr("Range").ToString())
SqlDataSource1.InsertParameters.Add("Concentration", dr("Concentration").ToString())
SqlDataSource1.InsertParameters.Add("Component_ID", dr("Component_ID").ToString())
SqlDataSource1.Insert()
SqlDataSource1.InsertParameters.Clear()
MsgBox(Session("NewID").ToString())
Next
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Session("NewID") = e.Command.Parameters("@NewID").Value.ToString
End Sub What I have done so far is to display the new id and I am going to use that return id later. However, the first time through the loop, I am able to get the new id but not the second time. So, I wonder, how do I every single new id each time the INSERT statement is executed?STAN
View 5 Replies
View Related
Sep 30, 2007
hi
it looks like my thread has been deleted but I keep on asking.
When trying to connect to a table i a SQL2005 database in a vb.Net webapplication I get the following error message from the wizard:
"The wizard detected the following errors when configuring the data adapter "SqlDataAdapter1".
"Check" Generated SELECT Statement
"Check" Generated Mappings
"Warning" Generated INSERT Statement
There were errors configuring the data adapter
"Warning" Generated UPDATE Statement
There were errors configuring the data adapter
"Warning" Generated DELETE Statement
There were errors configuring the data adapter"
The same connection works fine in a vb6 application. I have seen other threads concerning this in this forum but I can't find any answers. Any ideas anyone?
ciao chris
View 2 Replies
View Related
Mar 18, 2007
Error 2 'Text' is not a member of 'System.Data.SqlClient.SqlParameter'. I think this error has something to do with the lack of an Import command, anyone point me in the right direction?
View 4 Replies
View Related
May 19, 2008
Dear All,
I have a problem while trying to update the content of my page to the database by the means of a stored procedure
string OcompConnection = ConfigurationManager.ConnectionStrings["GenUserCode"].ConnectionString;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(OcompConnection);
System.Data.SqlClient.SqlCommand ocmd = new System.Data.SqlClient.SqlCommand("Dealer_Insert", conn);ocmd.CommandType = CommandType.StoredProcedure;
ocmd.Parameters.Add(new SqlParameter("@UserCode"), SqlDbType.NVarChar);
ocmd.Parameters["@UserCode"] = TxtUserCode;
and also there is another error message saying that argument1: can not comvert from system.data.sqlclient.sqlparameter to string.
What am i Missing???
Eventually there is the try{open} and finally{close}
Many Thanks
View 3 Replies
View Related
Aug 16, 2006
Hi guys. Does anybody have any clue about how to create a class which inherits from SqlDataAdapter? I tried but looks like SqlDataAdapter is not inheritable. I would like to extend it with some custom method such as a method which fills a Dataset (used with a DataGrid) with the only few records to display per page (say 20) against the thousand records it might instead contain otherwise.Any workarounds? Many thanks in advance for your help.
View 1 Replies
View Related
Apr 25, 2007
It's a pretty basic question but I haven't been able to find any examples out there. I dimmed a dataadapter and would like to reuse later in my code (line 3 in the code below). What is the correct syntax to do this? Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)da.Fill(myDataTable)da.______ ("SELECT * FROM myTable2", conn)da.Fill(myDataTable2)
View 2 Replies
View Related
Aug 15, 2007
I'm working on a project to dynamically create PDFs with content from an SQL server. My current approach is to get the data I need into a DataSet using SqlDataAdapter, write the DataSet to a stream as XML, transform the XML into FO and then output a pdf using FOP. For some reason I get the following exception "System.NullReferenceException - Object reference not set to an instance of an object" when I try to set the SelectCommand property of my data adapter. Code for the project follows:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim con As SqlConnection Dim cmd As SqlCommand Dim IDlist As String Dim keyidary As Array Dim query As String Dim tempxml As Stream query = "*****query is valid and very long, so it's not included here*****" IDlist = Request.Form("chosenIDs") keyidary = Split(IDlist, ",") Dim makequery As New StringBuilder(query) For Each ID As String In keyidary makequery.Append(" OR (KeyID = '" & ID & "')") Next query = makequery.ToString() 'Response.Write(query) When uncommented this prints the query just fine 'Response.End() Try con = New SqlConnection con.ConnectionString = "****Connection String is valid****" cmd = New SqlCommand cmd.CommandText = query cmd.CommandType = CommandType.Text cmd.Connection = con Dim adpt As SqlDataAdapter adpt.SelectCommand = cmd 'Response.Write(query) When these are above or between the previous 2 statements, query is printed, 'Response.End() otherwise I get the error described above. Dim profiles As New DataSet adpt.Fill(profiles) profiles.WriteXml(tempxml) Dim step1 As XslTransform = New XslTransform Dim step2 As XslTransform = New XslTransform step1.Load(Server.MapPath("TransAttmpt1.xslt")) step2.Load(Server.MapPath("formatXML.xsl")) Dim xml, pdf As String xml = "profiles.xml" pdf = "Profiles.pdf" Dim temp2xml As New XPathDocument(tempxml) Response.Write(query) Response.End() Dim midstream As Stream Dim finalxml As StreamWriter = New StreamWriter(xml) step1.Transform(temp2xml, Nothing, midstream, Nothing) Dim xpathdoc2 As XPathDocument = New XPathDocument(midstream) step2.Transform(xpathdoc2, Nothing, finalxml, Nothing) GeneratePDF(xml, pdf) 'There's a lot more but it doesn't seem relevant now.... I'm somewhat at a loss on how to proceed and any help is very greatly appreciated.Thanks!
View 2 Replies
View Related
Oct 1, 2007
Hi all,
I have datatable having around 50 rows and 3 columns ID, Name and ExpVal, which is an expression columns,where the values can be any SQL functions Like REPLICATE(), SOUNDEX ( 'value' ) Or REVERSE ( 'value' ).....
i want to insert each row in that datatable like
INSERT INTO TAB1 ( ID, Name, ExpVal) VALUES (1, 'some name', SOUNDEX ( 'some name' ) )
so that the ExpVal will have value of the function ie inserted row look like
ID Name ExpVal1 some name S500 <--- Result of SOUNDEX ( 'some name' )
I'm using sqldatadapter to insert these values to the database
string sql = "INSERT INTO TAB1 (ID, Name, ExpVal)VALUES (@ID, @Name, @ExpVal) ";SqlDataAdapter sqlAdptr = new SqlDataAdapter();SqlCommand sqlCmd = new SqlCommand(sql, con);sqlCmd.parameters.Add("@ID", SqlDbType.Int, 0, "ID");sqlCmd.parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");sqlCmd.parameters.Add("@ExpVal", SqlDbType.VarChar, 100, "ExpVal");sqlAdptr.InsertCommand = sqlCmd;sqlAdptr.Update(dataTable);
This works fine, but the problem is, now the TAB1 contains
ID Name ExpVal1 some name 'SOUNDEX ( 'some name' )' instead of S500
Thatis the sql funtion is passed by SqlDataAdapter to database as a string and it is not executing while row is inserted to the table.Please provide what changes i have to make if i want SOUNDEX ( 'some name' ) to executed while data insertion take place
Thanks in advance
View 1 Replies
View Related
Jun 3, 2008
I'm getting a strange error with my dataadapter. Here's my code: using (SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter("sprocCUSTOM", conn);
da.SelectCommand.Parameters.AddWithValue("@eventID", EventID);
conn.Open(); //open connection
DataSet ds = new DataSet();
da.Fill(ds);
gvAttendees.DataSource = ds;
gvAttendees.DataBind();
}This is the error I'm getting: Incorrect syntax near 'sprocCUSTOM'.But the error is highlighted at the line that reads "da.Fill(ds);"There's nothing wrong with the stored procedure either. It exists and works fine.
View 2 Replies
View Related
Feb 6, 2004
I am using SqlDataAdapter.Update(DataSet) to insert records into multiple tables in one call, but for some reason only first table was inserted.
Dim _cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim _sql As String = "Select a,b FROM Table1;Select d, c From Table2;"
Dim _da As New SqlDataAdapter(_sql, _cnn)
Dim _sqlCmdBdr As New SqlCommandBuilder(_da)
Dim _ds As New DataSet
_da.Fill(_ds)
Dim _newrow1 As DataRow = _ds.Tables(0).NewRow
_newrow1("a") = "NewA"
_newrow1("b") = "NewB"
_ds.Tables(0).Rows.Add(_newrow1)
Dim _newrow2 As DataRow = _ds.Tables(1).NewRow
_newrow2("d") = "NewD"
_newrow2("c") = "NewC"
_ds.Tables(1).Rows.Add(_newrow2)
_da.Update(_ds)
new record inserted only into Table1, no new rows in Table2
Please advise!
Thanks
View 1 Replies
View Related
Jul 6, 2007
Hi,
I am porting an ASP.NET/C# application from Microsoft Access to SQL Server 2005. In my code I have a method that uses the DataAdapter class to open a table, read some values out of it which are stored, open a second table, read some additional data from it storing the data in the same structure as used before, then updating the first table with the combined data.
When I execute the dataAdapter.Update() method I get the following error:
"Dynamic SQL generation for the Update Command is not supported against a Select Command that does not return any key column information."
I am uncertain what this error is about. This code worked fine against Microsoft Access so it must be something different in how SQL Server works.
Can anyone tell me what this error is complaining about?
Many thanks!
Bob
View 1 Replies
View Related
Mar 16, 2007
With MUCH help from this site and its users, I now know how to link a SqlCommand to a stored procedure:
What I don't know how to do, if it is possible, is to use a stored procedure with a SQLDataAdapter...
Here is the code as I am using, anyone have an idea on how to do this the right way?
' Define data objects
Dim conn As SqlConnection
Dim dataSet As New DataSet
Dim adapter As SqlDataAdapter
If ViewState("FooterDataSet") Is Nothing Then
' Read the connection string from Web.config
Dim connectionString As String = _
ConfigurationManager.ConnectionStrings( _
"Greensheet").ConnectionString
' Initialize connection
conn = New SqlConnection(connectionString)
' Create adapter
adapter = New SqlDataAdapter("SELECT * " & _
"FROM Table " & _
"WHERE (ID= " & intID & ")", conn)
' Fill the DataSet
adapter.Fill(dataSet, "Footer")
' Store the DataSet in view state
ViewState("FooterDataSet") = dataSet
Else
dataSet = ViewState("FooterDataSet")
End If
' Prepare the sort expression using the gridSortDirection and
' gridSortExpression properties
Dim sortExpression As String
If gridSortDirection = SortDirection.Ascending Then
sortExpression = gridSortExpression & " ASC"
Else
sortExpression = gridSortExpression & " DESC"
End If
' Sort the data
dataSet.Tables("Footer").DefaultView.Sort = sortExpression
' Bind the grid to the DataSet
footerGrid.DataSource = _
dataSet.Tables("Footer").DefaultView
footerGrid.DataBind()
View 1 Replies
View Related
Jul 13, 2007
Since my select command involves multiple tables, I manually write the insert command, proplating the SqlParameters as follows:
string insCmdText = "INSERT INTO itcac_alan.COURSE " + " ([COURSE_ID], [COURSE_NAME], [COURSE_NO], [BEGIN_DATE], [END_DATE], [CREATER]) " + " VALUES(@COURSE_ID, @COURSE_NAME, @COURSE_NO, @BEGIN_DATE, @END_DATE, @CREATER)";
SqlCommand insCmd = new SqlCommand(insCmdText, conn); insCmd.Parameters.Add("@COURSE_ID", SqlDbType.VarChar); insCmd.Parameters.Add("@COURSE_NAME", SqlDbType.VarChar); insCmd.Parameters.Add("@COURSE_NO", SqlDbType.VarChar); insCmd.Parameters.Add("@BEGIN_DATE", SqlDbType.DateTime); insCmd.Parameters.Add("@END_DATE", SqlDbType.DateTime); insCmd.Parameters.Add("@CREATER", SqlDbType.VarChar);
da.InsertCommand = insCmd; // da is a data adapter
However, as I use the preceding adapter to update the underlying database which in fact is based on a disconnected DataRow, an exception is thrown:
The parameterized query '(@COURSE_ID varchar(8000),@COURSE_NAME varchar(8000),@COURSE_NO ' expects the parameter '@COURSE_ID', which was not supplied.
The code I update the database is as follows:
DataRow newRow = sourceTable.NewRow(); //sourceTable is a DataTable whose data rows are populated with da (SqlDataAdpater) in the preceding code
// Populating the newRow
Hashtable newValues = new Hashtable();
.....................
foreach (DictionaryEntry entry in newValues) { // entry.Key is identical to a column name in the newRow newRow[(string)entry.Key] = (entry.Value == null ? DBNull.Value : entry.Value); }
sourceTable.Rows.Add(newRow);
adapter.SelectCommand.Connection.Open();
adapter.Update(sourceTable); // Exception is thrown here
adapter.SelectCommand.Connection.Close();
Please give me some directions to debug. It seems that the values of Columns withn DataRow cannot be fetched into the corresponding adapter.InsertCommand.Parameters by the adpater. (As I use SqlCommandBuilder to automatically generate insert command, the code above wroks rightly.)
Thanks a million.
Ricky.
View 2 Replies
View Related
Aug 14, 2007
I know there are loads of posts on this, but this just will not update. I have tried various forms of the code all to no avail. And now I'm ready to throw my PC out the window........
What am I doing wrong?
cheers Mike
Dim da As SqlDataAdapterDim dSetOrp As DataSet
oCn = New SqlConnection(db.m_ConnectionString)
oCn.Open()da = New SqlDataAdapter("Select * from contact WHERE conid = " & lngConId, oCn)
dSetOrp = New DataSet
da.Fill(dSetOrp, "locTable") ' Fill the DataSet.
dSetOrp.Tables(0).Rows(0)("ConSttDate") = dtEffDateDim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
myBuilder.GetUpdateCommand()
da.UpdateCommand = myBuilder.GetUpdateCommand()
lRows = da.Update(dSetOrp, "locTable")
View 2 Replies
View Related
Feb 7, 2008
MyCommand.Parameters.Add(new SqlParameter("@ConsultantName",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Calls",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@PPC",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Mth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@DaysInMonth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Coach",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Center",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ProductValue",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ObjectiveValue",SqlDbType.VarChar));
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.CommandTimeout = 360;
try
{
SqlDataAdapter saveCenterCoaches = new SqlDataAdapter(MyCommand);
saveCenterCoaches.InsertCommand = MyCommand;
DataSet updateSet = finalSet.GetChanges(DataRowState.Added);
saveCenterCoaches.Update(updateSet.Tables[0]);
}
catch(Exception ex)
{
throw ex;
}
Iam getting "Procedure expects parameter @ConsultantName, which was not supplied."
I have consultantname and other parameters built in my datatable.
Is it the correct way of doing?
Can someone help.It is urgent.
View 5 Replies
View Related
Feb 20, 2008
Hi, I was looking at how to update a database using SqlDataAdapter and I stumbled upon this code snippet from (http://www.java2s.com/Code/CSharp/Database-ADO.net/UpdatedatabaseusingtheSqlDataAdapter.htm):using System;
using System.Data;
using System.Data.SqlClient;
class Class1{
static void Main(string[] args){
SqlConnection thisConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Employee");
Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";
thisAdapter.Update(thisDataSet, "Employee");
Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);
}
}I was just wondering, without iteration, how did the line "thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";" managed to know which row it is to update? I tried it on my own application and it worked fine.Any help to assist me in understanding this would be appreciated. Thanks
View 3 Replies
View Related
Apr 28, 2006
Hello Dears;
I have an SqlDataAdapter which contains may queries. One query contians 3 query parameters as follows:
SELECT SUM(Amount) AS TotalFROM BoxesWHERE EntryDate BETWEEN @date1 AND @date2 AND Area=@Area
the query cannot be ran unless the queries parameters were provided. I have to access this query in code to add the parameters from controls. Is it possible to access it or use sqlDataSource instead?
Thanks alot
View 2 Replies
View Related
Sep 21, 2006
I'm trying to pass a querystring to an SqlDataAdapter object. To check if the query is a valid SELECT statement, I simply use a try-catch. But dispite the try-catch it still accepts valid INSERT statements. However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong? Here is my code:try
{
my_conn = conn_open();
da = new SqlDataAdapter(query, my_conn);
da.Fill(result.resultDataset);
my_conn.Dispose();
}
catch (Exception e)
{
result.errMsg = "Database Error: " + e.Message;
result.success = false;
} Kehil
View 1 Replies
View Related
Aug 14, 2007
How to configure sqldatadapter with query like
"select name ,id from tlb1 where id in (select id from tlb2 where dept=@dept)"
Is the nested subquery is not allowed while configuring sqldaadapter?
Swati
View 1 Replies
View Related