Help On Using SqlDataAdapter InsertCommand Targeting LocalSqlServer Connection

Apr 29, 2008

 I am trying to use a sqldataadapter to log a record to a table with a stored procedure call. I am getting the following error: "System.NullReferenceException: Object reference not set to an instance of an object.
at ContactUs.LogEmail() in c:inetpubwwwrootMDWelcomeContactUs.aspx.vb:line 55"Here is my code: Protected Sub LogEmail()
'Trace.IsEnabled = True
Dim strConnect As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString
Dim strCommandText As String = "dbo.EmailsLogged_Insert"
Dim objConnect As New SqlConnection(strConnect)
Try
objConnect.Open()
Dim objDataAdapter As New SqlDataAdapter(strCommandText, objConnect)
objDataAdapter.InsertCommand.CommandText = strCommandText'this is line 55 in my source where error is thrown
objDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure
With objDataAdapter.InsertCommand.Parameters
.Add("@emailaddress", SqlDbType.VarChar, 255).Value = UsersEmail.Text
.Add("@subject", SqlDbType.VarChar, 255).Value = Subject.Text
.Add("@company", SqlDbType.VarChar, 255).Value = Company.Text
.Add("@phone", SqlDbType.VarChar, 255).Value = Phone.Text
.Add("@emailbody", SqlDbType.VarChar, 255).Value = Body.Text

End With
objDataAdapter.InsertCommand.ExecuteNonQuery()
objConnect.Close() 'then close the connection
Catch SQLError As SqlException
Response.Write("<H1>SQL Exception:</H1>")
Response.Write("<h2>" & SQLError.Procedure & "</h2><br />")
Response.Write("<h2>" & SQLError.Message & "</h2><br />")
Dim lineNum As Integer
Dim errMsgArray() As String = Split(SQLError.StackTrace, vbCrLf, , Microsoft.VisualBasic.CompareMethod.Text)
Do While lineNum <= errMsgArray.Length - 1
If Trim$(errMsgArray(lineNum)) <> "" Then
Response.Write("<br>" & errMsgArray(lineNum))
End If
lineNum = lineNum + 1
Loop
Catch objError As Exception
Dim oMsg As String
'display error details
oMsg = "<b>* Error while accessing data</b>.<br />" _
& objError.Message & "<br />" & objError.Source

Trace.Write(oMsg)
Response.Write(oMsg)
Response.End()
End Try
End Sub
 I think the connection is OK (debugging shows that objConnect.State is open) but I am unclear why this is incorrect code here. 

View 2 Replies


ADVERTISEMENT

The Connection Name 'LocalSqlServer' Was Not Found In The Applications Configuration Or The Connection String Is Empty.

Sep 27, 2007

Hi,
I'm having a BIG problem, this is my 3rd day looking for answer !I'm trying to create a custom membership provider with MS SQL database 2005 but not the default ASPNETDB. I've changed the web.config to be as following:<connectionStrings><add name="sqlConn" connectionString="Data Source=.;Integrated Security=True;Initial Catalog=ASPNETDB;"providerName="System.Data.SqlClient" /></connectionStrings> <system.web><trace enabled="true" /><roleManager enabled="true" /><authentication mode="Forms" />
<membership defaultProvider="MySqlProvider"><providers><remove name="AspNetSqlProvider"/><add name="MySqlProvider" connectionStringName="SqlConn" type="System.Web.Security.SqlMembershipProvider" applicationName="/" /></providers></membership>
But when I try to login to the site using the login control the following error occurs:
Server Error in '/etest' Application.


Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.Source Error:



Line 149: <roleManager>
Line 150: <providers>
Line 151: <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Line 152: <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Line 153: </providers>Source File: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Configmachine.config    Line: 151


Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.213
 It is clear that this is a prblem with my Machine.Config file - since I've "worked!" on this file for a while. But when I've checked the Machine.config file I've found the LocalSqlServer connection it is talking about! .
I'm lost and I dunno what to do, can anyone help?
Here is the mahine.config in case if you need it:<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /></connectionStrings>
<system.data><DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /></DbProviderFactories>
</system.data><system.web>
<processModel autoConfig="true" /><httpHandlers />
<membership><providers>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" /></providers>
</membership><profile>
<providers><add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers></profile>
<roleManager><providers>
<add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /><add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers></roleManager>
</system.web>

View 8 Replies View Related

LocalSqlServer

Jun 18, 2007

I finally got my website to run on the local machine. I thought it was going to run on the server but I just get my login. After I enter my info and click "login" I get the following error. Do I need to enable the SQL Server Browser service?
Server Error in '/' Application.


Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.Source Error:



