I am currently writing my Bachelor Thesis. In this context I want to integrate data from my Excel spreadsheet into the SQL database. Behind it is a website with PHP access. In this way I use Xampp as server and use the MYSQL Workbench. I encountered the following errors with the connection of the database and the Execute non query as For each:
Connect Database: The format of the initialization string does not match the specification, which begins at index ‘58’.
Execute non query: The object reference was not set to an object instance.
I append the input values for the respective inputs in the appendix.
First you need to make sure you are able to connect into the database. Since you are having an error there you need to fix it before doing anything else (such as debugging the non query). For mysql i believe you must connect using ODBC which requires special software to be installed on the client side (make sure it is 32bit)
This error ocurs when you try to reference a variable/argument/etc, but you never initialized it. What variables are you using in your non-query? Check each variable one-by-one to make sure it is not null immediately before your non-query
Error ERROR Validation error When processing the expression ““INSERT INTO customer data(customer ID, password, salutation, first name, last name, date of birth, street, postal code, city, employment, email, phone) VALUES(”'+row(0).ToString+”‘,’“+row(1).ToString+”‘,’“row(2).ToString+”‘, ‘“+row(3). ToString+”’,’“+row(4).ToString+”‘,’“+row(5).ToString+”‘,’“+row(6).ToString+”‘,’“+row(7).ToString+”‘,’“+row(8).ToString+”‘,’“+row(9). ToString+”‘,’“+row(10).ToString+”‘,’“+row(11).ToString+”‘,’“+row(12).ToString+”')“” at least one compiler error occurred.
End of expression expected.
For sure will be some syntax error in all those quotes, but we cant really help if you dont format well in here, please use Control+Shift+C with the exact string you have for the command. So we can see it better.
Seems you dont have a plus sign before row(2) and you could replace all those values with something like this: String.Join(",", row.ItemArray.Select(Function(i) If(TypeOf i Is String, "'" + i + "'", i.ToString())))
will be like this: INSERT INTO customer data(customer ID, password, salutation, first name, last name, date of birth, street, postal code, city, employment, email, phone) VALUES("+String.Join(",", row.ItemArray.Select(Function(i) If(TypeOf i Is String, "'" + i + "'", i.ToString())))+")
Error ERROR Validation error When processing the expression ““INSERT INTO customer data(CustomerID, Password, Salutation, First Name, Last Name, Date of Birth, Street, Post Code, City, Employment, Email, Phone) VALUES(”+String.Join(”,“, row.ItemArray.Select(Function(i) If(TypeOf i Is String, “'” + i + “'”, i.ToString()))+”)" at least one compiler error has occurred.
“Option Strict On” does not allow operands of the type “Object” for the ±operator.
You don’t need to have anything in the output, it’s optional. If you hover over the AffectedRecords portion, it will tell you a brief description of what it does along with the variable type & direction. In this case, I believe it returns a collection of datarows, but I don’t remember for sure.
As for the ‘Insert’ activity, I personally avoid using it. As you have already seen, it is very easy to run into type issues unless the schema in your datatable matches the table in the database exactly. If you can get it to work it’s great - but that can take a lot of datatable manipulation and troubleshooting. It’s much easier in my opinion to use the ‘execute non query’ like you were using previously and just insert everything one row at a time
mkankatala
ashokkarale
Yoichi
sven.wullum1
chandreshsinh.jadeja
sharazkm32
sonaliaggarwal47
Ankit_Kumar2
SorenB
fanmixco