I am trying to add a DateTime? parameter to SqlCommand. It works when the variable has a value, but when its null, an exception gets thrown saying that parameter was not supplied.
What is causing this error?
Using SqlCommand, this is how I am updating a database table:
Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs) strSQL = "UPDATE Basket SET Quantity = Qty, Total = TotAmt WHERE BasketID = BID AND ProductID = PID" sqlCmd = New SqlCommand(strSQL, sqlConn) With sqlCmd .Parameters.Add("Qty", SqlDbType.Int).Value = CInt(iQty) .Parameters.Add("TotAmt", SqlDbType.Money).Value = CInt(iQty) * CType(ea.Item.FindControl("lblPrice"), Label).Text .Parameters.Add("BID", SqlDbType.VarChar, 50).Value = strBasketID .Parameters.Add("PID", SqlDbType.VarChar, 50).Value = CType(ea.Item.FindControl("lblID"), Label).Text End With sqlConn.Open() sqlCmd.ExecuteNonQuery() sqlConn.Close()End Sub
But the above code generates the following error pointing to the red colored line in the above code:
Invalid column name 'BID'.Invalid column name 'PID'.
BID & PID are not the column names in the actual database table but can't it be done in the way I have done above? In fact, Qty & TotAmt are not the column names in the actual database table as well; so why isn't the error pointing to Qty & TotAmt as they will be evaluated before BID & PID, if I am not mistaken?
Hi:
I am using sqlcommand.parameters.add() and up to 74 parameters for one sqlcommand, it gets the error "too many parameters for the sqlcommand", I wonder if someone know is there limitation of the paramters that I can pass to sqlcommand? If so, how many parameters I can pass to the sqlcommand at one time?
thank u in adv.
i am inserting into a table using the sqlCommand parameters property.for example i do :sqlCmd.Parameters.Add("@date_birth_hebrew", Request("date_birth_hebrew"))the date_birth_hebrew is not a must and has allow null value in the db.when the user submits the form and leaves the textbox of date_birth_hebrewnot field out and i try to add this value to parameters as the above code i recive :Prepared statement '(@date1 datetime,@first_name nvarchar(4000),@last_na' expects parameter @date_birth_hebrew, which was not supplied. as i understand beacuse the Request("date_birth_hebrew") is empty the sqlCommand.Parameters acts as no value entered.i hae solved this problem by on each value checking thatif Parameters Request("date_birth_hebrew")="" thensqlCmd.Parameters.Add("@date_birth_hebrew", "")but isnt there any another way?thnaks in advancepeleg
View 1 Replies View RelatedI was writing a simple sign up class the other day and coded
a Register method with a SqlCommand and Parameters like this:
SqlCommand sqlCmd =
new SqlCommand("Regester", sqlConn);
sqlCmd.CommandType =
CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 55);
sqlCmd.Parameters["@UserName"].Value = userName;
sqlCmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 55);
sqlCmd.Parameters["@Firstname"].Value = fName;
etc...
Seems pretty straight forward right? A senior coder writes his params like this
SqlParameter pUserName
= new SqlParameter("@UserName", SqlDbType.VarChar, 55);
pUserName .Value =
address2;
sqlCmd.Parameters.Add(pUserName );
SqlParameter pFName =
new SqlParameter("@FName", SqlDbType.VarChar, 55);
pFName .Value = city;
sqlCmd.Parameters.Add(pFName );
etc...
What is the benefit, if any, of creating a new instance of SqlParameter for
each value passed to the sp?
How could I easily test the code for speed and resource use besides tracing?
The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?
System::Boolean rs::sp(System::String ^ ssp){
System::String ^ k0s0; bool bOK;
System::Data::SqlClient::SqlParameter ^ parami0;
System::Data::SqlClient::SqlParameter ^ parami1;
System::Data::SqlClient::SqlParameter ^ parami2;
System::Data::SqlClient::SqlParameter ^ paramz0;
System::Data::SqlClient::SqlParameter ^ paramz1;
System::Int32 pz0=0;System::Int32 pz1=0;
k0s = ssp;
k0->CommandType=System::Data::CommandType::StoredProcedure;
k0->CommandText=k0s;
paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
paramz0->Direction=System::Data::ParameterDirection::ReturnValue;
paramz0->DbType=System::Data::DbType::Int32;
parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");
parami0->Direction=System::Data::ParameterDirection::Input;
parami1=k0->Parameters->AddWithValue("@SRCXV","UU");
parami1->Direction=System::Data::ParameterDirection::Input;
//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);
paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);
paramz1->Direction=System::Data::ParameterDirection::InputOutput;
paramz0->DbType=System::Data::DbType::Int32;
//k0->Parameters->GetParameter("@RCOUNT");
r0=k0->ExecuteReader();
//pz0=System::Convert::ToInt32(paramz0->SqlValue);
bOK=k0->Parameters->Contains("@RCOUNT");
//k0->Parameters->GetParameter("@RCOUNT");
pz0=System::Convert::ToInt32(paramz0->Value);
pz1=System::Convert::ToInt32(paramz1->Value);
ndx = -1;
while(r0->Read()){
if (ndx == -1){
ndx=0;
pai0ndx=0;
pad0ndx=0;
r0nf=r0->FieldCount::get();
for (iG1_20=0;iG1_20<r0nf;iG1_20++){
this->psf0[iG1_20]=this->r0->GetName(iG1_20);
this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);
this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
else {
pai0ndx=0;
pad0ndx=0;
for (iG1_20=0;iG1_20<r0nf;iG1_20++)
{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
ndx++;
}
r0nr=ndx;
r0->Close();
k0->Parameters->Remove(paramz0);
k0->Parameters->Remove(parami1);
k0->Parameters->Remove(parami0);
k0->Parameters->Remove(paramz1);
return true;
}
Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: {
Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
Line 26: mySqlDataAdapter.Fill(myDataSet);
Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System;
using System.Data;
using System.Data.SqlClient ;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
public partial class createGeoRSSFile : System.Web.UI.Page
{
protected void Page_Load(object sender, DataSet myDataSet, EventArgs e)
{
string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password";
string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";
using (SqlConnection mySqlConnection = new SqlConnection(connString))
{
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
mySqlDataAdapter.Fill(myDataSet);
return myDataSet;
}
// Create a new XmlTextWriter instance
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);
// Start writing!
writer.WriteStartDocument();
writer.WriteStartElement("item");
// Creating the <town> element
writer.WriteStartElement("town");
writer.WriteElementString("PostCode",myDataSet .Tables[1].Columns("PostCode"));
writer.WriteElementString("geo:lat",myDataSet.Tables[1].Columns("Latitude"));
writer.WriteElementString("geo:lon", myDataSet.Tables[1].Columns("Longitude"));
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();
writer.Close();
}
}What seems to be causing this error?Thanks.
Hi
Is it possible To pass an SQL command to a ASp.net web service as system.data.SQLclient.sqlcommand?
That means is ispossible to pass the actuall sql command instead of just the string?
If yes how can you do that??
Cheers
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
Hi,
I want to define Primary Key non-clustered on Nullable columns with datatype
as varchar ( 50 ) .
When i try to assign primary key , it gives me an error that
' Cannot define Primary Key contraint on nullable columns in table 'Table 1 '.
Could not create contraint . See previous errors . '
Any ideas how to resolve this issue ?
Thanks,
Anita.
Is there a way to set a column nullable in a script if all I have is the column name, something like:
exec('ALTER TABLE xyz ALTER COLUMN ' + @col_name + ' NULL')
The problem is that ALTER TABLE requires the type, so you have to say something like:
exec('ALTER TABLE xyz ALTER COLUMN ' + @col_name + ' decimal(12,4) NULL')
But the data type is not given to the script, so it is stuck.
I realize that syscolumns provides some relevant information, but it is not clear how to convert its information to a string like "decimal(12,4)". It seems, for example, that a decimal type has syscolumns.xtype = 106; however I can find no documentation on this, nor am I assured I can get all the right codes by trial and error. Does anyone know of a clear means of getting this information?
What happens if a column which has been defined so that it should not be Nullable is passed a value which is null?
i.e. IDNumber - Primary Key and Nullable = No.
Thanks
Hi all,
I am using a SQL statement with a JOINs to display a summary list of Work Orders with some information from the related Purchase Order. I don't require that a Work Order be attached to a Purchase Order, so the JOIN field, POLineItemID, may not be present. My current problem is that my summary page does not show Work Orders that have a NULL value for POLineItemID -- ie, the JOINing field becomes required.
How can I continue to use this JOIN structure but still return rows that don't have this JOIN field?
Thanks in advance for your help. Here is my SQL statement for this...
SELECT tblWorkOrder.WorkOrderNumber, tblWorkOrder.ActualCompletionDate, tblWorkOrder.EstimatedCompletionDate, tblWorkOrder.AssignedTo, tblWorkOrder.DelegatedTo, tblWorkOrder.Due, tblPurchaseOrders.PONumber, tblWorkOrder.POLineItemID, tblPOLineItems.POLineItemID AS Expr1, tblPOLineItems.PurchaseOrderID, tblPurchaseOrders.PurchaseOrderID AS Expr2, tblProducts.ProductID, tblProducts.ProductCategory, tblWorkOrder.ProductID AS Expr3, tblWorkOrder.WorkOrderStatusFROM (((tblWorkOrder INNER JOIN tblPOLineItems ON tblWorkOrder.POLineItemID = tblPOLineItems.POLineItemID) INNER JOIN tblPurchaseOrders ON tblPOLineItems.PurchaseOrderID = tblPurchaseOrders.PurchaseOrderID) INNER JOIN tblProducts ON tblWorkOrder.ProductID = tblProducts.ProductID)ORDER BY tblWorkOrder.Due
Is it bad design to allow nulls on a date field ? I can think of one case such as a sale of an item and populating a field for the date of purchase, only when the purchase took place (and null until then).
comments ?
Hi All!General statement: FK should not be nullabe to avoid orphans in DB.Real life:Business rule says that not every record will have a parent. It isimplemented as a child record has FK that is null.It works, and it is simpler.The design that satisfy business rule and FK not null can beimplemented but it will be more complicated.Example: There are clients. A client might belong to only one group.Case A.Group(GroupID PK, Name,Code…)Client(ClientID PK, Name, GroupID FK NULL)Case B(more cleaner)Group(GroupID PK, Name, GroupCode…)Client (ClientID PK, Name, ….)Subtype:GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)There is one more entity in Case B and it will require an additionaljoin in compare with caseAExample: Select all clients that belongs to any groupSummary Q: Is it worth to go with CaseB?Thank you in advance
View 20 Replies View RelatedHi,I've enherited a big mess, a SQL Server 2000 database withapproximately 50 user tables and 65+ GB data, no explicitrelationships among entities (RI constraints whatsover), attempt tocreate an ERD would more than likely kill the relatively complexedapp, the owner would want to drop out of window, so,I don't intend to do that, you get the picture, sorry no DDLs thistime, and many tables have many nullable columns, joins slows down thesystem quite a bit, what's my best bet to improve its performance?change most of these nullable columns into non-nullable with defaultvalue of something like ' ', any thoughts along this line would beappreciated.
View 6 Replies View RelatedHi All!I would like to have a composite PK on 3 columns, one of them is nullCREATE TABLE TableA (ColA int NOT NULL ,ColB int NOT NULL ,ColC char (3) NULL ,......)GOALTER TABLE TableA ADDCONSTRAINT TableA_PK PRIMARY KEY CLUSTERED(ColA,ColB,ColC)GOSQL Server does not allow having a composite PK with one nullable column:What is wrong to have values?1,100,NULL1,200,ABC1,200,ABD.....Code in C applies to Values in B and for some values in B the code does not exist.I can work out and define a special Code:NEV(not existing value), but in general I do not understand this restriction.Thanks
View 3 Replies View RelatedThis is what I would like to do...
1. Alter Table Status
Add ConsiderOpenFlag int null
2. UPDATE values...
3. Alter Table Status
Alter ConsiderOpenFlag int not null
Steps 1 and 2 are easy. What I cannot figure out is step three.
I don't want to have a default on that column, though I wouldn't mind adding it and then dropping it later if it would help.
Jonathan
Hi,
Could anyone tell me what governs whether a column is set as nullable or not nullable when creating a table using SELECT...INTO. It just seems to pick at random for me! I'm quite sure this is not the case. Is there a way to force a column to be non- nullable? I seem to be wasting a lot of time going through and altering the schema so I can use the columns in keys and indexes.
Dave
Hi,
I have a table name bla.
PKEY id, int, NOT NULL
group, int
name, string, NOT NULL
how do I compare them with int?
for example the following data.
1, NULL, 'freelance'
2, 1, 'group1'
3, 2, 'group2'
select * from bla where group<>1 <-- this fails?
What is the proper SQL Statement for this?
Regards,
Max
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
View 6 Replies View RelatedI have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are
1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'
greetings
Sir i want to check and use the select statement to select which field have a nullable=false in a table
greetings
i am use this query to select the primary field colums in a table
"select Column_Name as PrimaryKeycolumn
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'tbl_Activity'
and Constraint_Name like 'PK_%'"
but i want to select the fields which have a nullable=false
for that i want know the information schema for null
thank u
Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
View 5 Replies View RelatedHello,
I am trying to recreate a primary key that I dropped in a table....
I'm using a statement like
ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED
(
num,
store
)
But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..
Does anyone know how to get it to use that column to create a primary key after the initial table creation?
Here is the error i get
Cannot define PRIMARY KEY constraint on nullable column in table
Thanks a lot
I would like to dynamically copy a table in Transact SQL, like this:
SELECT * into NEW_TABLE from MY_TABLE where 1 = 2
This creates an empty NEW_TABLE as desired. However, NEW_TABLE retains the column nullability of MY_TABLE; I would like NEW_TABLE to have all its columns nullable.
I tried to write Transact SQL logic to update the isnullable column in syscolumns for NEW_TABLE, but was told that the entire SQL Server would have to be reconfigured to allow this.
Does anyone know of another way to do this?
I have a dimension with a date field (int key value actually) that is generally null that I'm using to link to a time dimension. Everything works great when I set Null Processing to UnknownMember in the Measure Group Bindings, but if I try to set it to Preserve or anything else, I get processing errors because it can't match the null attribute key to the time dimension. I think I understand this, but I'm concerned that I'm not doing it the best way. Also, why does this page say "UnknownMember causes a data integrity error at processing time" when that's the only setting that does not give me errors?
http://technet.microsoft.com/en-us/library/microsoft.analysisservices.nullprocessing.aspx
I have a case where a table has two candidate primary keys,but either (but not both) may be NULL. I don't want to storea copy of the concatenated ISNULL'ed fields as an additionalcolumn, though that would work if necessary. Instead, I triedthe following (this is a related simplified example, not myreal one):CREATE FUNCTION ApplyActionPK(@IP int = NULL,@DNS varchar(64) = NULL)RETURNS varchar(74) -- NOT NULLASBEGINdeclare @val varchar(74)set @val = str(ISNULL(@IP, 0), 10)set @val = @val + ISNULL(@DNS, '')return @val-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"-- ... and other things...ENDGOcreate table ApplyAction(-- An action applies to a computerAct varchar(16) NOT NULL,-- The action to applyIP int NULL,-- The computer IP address, orDNS varchar(64) NULL,-- The DNS name of the computerTarget as dbo.ApplyActionPK(ComputerID, DNS), -- PK value-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target))SQL Server always complains that the primary key constraint cannot becreated over a nullable field - even though in no case will the 'Target'field be NULL.Please don't explain that I should store an IP address as a string.Though that would suffice for this example, it doesn't solve myactual problem (where there are four nullable fields, two of whichare FKs into other tables).What's the reason for SQL Server deciding that the value is NULLable?What's the usual way of handling such alternate PKs?Clifford Heath.
View 7 Replies View RelatedI recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
I have a very large table that I need to partition. Ideally the table will write to three filegroups. I have defined the Partition function and scheme as follows.
CREATE PARTITION FUNCTION vm_Visits_PM(datetime)
AS RANGE RIGHT FOR VALUES ('2012-07-01', '2013-06-01')
CREATE PARTITION SCHEME vm_Visits_PS
AS PARTITION vm_Visits_PM TO (vm_Visits_Data_Archive2, vm_Visits_Data_Archive, vm_Visits_Data)
This should create three partitions of the vm_Visits table. I am having a few issues, the first has to do with adding a new clustered index Primary Key to the existing table. The main issue here is that the closed column is nullable (It is a datetime by the way). So running the following makes SQL Server upset:
ALTER TABLE dbo.vm_Visits
ADD CONSTRAINT [PK_vm_Visits] PRIMARY KEY CLUSTERED
(
VisitID ASC,
Closed
)
ON [vm_Visits_PS](Closed)
I need to define a primary key on the VisitId column, but I need to include the Closed column in order to partition on it.how I would move data between partitions on a monthly basis. Would I simply update the Partition function, or have to to some sort of merge, split, or switch function?
I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.
Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.
When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."
Am I on the right tracks here or do I need to adopt a different approach?
Code block to illustrate the problem :-
Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);
m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");
m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);
m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);
// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;
//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;
m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!
m_pRs->Update();
m_pRs->Close();
m_pConn->Close();
Hi guys. I'm having trouble declaring an sqlcommand. What I want to do is declare a global sqlcommand and I would want this sqlcommand to vary depending on the conditions on my page_load.
Here's the code....
Dim p_s_syounin2 As New SqlCommand
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2 = ("INSERT INTO (p_s_syounin2) SELECT syain_hnm FROM TR_syainID WHERE TR_syainID.syozokubu_id=20 AND TR_syainID.syozokuka_id=21 AND TR_syainID.kaikyuu_id=23, cnn") '''' THIS DOES NOT WORK!
End If
End Sub
p_s_syounin2 .ExecuteNonQuery()
What is the correct way of declaring p_s_syounin2?
Thanks.
Best Regards,
Audrey