Line 138: <providers>
Line 139: <add name="AspNetSqlMembershipProvider"
Line 140: type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Line 141: connectionStringName="LocalSqlServer"
Line 142: enablePasswordRetrieval="false"Source File: C:WINNTMicrosoft.NETFrameworkv2.0.50727Configmachine.config    Line: 140


Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

View 9 Replies View Related

Abort Insertcommand

Apr 1, 2007

hey. I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible? 

View 1 Replies View Related

Two Inserts In One InsertCommand

Apr 11, 2007

I'm using a SQLDataSource and trying to do two inserts into two different tables with one InsertCommand, but it's not working. Here's the code I'm trying to use. Do you see anything wrong with the syntax? I keep getting an error that says error near ','  but I can't figure out why. Thanks
 
InsertCommand="INSERT INTO [OurProjects] ([Title], [Description], [Location], [Anchors], [Size], [Developer], [DesignBuilder], [Architect], [ImageName], [MapName], [ProjectTypeAbbrev], [Deleted]) VALUES (@Title, @Description, @Location, @Anchors, @Size, @Developer, @DesignBuilder, @Architect, @ImageName, @MapName, @ProjectTypeAbbrev, @Deleted),
INSERT INTO [OurProjectsImages] ([OurProjectsID], [ImageMonthName], [SwfName]) VALUES (@OurProjectsID, @ImageMonthName, @SwfName)"

View 2 Replies View Related

InsertCommand, Add Strings...

Nov 23, 2007

I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
 so like
InsertCommand="INSERT INTO customer_mod (phone)  Values (@Telephone_1)
 <asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
 I don't think that is gonna work, so can you please help me make that code work?

View 3 Replies View Related

How Can I Add 'InsertCommand' In SqlDataSource?

Dec 17, 2007

I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?

View 5 Replies View Related

Insertcommand @value Null...

Apr 29, 2008

My problem is when trying to insert it's coming back that "Column 'textmenu' cannot be null" who is right for that fieldCode below: <script language="vb" runat="server">    sub Page_Load(s as Object, e as EventArgs)        if ( Page.IsPostBack ) then            Exit sub        end if    end sub    private sub SubmitBtn_Click(s As Object, e As EventArgs)        SqlDataSource1.Insert()    end sub</script>    <asp:sqldatasource id="SqlDataSource1" runat="server"        ConnectionString="<%$ ConnectionStrings:test %>" ProviderName="<%$ ConnectionStrings:test.ProviderName %>"            insertcommand="INSERT INTO test (textmenu) VALUES (@somevalue)">        <insertparameters>            <asp:ControlParameter PropertyName="text" ControlID="CompanyNameBox" Name="somevalue" Type="String" />        </insertparameters>    </asp:sqldatasource>    <asp:textbox id="CompanyNameBox" runat="server" />    <asp:Button id="submitButton" Text="Envoyer" OnClick="SubmitBtn_Click" runat="server"/></asp:Content>  So I change the constrain to accept "null" value then I check in the table database and there is a record but no valueSo I change the sql command to "INSERT INTO test (textmenu) VALUES ('test')" and it's working I have a record and the value "test" in the tableI don't know what I'm doing wrong up here with the @value. Can anyone help? Thanks  

View 10 Replies View Related

A SqlDataSource Can Execute Two InsertCommand??

May 3, 2007

hi everyone
i have a SqlDataSourceand i want execute two T-Sql (two InsertCommand)but not successHow did i do this?
Thanks
1      Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted2            Dim SDSTemp As SqlDataSource = Nothing3            Try4                SDSTemp = New SqlDataSource(CnStr, "")5                SDSTemp.InsertCommandType = SqlDataSourceCommandType.Text6                SDSTemp.InsertCommand = "INSERT INTO Table1 (id,t1,t2) VALUES (@id,@t1,@t2)"7                Dim id As String = e.Command.Parameters("@id").Value.ToString8                SDSTemp.InsertParameters.Add("id", id)9                SDSTemp.InsertParameters.Add("t1", CType(FormView1.FindControl("TextBox1"), TextBox).Text)10               SDSTemp.InsertParameters.Add("t2", CType(FormView1.FindControl("TextBox2"), TextBox).Text)11               SDSTemp.Insert()12   13              SDSTemp.InsertCommand = "INSERT INTO Table2 (id) VALUES (@id)"14   15              SDSTemp.InsertParameters.Add("id", id)16   17              SDSTemp.Insert()18           Catch ex As Exception19               Message.Text = ex.Message.ToString20           End Try21       End Sub22    

View 1 Replies View Related

InsertCommand Using Data From A Second SqlDataSource - ASP.NET 2.0

Nov 29, 2007

I have a process that inserts a new record using the InsertCommand of a SqlDataSource.  As part of the process, I need to insert data the is available in a different SqlDataSource.  I was trying this with the Insert Parameter:

View 1 Replies View Related

NVarchar And SelectedValue And InsertCommand

Dec 19, 2007

Hi guys,I've got a  problem  inserting data into my db.I've created a NVARCHAR column and I'm using SelectedValue Parameters.I only have a problem in the INSERT mode.The UPDATE and DELETE are working fine.All the fields can be updated or deleted, but I can't insert new data inside my db.I've changed one column to NVARCHAR :   "reference"I use NVARCHAR because I have some Arabic Fields (unicode) into my db.But I've copy-pasted everything about the SqlDataSource.10x a lot anyway !  ASP.NET using MS Visual Studio 2005 :.... <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"        OldValuesParameterFormatString="original_{0}"        OnDeleted="SqlDataSource2_Deleted"        OnUpdated="SqlDataSource2_Updated"        OnInserted="SqlDataSource2_Inserted"        SelectCommand=            "SELECT [reference], [ddf], [description], [quantity], [pru], [supname], [catname]                FROM [Products]                WHERE ([reference] = @reference)"        InsertCommand="INSERT INTO [Products]                ([reference], [ddf], [description], [quantity], [pru], [supname], [catname])                VALUES (@reference, @ddf, @description, @quantity, @pru, @supname, @catname)"        UpdateCommand="UPDATE [Products]                SET [ddf] = @ddf,                    [description] = @description,                    [quantity] = @quantity,                    [pru] = @pru,                    [supname] = @supname,                    [catname] = @catname               WHERE [reference] = @original_reference"               DeleteCommand="DELETE FROM [Products] WHERE [reference] = @original_reference">                <SelectParameters>            <asp:ControlParameter ControlID="GridView1" Name="reference" PropertyName="SelectedValue" Type="String" />              </SelectParameters>                <InsertParameters>            <asp:Parameter Name="reference"                Type="String" />            <asp:Parameter Name="ddf"                Type="DateTime" />             <asp:Parameter Name="description"                Type="String" />            <asp:Parameter Name="quantity"                Type="String" />            <asp:Parameter Name="pru"                Type="Decimal" />              <asp:Parameter Name="supname"                Type="String" />              <asp:Parameter Name="catname"                Type="String" />        </InsertParameters>                <UpdateParameters>            <asp:Parameter Name="ddf"                Type="DateTime" />             <asp:Parameter Name="description"                Type="String" />            <asp:Parameter Name="quantity"                Type="String" />            <asp:Parameter Name="pru"                 Type="Decimal" />              <asp:Parameter Name="supname"                Type="String" />              <asp:Parameter Name="catname"                Type="String" />            <asp:Parameter Name="original_reference"                Type="String" />           </UpdateParameters>                <DeleteParameters>            <asp:Parameter Name="original_reference"                Type="String" />        </DeleteParameters>            </asp:SqlDataSource>SQL SERVER 2005 : Create Database FileUSE masterGOIF EXISTS(SELECT * FROM sysdatabases        WHERE name='Products')    DROP DATABASE ProductsGOCREATE DATABASE ProductsON (    NAME=Product,    FILENAME = 'C:WebAppApp_DataProducts.mdf',    SIZE=10 )GOUSE ProductsGOCREATE TABLE Categories (    catname         VARCHAR(25)     NOT NULL,    PRIMARY KEY (catname) )GOCREATE TABLE Suppliers (    supname        VARCHAR(25)    NOT NULL,    tel            VARCHAR(50)    ,    cell        VARCHAR(50)    ,    fax            VARCHAR(50)    ,    pob            VARCHAR(25)    ,    address        VARCHAR(300)    ,    nearby        VARCHAR(100)    ,    website        VARCHAR(100)    ,    email        VARCHAR(100)    ,    skypephone    VARCHAR(100)    ,    PRIMARY KEY (supname) )GOCREATE TABLE Products (    reference     NVARCHAR(25)     NOT NULL,    ddf         DATETIME        NOT NULL,    description VARCHAR(50)     NOT NULL,    quantity     VARCHAR(10)     NOT NULL,    pru         MONEY             NOT NULL,    supname        VARCHAR(25)    NOT NULL,    catname        VARCHAR(25)        NOT NULL,    pv            MONEY            NOT NULL,    PRIMARY KEY(reference),    FOREIGN KEY(catname) REFERENCES Categories(catname),    FOREIGN KEY(supname) REFERENCES Suppliers(supname) )GO 

View 3 Replies View Related

InsertCommand.ExecuteNonQuery() And Violation Of PrimaryKey

Jan 15, 2007

Hey,
I have a page that inserts into a customers table in the DataBase a new customer account using this function:
Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "")
Dim result As New DataSet
Dim tempid As Integer
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn"))
Dim Adcust As New SqlDataAdapter
Adcust.InsertCommand = New SqlCommand
Adcust.SelectCommand = New SqlCommand
Adcust.InsertCommand.Connection = conn
Adcust.SelectCommand.Connection = conn
sessionExists(email, sessionid, 1)
conn.Open()
If fname = "" Then
Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')"
Else
Dim strsql As String
strsql = "Insert Into neelwafu.customers"
strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values("
strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')"
Adcust.InsertCommand.CommandText = strsql
End If
Adcust.InsertCommand.ExecuteNonQuery()
Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers"
tempid = CInt(Adcust.SelectCommand.ExecuteScalar())
conn.Close()
Return tempid
End Function
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now, I am getting an error:
Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The customers table has as a primary key the 'email'.....
so plz can I know why am I getting this error ????
Thank you in advance
Hiba

View 4 Replies View Related

Sqldatasource InsertCommand && Oracle Sequence.

Jan 10, 2006

I cannot get an Oracle sequence to work in an Sqldatasource InsertCommand:I've tried the following:
InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)And: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (:Testing,:CUSTOMER_ID)<asp:Parameter DefaultValue="whsts_id.nextval" Name="testing" />AndInsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)
This is the classic error I get:ORA-01036: illegal variable name/numberI did review the asp.net forums before posting this.  As well, I've looked through the VWD 2005 documentation and cannot find the answer to this question.  Argg!  Any help is appreciated.   I've also worked to try and find out how to view the SQL that the Sqldatasource is generating, but I can't find the answer to this either.

View 8 Replies View Related

Change The InsertCommand Of A Datasource Before Insert

Jun 8, 2006

Hi,
I am trying to set the InsertCommand of a SqlDataSource prior to a new record being created in DetailsView. It has to be changed because certain fields are not being used in one scenerio and it is causing Null's to be written to the database.
Here is what I have tried:
I set the Insert command on page Load:
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource3.InsertCommand = "Insert into table (name,realName,type,extraValue) VALUES (@name,@realName,@type,@extraValue)";
}
But I don't want to insert the "extraValue if a certain condition is true so I want to change the insert command to SqlDataSource3.InsertCommand = "Insert into table (name,realName,type) VALUES (@name,@realName,@type)
I tried to do that in the following:
protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)
{
SqlDataSource3.InsertCommand = "Insert into table (name,realName,type,extraValue) VALUES (@name,@realName,@type,@extraValue)";
if (e.Exception != null)
{
ErrorMessageLabel.Text = "An error occured while entering this record. Please verify you have entered data in the correct format.";
e.ExceptionHandled = true;
Response.Write(e.Exception);
}
GridView1.DataBind();
GridView1.DataBind();
}
But it doesn't seem to change,
Any Help would be appreciated,
Doug

View 5 Replies View Related

Passing A String Into The InsertCommand Of SqlDataSource At The @color Character

Apr 27, 2007

Ok, so I'm a JSP guy and thing it should be easy to replace "@color" with t_color after I initialized it to red by         String t_color = "red";and then calling the insert         SqlDataSource1.Insert();here is insert command:             InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"  I've tried       InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "+ t_color+")"  Ive tried        InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "<%$ t_color %>" )"   Is there any easy way to do this? or Can I set it like @color = t_color?  Thanks in advance for ANY help JSP turning ASP (Maybe)Dan 

View 4 Replies View Related

Sqldataadapter

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

Inherits SqlDataAdapter

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

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 View Related

How Do I Reuse A SqlDataAdapter

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

Problems With SqlDataAdapter

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

SqlDataAdapter Update

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

SqlDataAdapter And DataSet

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

SqlDataAdapter.Update

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

SQLDataAdapter Error

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

Using Stored Procedures With A SQLDataAdapter

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

A Problem About Inserting A New Row With SqlDataAdapter

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

Updatecommand Not Working With SqlDataAdapter

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

Help With SqlDataAdapter.Update URGENT

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

Update Database Using The SqlDataAdapter

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

Accessing Queries In SqlDataAdapter

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

Checking The SELECT Statement For An SqlDataAdapter

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

Configuring SQLdataadapter With Nested Query.

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

Updating DataTable In DataBase Using SqlDataAdapter

Sep 4, 2007

Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value should be reflected to the already existed rows in database and only new rows should be  inserted.I am using a SQLDataAdapter having two sqlcommands , one for update with update procedure name and parameter mapping and another for insert with parameter mapping.But SqlDataAdapter is inserting fresh new rows all the time in the database table not updating the older one.Please help me in the matterThanks & RegardsVishal Sharma 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved